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);
?>
