How to return "no results found" from database query

[wattsup88]

Hello all,

I need to know how to return the value "no results found please broaden your search" or something like that when the mysql query comes up empty... Right now Im using something like this...

[code]
$sql = "SELECT count(*) AS num FROM jobs WHERE jbcategory = '$value' AND jbstate = '$value1'";

$getcount = mysql_query($sql) or die(mysql_error());

$rows = mysql_num_rows($getlist);

if ($rows==0){
no results returned blah blah blah...
}else{
display results here
}
[/code]

this of course is only the concept... I assume that if the database is returning no results then it cant count rows that aren't returned... so it wont work... but what do i know... anyway i need a method that will return a value if the database doesn't return a row when queried...

[papaface]

I believe the way you are doing it is the correct way to do it...

[wattsup88]

Then why isn't it working... here is the exact code:

[code]<?php
//calls up the job category selected
if ( is_array( $_POST['category'] ) ) {
foreach ( $_POST['category'] as $value ) {
}
}
//calls up the state selected
if ( is_array( $_POST['stabbrev'] ) ) {
foreach ( $_POST['stabbrev'] as $value1 ) {
}
}

$_SESSION['value'] = $value;
$_SESSION['value1'] = $value1;


//start up alternating rows
$color2 = "#CCCCCC";
$color1 = "#AAAAAA";
$row_count = 0;
//start up alternating rows

//*********pagination variables************
$page = (is_numeric($_GET['page']) && $_GET['page'] > 0) ? $_GET['page'] : 1;
$max_results = 5;
$from = (($page * $max_results) - $max_results);
$allowed = array('jbcategory','jbtitle','jbsalary','jbstate');
$sortby = (in_array($_GET['sortby'],$allowed)) ? $_GET['sortby'] : 'jbtitle';

//Query
if ($value=="---Select Job Category---" AND $value1=="---Select State---"){
$sql = "SELECT * FROM jobs ORDER BY $sortby LIMIT $from, $max_results";

}else if ($value=="---Select Job Category---"){
$sql = "SELECT * FROM jobs WHERE jbcategory = '$value' OR jbstate = '$value1' ORDER BY $sortby LIMIT $from, $max_results";

}else if ($value1=="---Select State---"){
$sql = "SELECT * FROM jobs WHERE jbcategory = '$value' OR jbstate = '$value1' ORDER BY $sortby LIMIT $from, $max_results";

}else{
$sql = "SELECT * FROM jobs WHERE jbcategory = '$value' AND jbstate = '$value1' ORDER BY $sortby LIMIT $from, $max_results";
}

$getlist = mysql_query($sql) or die(mysql_error());


// Figure out the total number of rows in table

if ($value=="---Select Job Category---" AND $value1=="---Select State---"){
$sql = "SELECT count(*) AS num FROM jobs";

}else if ($value=="---Select Job Category---"){
$sql = "SELECT count(*) AS num FROM jobs WHERE jbcategory = '$value' OR jbstate = '$value1'";

}else if ($value1=="---Select State---"){
$sql = "SELECT count(*) AS num FROM jobs WHERE jbcategory = '$value' OR jbstate = '$value1'";

}else{
$sql = "SELECT count(*) AS num FROM jobs WHERE jbcategory = '$value' AND jbstate = '$value1'";
}

$getcount = mysql_query($sql) or die(mysql_error());
$total_results = mysql_result($getcount, 0) or die(mysql_error());


// Figure out the total number of pages we will have
$total_pages = ceil($total_results / $max_results);


// Figure out the current page result numbers
$fr = $from + 1;
$to = $from + mysql_num_rows($getlist);
//**********pagination variables********


//*********Displaying the query***********
$rows = mysql_num_rows($getlist);

