MySQL SELECT rows between two values

Question

I need to select data from a mysql database from the past 12 months based on the current date. I have the dates saved in the database as unix timestamps but my query does not seem to work.

$query="SELECT user_id, COUNT(first_name) AS member_count
FROM main_user
WHERE renew_date<'$time' AND renew_date>'$old_time' WHERE renew_date!=''";

Basically I need to count all instances of first_name where there is a renew_date timestamp.

even your variable names says us that DATE cant be compared with TIME. — Stranger
– Stranger, Commented Dec 5, 2012 at 15:50

jeroen · Accepted Answer · 2012-12-05 15:51:28Z

5

You have an additional WHERE where you should use AND:

$query="SELECT user_id, COUNT(first_name) AS member_count
FROM main_user
WHERE renew_date<'$time' AND renew_date>'$old_time' AND renew_date!=''";
                                                    ^^^

answered Dec 5, 2012 at 15:51

jeroen

91.8k22 gold badges118 silver badges132 bronze badges

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

Comments

m4t1t0 · Accepted Answer · 2012-12-05 15:51:34Z

3

You have an error in your query, you have two WHERE clauses!

answered Dec 5, 2012 at 15:51

m4t1t0

5,7313 gold badges25 silver badges30 bronze badges

Comments

Olaf Dietsche · Accepted Answer · 2012-12-05 15:58:30Z

1

You can find this and other errors, when you test the return value from your query

$query = 'select ...';
$result = $mysqli->query($query);
if ($result === false) {
    // error handling
    echo $mysqli->error;
} else {
    // query successful
    // process result set
}

answered Dec 5, 2012 at 15:58

Olaf Dietsche

74.4k9 gold badges113 silver badges214 bronze badges

Comments

Shiplu Mokaddim · Accepted Answer · 2012-12-05 16:00:35Z

1

You put WHERE twice. You can use From_UNIXTIME function in mysql

WHERE FROM_UNIXTIME(renew_date)<NOW() 
    AND FROM_UNIXTIME(renew_date)> (NOW()-INTERVAL 1 year)
    AND renew_date !=''

edited Dec 5, 2012 at 16:00

answered Dec 5, 2012 at 15:54

Shiplu Mokaddim

57.5k20 gold badges147 silver badges193 bronze badges

Collectives™ on Stack Overflow

MySQL SELECT rows between two values

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related