How to connect to mysql database using php
Jul 19, 2012, by admin
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 to connect to mysql database using php
Connect to a Server
Create a new PHP file on your web server, and open it in your favorite text editor.
Start your file by typing the open and close PHP tags with some space in between to work with.
<?php
?>
Type 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.
Select a Database
Type 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 exact 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
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());
Type this on a new line.
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).
Type 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.
