Querying Events From MySQL db, in 2 tables

[mb1]

Hi,

 

I am querying events for "today". So I have this code:

$query3 ="SELECT * FROM `mb_foursquare`.`eventsbydate` WHERE `mb_foursquare`.`eventsbydate`.`datetime1` BETWEEN DATE_ADD(CURDATE(), INTERVAL -1 DAY) AND DATE_ADD(CURDATE(), INTERVAL 2 DAY)";

 

It works ok (if there is a better way to do this, feel free to let me know). But then I also need to pull data from a different table. So I added the other query but somehow it is triggering an error. Not sure what could be wrong, could you please help me?

 

Here is the code that's triggering an error:

$query3 ="SELECT * FROM `mb_foursquare`.`eventsbydate` WHERE `mb_foursquare`.`eventsbydate`.`datetime1` BETWEEN DATE_ADD(CURDATE(), INTERVAL -1 DAY) AND DATE_ADD(CURDATE(), INTERVAL 2 DAY), `mb_foursquare`.`venues` WHERE eventsbydate.v_id = venues.v_id";

 

Thank you!

[tbare]

your second query has 2 WHERE statements... if you need to WHEREs, use WHERE a AND b...

[mb1]

Thank you. That's only because i am querying 2 tables.

 

I have this on one side:

`mb_foursquare`.`eventsbydate` WHERE `mb_foursquare`.`eventsbydate`.`datetime1` BETWEEN DATE_ADD(CURDATE(), INTERVAL -1 DAY) AND DATE_ADD(CURDATE(), INTERVAL 2 DAY)

 

And this on the other side:

`mb_foursquare`.`venues` WHERE eventsbydate.v_id = venues.v_id

 

 

[tbare]

Look into a LEFT JOIN.

 

I'm not exactly sure what you're looking to do, but here's an example of a left join:

 

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

[mb1]

Thank you again. Let me be a little more specific about what i want to do: I am selecting from my event table (eventsbydate) all the events that are happening today. Then I am also pulling data on all the venues from a different table (venues).

 

I tried this and it is still triggering an error...

 

SELECT * FROM `mb_foursquare`.`eventsbydate` 
WHERE `mb_foursquare`.`eventsbydate`.`datetime1` 
BETWEEN DATE_ADD(CURDATE(), INTERVAL -1 DAY) AND DATE_ADD(CURDATE(), INTERVAL 2 DAY) 
LEFT JOIN `mb_foursquare`.`venues` ON eventsbydate.v_id = venues.v_id

 

Any idea what the issue could be?

 

[tbare]

sorry i didn't reply back - long weekend - but not long enough to do too much on the computer.

 

I'll look a bit more into it, and get back to ya today. (unless someone else chimes in, too).

 

T

[mb1]

Thank you. Actually, I figured it out!

 

SELECT * FROM `mb_foursquare`.`eventsbydate`, `mb_foursquare`.`venues` WHERE DATE(datetime1) = DATE(CURDATE()) AND eventsbydate.v_id = venues.v_id

 

I had to put the FROM table 1, table 2 at the very beginning. and attach the WHENs with an AND.

[tbare]

great! glad you got it working!

 

Good luck on the rest of your project!