Page 1 of 1

Displaying Error if no matching data in table

Posted: Wed May 18, 2011 2:46 am
by newphper
I have been learning PHP/MySql using Julie Meloni's' book PHP, MySQL and Apache.

My code works as far as searching my table and display data that I search for. :D

I am having trouble displaying a message if their are no records in my table. :(

Can anyone assist me by having a look and telling me where the code needs adjustment please ?

This is the code that I am using. Thanks in advance for any help..

Code: Select all

<?php
$mysqli = mysqli_connect("127.0.0.1", "root", "mypassword", "mytable");

if (mysqli_connect_errno()) {
	printf("Connect failed: %\n", mysqli_connect_error());
	exit();
} else {
	$sql = ("SELECT customer_id, first_name, last_name, phone_mobile, postcode, trade_type FROM customer WHERE postcode = '".$_POST['postcode'] . "' && trade_type = '".$_POST['trade_type'] . "'");
	$res = mysqli_query($mysqli, $sql);

	if ($res) {
		while ($newArray = mysqli_fetch_array($res, MYSQLI_ASSOC)) {
			echo("<TABLE align='center' border = '1' bgcolor = 'A7E3F6'><TH><strong>SEARCH RESULTS<strong></TH>");
			echo("<TR><TD><strong>Name :<strong>\n$row[first_name]\n$row[last_name]</TD></TR>");
			echo("<TR><TD><strong>Phone :<strong>\n$row[phone_mobile]</TD></TR>");
			echo("<TR><TD><strong>Postcode :<strong>\n$row[postcode]</TD></TR>");
			echo("<TR><TD><strong>Trade Type :<strong>\n$row[trade_type]</TD></TR></TABLE>");
	   		}
	} else {
		
		echo("<TABLE align='center' border = '1' bgcolor = 'A7E3F6'><TH><strong>There are no records to show,sorry.<strong></TH></TABLE>");
	}

	mysqli_free_result($res);
	mysqli_close($mysqli);
}

?>

Re: Displaying Error if no matching data in table

Posted: Wed May 18, 2011 10:47 am
by Jade
The problem is that you'll always get a result back if you use mysqli_query. You need to check to see how many rows were returned to determine whether or not to display the table. Also, please use code tags around your code:

Code: Select all

<?php
$mysqli = mysqli_connect("127.0.0.1", "root", "mypassword", "mytable");

if (mysqli_connect_errno()) {
printf("Connect failed: %\n", mysqli_connect_error());
exit();
} else {
$sql = ("SELECT customer_id, first_name, last_name, phone_mobile, postcode, trade_type FROM customer WHERE postcode = '".$_POST['postcode'] . "' && trade_type = '".$_POST['trade_type'] . "'");
$res = mysqli_query($mysqli, $sql);

if (!empty($res)) {
while ($newArray = mysqli_fetch_array($res, MYSQLI_ASSOC)) {
echo("<TABLE align='center' border = '1' bgcolor = 'A7E3F6'><TH><strong>SEARCH RESULTS<strong></TH>");
echo("<TR><TD><strong>Name :<strong>\n$row[first_name]\n$row[last_name]</TD></TR>");
echo("<TR><TD><strong>Phone :<strong>\n$row[phone_mobile]</TD></TR>");
echo("<TR><TD><strong>Postcode :<strong>\n$row[postcode]</TD></TR>");
echo("<TR><TD><strong>Trade Type :<strong>\n$row[trade_type]</TD></TR></TABLE>");
}
} else {

echo("<TABLE align='center' border = '1' bgcolor = 'A7E3F6'><TH><strong>There are no records to show,sorry.<strong></TH></TABLE>");
}

mysqli_free_result($res);
mysqli_close($mysqli);
}

?>

Re: Displaying Error if no matching data in table

Posted: Wed May 18, 2011 4:17 pm
by newphper
Thanks for taking the time to help.

Unfortunately, I still have no message appearing with your revised code as yet.

If I rephrased my question to.....

If my search criteria is not met, even though there are other records in the table, I need to display a message on screen.

Your code seems logical to me, alas, it may be that it only displays a message if there are no records at all in the table ??

I am new at programming and am slowly getting my head around it......

Re: Displaying Error if no matching data in table

Posted: Wed May 18, 2011 7:08 pm
by newphper
Just tried to enclose the php code in "tags" for the forum, but.........no luck

Re: Displaying Error if no matching data in table

Posted: Wed May 18, 2011 10:03 pm
by newphper
Ok, I have attacked it another way and this time it works,probably not the best code as I am new.
If anyone can suggest a better way to code this snippet, please assist :-)

Code: Select all

<?php

/******************** This needs to be put into an inc file located elswhere for security reasons ******************/

$db=mysql_connect ("127.0.0.1", "root", "mypassword") or die ('Could not connect to the database: ' . mysql_error());

