index act articles resource 43 ext iframe

PHP mySQL Code Samples

This article displays code examples of how to get PHP to interact with the mySQL database platform.

PHP mySQL Code Samples
1452 Views
You should already have knowledge of PHP before reading this. This article contains code examples and does not explain functions, only the ideal procedure and code to use.




Firstly, you should connect your PHP script to the database. In the code below, you will note a configuration array ($config) and the tables of the database are defined as constants. This is just a systematic approach. To start off:

Code: [hide]
  1. /*
     
  2. * Typical mySQL Connection Script
     
  3. */
     
  4. $config = array(
     
  5.    ‘database_host’ => ‘host‘,
     
  6.    ‘database_name’ => ‘dbname‘,
     
  7.    ‘database_user’ => ‘user‘,
     
  8.    ‘database_pass’ => ‘pass‘,
     
  9. );
     
  10. define(‘TABLE_ONE’, ‘table_one_name’);
     
  11. define(‘TABLE_TWO’, ‘table_two_name’);
     

  12.  
  13. /*
     
  14. * Connect To mySQL
     
  15. */
     
  16. $db_link = mysql_connect($config[‘database_host’], $config[‘database_user’], $config[‘database_pass’]);
     
  17. if (!$db_link)
     
  18. {
     
  19.    die(‘Database Error: ‘ . mysql_error());
     
  20. } 

Next we should select the actuall database we want to work with:

Code: [hide]
  1. /*
     
  2. * Select mySQL Database
     
  3. */
     
  4. $db_selected = mysql_select_db($config[‘database_name’], $db_link);
     
  5. if (!$db_selected)
     
  6. {
     
  7.    die (‘Database Error: ‘ . mysql_error());
     
  8. } 

Once we have select the database, we can run a query. An example query is shown below – note the table constant defined earlier.

Code: [hide]
  1. /*
     
  2. * Run SQL Query
     
  3. */
     
  4. $sql = ‘SELECT * FROM ‘ . TABLE_ONE;
     
  5. $result = mysql_query($sql);
     
  6. if (!$result)
     
  7. {
     
  8.    die(‘Database Error: ‘ . mysql_error());
     
  9. }
     
  10. while ($row = mysql_fetch_assoc($result))
     
  11. {
     
  12.    echo ‘

    ‘ . $row[‘field_one’] . ‘
    ‘ . $row[‘field_two’] . ‘

    ‘;
     

  13. } 

Its now a good idea to clear the result memory, we do this by:

Code: [hide]
  1. /*
     
  2. * Free SQL Result Memory
     
  3. */
     
  4. mysql_free_result($result); 

The above code should be executed after every query for performance reasons.




The code example below is how to select a record in the database based upon the URL. Note the security functions and checking procedures used.

Code: [hide]
  1. /*
     
  2. * Select A Record Based Upon URL
     
  3. */
     
  4. $url_field = (!empty($_GET[‘field_two’])) ? mysql_real_escape_string($_GET[‘field_two’]) : ”;
     
  5. $url_row  = array();
     
  6. if (empty($url_field))
     
  7. {
     
  8.    die(‘Parameter Error: No field value specified’);
     
  9. }
     
  10. $sql = ‘SELECT * FROM ‘ . TABLE_TWO . ‘ WHERE field_two = “‘ . $url_field . ‘”‘;
     
  11. $result = mysql_query($sql);
     
  12. if (!$result)
     
  13. {
     
  14.    die(‘Database Error: ‘ . mysql_error());
     
  15. }
     
  16. if (mysql_num_rows($result) == 0)
     
  17. {
     
  18.    die(‘Nothing Found: no records were found’);
     
  19. }
     
  20. while ($row = mysql_fetch_assoc($result))
     
  21. {
     
  22.    $url_row = $row;
     
  23. }
     
  24. echo ‘

    ‘ . $url_row[‘field_two’] . ‘
    ‘ . $url_row[‘field_one’] . ‘

    ‘;
     

  25. mysql_free_result($result); 

Lastly, to close your script after you have finished using the database simply use:

Code: [hide]
  1. mysql_close($db_link); 

PHP mySQL Code Samples