search mysql + echo on 0 results

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

search mysql + echo on 0 results

Post by Jim_Bo »

Hi,

Here is my search code, I have tried to use mysq_num_rows to echo 'No records found' if 0 results are found .. but it seems to just display blank page when 0 results are found.

I also added an if !$results error, so assumming i change a field in the query that doesnt exist in the table, it should echo that error .. but it seems to just give a line error.

Here is the code:

Code: Select all

<?php

include("db.php");

if (isset($_POST['search'])) {

$search = $_POST['search'];

$sql = "SELECT user_name, email_address FROM users WHERE (user_name LIKE('%$search%') OR email_address LIKE('%$search%'))";

} else 

$sql = "SELECT user_name, email_address FROM users";

$result = mysql_query($sql);
		while ($row = mysql_fetch_array($result)) {
		$user_name = $row['user_name'];
		$email_address = $row['email_address'];
	
if (!$result) {

	echo "<center><b>Error!</b> Please contact the administrator.</center>";
	
}

if (mysql_num_rows($result) == 0) {
	
	echo "<center>no records found</center>";
	
	}

if (mysql_num_rows($result) == 0) {
	
	echo "<center>no records found</center>";
	
} else {

	echo "$user_name -- $email_address<br>";
	
 }
}

?>

<form name="form" method="post" action="search.php">
Search: <input name="search" type="text" size="25">
<input type="submit" name="Submit" value="Submit"></form>
Thanks
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post by shiznatix »

1st off you should do the error reporting for sql like this

Code: Select all

$result = mysql_query($sql) or die(mysql_error().__LINE__);
makes likes a whole lot easier

also, why do you have this twice?

Code: Select all

if (mysql_num_rows($result) == 0) {        echo "<center>no records found</center>";        } if (mysql_num_rows($result) == 0) {        echo "<center>no records found</center>";    }
but if you dont get a mysql_error and the no results is still not working try

Code: Select all

$num = mysql_num_rows($result);
echo $num.'---the number of rows returned!';
//if that is 0 then the following should work...

if (!mysql_num_rows($result))
{
echo 'no results';
}
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Post by Jim_Bo »

Hi,

The double "if" was a paste error .. actual code isnt like that.

With the code posted above, when I visit the page I see:

86---the number of rows returned!"user_name" -- "email_address"

for each user in the database, which is good as thats what I want. After submiting the search with a string that doesnt exist in the table, all I get is the form showing on the screen, without "no results". If I search with a sting that exists in the table the results are echoed correctly.

Code:

Code: Select all

<?php

include("db.php");

if (isset($_POST['search'])) {

$search = $_POST['search'];

$sql = "SELECT user_name, email_address FROM users WHERE (user_name LIKE('%$search%') OR email_address LIKE('%$search%'))";

} else 

$sql = "SELECT user_name, email_address FROM users";

$result = mysql_query($sql);
		while ($row = mysql_fetch_array($result)) {
		$user_name = $row['user_name'];
		$email_address = $row['email_address'];
	
if (!$result) {

	echo "<center><b>Error!</b> Please contact the administrator.</center>";
	
}

$num = mysql_num_rows($result);

echo $num.'---the number of rows returned!';
//if that is 0 then the following should work... 

if (!mysql_num_rows($result)){

echo 'no results';

} else {

	echo "$user_name -- $email_address<br>";
	
 }
}

?>

<form name="form" method="post" action="search.php">
Search: <input name="search" type="text" size="25">
<input type="submit" name="Submit" value="Submit"></form>
Thanks
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post by shiznatix »

ohhhhhhh becuase if there is nothing in the $_POST[search] then when you do this if ($_POST[search]) then it will return false and show the form again because you didnt put anything in the search text field. or is that not the problem?
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Post by Jim_Bo »

hi,

Um no not really

When I view 'search.php' I see all the records along with the search form, If I submit the from with no value it shows all the records in the table which is good. If I submit the form with a value that exists in the table it displays the matching records which is good, When I submit the form with a value that doesnt exist in the table, it only shows the search form with no data. Which is where 'no results' should be echoed to the screen.

Thanks
hongco
Forum Contributor
Posts: 186
Joined: Sun Feb 20, 2005 2:49 pm

Post by hongco »

I would check for number of rows first before fetching arrays.

There is no point of fetching if there is no result; Hence:

Code: Select all

$num_rows = mysql_num_rows($result);

if($num_rows > 0 )
{
   while ($row = mysql_fetch_array($result)) {
        $user_name = $row['user_name'];
        $email_address = $row['email_address'];
   }
}
else {
   echo "no results";
}
PS. i am not sure if i remember it correctly, but it would have error if you try to use msql_fetch_array with no result found. I am too lazy to test it :) so if you use the code above, and it works, then my memory is still somewhat good. :)
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Post by Jim_Bo »

