Home >> FAQs/Tutorials >> PHP Script Tutorials and Tips >> Index

PHP Script Tips - Working with MySQL Database

By: FYICenter.com

Part:   1   2  3  4  5  6  7 

A collection of 21 tips on working with MySQL database. Clear explanations and tutorial exercises are provided on connecting and selecting MySQL database, creating and dropping tables, inserting, updating, and deleting rows, selecting and looping through data rows, searching by key words, setting auto-increment IDs. Topics included in this collections:

  1. How To Install MySQL?
  2. How To Use MySQL Command Line Interface?
  3. What Do You Need to Connect PHP to MySQL?
  4. How To Connect to MySQL from a PHP Script?
  5. How To Create a Database?
  6. How To Select an Exiting Database?
  7. How To Run a SQL Statement?
  8. How To Create a Table?
  9. How To Get the Number of Rows Selected or Affected by a SQL Statement?
  10. How To Insert Data into a Table?
  11. How To Insert Rows Based on SELECT Statements?
  12. What Is a Result Set Object?
  13. How To Query Tables and Loop through the Returning Rows?
  14. How To Update an Existing Rows in a Table?
  15. How To Delete an Existing Rows in a Table?
  16. How To Quote Text Values in SQL Statements?
  17. How To Quote Date and Time Values in SQL Statements?
  18. How To Perform Key Word Search in Tables?
  19. How To Query Multiple Tables Jointly?
  20. How To Get the ID Column Auto-Incremented?
  21. How To Get the Last ID Assigned by MySQL?

How To Install MySQL?

MySQL is an open source database management system developed by MySQL AB, http://www.mysql.com. You can download a copy and install it on your local computer. Here is how you can do this:

  • Go to http://dev.mysql.com/downloads/mysql/5.0.html.
  • Select the "Windows" and "Without installer" version.
  • Unzip the downloaded file to "\mysql" directory, and double click on "\mysql\setup.exe" to start and finish the installation process.
  • Open a command window and run "\mysql\bin\mysqld" to start MySQL server

How To Use MySQL Command Line Interface?

MySQL server comes with a command line interface, which will allow you to operate with the server with SQL statements and other commands. To start the command line interface, you can run the \mysql\bin\mysql program. The tutorial exercise below shows you how to use the command line interface to create a table and insert a row to table:

>\mysql\bin\mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.

mysql> use test;
Database changed

mysql> CREATE TABLE fyi_links (url varchar(80));
Query OK, 0 rows affected (0.58 sec)

mysql> INSERT INTO fyi_links VALUES ('dev.fyicenter.com');
Query OK, 1 row affected (0.38 sec)

mysql> SELECT * FROM fyi_links;
+-------------------+
| url               |
+-------------------+
| dev.fyicenter.com |
+-------------------+
1 row in set (0.00 sec)

mysql> DROP TABLE fyi_links;
Query OK, 0 rows affected (0.34 sec)

mysql> quit;
Bye

What Do You Need to Connect PHP to MySQL?

If you want to access MySQL database server in your PHP script, you need to make sure that MySQL module is installed and turned on in your PHP engine. Check the PHP configuration file, php.ini, to make sure the extension=php_mysql.dll is not commented out.

The MySQL module offers a number of functions to allow you to work with MySQL server. Some commonly used MySQL functions are:

  • mysql_connect -- Open a connection to a MySQL Server
  • mysql_close -- Close MySQL connection
  • mysql_db_query -- Send a MySQL query
  • mysql_fetch_array -- Fetch a result row as an associative array, a numeric array, or both
  • mysql_free_result -- Free result memory
  • mysql_list_tables -- List tables in a MySQL database
  • mysql_list_fields -- List MySQL table fields

How To Connect to MySQL from a PHP Script?

If you want access the MySQL server, you must create a connection object first by calling the mysql_connect() function in the following format:

$con = mysql_connect($server, $username, $password);

If you are connecting to a local MySQL server, you don't need to specify username and password. If you are connecting to a MySQL server offered by your Web hosting company, they will provide you the server name, username, and password.

The following script shows you how to connect to a local MySQL server, obtained server information, and closed the connection:

<?php
  $con = mysql_connect('localhost');
  print(mysql_get_server_info($con)."\n");
  print(mysql_get_host_info($con)."\n");
  mysql_close($con); 
?>

If you run this script, you will get something like this:

5.0.2-alpha
localhost via TCP/IP

(Continued on next part...)

Part:   1   2  3  4  5  6  7 


Selected Developer Jobs:

More...