/************************** If you cant connect to localhost, send message to screen *******************************/

if (!$db)
  {
  die('Could not connect: ' . mysql_error());
  }
 
/******************************* If you do connect nominate the database you want to use **************************/
 
mysql_select_db ("mytable", $db); 

/**************************************** gather the query information ********************************************/

$res=mysql_query("SELECT customer_id, first_name, last_name, phone_mobile, postcode, trade_type FROM customer WHERE postcode = '".$_POST['postcode'] . "' && trade_type = '".$_POST['trade_type'] . "'");


/**************************************** DISPLAY QUERY RESULTS HERE *********************************************/

      while ( $row = mysql_fetch_array( $res ) ) 
  	{	
	echo("<TABLE align='center' border = '1' bgcolor = 'A7E3F6'><TH><strong>SEARCH RESULTS<strong></TH>");
	echo("<TR><TD><strong>Name :<strong>\n$row[first_name]\n$row[last_name]</TD></TR>");
	echo("<TR><TD><strong>Phone :<strong>\n$row[phone_mobile]</TD></TR>");
	echo("<TR><TD><strong>Postcode :<strong>\n$row[postcode]</TD></TR>");
	echo("<TR><TD><strong>Trade Type :<strong>\n$row[trade_type]</TD></TR></TABLE>");
	}

/*********************** If no matching records in my table...DISPLAY MESSAGE HERE ******************************/

	if (mysql_num_rows($res) == 0) {
		
  echo ("<strong>No Records to show, sorry.</strong>"); 
}
		
mysql_free_result($res);
mysql_close($db);

?>

Re: Displaying Error if no matching data in table

Posted: Thu May 19, 2011 12:43 pm
by Jade
newphper wrote:If my search criteria is not met, even though there are other records in the table, I need to display a message on screen.
So basically you want to display a message saying 0 of {total records in the database} found?

Code: Select all

<?php

/******************** This needs to be put into an inc file located elswhere for security reasons ******************/

$db=mysql_connect ("127.0.0.1", "root", "mypassword") or die ('Could not connect to the database: ' . mysql_error());

/************************** If you cant connect to localhost, send message to screen *******************************/

if (!$db)
  {
  die('Could not connect: ' . mysql_error());
  }
 
/******************************* If you do connect nominate the database you want to use **************************/
 
mysql_select_db ("mytable", $db);

/**************************************** gather the query information ********************************************/

$res=mysql_query("SELECT customer_id, first_name, last_name, phone_mobile, postcode, trade_type FROM customer WHERE postcode = '".$_POST['postcode'] . "' && trade_type = '".$_POST['trade_type'] . "'");

$total = mysql_num_rows(mysql_query("SELECT customer_id FROM customer"));

/**************************************** DISPLAY QUERY RESULTS HERE *********************************************/

      while ( $row = mysql_fetch_array( $res ) )
        {      
        echo("<TABLE align='center' border = '1' bgcolor = 'A7E3F6'><TH><strong>SEARCH RESULTS<strong></TH>");
        echo("<TR><TD><strong>Name :<strong>\n$row[first_name]\n$row[last_name]</TD></TR>");
        echo("<TR><TD><strong>Phone :<strong>\n$row[phone_mobile]</TD></TR>");
        echo("<TR><TD><strong>Postcode :<strong>\n$row[postcode]</TD></TR>");
        echo("<TR><TD><strong>Trade Type :<strong>\n$row[trade_type]</TD></TR></TABLE>");
        }

/*********************** If no matching records in my table...DISPLAY MESSAGE HERE ******************************/

echo "<strong>" . mysql_num_rows($res) . " of " . number_format($total) . " records found.";
               
mysql_free_result($res);
mysql_close($db);

?>

Re: Displaying Error if no matching data in table

Posted: Fri May 20, 2011 1:19 am
by newphper
Thanks for your continued interest and help Jade.

I actually have achieved that part, just wanted to display a message if no records in the postcode area. I have managed to do that part.

Now I am stuck again...In my postcode table I may have 20 postcodes(zipcodes) with the same area_id.
My pc_test table has the fields "postcodes", "area_name", "area_id".
I now want to be able to display all postcodes linked to the same area_id.

Current code is below...........................

Code: Select all

<?php
//error_reporting(E_ALL);

///////////////////////Connect to the database and close the connection when finished///////////////////////////////

include ("dbconnect.php");

///////////////////////////////// Gather and Display area_id  ///////////////////////////////

$res=mysql_query("SELECT area_id FROM pc_test WHERE postcodes = '".$_POST['postcode']."'");
while ($row = mysql_fetch_array($res))
{

echo("$row[area_id]"); 
}

//88//88//88//88//88//This is where I need to query and display all postcodes that belong to an area_id  ////////////////////




///////////// Would anyone have the time and/or inclination to have a go at this piece of code  ???  ////////////////////////

