PHP’s Built-In MYSQL Functions

July 27th, 2012 by laeeq | 2 comments

The MySQL database server is one of the most popular open source databases in the world. Most of php application developed using mysql database. PHP has many built-in MySQL functions which works with MySQL to execute queries. these functions help you to manage your database and speed-up development.

Below is the list of most used PHP/MYSQL built in functions in php.

1. mysql_affected_rows

The mysql function mysql_affected_rows() will return the number of rows or records affected by any update, insert or delete query. By using this function, you can check the success of ny updating. for example if you want to check whether the password changed or not, you can simply use this function after update action. if it returns one then updation is successful otherwise not.

view plaincopy to clipboardprint?
  1. <?php
  2. /* mysql connection is required here to run this script */
  3. /* Update records */
  4. mysql_query(“UPDATE user SET password=’xyz’ WHERE id=’101′”);
  5. printf (“Updated records: %d\n”, mysql_affected_rows());
  7. /* Output; Updated Records: 1 */
  8. ?>

2. mysql_fetch_field

You can get all the details about the fields used in a table by mysql_fetch_field() function. This function returns an object and using that we can get required information about the field.

view plaincopy to clipboardprint?
  1. <?php
  2. /* mysql connection is required here to run this script */
  3. $result = mysql_query(“SELECT id,email FROM people WHERE id = ’42′”);
  4. echo mysql_num_fields($result);
  6. /* OUTPUT: returns 2 because id,email === two fields */
  7. ?>

3. mysql_fetch_lengths

This function mysql_fetch_lengths() returns an array and all elements of this array stores the length of data in each field of that row

view plaincopy to clipboardprint?
  1. <?php
  2. /* mysql connection is required here to run this script */
  3. $result = mysql_query(“SELECT id,email FROM people WHERE id = ’42′”);
  4. $lengths = mysql_fetch_lengths($result);
  5. print_r($row);
  6. print_r($lengths);
  7. ?>
  9. Output:
  11. Array
  12. (
  13. [id] => 42
  14. [email] => user@example.com
  15. )
  16. Array
  17. (
  18. [0] => 2
  19. [1] => 16
  20. )

4. mysql_fetch_row

The function mysql_fetch_row() returns a record by taking a result identifier. it returns the set of data as an array. To get the value from the array you have to use array offset staring from 0. Each call to this mysql_fetch_row function returns the next record.

view plaincopy to clipboardprint?
  1. <?php
  2. /* mysql connection is required here to run this script */
  3. $result = mysql_query(“SELECT id,email FROM people WHERE id = ’42′”);
  4. $row = mysql_fetch_row($result);
  6. echo $row[0]; // 42
  7. echo $row[1]; // the email value
  8. ?>

5. mysql_field_flags

This function is used to collect flags associated with a field in a table. It takes a result set and field offset value of the field and returns all the flags separated with single space. Here all returned flags are separated by a single space so we can use explode function of PHP to create an array of flags.

view plaincopy to clipboardprint?
  1. <?php
  2. /* mysql connection is required here to run this script */
  3. $result = mysql_query(“SELECT id,email FROM people WHERE id = ’42′”);
  4. $flags = mysql_field_flags($result, 0);
  5. echo $flags;
  6. print_r(explode(‘ ’, $flags));
  7. ?>
  8. Output:
  10. not_null primary_key auto_increment
  11. Array
  12. (
  13. [0] => not_null
  14. [1] => primary_key
  15. [2] => auto_increment
  16. )

6. mysql_field_type

The function mysql_field_type will return the type of field associated with the field. Different field types are varchar, char, int, blob, text, datetime etc… This command takes one result set as input along with a field identifier or an offset. It returns a string holding field type details.

view plaincopy to clipboardprint?
  1. <?php
  2. mysql_connect(“localhost”, “mysql_username”, “mysql_password”);
  3. mysql_select_db(“mysql”);
  4. $result = mysql_query(“SELECT * FROM people”);
  5. $fields = mysql_num_fields($result);
  6. $rows   = mysql_num_rows($result);
  7. $table  = mysql_field_table($result, 0);
  8. echo “Your ’” . $table . “‘ table has ” . $fields . “ fields and ” . $rows . “ record(s)\n”;
  9. echo “The table has the following fields:\n”;
  10. for ($i=0; $i < $fields; $i++) {
  11. $type  = mysql_field_type($result, $i);
  12. $name  = mysql_field_name($result, $i);
  13. $len   = mysql_field_len($result, $i);
  14. $flags = mysql_field_flags($result, $i);
  15. echo $type . “ ” . $name . “ ” . $len . “ ” . $flags . “\n”;
  16. }
  17. mysql_free_result($result);
  18. mysql_close();
  20. ?>

Output:

Your ‘people’ table has 4 fields and 1 record(s)
The table has the following fields:
string name 64 not_null primary_key binary
int age 11 not_null
string address 100 not_null
string country 100 not_null enum

7. mysql_pconnect

This function is used to make persistence connection to MySQL database from PHP script. Mysql_pconnect function keeps the connection live even the script execution is over. It does not close the connection like mysql_connect case where it closes the connection once the script execution is over. The other difference is it tries to find any existing connection exists and return a identifier if exists.

The main purpose of using mysql_pconnect function is to maintain a persistence connection to the mysql server. Function mysql_close() can’t close persisitence connection.

view plaincopy to clipboardprint?
  1. <?php
  2. mysql_pconnect (“$servername”,“$dbuser”,“$dbpassword”);
  3. ?>

8. mysql_connect

