Transcript Having PHP get data from a MySQL database

PHP getting data from a
MySQL database
Replacing XML as data source with
MySQL
• Previously we obtained the data about the
training session from an XML file. Now we
would like to get the information out of the
MySQL database.
• PHP must connect to the database,
perform a SQL select statement of the
appropriate table or tables and then parse
the query result and display the
information.
Back to the sign-up-for-training form
Code to load database information into
select element (drop-down list)
Information needed to connect to
database
$host = "localhost";
//if PHP server and mySQL server same
$user="blum2";
$password = “tb4db";
$database = "blum2";
Even though the client will never see this code, it is standard procedure to
place username and password data eventually gets placed in another file.
Code to connect to MySQL
$dbc = mysql_connect($host,$user,$password);
if($dbc==false)
{
die("Problem connecting to MySQL.");
//The die() function prints a message
//and exits the current script.
}
Code to choose a database
$db = mysql_select_db($database, $dbc);
if($db==false)
{
die("problem with database.");
}
Code to ask for data from Session table
$sql = "SELECT SessionID, SubjectID,
LocationID, Date, Time FROM Session";
//$sql = "SELECT * FROM Session";
$result = mysql_query($sql,$dbc);
The $sql variable above will hold a string corresponding to a SQL query
requesting data from the Session table.
The last statement performs the SQL query and places the results in a variable
result.
Displaying the results in the drop-down
list
while($row = mysql_fetch_array($result, MYSQL_NUM))
{
print "<option value =\"$row[0]\">$row[1]: $row[3]
$row[4]</option>";
}
This code loops through the records resulting from the query.
For each record, row becomes an array of the fields of that
record. The order of fields in row matches the order in the
SQL statement.
Result in browser
We need to re-insert our code that only displayed present and future training
sessions. It would be preferable if the
Code to display only future training
while($row = mysql_fetch_array($result, MYSQL_NUM))
{
list($year, $month, $day) = split('[/.-]', $row[3]);
//note info from database arranges it year, month, day
//month already a number
$trainingTime = mktime(0,0,0,$month, $day+1, $year);
$now = time();
if($trainingTime >= $now)
{
print "<option value =\"$row[0]\">$row[1]: $row[3] $row[4]</option>";
}
}
The date format has changed –
$year comes first and $month is a number
list($year, $month, $day) = split('[/.-]', $row[3]);
//note info from database arranges it year, month, day
//month already a number
//same as before
$trainingTime = mktime(0,0,0,$month, $day+1, $year);
$now = time();
if($trainingTime >= $now)
Result in browser
Return to phpmyadmin, click on your
database and the Query
Choose the tables to be used in the
query, then start choosing the fields
Choose SubjectTitle from SubjectMatter
instead of SubjectID from Session
Also choose to add additional fields to the query.
Make a “natural join” between the
Session and SubjectMatter tables
Note that we have chosen to display all of the fields except the last, and on the
last field SubjectMatter.SubjectID we impose the constraint that its value equal
that from Session.SubjectID -- that is we want the records from the two tables to
have the proper relationship.
The character ` seen above is above the Tab on the upper left of the keyboard.
Update the query, then Submit the
query
Results of Query
Query results
Be careful to use the phpmyadmin’s navigation, such as
Edit, and not the browser’s back button if you need to fix
anything.
If happy click Create PHP Code
The phpmyadmin generated PHP code
for the query
Copy the code over to your PHP script.
Join query code pasted into PHP script
Page with Subject titles instead of
Subject IDs