PHP integer variable in mySQL query

Question

I'm trying to input a PHP variable (in this case $beg) into a mySQL query but it returns an empty array result. The type of the field in the database is an integer. When I type in an actual value instead of the variable I get the correct result. What's wrong?

    $beg = time()-5000;
    settype($beg, "integer");

    $result = mysql_query('SELECT * FROM records WHERE time>=$beg ORDER BY time ASC');

    $statusdata = array();
    while ($row = mysql_fetch_array($result)) {
    array_push($statusdata, $row["status"]);
    }

you shouldn't be mixing php and mysql time values. there's subtle race conditions that can occurl. mysql is perfectly capable of doing its own date math: where time >= unix_timestamp(now() - interval 5000 second) — Marc B
– Marc B, Commented May 28, 2014 at 15:03
For that I would set the field as a timestamp in the DB right? — Ben
– Ben, Commented May 28, 2014 at 15:08
you're already going integer comparisons, so I'm assuming the time field is an int. if it's a datetime, then you don't need the timestamp stuff at all. time >= now() - interval 5000 second — Marc B
– Marc B, Commented May 28, 2014 at 15:09

Dylan · Accepted Answer · 2014-05-28 14:49:48Z

5

Make sure you use double quotes when using $variables inside the string.

    $result = mysql_query("SELECT * FROM records WHERE time>= $beg ORDER BY time ASC");

answered May 28, 2014 at 14:49

Dylan

1,4121 gold badge12 silver badges13 bronze badges

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

1 Comment

Niet the Dark Absol Over a year ago

Or better, use string concatenation. Or better still, don't use mysql_* function and use prepared queries instead

Pipala · Accepted Answer · 2014-05-28 15:04:55Z

2

You should use prepared statements instead of mysql_query.

$beg = time()-5000;
settype($beg, "integer");

$db = new mysqli("host","user","pw","database");
$stmt = $db->prepare("SELECT status FROM records WHERE time>=? ORDER BY time ASC");
$stmt->bind_param('i', $beg);
$stmt->execute();

$stmt->store_result();
$stmt->bind_result($status);

$statusdata = array();
while($stmt->fetch())
{
  array_push($statusdata, $status);
}

$stmt->close();

edited May 28, 2014 at 15:04

answered May 28, 2014 at 14:58

Pipala

1972 silver badges10 bronze badges

2 Comments

Ben Over a year ago

I'm not quite there yet still learning the basics but thanks, really nice example.

Pipala Over a year ago

Using prepared statements prevents you from SQL injections that you can face using mysql_query which is deprecated. You should use pdo or mysqli instead.

Ivan Cachicatari · Accepted Answer · 2014-05-28 14:51:03Z

0

Change the line

$result = mysql_query("SELECT * FROM records WHERE time>=$beg ORDER BY time ASC");

You must use double quote strings to put variables.

answered May 28, 2014 at 14:51

Ivan Cachicatari

4,3143 gold badges24 silver badges41 bronze badges

Comments

Emilio Gort · Accepted Answer · 2014-05-28 14:52:02Z

0

Change your query

$result = mysql_query(" SELECT * FROM records WHERE time >= $beg ORDER BY time ASC ");

You cannot use variable inside single quotes.

edited May 28, 2014 at 14:52

Emilio Gort

3,4553 gold badges32 silver badges44 bronze badges

answered May 28, 2014 at 14:51

Purushottam zende

5521 gold badge6 silver badges20 bronze badges

Comments

c42759 · Accepted Answer · 2014-05-28 14:55:14Z

0

try this method, I use a lot:

$beg = time() - 5000;
$query = sprintf("SELECT * FROM %s WHERE time >= '%o' ORDER BY %s ASC", "records", $beg, "time");
$result = mysql_query($query);

remeber, time() result is Integer, you don't need set him in to an Integer

answered May 28, 2014 at 14:55

c42759

193 bronze badges

Collectives™ on Stack Overflow

PHP integer variable in mySQL query

5 Answers 5

1 Comment

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related