Android php mysql query

Question

I get a string from java: "2011-11-25 08:16:50" post_date column is TIMESTAMP But the following query gives no results. What is the error?

$date2 = $_POST['Date'];

$result= mysql_query("Select * FROM (Select * FROM user WHERE latitude > $minLat AND latitude < $maxLat AND longitude > $minLon AND longitude < $maxLon AND post_date > $date2 ORDER BY post_date DESC LIMIT $amount1) a ORDER BY post_id");

while($results = mysql_fetch_assoc($result)) 
    $output[]=$results;

print(json_encode($output));
mysql_close();

Can you clarify whether there is no output at all, or an empty JSON array? Either way, you are not doing any error checking. You need to do that after a mysql_query() call. Otherwise, your script will break if the query fails. How to do this is outlined in the manual on mysql_query() or in this reference question. — Pekka
– Pekka, Commented Nov 26, 2011 at 23:35
Also, the code you show is vulnerable to SQL injection. Use the proper sanitation method of your library (like mysql_real_escape_string() for the classic mysql library), or switch to PDO and prepared statements. — Pekka
– Pekka, Commented Nov 26, 2011 at 23:35
please edit the query to $result= mysql_query("Select * FROM (Select * FROM user WHERE latitude > $minLat AND latitude < $maxLat AND longitude > $minLon AND longitude < $maxLon AND post_date > $date2 ORDER BY post_date DESC LIMIT $amount1) a ORDER BY post_id") or die(mysql_error()); and give us the error I think this is a syntex error with mysql — Seder
– Seder, Commented Nov 26, 2011 at 23:36

Michael Berkowski · Accepted Answer · 2011-11-26 23:38:08Z

1

You must single-quote dates in MySQL. Additionally, since you are acquiring it directly from $_POST, you are advised to escape it with mysql_real_escape_string() first.

$result= mysql_query("
  Select * FROM (
    Select * FROM user 
    WHERE
      latitude > $minLat 
      AND latitude < $maxLat 
      AND longitude > $minLon 
      AND longitude < $maxLon 

      -- Surround $date2 with quotes
      AND post_date > '$date2'
    ORDER BY post_date DESC LIMIT $amount1) a
  ORDER BY post_id");

answered Nov 26, 2011 at 23:38

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

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

1 Comment

heav3n Over a year ago

duh...thanks a lot guys it was the quote...anyway..yes I know for the sql injection thanks again.

Collectives™ on Stack Overflow

Android php mysql query

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related