[RESOLVED] How do I make a pretty error when a record can't be found?

[schwim]

Hi there,

I've created a page that shows all records if no id is defined via the URL, and shows the record in question if an id is passed via the url. Everything seems to be working fine, except that if the id doesn't exist in the database, it simply creates a blank page.

I've added an error to the end of the db connection string, but I'd like to be able to make a nice little table in the middle of the page saying that the id doesn't exist. I can't seem to figure out how to do that.

Here's my current layout(minus all of the boring html):

        if(isset($_GET['id'])){

	$id = $_GET['id'];

	$query = "SELECT id,active,author,authorweb,authorwebname,title,testtext," .

	"DATE_FORMAT(postdate, '%M %D, %Y') as date " .

	"FROM ".$prefix."testimonials WHERE id=$id";




	$result = mysql_query ($query);

	while ($row = mysql_fetch_assoc ($result)) {

		/* display testimonials in a simple table */


		$active = $row['active'];

		if($active != 1){
			echo("This testimonial is not active.");
		}else{
			$date = $row['date'];

    		$author = stripslashes ($row['author']);

    		$authorweb = stripslashes ($row['authorweb']);

    		$authorwebname = stripslashes ($row['authorwebname']);

    		$title = stripslashes ($row['title']);

    		$testtext = stripslashes ($row['testtext']);



    		/* display the data */


                            I am displaying the data for a single record here.


		}
	}







}else{



$query = "SELECT id,active,author,authorweb,authorwebname,title,testtext," .

	"DATE_FORMAT(postdate, '%M %D, %Y') as date " .

	"FROM ".$prefix."testimonials WHERE active=1 ORDER BY postdate DESC";



	$result = mysql_query ($query);

	while ($row = mysql_fetch_assoc ($result)) {

  	/* display testimonials in a simple table */



		$date = $row['date'];

    	$author = stripslashes ($row['author']);

    	$authorweb = stripslashes ($row['authorweb']);

    	$authorwebname = stripslashes ($row['authorwebname']);

    	$title = stripslashes ($row['title']);

    	$testtext = stripslashes ($row['testtext']);



    	/* display the data */


                   I am displaying the data for all records here, if no id is set in the URL.



}
}

What do I need to do to get the script to say "Whoops! That record doesn't exist in the database." in a nice format(not just a mysql error).

thanks,
json

[bradgrafelman]

function queryError() {
     echo <<<EOF
<div align="center"><h2>Woops!</h2></div><br/>
<p>Looks like there was an error with a MySQL query.</p>
EOF;
}

$example = @mysql_query($query) or queryError();

[schwim]

Hi there Brad, and thanks very much for the reply.

I tried adding

function queryError() {
   	echo <<<EOF
		<div align="center"><h2>Woops!</h2></div><br/>
		<p>Looks like there was an error with a MySQL query.</p>
		EOF;
	}

right before the first if statement, but when I run it, I still get a blank spot where the testimonial should be.

Here's how I'm formatting the mysql query:

$query = ("SELECT id,active,author,authorweb,authorwebname,title,testtext," .

	"DATE_FORMAT(postdate, '%M %D, %Y') as date " .

	"FROM ".$prefix."testimonials WHERE active=1 ORDER BY postdate DESC") or queryError();

Am I putting it in the wrong spot?

thanks,
json

[bpat1434]

Why would you be using the or conditional on a string? It should be something like:

<?php
$query = "SELECT id,active,author,authorweb,authorwebname,title,testtext," .

    "DATE_FORMAT(postdate, '%M %D, %Y') as date " .

    "FROM ".$prefix."testimonials WHERE active=1 ORDER BY postdate DESC";

$result = @mysql_query($query) or die(queryError());

[schwim]

Thank you. I know it makes me look like even more of an idiot, but:

I moved the function to where you stated:

