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.
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
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>");
}