Page 2 of 3

Re: MySQL query - finds somethings, but all everything!

Posted: Thu Aug 27, 2009 2:00 pm
by simonmlewis
Sorry, may I ask what that means please?

Re: MySQL query - finds somethings, but all everything!

Posted: Thu Aug 27, 2009 2:03 pm
by simonmlewis
Sorry, I think you mean in my statement.

They are for page numbers, and always start at '1'.

Re: MySQL query - finds somethings, but all everything!

Posted: Thu Aug 27, 2009 2:06 pm
by SpankMarvin
Shouldn't the second limit value start at zero? This might be why it's always skipping the first value.

Re: MySQL query - finds somethings, but all everything!

Posted: Thu Aug 27, 2009 2:07 pm
by simonmlewis

Code: Select all

$rowsPerPage = 20;
// by default we show first page
$pageNum = 1;
 
// if $_GET['pagenum'] defined, use it as page number
if(isset($_GET['pagenum']))
{
    $pageNum = $_GET['pagenum'];
}
 
// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;
    
echo "<table width='100%' cellpadding='0' cellspacing='0' class='table'>";
$result = mysql_query ("SELECT * FROM products WHERE MATCH(title, description) AGAINST('$search') pause = 'off' or id = '$search' LIMIT $offset, $rowsPerPage") or die (mysql_error());

Re: MySQL query - finds somethings, but all everything!

Posted: Thu Aug 27, 2009 2:16 pm
by SpankMarvin
simonmlewis wrote:

Code: Select all

$rowsPerPage = 20;
// by default we show first page
$pageNum = 1;
 
// if $_GET['pagenum'] defined, use it as page number
if(isset($_GET['pagenum']))
{
    $pageNum = $_GET['pagenum'];
}
 
// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;
    
echo "<table width='100%' cellpadding='0' cellspacing='0' class='table'>";
$result = mysql_query ("SELECT * FROM products WHERE MATCH(title, description) AGAINST('$search') pause = 'off' or id = '$search' LIMIT $offset, $rowsPerPage") or die (mysql_error());

I'm sorry I meant the first number, but I see what you're trying to do.

I wonder if the offset values are wrong? I.e. this looks like it'll work for pagenum being 1, but might be 1 out for all subsequent page numbers.

pagenum is 1, offset is 0. Good
pagenum is 2, offset is 20. Should be 19?
pagenum is 3, offset is 40. Should be 39?

Would this work?

Code: Select all

// counting the offset
if ($pageNum == 1){
$offset = ($pageNum - 1) * $rowsPerPage;
} else {
$offset = (($pageNum - 1) * $rowsPerPage) - 1;
}
 
Sorry if I'm getting the wrong end of the stick here.

Re: MySQL query - finds somethings, but all everything!

Posted: Thu Aug 27, 2009 2:19 pm
by simonmlewis
Mmmmm I think you are.
For some reason this DID work once before using this page numbering code.

In fact I have it working on an 'Advanced Search' page.

It's just that here it will not work if I enter something specific.

Now for something even odder. I have 4 'Ferrari' products here. One of a F430. If I type in "Ferrari", it will find THREE of the product, and ignore the F430.

:banghead:

Re: MySQL query - finds somethings, but all everything!

Posted: Thu Aug 27, 2009 2:25 pm
by SpankMarvin
simonmlewis wrote:Mmmmm I think you are.
For some reason this DID work once before using this page numbering code.

In fact I have it working on an 'Advanced Search' page.

It's just that here it will not work if I enter something specific.

Now for something even odder. I have 4 'Ferrari' products here. One of a F430. If I type in "Ferrari", it will find THREE of the product, and ignore the F430.

:banghead:
This has to be an issue with the limit values, somehow. If you're entering a specific value it returns nothing, a like value and you get all but one.

Try entering concrete values for the limit values, instead of the variables, and check whether you still get the errors? E.g. 0, 20

Re: MySQL query - finds somethings, but all everything!

Posted: Thu Aug 27, 2009 2:27 pm
by simonmlewis
What do you mean, "try entering concrete values"?
I am happy to change the pagenums if that helps, but not sure how you mean.

Re: MySQL query - finds somethings, but all everything!

Posted: Thu Aug 27, 2009 2:29 pm
by simonmlewis
This is the full script for the first half of the page (ignoring the actual page numbering for now)

Code: Select all

<?php 
if(isset($_POST['search']))
{
    $search = $_POST['search'];
    $_SESSION['search']=$search;
}
include "dbconn.php";
echo "<div class='head'>Search for $search</div>";
 
$rowsPerPage = 20;
// by default we show first page
$pageNum = 1;
 
