If you already know some of the basics of writing PHP scripts, you may be ready to learn about a set of built-in PHP functions that allow you to connect to and manipulate a MySQL database. If you do not already have a MySQL server (Most web hosts with PHP do), I suggest either downloading and installing WampServer on your web server, or following this tutorial. It is also recommended that you understand basic concepts of SQL.
In this tutorial we will learn how to connect to a MySQL database, select a database, query a table, and "fetch" and array from that data.
Steps
Connect to a Server
- 1Create a new PHP file on your web server, and open it in your favorite text editor.
- 2Start your file by typing the open and close PHP tags with some space in between to work with.
<?php ?>
- 3Type this on a new line.
$con = mysql_connect("localhost","root","");- This line of code creates the foundation for the communication with the MySQL server. The function "mysql_connect" takes a minimum of 3 string arguments. The first is the IP address or domain name of your server; you should change this from localhost to the address of your MySQL server. The second argument is the MySQL user that we will authenticate, and the third argument is the password for our MySQL user (in this case I left the password blank).
- The output of "mysql_connect" is a resource datatype, and in the code above we assigned it to a variable ($con) so we could use it later. For more information on mysql_connect(), visit the PHP documentation.
- This line of code creates the foundation for the communication with the MySQL server. The function "mysql_connect" takes a minimum of 3 string arguments. The first is the IP address or domain name of your server; you should change this from localhost to the address of your MySQL server. The second argument is the MySQL user that we will authenticate, and the third argument is the password for our MySQL user (in this case I left the password blank).
Select a Database
- 1Type this on a new line.
mysql_select_db("test", $con) or die(mysql_error());- This line is fairly simple. Before your can run any queries on a specific database, you must select which database. In PHP to do this we use the "mysql_select_db" function, which requires 1 argument. The first argument in the code is required, it is the name of the database to connect to. The second argument I used is not required but good practice; it defines which server connection to use to select the database.
- This line is fairly simple. Before your can run any queries on a specific database, you must select which database. In PHP to do this we use the "mysql_select_db" function, which requires 1 argument. The first argument in the code is required, it is the name of the database to connect to. The second argument I used is not required but good practice; it defines which server connection to use to select the database.
Query a Table
- 1Type this on a new line.
mysql_query("CREATE TABLE `php tutorial` (`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR( 255 ) NOT NULL) ENGINE = MYISAM", $con) or die(mysql_error());- The function mysql_query works in two ways. "For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error.
- For other type of SQL statements, INSERT, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error." (PHP documentation).
- These lines of code do two things. First they call "mysql_query," which will return true or false based on the success of the query. If the output of mysql_query is false the script will die and run the "mysql_error" function (which simply returns the previous MySQL error).
- The mysql_query function only requires one argument, but like the mysql_select_db function, it is good practice to include a second.
- The first argument is a string: a single MySQL query (multiple queries not allowed). The query above creates a new table called "php tutorial".
- The second argument is the connection resource we are using (in our case $con).
- The first argument is a string: a single MySQL query (multiple queries not allowed). The query above creates a new table called "php tutorial".
- The function mysql_query works in two ways. "For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error.
- 2Type this on a new line.
mysql_query("INSERT INTO `php tutorial` (`name`) VALUES ('chuck')", $con) or die(mysql_error()); $result = mysql_query("SELECT * FROM `php tutorial`", $con); if(!$result) { die(mysql_error()); } else { while($row = mysql_fetch_array($result)) { echo row['name']; } }
- The first line of code is similar to the last step's code. It is in there to give of some data to select. Below, see we are setting the output of mysql_query to a variable called $result.
- Also notice how the code does not use "or die(" instead it uses the if control statement. This can save you grief when you start getting into error handling beyond simply killing the script. You don't have to worry about it too much now, and you can use "or die" on MySQL queryies that return a result set if you want
- After the else statement we have a while statement. This part of the code can be confusing so bear with me. mysql_fetch_array will return an array of data containing a single row of the result, labeled by column; however, the next time mysql_fetch_array is run it will return an array of the next row in the result set.
- The while statement will keep iterating through all the rows (assigning them as arrays to $row), until mysql_fetch_array reaches the end of the result set, then it will return false and the while statement will close. Confusing yes. But this method works well.
- The while statement will keep iterating through all the rows (assigning them as arrays to $row), until mysql_fetch_array reaches the end of the result set, then it will return false and the while statement will close. Confusing yes. But this method works well.
- The first line of code is similar to the last step's code. It is in there to give of some data to select. Below, see we are setting the output of mysql_query to a variable called $result.
- 3Take some time to play around with these concepts. These functions allow you to do so much more with PHP. I would suggest taking a look at this wikiHow page: How to Create a Basic Login Script in PHP. I would also suggest making your own project like a simple browser turn based rpg to get the hang of working with a database.
Source : www.wikihow.com
