PHP - mySQL "like" query return nothing

Question

I know that this is same with a lot of other posts here but i can't figure it out!

my code looks like this:

$i=0;
$shelves = array();
$shelves['position'] = array();
$query = "select id, cat_id, book_title, writer, publisher, issue_year, copies, abstract from library where $table like '%$search_param%'";
$result = mysql_query($query);
while ( $data = mysql_fetch_assoc($result) ) {
   error_log($data['id']);
   $shelves['position'][$i]['id'] = $data['id'];
   $shelves['position'][$i]['cat_id'] = $data['cat_id'];
   $shelves['position'][$i]['book_title'] = $data['book_title'];
   $shelves['position'][$i]['writer'] = $data['writer'];
   $shelves['position'][$i]['publisher'] = $data['publisher'];
   $shelves['position'][$i]['issue_year'] = $data['issue_year'];
   $shelves['position'][$i]['copies'] = $data['copies'];
   $shelves['position'][$i]['abstract'] = $data['abstract'];
   ++$i;
}
error_log( count($shelves['position']) );

And because there are tones of other posts like this one i tried their solution:

$query = sprintf("select id, cat_id, book_title, writer, publisher, issue_year, copies, abstract from library where %s like'%%%s%'",mysql_real_escape_string($table),mysql_real_escape_string($search_param) );

Or Something like that:

$query = "select id, cat_id, book_title, writer, publisher, issue_year, copies, abstract from library where $table like '%{$search_param}%'";

I also tried run the query without dynamic variables just text and i got the same thing.

$query = "select id, cat_id, book_title, writer, publisher, issue_year, copies, abstract from library where book_title like '%lord%'";

Nothing seems to work.

I've tested my query on mysql workbench and it works like a charm!

In all of three queries i never get a log of the first error_log and the second one yells at me 0 every time!

Could someone light the way please?

Lets do something simple, first mysql_query($query) or die(mysql_error(); so we can see if there is any error. — DevZer0
– DevZer0, Commented Jul 11, 2013 at 3:58
@swapnesh the $table variable is holding a sting like book_title! — gs_it
– gs_it, Commented Jul 11, 2013 at 4:12
@GeorgeSfitis So it's blank? Could it be that nothing in the database matches the query? — Tom
– Tom, Commented Jul 11, 2013 at 4:15

LexLythius · Accepted Answer · 2013-07-11 04:44:27Z

2

Well, the only thing suspicious here is the charset/collation you're using, which might result in a case sensitiveness issue. Try forcing a non-sensitive collation and see what happens.

SET NAMES 'utf8' COLLATE 'utf8_general_ci';

A utf8_bin (or any *_bin for that matter) makes comparison case-sensitive. If setting the connection collation to insensitive works, that would explain the difference between your script and MySQL Workbench.

Anyway, I would set the column collation to case-insensitive to avoid this kind of issues.

edited Jul 11, 2013 at 4:44

answered Jul 11, 2013 at 4:14

LexLythius

1,9441 gold badge13 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

gs_it Over a year ago

My table collation is utf8 - utf8_bin. About the forcing thing can you give me an example i haven't done it till now! Thanks.

gs_it Over a year ago

Thanks that was my problem hope this helps other people!

naveen · Accepted Answer · 2013-07-11 04:04:42Z

0

The query getting correct answer.. 

Try this:

$i=0; // before start while loop,you need to initialize i

while ( $data = mysql_fetch_assoc($result) ) {
   error_log($data['id']);
   $shelves['position'][$i]['id'] = $data['id'];
   $shelves['position'][$i]['cat_id'] = $data['cat_id'];
   $shelves['position'][$i]['book_title'] = $data['book_title'];
   $shelves['position'][$i]['writer'] = $data['writer'];
   $shelves['position'][$i]['publisher'] = $data['publisher'];
   $shelves['position'][$i]['issue_year'] = $data['issue_year'];
   $shelves['position'][$i]['copies'] = $data['copies'];
   $shelves['position'][$i]['abstract'] = $data['abstract'];
   ++$i;
}

answered Jul 11, 2013 at 4:04

naveen

871 gold badge3 silver badges10 bronze badges

2 Comments

gs_it Over a year ago

Sorry about that i've got the line you mentioned i just forgot to put in on the question!

naveen Over a year ago

it's ok.. you try fetch array "while($data = mysql_fetch_array($result))"

Prabhakar Manthena · Accepted Answer · 2013-07-11 04:52:25Z

0

Try this it may help.

$query ="select id,
                cat_id, 
                book_title,
                writer, 
                publisher, 
                issue_year, 
                copies, 
                abstract 
                from library 
                where ".$table." like "%".$search_param."%";

But it is not tested yet.

answered Jul 11, 2013 at 4:52

Prabhakar Manthena

2,3234 gold badges18 silver badges31 bronze badges

Collectives™ on Stack Overflow

PHP - mySQL "like" query return nothing

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related