// if $_GET['pagenum'] defined, use it as page number
if(isset($_GET['pagenum']))
{
    $pageNum = $_GET['pagenum'];
}
 
// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;
    
echo "<table width='100%' cellpadding='0' cellspacing='0' class='table'>";
$result = mysql_query ("SELECT * FROM products WHERE MATCH(title, description) AGAINST('$search') pause = 'off' or id = '$search' LIMIT $offset, $rowsPerPage") or die (mysql_error());
 
if (mysql_num_rows($result)==0) { echo "<tr><td><div class='sectionhead'>Sorry, there are no results for $search.</div></td></tr>"; }
else {
 
while ($row = mysql_fetch_object($result))
      while ($row = mysql_fetch_object($result)) {
    echo "
<div class='cat_prodlistbox'>
<div class='cat_producttitle'>";
 
                    $position=43; //Defines how many characters will be displayed from content field.
$postcontent = substr($row->title,0,$position);
echo "$postcontent ...</div>£$row->price<br/>";
if ($row->photoprimary == NULL) { echo "<a href='index.php?page=product&menu=categ&category=$row->category&product=$row->id&head=$row->title' title='Look at the $row->title'><img src='images/blank.gif' border='0' /></a>";}
            elseif ($row->photoprimary != NULL) { echo"
            <a href='index.php?page=product&menu=categ&category=$row->category&product=$row->id&head=$row->title' title='Look at the $row->title'><img src='images/productphotos/$row->photoprimary' border='0' /></a>";}
echo "</div>
                    
            ";
            }}          
    mysql_free_result($result);
    echo "</table>";
If I enter "Ferrari", it does not find all Ferraris.

Re: MySQL query - finds somethings, but all everything!

Posted: Thu Aug 27, 2009 2:32 pm
by SpankMarvin
I mean instead of entering

Code: Select all

$result = mysql_query ("SELECT * FROM products WHERE MATCH(title, description) AGAINST('$search') pause = 'off' or id = '$search' LIMIT $offset, $rowsPerPage") or die (mysql_error());
you enter something like

Code: Select all

$result = mysql_query ("SELECT * FROM products WHERE MATCH(title, description) AGAINST('$search') pause = 'off' or id = '$search' LIMIT 0, 20") or die (mysql_error());
(note replacing your final variables for the limit values with explicit integers, for testing purposes)

Re: MySQL query - finds somethings, but all everything!

Posted: Thu Aug 27, 2009 2:36 pm
by simonmlewis
Error report:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'pause = 'off' or id = 'ferrari' LIMIT 0, 20' at line 1
I tried this locally in the MySQL db too and it produced no results:

Code: Select all

SELECT * FROM products WHERE title = '%ferrari%' OR description = '%ferrari%' AND pause = 'off' or id = '%ferrari%'

Re: MySQL query - finds somethings, but all everything!

Posted: Thu Aug 27, 2009 2:40 pm
by SpankMarvin
simonmlewis wrote:Error report:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'pause = 'off' or id = 'ferrari' LIMIT 0, 20' at line 1
I tried this locally in the MySQL db too and it produced no results:

Code: Select all

SELECT * FROM products WHERE title = '%ferrari%' OR description = '%ferrari%' AND pause = 'off' or id = '%ferrari%'
Can you try the SQL query without the % if you are using =

or use like?

Re: MySQL query - finds somethings, but all everything!

Posted: Thu Aug 27, 2009 2:45 pm
by simonmlewis

Code: Select all

SELECT *
FROM products
WHERE title LIKE '%ferrari%'
OR description LIKE '%ferrari%'
AND pause = 'off'
OR id = 'ferrari'
produced 3 results.

Then using the same, with the offset

Code: Select all

$result = mysql_query ("SELECT * FROM products WHERE title LIKE '%$search%' OR description LIKE '%$search%' AND pause = 'off' or id = '$search' LIMIT 0, 20") or die (mysql_error());
Produced just 2 results.

Re: MySQL query - finds somethings, but all everything!

Posted: Thu Aug 27, 2009 2:49 pm
by simonmlewis
correction:

Code: Select all

SELECT *
FROM products
WHERE title LIKE '%ferrari%'
OR description LIKE '%ferrari%'
AND pause = 'off'
OR id = 'ferrari'
LIMIT 0 , 20
produced THREE results in the local DB (not via the web site).

Re: MySQL query - finds somethings, but all everything!

Posted: Thu Aug 27, 2009 2:56 pm
by simonmlewis

Code: Select all

while ($row = mysql_fetch_object($result))
Think I have cracked it.
If you look at the code I sent in full, you will find this line TWICE!
Why that's blocks out the first result always, I don't know.

But it has, and I think it's now fine.