/////////////My pc_test table has an "area_id" and there may be 20 postcodes with that area_id number  //////////////////////

////////////////////How does one query that and then pass it and then display the result ??  ////////////////////////////////

////////////////////  My guess is to put them into an array and then echo them out ??  //////////////////////////////////////




////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
	
/**************************************** Gather the query information ********************************************/

$res2=mysql_query("SELECT customer_id, first_name, last_name, phone_mobile, postcode, trade_type FROM customer WHERE postcode = '". $_POST['postcode'] . "' && trade_type = '".$_POST['trade_type'] . "'");


/**************************************** DISPLAY QUERY RESULTS HERE *********************************************/

      while ( $row2 = mysql_fetch_array( $res2 ) ) 
  	{	
	echo("<TABLE align='center' border = '1' bgcolor = 'A7E3F6'><TH><strong>SEARCH RESULTS<strong></TH>");
	echo("<TR><TD><strong>Name :<strong>\n$row2[first_name]\n$row2[last_name]</TD></TR>");
	echo("<TR><TD><strong>Phone :<strong>\n$row2[phone_mobile]</TD></TR>");
	echo("<TR><TD><strong>Postcode :<strong>\n$row2[postcode]</TD></TR>");
	echo("<TR><TD><strong>Trade Type :<strong>\n$row2[trade_type]</TD></TR></TABLE>");
	}

/*********************** If no matching records in my table...DISPLAY MESSAGE HERE ******************************/

	if (mysql_num_rows($res2) == 0) {
		
  echo ("<strong>No Result, sorry</strong>"); 
}

//include ("db_close.php");

?>

Re: Displaying Error if no matching data in table

Posted: Fri May 20, 2011 12:42 pm
by Jade
Then you need to do a query where you pass the area_id instead of the post code:

Code: Select all

$res = mysql_query("SELECT postcode FROM pc_test WHERE area_id = '{$_POST['area_id']}'")
or die ("cannot select postcodes from area {$_POST['area_id']}: " . mysql_error());

Re: Displaying Error if no matching data in table

Posted: Fri May 20, 2011 10:04 pm
by newphper
Unfortunately, my query has an error in it somewhere...
I have tried quite a few different ways, yet to manage the right way :(

The code below seems close to me, but I am missing something, it displays nothing at all.

Code: Select all

////////////////// Gather and Display postcodes relating to area_id  ////////////////////////

$res3 = mysql_query("SELECT postcodes FROM pc_test WHERE area_id = '".$_POST['postcode']."'")
or die ("cannot select postcodes from area {$_POST['area_id']}: " . mysql_error());

while ($row3 = mysql_fetch_array($res3))
{

echo("$row3[postcodes]"); 
}

Re: Displaying Error if no matching data in table

Posted: Sat May 21, 2011 1:35 am
by newphper
this one is driving me nutty.....

Code: Select all

WHERE area_id = '".$_POST['postcode']."'
if I replace (area_id) with (postcodes), the postcode entered by user is displayed

I just need it to show all postcodes associated with the area_id.

Re: Displaying Error if no matching data in table

Posted: Sat May 21, 2011 2:06 am
by newphper
If i add an if statement to the following code, the message, No postcodes match area_id. is displayed....

But have about 7 postcodes with the id of 1 in my pc_test table, the code is not quite right somewhere....

Code: Select all

$res3=mysql_query("SELECT postcodes FROM pc_test WHERE area_id = '".$_POST['postcode']."'")
or die ("cannot select postcodes from area {$_POST['area_id']}: ". mysql_error());

while ($row3 = mysql_fetch_array($res3))
{

echo("$row3[postcodes]"); 
}
if (mysql_num_rows($res3) == 0) {
		
  echo ("<strong>No postcodes match area_id.</strong>"); 
}

Re: Displaying Error if no matching data in table

Posted: Sun May 22, 2011 1:30 am
by newphper
Problem Solved :D

Now on to the next one .....

Thanks for your interest and advice Jade.

Code: Select all

///////////////////////////////// Gather and Display area_id  //////////////////////////////

$res=mysql_query("SELECT area_id FROM pc_test WHERE postcodes = '".$_POST['postcode']."'");
while ($row = mysql_fetch_array($res))
{

echo("$row[area_id]"); 
$value="$row[area_id]";
}

////////////////// Gather and Display postcodes relating to area_id  ////////////////////////

$res3=mysql_query("SELECT postcodes FROM pc_test WHERE area_id = '$value'")
or die ("cannot select postcodes from area {$_POST['postcode']}: ". mysql_error());

while ($row3 = mysql_fetch_array($res3))
{

echo("\n$row3[postcodes]"); 
}

if (mysql_num_rows($res3) == 0) {
		
  echo ("<strong>No postcodes match area_id.</strong>"); 
}