Page 1 of 1

Simple MySQL Query not working - anyone know why?

Posted: Tue Nov 30, 2010 8:09 am
by simonmlewis

Code: Select all

$result = mysql_query ("SELECT * FROM products WHERE (title LIKE '%$search%' OR description LIKE '%$search%' OR id = '$search') AND pause != 'on' AND deleted != 'yes' LIMIT $offset, $rowsPerPage") or die (mysql_error());
$search has the word "penguin" in it's variable. If we do a search for anything, even "tactical" which we know has products that should show up, it still produces nothing.

I've tried it with pause <> 'on' and that fails too.

I don't get errors, it just finds nothing. I even did this is the phpmyadmin screen, and that produced nothing.

Am I making some kind of silly mistake in the layout of this query. Any help would be appreciated.

Re: Simple MySQL Query not working - anyone know why?

Posted: Tue Nov 30, 2010 8:53 am
by Darhazer
Try the query without the ANDs to check if pause or deleted column is not the one because of which you don't receive results.

By the way, you HAVE TO escape your input! Use mysql_escape_string()

Re: Simple MySQL Query not working - anyone know why?

Posted: Tue Nov 30, 2010 9:00 am
by simonmlewis
It does work without the pause or deleted parts of the query.
But those fields are used correctly.

Re: Simple MySQL Query not working - anyone know why?

Posted: Tue Nov 30, 2010 11:13 am
by Darhazer
simonmlewis wrote:It does work without the pause or deleted parts of the query.
But those fields are used correctly.
Can you show the output of the query without pause and deleted parts from phpmyadmin?
What is the field type of those columns?

Re: Simple MySQL Query not working - anyone know why?

Posted: Tue Nov 30, 2010 11:18 am
by simonmlewis
Not quite sure what you mean.
without pause or deleted, it does work.
They are both 'varchar' fields.

Re: Simple MySQL Query not working - anyone know why?

Posted: Wed Dec 01, 2010 3:44 am
by simonmlewis
We got a result on this, but not a perfect one.
'deleted' field has been altered so it is either 'no' or 'yes'.

However, if you are searching for the word "car", and u expect Audi, Ford etc.... you also get "carry handles".

How do you alter the script so that 'carry' is noe part of the LIKE '%$search%' result??

Re: Simple MySQL Query not working - anyone know why?

Posted: Wed Dec 01, 2010 4:08 am
by simonmlewis

Code: Select all

$result = mysql_query ("SELECT * FROM products WHERE MATCH (title,description) AGAINST ('$search') AND pause = 'off' AND deleted = 'no' LIMIT $offset, $rowsPerPage") or die (mysql_error());
Edit Drop title FULLTEXT No No title 1
Edit Drop description FULLTEXT No No description 1

When I run this query it gives a result of:

[text]Can't find FULLTEXT index matching the column list
[/text]

Yet both fiends are FULLTEXT.

Re: Simple MySQL Query not working - anyone know why?

Posted: Wed Dec 01, 2010 8:04 am
by simonmlewis

Code: Select all

$result = mysql_query ("SELECT * FROM products WHERE pause = 'off' AND deleted = 'no' AND (title LIKE '% ".$search." %' OR description LIKE '% ".$search." %' OR id = '$search') LIMIT $offset, $rowsPerPage") or die (mysql_error());
I've been told this will query the fields and force a space either side of the $variable. So it looks only for whole words, not 'car' in 'carry'.

Yet I get no results. Or if I do is as

Code: Select all

$result = mysql_query ("SELECT * FROM trimex_products WHERE pause = 'off' AND deleted = 'no' AND (title LIKE "% ".$search." %" OR description LIKE "% ".$search." %" OR id = '$search') LIMIT $offset, $rowsPerPage") or die (mysql_error());
Then I get

[text]Warning: Division by zero in C:\xampp\phpMyAdmin\site\includes\search.inc on line 30

Warning: Division by zero in C:\xampp\phpMyAdmin\site\includes\search.inc on line 30

Warning: Division by zero in C:\xampp\phpMyAdmin\site\includes\search.inc on line 30

Warning: Division by zero in C:\xampp\phpMyAdmin\site\includes\search.inc on line 30
Query was empty
[/text]

I've never been that good at the dots between variables. So does anyone know what I've done wrong here please?