Often used PHP Mysql functions

Before you start this lesson, please check if you have installed MySQL on your PC. If not, install your MySQL database server now.

The most often used database by PHP is MySQL. The following functions are most often used MySQL functions:

The above functions could be grouped into the following categories:

1. deal with database connection (mysql_connect, mysql_select_db, mysql_close)
2. Query database and get result (mysql_query)
3. dealing with result(mysql_fetch_array, mysql_fetch_row, mysql_num_rows, mysql_num_fields,mysql_result,mysql_free_result)

These 3 categories are the basic functions any talking to database language should have.

The following example shows basic usage of the above functions. It could fit into your PHP file and with a little bit change.

<?php
// Connecting, selecting database
$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')
or die('Could not connect: ' . mysql_error());
echo 'Connected successfully';
//select which database you want to use
mysql_select_db('my_database') or die('Could not select database');
// Performing SQL query
$query = 'SELECT * FROM my_table';
//get query result
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
// Printing results in HTML
//get query result into array $line
echo "<table>\n";
while ($line = mysql_fetch_array($result,MYSQL_ASSOC)) {
echo "\t<tr>\n";
foreach ($line as $col_value) {
echo "\t\t<td>$col_value</td>\n";
}
echo "\t</tr>\n";
}
echo "</table>\n";
// Free resultset
mysql_free_result($result);
//close databae connection nnection
mysql_close($link);
?>

Another way to get result set is using while loop and mysql_result function:

<?php
$row=0;
//how many rows in result;
$numrows=mysql_num_rows($result);
//how many columns in result
$numcols=mysql_num_fields($result);
//write result into html table;
echo "<table border=1>";
while($row<$numrows)
{
$col=0;
while($col<$numcols)
{
//mysql_field_name($result,$col) get field name
$name=mysql_result($result,$row,mysql_field_name($result,$col));
echo "<tr><td>$name</td></tr>";
$col++;
}
$row++;
}
?>