if ($rows==0){
echo "<tr><td>
<table style=\"border-color:990101\" align=\"left\" border=\"0\" bgcolor=\"#990101\" width=\"800\" height = \"400\" cellpadding=\"20\">
<tr>
<td width=\"800\" height=\"10\" bgcolor=\"#990101\" align\"center\"colspan=\"5\">
<em><font  size=\"+5\" color=\"#FFFFFF\" face=\"Arial, Helvetica, sans-serif\"><b>Please broaden your search or view all jobs.</b></font></em>
</td></tr>";

}else{

echo "<tr><td>
<table style=\"border-color:990101\" align=\"left\" border=\"0\" bgcolor=\"#990101\" width=\"800\" cellpadding=\"0\">

<tr>

<td width=\"30\" rowspan=\"3\"><img src=\"/Images/strip.gif\" width=\"20\" height=\"400\" align=\"top\"></td>

<td width=\"800\" height=\"10\" bgcolor=\"#990101\" colspan=\"5\">

<em><font  size=\"-1\" color=\"#FFFFFF\" face=\"Arial, Helvetica, sans-serif\"><b>Showing $fr to $to of $total_results results</b></font></em>

</td></tr>";



print "<tr><td>\n";
print "<table style=\"border-color:990101\" align=\"left\" border=\"0\" bgcolor=\"#990101\" width=\"750\" cellpadding=\"20\">";


while ($row = mysql_fetch_array($getlist))
  {
        $jbc = $row['jbcategory'];
        $jbt = $row['jbtitle'];
        $jbs = $row['jbsalary'];
        $jbst = $row['jbstate'];
$jbid = $row['id'];

$row_color = ($row_count % 2) ? $color1 : $color2;

    print "<tr>\n";

echo "<td  width=\"240\" height=\"10\" bgcolor=\"$row_color\"><a href=\"viewid.php?id=$jbid\">$jbt</a></td>";
echo " <td height=\"10\" bgcolor=\"$row_color\">$jbc</td>";
echo "<td height=\"10\" bgcolor=\"$row_color\">$jbs</td>";
echo " <td height=\"10\" bgcolor=\"$row_color\">$jbst</td>";
//echo " <td width=\"45\" height=\"10\" bgcolor=\"$row_color\">".stripslashes($jbid)."</td>";

 
$row_count++;
  }
 
  echo "</table>";
//*********Displaying the query***********


 
//*********pagination links start************ 
  echo "<tr><td bgcolor=\"#990101\" align= \"center\" width =\"800\" colspan=\"5\">";
  if($page > 1){
    $prev = ($page - 1);
    echo "<a href='{$_SERVER['PHP_SELF']}?page=1&sortby=$sortby'>First</a> ";
    echo "<a href='{$_SERVER['PHP_SELF']}?page=$prev&sortby=$sortby'>Prev</a> ";
} // end if

// build the links to the 2 previous and 2 next pages
for($i = ($page - 2); $i <= ($page + 2); $i++){
  // only make a link if the prev/next is a valid page number
  if (($i >= 1) && ($i <= $total_pages)) {
    echo ($page == $i) ? "[$i] " : "<a href='{$_SERVER['PHP_SELF']}?page=$i&sortby=$sortby'>$i</a> ";
  } // end if
} // end for
   
// Build Next and Last links. If on last page, we won't make a links
if($page < $total_pages){
    $next = ($page + 1);
    echo "<a href='{$_SERVER['PHP_SELF']}?page=$next&sortby=$sortby'>Next</a> ";
    echo "<a href='{$_SERVER['PHP_SELF']}?page=$total_pages&sortby=$sortby'>Last</a>";
} // end if
echo "</tr></td>";
echo "<tr>
    <td height=\"32\" colspan=\"4\"><img src=\"/Images/striphorizontal.gif\" width=\"795\" height=\"30\">
    </tr>";
print "</table>\n";
/***** end of pagination ******/

}//end else statement
?>[/code]

the database connection is not shown but it is connected of course...

[ShogunWarrior]

Sorry "Not working" is too ambiguous and the code is pretty long to look through without knowing the problem.

[wattsup88]

ok fine here is the specific code...
<?
$sql = "SELECT count(*) AS num FROM jobs WHERE jbcategory = '$value' AND jbstate = '$value1'";
$getcount = mysql_query($sql) or die(mysql_error());
$rows = mysql_num_rows($getlist);

if ($rows==0){
echo "<tr><td>
<table style=\"border-color:990101\" align=\"left\" border=\"0\" bgcolor=\"#990101\" width=\"800\" height = \"400\" cellpadding=\"20\">
<tr>
<td width=\"800\" height=\"10\" bgcolor=\"#990101\" align\"center\"colspan=\"5\">
<em><font  size=\"+5\" color=\"#FFFFFF\" face=\"Arial, Helvetica, sans-serif\"><b>Please broaden your search or view all jobs.</b></font></em>
</td></tr>";

}else{
Return query blah blah blah...
}
?>

oh and Shogun Warrior the problem is specifically posted in my first post and its not "not working"; its "how do you do this" and "here is how im attempting to do it..."
id rather post all the code immediately than have people ask for parts of it later when its not posted, & im not at my keyboard to post it

[papaface]

If it isnt working by your definition, what happens?

[corbin]

If there is no result mysql_num_rows returns a resource error if I remember right.

try
if ($rows < 1){

[wattsup88]

I apologize for not specifying that... It does not return the first part of the if statement... if there are rows returned then it displays them, but if there are no values it won't return the first if part... does that make sense?

[wattsup88]

oh and still nothing after trying what corbin said... sorry guys... thanks for the help so far...

[corbin]

Do you mean the actual mysql response be "No results returned...."???  Cause you would have to use an if statement then change it.

[wattsup88]

no i just want php to be able to display it...I dont want that response to come from the database...

[ShogunWarrior]

Ok, instead of checking $rows==0 use this method: (pseudocode)
[code]
if( $getcount && mysql_num_rows($getcount)>0 )
{
  //Holy sh*t there are rows, output them!
}
else
{
  //There are no rows
}
[/code]

[corbin]

I either don't understand what you're trying to do, or I don't understand why you're having so much trouble with it.

Anyways what ShogunWarrior posted should work perfectly. :p

[trq]

Where is $getlist being defined? Try...

[code=php:0]
$rows = mysql_num_rows($getcount);
[/code]

[wattsup88]

thank you all i was definatly using the wrong query as thorpe pointed out i was using $getlist instead of $getcount... I changed it but it is still giving me nothing...

[wattsup88]

Yeah so i know you all hate me by now, but its STILL not displaying the else part... although just for grins i took the statement and flipped it like:

[code]
if(the psuedocode posted earlier){
show message
}else{
show rows
}[/code]

and it returned the message whether there were rows being returned or not... and i tried reversing the >0 to <1 and it still would give only the if part if it was true, but no else part if the if statement was false... man thats confusing to me let alone you all... im sorry trying to explain best as i can...

oh and i have seen psuedocoding before... how does it work? and if noone wants to elaborate where can i learn about it...