mysql_connect function is used to connects Mysql databas. We can check the success of the function by checking the result. We will get a true result in case connection is established. Based on this we can even print a message saying the details. This function takes three parameters, first one is hostname then username and then password. We can also give the port number along with the hostname also.

Here is the function to connect to mysql database

view plaincopy to clipboardprint?
  1. <?php
  2. mysql_connect (“$servername”,“$dbuser”,“$dbpassword”);
  3. ?>

9. mysql_create_db

You can create database in mysql server by using mysql_create_db function. If sufficient permission is there for the user then this function will create the database in MySQL Server.

Example:

view plaincopy to clipboardprint?
  1. <?php
  2. /* mysql connection details*/
  3. $servername=‘localhost’;
  4. $dbusername=‘username’;
  5. $dbpassword=‘password’;
  7. $link=mysql_connect (“$servername”,“$dbusername”,“$dbpassword”)
  8. or die ( “ Not able to connect to server ”);
  10. /* Create new database*/
  11. if (mysql_create_db (“new_db”)) {
  12. print (“Database created successfully
  13. “);
  14. } else {
  15. print (“Error creating database:
  17. “. mysql_error ());
  18. }
  19. ?>

10. mysql_db_name

By using this function, you can get list all the databse hosted on the mysql server. Below is the example code to get list of all mysql database.

view plaincopy to clipboardprint?
  1. <?php
  2. /*  mysql connection is required here to run this script */
  3. $list_of_dbs = mysql_list_dbs();
  4. $i = 0;
  5. $total = mysql_num_rows($list_of_dbs);
  6. while ($i < $total) {
  7. echo mysql_db_name($list_of_dbs, $i) . ”
  8. “;
  9. $i++;}
  10. ?>

11. mysql_num_rows

The function mysql_num_rows return total number of rows in a table. This function is to be used along with mysql select query. This function is widely used in different php scripts.

Example :

view plaincopy to clipboardprint?
  1. <?php
  2. /* mysql connection is required here to run this script */
  3. $query = mysql_query(“SELECT * FROM people”);
  4. $number=mysql_num_rows($query);
  5. echo “Total records in people table= ”. $number;
  6. ?>

12. mysql_query

This function is used to execute a sql query to mysql database. You can write any sql query like insert , select , update , delete etc and pass the query to mysql database. The result of the execution of the query will be known by monitoring the status. We will get true or false based on the success or failure status of the query.

Example :

view plaincopy to clipboardprint?
  1. <?php
  2. /* mysql connection is required here to run this script */
  3. $query=“select name, address, country from people”;
  4. $status=mysql_query($query) or die( “sqlquery failed”);
  5. if($status){echo “sqlquery is successful”;}
  6. else {echo “sqlquery failed”;}
  7. ?>

13. mysql_errno

This function is used to display error number after execution of a sql query.

Example :

view plaincopy to clipboardprint?
  1. <?php
  2. /* mysql connection is required here to run this script */
  3. $querry = mysql_query(“SELECT test_field FROM people”);
  4. echo “Error Number= ”.mysql_errno(); // will print 1054
  5. ?>

In the above table people test_field is a non existence field or column. That’s why it will show errorno.

14. mysql_error

This function is used to display error message after execution of a sql query.

Example :

view plaincopy to clipboardprint?
  1. <?php
  2. /* mysql connection is required here to run this script */
  3. $querry = mysql_query(“SELECT test_field FROM people”);
  4. echo “Error message = ”.mysql_error();
  5. // Displaye error message after executing a query
  6. ?>

15. mysql_close

The function mysql_close() is used to close mysql connection. It can takes a optional parameter as link and closes it. If no link identifier is specified then last opened connection is closed. It is not necessary to use mysql_close() function as all connections are closed at the end of the script execution.

Example :

view plaincopy to clipboardprint?
  1. <?php
  2. /* mysql connection is required here to run this script */
  3. mysql_close()
  4. ?>

Note: mysql_close() function will not close persistent connection created by using mysql_pconnect()

16. mysql_fetch_array

This function fetch a result row as an associative array, a numeric array, or both

Example Code:

view plaincopy to clipboardprint?
  1. <?php
  2. /* mysql connection is required here to run this script */
  3. $result = mysql_query(“SELECT id, name FROM people”);
  4. while ($row = mysql_fetch_array($result)) {
  5. echo $row[0];
  6. echo $row[1];
  7. }
  8. ?>

17. mysql_result

The function mysql_result() is used to get large result data. You can pass numeric offset for the field argument to get records much quicker.

Example Code:

view plaincopy to clipboardprint?
  1. <?php
  2. /* mysql connection is required here to run this script */
  3. $result = mysql_query(‘SELECT name FROM work.employee’);
  4. echo mysql_result($result, 2); // outputs third employee’s name
  5. ?>

18. mysql_fetch_assoc

This function retuns an associative array that corresponds to the fetched row.

Example Code:

view plaincopy to clipboardprint?
  1. <?php
  2. /* mysql connection is required here to run this script */
  3. $result = mysql_query(‘SELECT name FROM work.employee’);
  4. $my_assoc_array=mysql_fetch_assoc($result);
  5. print_r($my_assoc_array); // print entire associative array
  6. ?>

You can subscribe to PHPZAG.COM posts by Email

 

Related Topics:

 

 

  1. joe news
    July 28th, 2012 at 14:35 | #1
    Reply | Quote

    It’s a nice post.

  2. Wordpress Development
    September 14th, 2012 at 12:43 | #2
    Reply | Quote

    little long but really happy to see it coz its very helpful for me and also for other.

  1. No trackbacks yet.