8 - PHP:MySQL - Now with PHP
We're familiar with using MySQL at the command line. Now we'll see how to have PHP send commands to the server for us.
Here's what the result of this code will look like.
It can look like a lot of code, but everything breaks down into basically simple, sensible blocks of code. To use the code shown on the following pages, select it all as denoted, copy-and-paste it into Notepad, then copy it from Notepad and back into your HTML editor, or simply work on it from Notepad. That will remove all the text formatting and leave you with pure code.
copy from just below this line....
<?
///This is the code to get the information from the table
so it can be displayed
// You need to do all your database connection work before
the html starts
// So first, we define the connection string
//with this syntax: mysql_connect($host, $username, $password)
//This gets us talking to mysql on the server
$conn = mysql_connect("localhost", "username","password")
or die("Could not connect to database");
//Here we select the database we want to use with $theConnectionString
mysql_select_db("databaseName") or die("Could not select
database");
//Define the SQL statement that gets sent to the database
$query = "SELECT * FROM userInfo";
//Put the results of your SQL statement into $aVariable,
in this case $result.
//Note that the variables used in this statement, $query and $conn, are what
//we defined in the previous few steps.
$result = mysql_query($query, $conn);
//You can get the number of returned records this way
$num_rows = mysql_num_rows($result);
//Planning ahead here. When I tell how many record(s)
were retrieved, I want proper grammar.
if ($num_rows > 1 || $num_rows == 0)
{
$recordWord = "records";
}
else
{
$recordWord = "record";
}
//Close your connection to the database once you've gotten
the information you need
mysql_close($conn);
// This is the code to get the information from the table
so it can be displayed
?>
<head>
<title>PHP/MySQL</title>
</head>
<body>
<p>Here's some static text, just so we know the page is working.
</p>
<p>
<?
// This shows the records we retrieved in the PHP code
in the head of the page.
//If the $result variable has any contents
if ($result){
//Notice the dot used for concatenation, and how you
can mix
//variables and literal text strings
echo " You retrieved " . $num_rows . " " . $recordWord .
".";
// Now we let PHP start writing the html for our table
echo "<table border=1>";
// $row gets an array stuffed into it. Each record in
our recordset is a row in the array.
// The while statement iterates its way through $row. Each
time
// through, if there's another element in the array,
// it executes the code in the function block and uses the values in row[iteration
number]
// of the array.
while ($row = mysql_fetch_array($result)) {
// The elements of the array are automatically named for
the column they come from in the table
$firstName = $row['firstName'];
$lastName = $row['lastName'];
$id = $row['id'];
// Cycle color for each iteration.
//Note the shorthand if-then notation
$rowColor == "#ffdddd" ? $rowColor = "#ddffdd": $rowColor
= "#ffdddd";
// Write the html to put the data of each record in its
own table row.
echo "<tr bgcolor = '" . $rowColor . "'>\n<td width='30%'>";
// Write the variables into the table cell
echo $id . " " . $firstName . " " . $lastName ;
// Close the html table row
echo "</tr>\n</td width='30%'>";
}
//If we're done with the while loop, we're done with the
table, so write the html to close the table.
echo "</table>";
}
else
{
//If no data was found in the database, give the user
feedback explaining the situation.
echo " sorry no data found ";
}
?>
</p>
</body>
</html>
... to just above this line.