Query Returns Too Many Rows

proportionality

Here is the query:

"SELECT * FROM guestbook WHERE guest_message LIKE '%$sqlquery%' ORDER BY id DESC LIMIT 0,10"

But, right after executing the query I check the number of rows that there are, and it says 15. Why isn't LIMIT doing anything?

Thanks for your time.

BeastRider

What species of database are you using?

proportionality

I am using MYSQL.

For somereason the limit does work on:

"SELECT * FROM guestbook ORDER BY id DESC LIMIT 0,10"

BeastRider

Post the routine so we can see more of how this is being done.

proportionality

The Routine with the LIKE:

$sqlquery = "SELECT * FROM guestbook WHERE guest_message LIKE '%$query%'";

$row = mysql_query($sqlquery) or die(mysql_error());
$num_rows = mysql_num_rows($row) or die(mysql_error());
$num_pages = $num_rows / $numperpage;

$sqlquery .= " ORDER BY id DESC LIMIT 0,10";
$row = mysql_query($sqlquery) or die($sqlquery. " ". mysql_error());

WITHOUT LIKE:

$sqlquery = "SELECT * FROM guestbook";
$num_rows = mysql_num_rows(mysql_query($sqlquery));
$num_pages = $num_rows / $numperpage;

$row = mysql_query("SELECT * FROM guestbook ORDER BY id DESC LIMIT 0, 10);

Then I use:

while($gbvalues=mysql_fetch_array($row))

To print out the things. The LIKE case is used when there is a search, and the other if not. The without LIKE works. The one with it does not. Because it prints out 15.

BeastRider

Wierd. I created a mimic of your code to run against my database. It works fine. See the results at

http://www.herbhost.com/phptest/liketest.php

You can see that 192 records get returned before the LIMIT is added, only 10 afterwards. Code below is exactly what is running the page above, except for user, pass, and db name

<?php

// build a query
$wort = "-E";
$query = "SELECT filename, annotation, pageno, thumbid
            FROM thumbs
            WHERE filename LIKE '%$wort%'";

// connect to the database
if(!($dbh=mysql_connect ("localhost",
                         "$user",
                         "$pass")))
  die("Cannot connect to the database");

// open the connected database
if (!mysql_select_db("$db",$dbh))
  die("Cannot open the database");

// do the query

if (!($result = @ mysql_query ($query, $dbh)))
  die("Cannot execute query against the database");

// if we got some rows back then output a pag of test data
$numrows = mysql_num_rows($result);

// print out what we got
echo "<br>Raw Row Count: $numrows<br>";

// add onto the query
$query .= " ORDER BY thumbid DESC LIMIT 0,10";

if (!($result = @ mysql_query ($query, $dbh)))
  die("Cannot execute query against the database");

// output test data
$numrows = mysql_num_rows($result);

// print out what we got
echo "<br>Limit Count: $numrows<br>";
echo "<br>Limit Array<br>";
while ($row = @ mysql_fetch_array($result))
  {
  $fname = $row["filename"];
  $thumb = $row["thumbid"];
  $page = $row["pageno"];
  echo "Filename - $fname   Page - $page  Thumb - $thumb<br>";
  }
?>

YappyDog

This one does not work because you execute the query before you add on the ORDER BY and the LIMIT part of the query.

The Routine with the LIKE:

$sqlquery = "SELECT * FROM guestbook WHERE guest_message LIKE '%$query%'";

$row = mysql_query($sqlquery) or die(mysql_error());
$num_rows = mysql_num_rows($row) or die(mysql_error());
$num_pages = $num_rows / $numperpage;

$sqlquery .= " ORDER BY id DESC LIMIT 0,10";
$row = mysql_query($sqlquery) or die($sqlquery. " ". mysql_error());

trooper

Well spotted YappyDog - now come and tell me whats wrong with all the code i've ever written (LOL)

BeastRider

Originally posted by trooper
Well spotted YappyDog - now come and tell me whats wrong with all the code i've ever written (LOL)

Well... not quite... you'll notice that after he appends the ORDER BY and LIMIT clauses he executes another query... that one should return 10 rows.

I agree that I can't see why someone would write code like that, but I duplicated his code against my database (earlier this thread)... the first query gets all the records matching the like, the second gets 10.

proportionality

i know my code isnt well written...

but it is very special to me.

trooper

aaahhh sweet 😃

proportionality

What is a better way to handle this situation? I need to know both how many search results there are, and be able to start and end where i need to.

trooper

What version of mysql u using ?

BeastRider

Do a SELECT COUNT as totrows on the first query. Use the same selection criteria. MySql attempts to get a total count based upon index information first, only then will it look at the records. Either way, all it returns is the count so you don't have the overhead of moving records you won't use to a result array.