Join

[timmah1]

Can somebody help me as to why I'm not getting any results?

$today = date("Y-m-d");

$week = date("Y-m-d", strtotime("+7 day"));

 

$sql = "SELECT * FROM schedule as t1

inner join 

teams as t2

on t1.schedule_home=t2.team_id

inner join

team as t3

on t1.schedule_away=t3.teams_id 

inner join

schedule as t4

on t4.schedule.date > '$today' WHERE t1.schedule.date < '$week' ";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
echo 'Results';
} else {
echo 'No Upcoming Games';
}

I'm trying to get all games that are greater than today's date, but  less than 7 days from now

[ginerjm]

And WHAT happens? 

 

BTW - is it 'teams_id' or 'team_id'?

[Barand]

 

Can somebody help me as to why I'm not getting any results?

Probably because of query errors.

 

Check what $conn->error contains.

 

Are you sure about the last join and the where condition?

Edited August 27, 2016 by Barand

[timmah1]

Yes, it is teams_id and team_id, those are correct.

 

This always returns nothing, no games found, but there are games, 8 of them, on September 2nd.

 

It works if i don't try to grab dates in between 2 dates, so that's why I'm not sure what is wrong.

[timmah1]

Im getting the same results doing this

 

$sql = "SELECT * FROM schedule as t1
inner join 
teams as t2
on t1.schedule_home=t2.team_id
inner join
team as t3
on t1.schedule_away=t3.teams_id WHERE t4.schedule.date > '$today' OR t1.schedule.date < '$week' ";

[Barand]

on t4.schedule.date > '$today' WHERE t1.schedule.date < '$week' ";

              ^                                 ^

              |                                 | 

[timmah1]

Sorry, I noticed that after I posted it

 

I have this

 

$sql = "SELECT * FROM schedule as t1
inner join 
teams as t2
on t1.schedule_home=t2.team_id
inner join
team as t3
on t1.schedule_away=t3.teams_id WHERE t1.schedule.date > '$today' OR t1.schedule.date < '$week' ";

[Barand]

Why the join to schedule t4?

 

Why not just

WHERE t1.schedule_date > '$today' AND t1.schedule_date < '$week'

And you should be using a prepared statement instead of dropping values into the query.

[Barand]

BTW, have you checked for any error messages yet?

[timmah1]

If was as simple as changing OR to AND?

Wow.

Thanks for your help, its working now

[Barand]

No - I think your problem was probably the "." in schedule.date.

 

But checking error messages would confirm.