Simple MySQL Query not working - anyone know why?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Simple MySQL Query not working - anyone know why?

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Darhazer
DevNet Resident
Posts: 1011
Joined: Thu May 14, 2009 3:00 pm
Location: HellCity, Bulgaria

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

Post 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()
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post by simonmlewis »

It does work without the pause or deleted parts of the query.
But those fields are used correctly.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Darhazer
DevNet Resident
Posts: 1011
Joined: Thu May 14, 2009 3:00 pm
Location: HellCity, Bulgaria

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

Post 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?
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post by simonmlewis »

Not quite sure what you mean.
without pause or deleted, it does work.
They are both 'varchar' fields.
Love PHP. Love CSS. Love learning new tricks too.
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: Simple MySQL Query not working - anyone know why?

Post 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??
Love PHP. Love CSS. Love learning new tricks too.
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: Simple MySQL Query not working - anyone know why?

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
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: Simple MySQL Query not working - anyone know why?

Post 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?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply