Page 1 of 1
search mysql + echo on 0 results
Posted: Thu May 26, 2005 4:02 am
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
Posted: Thu May 26, 2005 5:17 am
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';
}
Posted: Thu May 26, 2005 5:31 am
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
Posted: Thu May 26, 2005 5:52 am
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?
Posted: Thu May 26, 2005 6:02 am
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
Posted: Thu May 26, 2005 6:25 am
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.

Posted: Thu May 26, 2005 7:00 am
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
Posted: Thu May 26, 2005 9:26 am
by Sphen001
It's reasonably tidy, although it's looking like you're missing a few braces. The code itself looks good though.
Sphen001
Posted: Thu May 26, 2005 9:28 am
by hongco
Code: Select all
else {
$sql = "SELECT user_name, email_address FROM users";
}
with {} you probably read the coder easier.
Posted: Thu May 26, 2005 4:53 pm
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> | ";
}
for ($i = 1; $i <= $total_pages; $i++) {
if (($page) == $i) {
echo "$i ";
} else {
echo "<a href=\"".$_SERVER['PHP_SELF']."?pages=members&cmd=$item&page=$i\">$i</a> ";
}
}
if ($page < $total_pages) {
$next = ($page + 1);
echo " | <a href=\"".$_SERVER['PHP_SELF']."?pages=members&cmd=$item&page=$next\">Next</a>";
}
echo "</center>";
?>
Thanks
Posted: Fri May 27, 2005 3:20 am
by Jim_Bo
*bump*

Posted: Sun May 29, 2005 5:10 pm
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
Posted: Sun May 29, 2005 5:50 pm
by shiznatix
search this forum for pagintation
Posted: Sun May 29, 2005 5:54 pm
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.