MySQL Query with UNIX_TIMESTAMP Not Working

Question

I am trying to see if a field (end_date) in a MySQL database in the form of 2015-6-11 21:28:02 is greater than the current time. Essentially, is that field in the future or not.

Here is my PHP Code:

$current_time = time();
$sql = "SELECT member_id, course_id, FROM ".TABLE_PREFIX."vbc_status WHERE end_date < NOW()";
$result = mysql_query($sql, $db);
while ($row = mysql_fetch_assoc($result)) {
    $echo $row['member_id'];
}

I keep getting an error:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /path/to/script.php on line 373.

Line 373 is the while ($row = mysql_fetch_assoc($result)) { part.

Any ideas of how this should be fixed?

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /path/to/my/script.php on line 373 — gtilflm
– gtilflm, Commented Jun 4, 2015 at 18:35
Ugh. You're using mysql_query, aren't you? You're calling that function incorrectly, wrong arguments, the query string itself is probably fine. Keep in mind a modern database driver like PDO is not hard to learn. A guide like PHP The Right Way can help explain best practices. — tadman
– tadman, Commented Jun 4, 2015 at 18:35
if you have a moment please read My Post about how to ask sql questions... it has a lot of good tips and tricks to help you ask a question. — John Ruddell
– John Ruddell, Commented Jun 4, 2015 at 18:37
Since your code is busted and not working, might as well do it correctly in PDO. It takes literally two lines to covert code like this. — tadman
– tadman, Commented Jun 4, 2015 at 18:39

tadman · Accepted Answer · 2015-06-04 18:34:48Z

3

Why not skip all that and just do this:

WHERE end_date < NOW()

Converting with UNIXTIME is extremely abusive on your database, it needs to evaluate that for every row in the table and cannot use indexes. The date field itself will evaluate very quickly if indexed.

answered Jun 4, 2015 at 18:34

tadman

212k23 gold badges237 silver badges266 bronze badges

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

8 Comments

gtilflm Over a year ago

Changed it to $sql = "SELECT member_id, course_id, FROM ".TABLE_PREFIX."vbc_status WHERE end_date < NOW()"; and I'm still getting the error.

tadman Over a year ago

Yes, because you're not calling the query function correctly. Read the mysql_query documentation to see how you're supposed to call it, as it requires a valid connection handle unless one has been established previously, and note the big red box.

John Ruddell Over a year ago

looks like you have an issue with how you are executing your query.. also echo $sql after you create it

gtilflm Over a year ago

So, SELECT member_id, course_id FROM AT_vbc_status` WHERE end_date > NOW()` works fine when I use this directly in PHP MyAdmin. However, this same logic isn't working when I put it in PHP. I'm also using similar logic later on in the code and it works fine. I'm going to update my original post with more code.

tadman Over a year ago

There's something awry in how you're calling that query, so that would help. Add your connection call with the passwords redacted, if applicable.

|

danielson317 · Accepted Answer · 2015-06-04 18:38:31Z

0

You can also set your variable format to same format used in the database.

define('DATETIME_SQL', 'Y-m-d H:i:s');
$current_time = date(DATETIME_SQL);
$sql = "SELECT member_id, course_id, FROM ".TABLE_PREFIX."vbc_status WHERE end_date < $current_time";

answered Jun 4, 2015 at 18:38

danielson317

3,3083 gold badges31 silver badges47 bronze badges

Comments

gtilflm · Accepted Answer · 2015-06-04 19:19:27Z

0

FOUND IT!!!

In $sql = "SELECT member_id, course_id, FROM ".TABLE_PREFIX."vbc_status WHERE end_date < NOW()"; there is an unnecessary , after course_id.

Stupid syntax.... :)

answered Jun 4, 2015 at 19:19

gtilflm

1,4854 gold badges25 silver badges56 bronze badges

Collectives™ on Stack Overflow

MySQL Query with UNIX_TIMESTAMP Not Working

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related