Hi,

Nice its working now, I merged hongco's code above into it.

code:

Code: Select all

<form name="form" method="post" action="search.php">
Search: <input name="search" type="text" size="25">
<input type="submit" name="Submit" value="Submit"></form>

<?php

include("db.php");

if (isset($_POST['search'])) {

$search = $_POST['search'];

$sql = "SELECT user_name, email_address FROM users WHERE (user_name LIKE('%$search%') OR email_address LIKE('%$search%'))";

} else 

$sql = "SELECT user_name, email_address FROM users";
$result = mysql_query($sql);

$num_rows = mysql_num_rows($result);

if ($num_rows > 0 ) {

while ($row = mysql_fetch_array($result)) {
	$user_name = $row['user_name'];
	$email_address = $row['email_address'];

echo "$user_name -- $email_address<br>";

}

} else {

echo "no results";

}

?>
Would that be classed as reasonably tidy code for the job?

Thanks
Sphen001
Forum Contributor
Posts: 107
Joined: Thu Mar 10, 2005 12:24 pm
Location: Land of the Beaver

Post by Sphen001 »

It's reasonably tidy, although it's looking like you're missing a few braces. The code itself looks good though.

Sphen001
hongco
Forum Contributor
Posts: 186
Joined: Sun Feb 20, 2005 2:49 pm

Post by hongco »

Code: Select all

else {
    $sql = "SELECT user_name, email_address FROM users";
}
with {} you probably read the coder easier.
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Post by Jim_Bo »

Hi,

Great, thanks for the tips.

I have another question, I have added pagination to the script. The small problem im having is when I search for a record it displays the matches found on page 1, but also adds links to pages 2,3,4 etc even tho there was only one or 2 matches, if there are more matches than the "$max_results" once you go to page 2 the search results are lost. How can I limit it by $result or mysql_num_rows rather than by the total records in the database?

Then maybe once a search is performed have a link "display all records"

code:

Code: Select all

<form name="form" method="post" action="search.php">
  <div align="center">Search: 
    <input name="search" type="text" size="25">
    <input type="submit" name="Submit" value="Submit">
  </div>
</form>

<?php

include("db.php");

if(!isset($_GET['page'])){ 
    $page = 1; 
} else { 
    $page = $_GET['page']; 
} 

$max_results = 10; 

$from = (($page * $max_results) - $max_results); 

if (isset($_POST['search'])) {

$search = $_POST['search'];

$sql = "SELECT user_name, email_address FROM users WHERE (user_name LIKE('%$search%') OR email_address LIKE('%$search%')) LIMIT $from, $max_results";

} else {

$sql = "SELECT user_name, email_address FROM users LIMIT $from, $max_results";

}

$result = mysql_query($sql);

$num_rows = mysql_num_rows($result);

if ($num_rows > 0 ) {

while ($row = mysql_fetch_array($result)) {
	$user_name = $row['user_name'];
	$email_address = $row['email_address'];

echo "<center>$user_name -- $email_address</center><br>";

}

} else {

echo "<center>no results</center>";

}

$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM users"),0); 

$total_pages = ceil($total_results / $max_results); 

echo "<center>";

if ($page > 1) { 
    $prev = ($page - 1); 
    echo "<a href=\"".$_SERVER['PHP_SELF']."?pages=members&cmd=$item&page=$prev\">Previous</a>&nbsp|&nbsp"; 
} 

for ($i = 1; $i <= $total_pages; $i++) { 
    if (($page) == $i) { 
        echo "$i&nbsp;"; 
        } else { 
            echo "<a href=\"".$_SERVER['PHP_SELF']."?pages=members&cmd=$item&page=$i\">$i</a>&nbsp"; 
    } 
} 

if ($page < $total_pages) { 
    $next = ($page + 1); 
    echo "&nbsp|&nbsp<a href=\"".$_SERVER['PHP_SELF']."?pages=members&cmd=$item&page=$next\">Next</a>"; 
}

echo "</center>";

?>
Thanks
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Post by Jim_Bo »

*bump* :?
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Post by Jim_Bo »

*Bump*

Hi,

I have tried modifying the following line with heaps of different code, but havnt managed make it pagination records found via the sql query rather than paginating all the records in the table:

$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM users"),0);

How would that have to be using maybe the variable $result or maybe mysql_num_rows?

Cheers
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post by shiznatix »

search this forum for pagintation
hongco
Forum Contributor
Posts: 186
Joined: Sun Feb 20, 2005 2:49 pm

Post by hongco »

there are lots of info on pagination.
After you get this work, you may want to write a class for it - you will be using it again and again and again.
Post Reply