PHP Tutorial : PHP & MySQL

PHP Tutorial : PHP & MySQL

In this PHP Tutorial you will learn about PHP and MySQL - Connecting to MySQL, Closing a connection, Selecting a database, Executing a query, Inserting data and Retrieving data.

Connecting to MySQL:

To connect to MySQL database server, use the function mysql_connect().

mysql_connect() takes three string arguments, the hostname, the username, and the password.

mysql_connect() returns an integer represents the connection index if the connection is successful, or returns false if the connection fails.

Example:

<?php
$con = mysql_connect(“localhost”, “john”, “smith”);
echo $con;
?>

Closing a connection:

To close a connection to MySQL, use the function mysql_close().
mysql_close() takes one argument, which is the connection index.

Example:

<?php
$con = mysql_connect(“localhost”, “john”, “smith”);
echo $con;
mysql_close($con);
?>

Selecting a database:

Now you are connected to the MySQL server, to start using queries, you have first to select a database, because MySQL server can contain many databases.

To select a database, use the function mysql_select_db().

mysql_select_db() takes two arguments, a string represents the database name, and the connection index.

Example:

<?php
$con = mysql_connect(“localhost”, “john”, “smith”);
mysql_select_db(“MyDB”, $con);
mysql_close($con);
?>

Executing a query:

To execute a SQL query, use the function mysql_query().
mysql_query() takes two arguments, a string represents the SQL statement, and the connection index.
mysql_query() returns a true or false.

Example:

<?php
$con = mysql_connect(“localhost”, “john”, “smith”);
mysql_select_db(“MyDB”, $con);
$query = “SELECT * FROM Products”;
$result = mysql_query($query, $con);
mysql_close($con);
?>

Inserting data:

Example:

<?php
$con = mysql_connect(“localhost”, “john”, “smith”);
mysql_select_db(“MyDB”, $con);
$query = “INSERT INTO Names VALUES(‘John’, ‘Smith’)”;
if(mysql_query($query, $con)) {
echo “Record added”;
}
else {
echo “Something went wrong”;
}
mysql_close($con);
?>

Retrieving data:

Data is retrieved using the SELECT statement, the statement returns data rows.
To get the number of the retrieved rows, use the function mysql_num_rows().
mysql_num_rows() takes one argument, which is the query result index.
mysql_num_rows() returns an integer, which is the number of retrieved rows.

Example:

<?php
$con = mysql_connect(“localhost”, “john”, “smith”);
mysql_select_db(“MyDB”, $con);
$query = “SELECT * FROM Products”;
$result = mysql_query($query, $con);
$num = $mysql_num_rows($result);
echo “The number of rows = $num”;
mysql_close($con);
?>

You can use the number of rows to make a loop and display all the result rows.
But a better solution is to make a while loop using the function mysql_fetch_array().
mysql_fetch_array() takes one argument, which is the result index.
mysql_fetch_array() returns an array of the next fetched row, or false if no more rows.

Example:

<?php
$con = mysql_connect(“localhost”, “john”, “smith”);
mysql_select_db(“MyDB”, $con);
$query = “SELECT first_name, last_name FROM Products”;
$result = mysql_query($query, $con);
while($dataArray = mysql_fetch_array($result) {
$firstName = $dataArray[‘first_name’];
$lastName = $dataArray[‘last_name’];
echo “$firstName $lastName<br />\n”;
}
mysql_close($con);
?>