0

This query works fine on Sequel Pro:

SELECT t1.* FROM `erapido_messages` t1
LEFT OUTER JOIN `erapido_messages` t2 ON `t1.sender_id` = `t2.sender_id` 
AND (`t1.msg_date` < `t2.msg_date` OR `t1.msg_date` = `t2.msg_date` AND `t1.sender_id` != `t2.sender_id`)
WHERE `t2.sender_id` IS NULL AND `t1.sender_id`!= `0` AND `t1.receiver_id`= 28
ORDER BY `t1.msg_date` DESC;

When I use it on my php script it returns an error. This is the complete query in php:

$query = "SELECT t1.* FROM `erapido_messages` t1
LEFT OUTER JOIN `erapido_messages` t2 ON `t1.sender_id` = `t2.sender_id` 
AND (`t1.msg_date` < `t2.msg_date` OR `t1.msg_date` = `t2.msg_date` AND `t1.sender_id` != `t2.sender_id`)
WHERE `t2.sender_id` IS NULL AND `t1.sender_id`!= `0` AND `t1.receiver_id`= ?
ORDER BY `t1.msg_date` DESC";

//$sql is my connection and it works fine on other queries
$statement = $sql->prepare($query);

//bind parameters for markers: s = string, i = integer, d = double,  b = blob
$statement->bind_param('i', $receiver_id);//$receiver_id is defined

//execute query
$statement->execute();

//store the results; allows to count the rows
$statement->store_result();

//bind result variables
$statement->bind_result($id, $receiver_name, $receiver_img, $receiver_email, $sender_id, $sender_name, $sender_email, $sender_img, $subject, $message, $msg_date);

This is the error:

Fatal error: Call to a member function bind_param() on boolean in /messages.php on line 53

I understand that this statement may return 'false' if the query fails:

$statement = $sql->prepare($query);

However, I can't see what is wrong in the query. Any help is welcome!

Thanks much.

Gabriel Ferraz
  • 592
  • 6
  • 26
  • You need to check if `$statement` is false after running `$statement = $sql->prepare($query);` If it is false then you can look for errors with `echo $sql->error;`. It will give more idea about what is wrong. – seartun Sep 09 '15 at 02:26
  • @JayBlanchard Please read the comments on the code: //$sql is my connection and it works fine on other queries. I am using an include file where I make the connection. That is not the problem. – Gabriel Ferraz Sep 09 '15 at 03:34
  • The error is `Unknown column 't2.sender_id' in 'where clause'`. I was changing up the query to see if I would get different results and apparently none of the aliases are working properly. I got errors for `'t1.sender_id'` as well. – Gabriel Ferraz Sep 09 '15 at 03:37
  • 1
    You cannot back tick groups, you have to back tick the t2 and then the sender_id separately. That goes for each place you have combined the alias and a column name. – Jay Blanchard Sep 09 '15 at 03:39
  • @JayBlanchard that almost fixed it for me! After changing the back ticks I changed the query a bit and it is now working. Thanks! – Gabriel Ferraz Sep 09 '15 at 03:52

1 Answers1

-2

Your prepare statement is returning false due to not valid query string. Change your query like the one below. Currently you are escaping your column names because they are in back-tick which results in an error while preparing it

$query = "select * from `dbname` table where `table`.column= ?
          ORDER BY `table`.column DESC LIMIT 2 ";

That should fix this error.

Amit.S
  • 431
  • 2
  • 9