mysql_query returns an empty resultset, even when query is valid

Question

Hi!

When I execute the following code in a php script. The result set is always empty. However, when I execute the same query in mysql console or in phpmyadmin,it rightfully shows 18 rows in the resultset. What could be going wrong here.

$result_set = mysql_query("SELECT * FROM categories WHERE 1 ", $this->database_handle);

var_dump($this->database_handle);
var_dump($result_set);
var_dump(mysql_num_rows($result_set));

-- the output is

resource(42) of type (mysql link)
resource(48) of type (mysql result) 
int(0) bool(false) 

However, when I try and insert data into this table, insertion is flawless.

As suggested in some of the previous responses I have already tried multiple combinations like

$result_set = mysql_query("SELECT * FROM categories WHERE 1=1 ", $this->database_handle);
$result_set = mysql_query("SELECT * FROM categories", $this->database_handle);
$result_set = mysql_query("select category_id, name from `categories` where `name` like 'cat-1'", $this->database_handle);
$result_set = mysql_query("SELECT category_id,name FROM categories WHERE 1 ", $this->database_handle);

--

structure of table

Field            Type           Null    Default Comments
category_id  int(11)    No       
name             varchar(255)   No               

Any help is much appreciated.

Answer 1

Change your query to

"SELECT * FROM categories WHERE 1=1 "

Answer 2

Hey,

The problem was because of a '\n'in the query string which I was putting in because I wanted to use the same string both for query as well as dumping it onto the screen.

It started working fine as soon as i removed the \n character from the string.

Funny part though, was php mysql library behaved really weirdly in this case. There was no warning or error.

Regards Nitin