Re: MySQL query - finds somethings, but all everything!
Posted: Thu Aug 27, 2009 2:00 pm
Sorry, may I ask what that means please?
A community of PHP developers offering assistance, advice, discussion, and friendship.
http://forums.devnetwork.net/
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());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());
Code: Select all
// counting the offset
if ($pageNum == 1){
$offset = ($pageNum - 1) * $rowsPerPage;
} else {
$offset = (($pageNum - 1) * $rowsPerPage) - 1;
}
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.
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>";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());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%'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%'
Code: Select all
SELECT *
FROM products
WHERE title LIKE '%ferrari%'
OR description LIKE '%ferrari%'
AND pause = 'off'
OR id = 'ferrari'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());Code: Select all
SELECT *
FROM products
WHERE title LIKE '%ferrari%'
OR description LIKE '%ferrari%'
AND pause = 'off'
OR id = 'ferrari'
LIMIT 0 , 20Code: Select all
while ($row = mysql_fetch_object($result))