$query = ("SELECT id,active,author,authorweb,authorwebname,title,testtext," .

	"DATE_FORMAT(postdate, '%M %D, %Y') as date " .

	"FROM ".$prefix."testimonials WHERE id=$id");




	$result = mysql_query ($query) or queryError();

	while ($row = mysql_fetch_assoc ($result)) {

But when calling an ID that doesn't exist, I still receive a blank page.

I'm sure it's another screw up on my part, but I'm not getting any errors at all, and I don't see what I did wrong.

thanks,
json

[bradgrafelman]

Yeah - I shouldn't have used heredoc syntax. It's not the prettiest of syntaxes(sp?).

You can't have anything before the ending delimiter - EOF in this case - including tabs. For simplicity's sake, just use a normal string:

function queryError() { 
       echo "<div align="center"><h2>Woops!</h2></div><br/>\n" 
        . "<p>Looks like there was an error with a MySQL query.</p>\n"; 
    } 

[schwim]

Hi there Brad,

I changed the function to what you suggested(escaping the quotes in the center), but I'm still getting nothing as a result.

Here's my code in it's entirety(I'm sorry for the length, but I imagine my problem lies in the fact that I messed up somewhere else along the way):

include("config.php");



/*this query will get the needed information to create the block as you would like it*/

$result = mysql_query("SELECT debug,main_showtitle,main_showdate,main_showauth,main_showauthweb FROM ".$prefix."config");

while ($row = mysql_fetch_assoc($result)) {



$debug = $row['debug'];

$main_showtitle = $row['main_showtitle'];

$main_showdate = $row['main_showdate'];

$main_showauth = $row['main_showauth'];

$main_showauthweb = $row['main_showauthweb'];



if($debug==1){

	error_reporting(E_ALL);

}

//function queryError() {
//echo ("<div align='center'><h2>Woops!</h2></div><br/>
	//<p>Looks like there was an error with a MySQL query.</p>");
//	}

function queryError() {
       echo "<div align=\"center\"><h2>Woops!</h2></div><br/>\n"
        . "<p>Looks like there was an error with a MySQL query.</p>\n";
    }

echo("<html>

<head>

<title>Automonial</title>

<script language=\"javascript\" type=\"text/javascript\">

<!--

/****************************************************

     Author: Eric King

     Url: http://redrival.com/eak/index.shtml

     This script is free to use as long as this info is left in

     Featured on Dynamic Drive script library (http://www.dynamicdrive.com)

****************************************************/

var win=null;

function NewWindow(mypage,myname,w,h,scroll,pos){

if(pos==\"random\"){LeftPosition=(screen.width)?Math.floor(Math.random()*(screen.width-w)):100;TopPosition=(screen.height)?Math.floor(Math.random()*((screen.height-h)-75)):100;}

if(pos==\"center\"){LeftPosition=(screen.width)?(screen.width-w)/2:100;TopPosition=(screen.height)?(screen.height-h)/2:100;}

else if((pos!=\"center\" && pos!=\"random\") || pos==null){LeftPosition=0;TopPosition=20}

settings='width='+w+',height='+h+',top='+TopPosition+',left='+LeftPosition+',scrollbars='+scroll+',location=no,directories=no,status=no,menubar=no,toolbar=no,resizable=no';

win=window.open(mypage,myname,settings);}

// -->

</script>

</head>

<body>

<div align=\"center\">");




if(isset($_GET['id'])){

	$id = $_GET['id'];

	echo ("Below is the requested testimonial.  Would you like to <a href=\"form.php\" onclick=\"NewWindow(this.href,'submit','600','400','yes','center');return false\" onfocus=\"this.blur()\">submit your own</a>?

	<TABLE border=\"0\" width=\"500\"><tr><td>\n"); //Create the initial table



	/* this query will get all of the testimonials */



	$query = ("SELECT id,active,author,authorweb,authorwebname,title,testtext," .

	"DATE_FORMAT(postdate, '%M %D, %Y') as date " .

	"FROM ".$prefix."testimonials WHERE id=$id");




	$result = mysql_query ($query) or queryError();

	while ($row = mysql_fetch_assoc ($result)) {

		/* display testimonials in a simple table */


		$active = $row['active'];

		if($active != 1){
			echo("This testimonial is not active.");
		}else{
			$date = $row['date'];

    		$author = stripslashes ($row['author']);

    		$authorweb = stripslashes ($row['authorweb']);

    		$authorwebname = stripslashes ($row['authorwebname']);

    		$title = stripslashes ($row['title']);

    		$testtext = stripslashes ($row['testtext']);



    		/* display the data */

			echo("<div align=center>

			<table width=90% border=1 cellpadding=1 cellspacing=1>

				<tr>

					<td bgcolor='#ffffff' width=400>");

			if($main_showtitle == 1) {

				echo("	<font size=3><b>$title</b></font>");

			}

				echo("</td>

					<td bgcolor='#ffffff' width=200>");

			if($main_showdate == 1) {

				echo("	<font size=1>Added On: $date</font>");

			}

				echo("</td>

				</tr>

				<tr>

					<td bgcolor='#ffffff' colspan=2>");

			if($main_showauth == 1) {

				echo("<font size=1>Posted By: $author</font> ");

			}

			if($authorweb != "") {

				if($main_showauthweb == 1) {

					echo("<font size=1>Website: <a href='$authorweb' target='_blank'>$authorwebname</a></font>");

				}

			}

			echo("

					</td>

				</tr>

				<tr>

					<td bgcolor='#ffffff' colspan=2>

					<font size=2>$testtext</font><p>

					</td>

				</tr>

			</table>

			</div><br>

			<br>");
		}
	}



		echo ("</td></tr></table>");



}else{



echo ("Below is a complete list of our testimonials.  Would you like to <a href=\"form.php\" onclick=\"NewWindow(this.href,'submit','600','400','yes','center');return false\" onfocus=\"this.blur()\">submit your own</a>?

	<TABLE border=\"0\" width=\"500\"><tr><td>\n"); //Create the initial table



	/* this query will get all of the testimonials */



	$query = ("SELECT id,active,author,authorweb,authorwebname,title,testtext," .

	"DATE_FORMAT(postdate, '%M %D, %Y') as date " .

	"FROM ".$prefix."testimonials WHERE active=1 ORDER BY postdate DESC");



	$result = mysql_query ($query) or queryError();

	while ($row = mysql_fetch_assoc ($result)) {

  	/* display testimonials in a simple table */



		$date = $row['date'];

    	$author = stripslashes ($row['author']);

    	$authorweb = stripslashes ($row['authorweb']);

    	$authorwebname = stripslashes ($row['authorwebname']);

    	$title = stripslashes ($row['title']);

    	$testtext = stripslashes ($row['testtext']);



    	/* display the data */

		echo("<div align=center>

		<table width=90% border=1 cellpadding=1 cellspacing=1>

			<tr>

				<td bgcolor='#ffffff' width=400>");

		if($main_showtitle == 1) {

			echo("	<font size=3><b>$title</b></font>");

		}

				echo("</td>

				<td bgcolor='#ffffff' width=200>");

		if($main_showdate == 1) {

			echo("	<font size=1>Added On: $date</font>");

		}

				echo("</td>

			</tr>

			<tr>

				<td bgcolor='#ffffff' colspan=2>");

		if($main_showauth == 1) {

			echo("<font size=1>Posted By: $author</font> ");

		}

		if($authorweb != "") {

			if($main_showauthweb == 1) {

				echo("<font size=1>Website: <a href='$authorweb' target='_blank'>$authorwebname</a></font>");

			}

		}

			echo("

				</td>

			</tr>

			<tr>

				<td bgcolor='#ffffff' colspan=2>

				<font size=2>$testtext</font><p>

				</td>

			</tr>

		</table>

		</div><br>

		<br>");

	}



	echo ("</td></tr></table>");



}
}

echo("</div>
</body>
</html>");

If I choose an id of 37, which exists, but is not active, it tells me that it's not active. If I choose an id of 1, it shows the proper record. If I don't pass an id, it posts all of the records, but if I post an id of 12(which doesn't exist), it shows nothing after asking if I'd like to submit a testimonial of my own.

See any mistakes that would keep it from working?

thanks,
json

[Weedpacket]

I typically find the most convenient pattern to deal with recordsets that may be empty runs like this:

$query = "Select blah from foo";
$result = mysql_query($query);
$result_rows = mysql_num_rows($result);

if($result_rows==0)
{
	// Bork! No records returned.
}
else
{
	// Records returned.
	for($i=0; $i<$result_rows; $i++)
	{
		$row = mysql_fetch_array($result);
		// Do stuff with the row.
	}
}

Bonus is an extra variable $i which can be used to number the records.

[bradgrafelman]

I would also move the queryError() function declaration outside of the while() loop, otherwise you're just going to be declaring it on each iteration - not good.

Also, since you're debugging things, are you sure this query:

$result = mysql_query("SELECT debug,main_showtitle,main_showdate,main_showauth,main_showauthweb FROM ".$prefix."config"); 

is even running properly?

For the moment, I would do some SQL debugging:

$query = "SELECT debug,main_showtitle,main_showdate,main_showauth,main_showauthweb FROM ".$prefix."config";
$result = mysql_query($query) or die('MySQL error: ' . mysql_error() . '<hr/>' . $query);

[schwim]

Hi there Brad,

I moved the function to the top of the page and altered my config db call to what you suggested. I also manually added the E_ALL error call to the top, so as not to rely on the db to check for errors. I still receive no errors, and calling an entry that doesn't exist still gives me a blank page 🙁

Weedpacket: Can I ask what you mean by numbering records? Do you mean that I could print $i to show the record number? I don't think that's what you mean, since I could just print $id for that, right?

thanks,
json

[schwim]

Hi there guys,

Just so I can move on with the script, I decided to try weedpacket's suggestion(not that I thought it wasn't good, I just wasn't sure I would be smart enough to implement it), and it's working a treat, doing what needs to be done in all instances.

Weedpacket, can you still explain what you meant by being able to use $i for row numbers?

Brad: Although I went the other route, can you still think of anything that might have caused my problem? It bothers me that I wasn't able to implement it, and I'd like to solve the problem if possible. I saved an alternate file until I figure it out 🙂

thanks,
json

[Weedpacket]

schwim wrote:

Weedpacket, can you still explain what you meant by being able to use $i for row numbers?

echo "<tr><td>$i</td><td>$row[thingy]</td></tr>";

And it's good for alternating row colours, too.

[schwim]

Thanks very much. Thinking on it last night, I realized $id was no good, because numbers got deleted(for example, my problem in this thread). It made sense to me that it wouldn't be a very good numbering system.

I'll look up on the row colors, as I was looking for a way to do that last week.

thanks,
json