Single
Table Queries
Access
and Display
When accessing any online data source, there are a number of distinct
steps:
- connect to the server and database
- issue the query
- collect the data
- display the data
- release memory occupied by query
- sever connection to database and server
All variations on the same
script, these "select all" queries indicate some of the power
and flexibility of PHP/MySQL. In each case, the section of the script
that deals with displaying the data is the same - this is because the
script itself determines the number of rows and fields in the answer
table and scales the output accordingly
Albums
| Tracks | Performers
Albums
table
<?php
$link = mysql_connect("localhost", "username", "password") or die("Unable to connect");
mysql_select_db("ipt") or die("Unable to select database");
$query1 = "select * from albums order by artist, pdate";
if ($result = mysql_query($query1)) {
echo "<p>Your Query has been processed.</p>";
echo "<p>Your Query was:<br><pre>".stripslashes($query3)."</pre></p>";
$numrows = mysql_num_rows($result);
$columns = mysql_num_fields($result);
echo "<p> Here is your result table: </p>";
echo "<p>The answer table has ".$numrows." rows, each having ".$columns." fields";
echo "<br> (collating may take some time - please be patient)</p>";
if ($numrows > 0) {
print "<p> <table border=1 cellpadding=1 cellspacing=0><tr>";
for ($x = 0; $x < $columns; $x++) {
print "<th>";
$name = mysql_field_name($result,$x);
print "<font face='Verdana, Arial, Helvetica, sans-serif'>$name</font>";
print "</th>";
}
print "</tr>";
while ($row = mysql_fetch_row($result)) {
print "<tr>";
for ($y =0; $y < $columns; $y++) {
print "<td> <font face='Verdana, Arial, Helvetica, sans-serif'>$row[$y]</font></td> ";
}
print "</tr>";
} //end while
print "</table>";
} //end if
} else { echo "<p>Error Processing Query: " .mysql_error() . "</p>"; }
?>
Run the Script Live....
Tracks
Table
<?php
$link = mysql_connect("localhost", "username", "password") or die("Unable to connect");
mysql_select_db("ipt") or die("Unable to select database");
$query2 = "select * from tracks order by sernum, disk, track";
if ($result = mysql_query($query2)) {
echo "<p>Your Query has been processed.</p>";
echo "<p>Your Query was:<br><pre>".stripslashes($query3)."</pre></p>";
$numrows = mysql_num_rows($result);
$columns = mysql_num_fields($result);
echo "<p> Here is your result table: </p>";
echo "<p>The answer table has ".$numrows." rows, each having ".$columns." fields";
echo "<br> (collating may take some time - please be patient)</p>";
if ($numrows > 0) {
print "<p> <table border=1 cellpadding=1 cellspacing=0><tr>";
for ($x = 0; $x < $columns; $x++) {
print "<th>";
$name = mysql_field_name($result,$x);
print "<font face='Verdana, Arial, Helvetica, sans-serif'>$name</font>";
print "</th>";
}
print "</tr>";
while ($row = mysql_fetch_row($result)) {
print "<tr>";
for ($y =0; $y < $columns; $y++) {
print "<td> <font face='Verdana, Arial, Helvetica, sans-serif'>$row[$y]</font></td> ";
}
print "</tr>";
} //end while
print "</table>";
} //end if
} else { echo "<p>Error Processing Query: " .mysql_error() . "</p>"; }
?>
Run
the Script Live....
Performers
Table
<?php
$link = mysql_connect("localhost", "username", "password") or die("Unable to connect");
mysql_select_db("ipt") or die("Unable to select database");
$query3 = "select * from performers order by sernum,artist,instrument";
if ($result = mysql_query($query3)) {
echo "<p>Your Query has been processed.</p>";
echo "<p>Your Query was:<br><pre>".stripslashes($query3)."</pre></p>";
$numrows = mysql_num_rows($result);
$columns = mysql_num_fields($result);
echo "<p> Here is your result table: </p>";
echo "<p>The answer table has ".$numrows." rows, each having ".$columns." fields";
echo "<br> (collating may take some time - please be patient)</p>";
if ($numrows > 0) {
print "<p> <table border=1 cellpadding=1 cellspacing=0><tr>";
for ($x = 0; $x < $columns; $x++) {
print "<th>";
$name = mysql_field_name($result,$x);
print "<font face='Verdana, Arial, Helvetica, sans-serif'>$name</font>";
print "</th>";
}
print "</tr>";
while ($row = mysql_fetch_row($result)) {
print "<tr>";
for ($y =0; $y < $columns; $y++) {
print "<td> <font face='Verdana, Arial, Helvetica, sans-serif'>$row[$y]</font></td> ";
}
print "</tr>";
} //end while
print "</table>";
} //end if
} else { echo "<p>Error Processing Query: " .mysql_error() . "</p>"; }
?>
Run
the Script Live....
|