-4

I want to count all the posts from the user, from the last hour. This is my code:

$all_user_recent_posts=mysql_query("select * from user_post where user_id=$userid and where post_time >= DATE_SUB(NOW(),INTERVAL 1 HOUR);");
$count_user_recent_posts=mysql_num_rows($all_user_recent_posts);

This code doesn't work(it has the value 0). But when I delete and where post_time >= DATE_SUB(NOW(),INTERVAL 1 HOUR), it does work, but it show me ALL the post of the user, and not posts from the last hour. Code: 

$all_user_recent_posts=mysql_query("select * from user_post where user_id=$userid;");
$count_user_recent_posts=mysql_num_rows($all_user_recent_posts);
Jay Blanchard
  • 34,243
  • 16
  • 77
  • 119
jaldk
  • 123
  • 1
  • 7
  • Because there are no posts in an hour interval? – u_mulder Mar 10 '16 at 18:45
  • I post every few seconds :P – jaldk Mar 10 '16 at 18:46
  • 4
    *ahem* `where user_id=$userid and where post_time` it's a syntax error. `where user_id=$userid and post_time` the where clause http://dev.mysql.com/doc/en/where-optimizations.html uses ONE where, not multiple and seperated by `AND` or `OR`. http://php.net/manual/en/function.mysql-error.php would have told you about it too. @jaldk – Funk Forty Niner Mar 10 '16 at 18:48
  • 1
    I would suspect that `mysql_num_rows()` is throwing an error, since `$all_user_recent_posts` is `false`. If you're not seeing that, you may want to turn on error reporting and logging. Additionally, you should always check if the SQL query is resulting in an error before trying to use the result. – David Mar 10 '16 at 18:51
  • @Fred-ii- How can I upvote your second comment 300 times :P – xQbert Mar 10 '16 at 18:51
  • @xQbert ah... that "one" counts enough for me *cheers* – Funk Forty Niner Mar 10 '16 at 18:52
  • @Fred-ii-: You certainly could have posted it as an answer. Rep farming is ok in moderation :) – David Mar 10 '16 at 18:52
  • 1
    [Your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) – Jay Blanchard Mar 10 '16 at 18:53
  • 1
    Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). – Jay Blanchard Mar 10 '16 at 18:54
  • @David True but now it got a downvote for the wiki I posted. Good thing huh? ;-) (wiki). and wiki has no rep gain/loss. – Funk Forty Niner Mar 10 '16 at 18:58
  • What is the column format for `post_time`? – Jay Blanchard Mar 10 '16 at 19:25
  • also, if you want to count posts, why not use `COUNT()`? what is the present output you're getting now and could you provide us with the db schema/structure? – Funk Forty Niner Mar 10 '16 at 19:29
  • also, if there are any errors and you're not checking for them, add error reporting to the top of your file(s) right after your opening PHP tag for example ` – Funk Forty Niner Mar 10 '16 at 19:32

2 Answers2

3

As I stated in comments, and posting this as a community wiki because nothing should be gained from this in regards to rep points.

where user_id=$userid and where post_time it's a syntax error.

where user_id=$userid and post_time the where clause uses ONE where, not multiple and seperated by AND or OR.

Reference:

and error checking against your query would have told you about it too.

and should also be checked against your query, should there be any there also.

Add or die(mysql_error()) to mysql_query()

Error reporting is an additional tool you can use.

Additionally, the post_time column must be a valid MySQL date type.

If you're trying to do math on a varchar type, then it won't work, or anything that isn't a valid date-related type.

Consult:

It is unknown as to how you're using mysql_num_rows().

The following two examples will react differently.

if(mysql_num_rows($result) > 0)

and

if(mysql_num_rows($result) == 1)
Funk Forty Niner
  • 74,450
  • 15
  • 68
  • 141
0

You have an extra where in your query near

and where post_time >= DATE_SUB(NOW(),INTERVAL 1 HOUR);

Replace your with this

$all_user_recent_posts=mysql_query("select * from user_post where user_id=$userid and post_time >= DATE_SUB(NOW(),INTERVAL 1 HOUR);");

Hope that helps :-)

Yousaf Hassan
  • 494
  • 4
  • 14