MySQL query - finds somethings, but all everything!
Moderator: General Moderators
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: MySQL query - finds somethings, but all everything!
Sorry, may I ask what that means please?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: MySQL query - finds somethings, but all everything!
Sorry, I think you mean in my statement.
They are for page numbers, and always start at '1'.
They are for page numbers, and always start at '1'.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
-
SpankMarvin
- Forum Newbie
- Posts: 17
- Joined: Mon Jul 27, 2009 1:08 am
Re: MySQL query - finds somethings, but all everything!
Shouldn't the second limit value start at zero? This might be why it's always skipping the first value.
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: MySQL query - finds somethings, but all everything!
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());Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
-
SpankMarvin
- Forum Newbie
- Posts: 17
- Joined: Mon Jul 27, 2009 1:08 am
Re: MySQL query - finds somethings, but all everything!
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;
}
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: MySQL query - finds somethings, but all everything!
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.

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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
-
SpankMarvin
- Forum Newbie
- Posts: 17
- Joined: Mon Jul 27, 2009 1:08 am
Re: MySQL query - finds somethings, but all everything!
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.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.
Try entering concrete values for the limit values, instead of the variables, and check whether you still get the errors? E.g. 0, 20
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: MySQL query - finds somethings, but all everything!
What do you mean, "try entering concrete values"?
I am happy to change the pagenums if that helps, but not sure how you mean.
I am happy to change the pagenums if that helps, but not sure how you mean.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: MySQL query - finds somethings, but all everything!
This is the full script for the first half of the page (ignoring the actual page numbering for now)
If I enter "Ferrari", it does not find all Ferraris.
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>";Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
-
SpankMarvin
- Forum Newbie
- Posts: 17
- Joined: Mon Jul 27, 2009 1:08 am
Re: MySQL query - finds somethings, but all everything!
I mean instead of entering
you enter something like
(note replacing your final variables for the limit values with explicit integers, for testing purposes)
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());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());-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: MySQL query - finds somethings, but all everything!
Error report:
I tried this locally in the MySQL db too and it produced no results: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
Code: Select all
SELECT * FROM products WHERE title = '%ferrari%' OR description = '%ferrari%' AND pause = 'off' or id = '%ferrari%'Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
-
SpankMarvin
- Forum Newbie
- Posts: 17
- Joined: Mon Jul 27, 2009 1:08 am
Re: MySQL query - finds somethings, but all everything!
Can you try the SQL query without the % if you are using =simonmlewis wrote:Error report:I tried this locally in the MySQL db too and it produced no results: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
Code: Select all
SELECT * FROM products WHERE title = '%ferrari%' OR description = '%ferrari%' AND pause = 'off' or id = '%ferrari%'
or use like?
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: MySQL query - finds somethings, but all everything!
Code: Select all
SELECT *
FROM products
WHERE title LIKE '%ferrari%'
OR description LIKE '%ferrari%'
AND pause = 'off'
OR id = 'ferrari'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());Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: MySQL query - finds somethings, but all everything!
correction:
produced THREE results in the local DB (not via the web site).
Code: Select all
SELECT *
FROM products
WHERE title LIKE '%ferrari%'
OR description LIKE '%ferrari%'
AND pause = 'off'
OR id = 'ferrari'
LIMIT 0 , 20Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: MySQL query - finds somethings, but all everything!
Code: Select all
while ($row = mysql_fetch_object($result))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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.