is there any way to do this? - The MySQL Relational Database

Posted by bschultz (bschultz), 26 July 2003

I'm running a high school sports site. Is there any way to have a script display a certain message if the table is empty?

For example, I have the links to each individual game on the schedule page. If the game has been played / entered, it displays the stats / results.

If the game hasn't been played / entered yet, it displays a blank page (since there is no data in the table yet). Is there a way to do this?:

if table is blank
echo "This game hasn't been played yet":

Brian

Posted by admin (Graham Ellis), 26 July 2003

You're a PHP user, aren't you? Here's a generic routine in PHP that will read a table from a database (assuming you have select access) and display it as a table. It also error checks the connection, and reports if the table is empty.

Code:

$html = "";
if (mysql_connect("dhansak","trainee","abc123")) {
if (mysql_select_db($_GET[database])) {
if ($resultset = mysql_query("select * from $_GET[table]")) {
$nrecs = 0;
while ($row = mysql_fetch_assoc($resultset)) {
if ($nrecs == 0) {
$html .= "<table border=1><tr>";
foreach (array_keys($row) as $colname) {
$html .= "<th>".htmlspecialchars($colname)."</th>";
}
$html .= "</tr>";
}
$nrecs++;
$html .= "<tr>";
foreach (array_keys($row) as $colname) {
$html .= "<td>".htmlspecialchars($row[$colname])."</td>";
}
$html .= "</tr>";
}
if ($nrecs == 0) {
$html .= "No rows in table";
} else {
$html .= "</table>";
}
} else {
$html .= "Unable to read table $_GET[table]";
}
} else {
$html .= "Unable to access database $_GET[database]";
}
} else {
$html .= "Unable to connect to mysql server";
}
?>

<html>
<head><title>Report page</title></head>
<body>
<h3>Report on database <?php print $_GET[database]; ?>
, table <?php print $_GET[table]; ?></h3>
<br> <?php print $html; ?>
<hr>
Graham Ellis, 2003
</body>
</html>

Posted by bschultz (bschultz), 28 July 2003

Thanks, Graham. Worked very well. But, now I'm trying to insert the "regular" html coding once there is something in the table. I need to replace all the double quotes (") in the html with single quotes (')...I'm looking at preg replace and others in the php.net manual, ...which one should I be using? Of course, I could just manually change them all, but who wants to do that much work?

Brian

Posted by admin (Graham Ellis), 28 July 2003

OK ... think of it like this.

(String) variables in memory hold the string of characters that you truely want to be stored and displayed ....
* before you write the string out to a database table, use the addslashes or quotemeta functions of PHP to ensure that you won't upset the SQL handler
* before you write the string out to HTML, use the htmlspecialchars or htmlentities functions of PHP to ensure that you won't upset the browser.

My example already uses htmlspecialchars, so it should work for you - just remember that if you store HTML tags in your database and process them through that function, you'll get all the < and > characters appearing, and all the stuff between too.

You ask "who wants to do that much work" as you talk about doing the job manually or with regular expressions. That's why there's a function provided to do it!

Posted by bschultz (bschultz), 28 July 2003

maybe I'm already doing too much work...I just was going to use:

print "html code here" if the table had something in it....the html code never went into the db that way. The html was already written, all I had to do was remove the form elements, and insert the php calls to the db.

I'll keep reading / trying it out.

Brian

This page is a thread posted to the opentalk forum at www.opentalk.org.uk and archived here for reference. To jump to the archive index please follow this link.