I am trying to create a loop that will show the matches for the dates in the database and sort the matches into blocks based on the date.
I cannt seem to get this working, what am I doing wrong?
PHP Code:
mysql_select_db($database_db, $db);
$query_match_fixturesD1 = "select m.match_id, date_format(m.date, '%W %D %M %Y') as mDate, m.time, t1.division, m.report, t1.team_name as team1_name, s1.score as score1, t2.team_name as team2_name, s2.score as score2, v.venue_name, r.fname, r.sname
from matches m left join (matchscores s1 left join team t1 on t1.team_id = s1.team) on (s1.match_id = m.match_id) left join (matchscores s2 left join team t2 on t2.team_id = s2.team) on (s2.match_id = m.match_id)
LEFT JOIN referee r ON r.ref_id = m.referee_id LEFT JOIN venue v ON v.venue_id = m.venue_id
where s1.team <> s2.team AND t1.division ='D1' AND t2.division = 'D1'
group by match_id
order by m.match_id LIMIT 5";
$match_fixturesD1 = mysql_query($query_match_fixturesD1, $db) or die(mysql_error());
$row_match_fixturesD1 = mysql_fetch_assoc($match_fixturesD1);
$totalRows_match_fixturesD1 = mysql_num_rows($match_fixturesD1);
mysql_select_db($database_db, $db);
$query_match_dateD1 = "select matches.match_id, date_format(date, '%W %D %M %Y') as dDate
from matches LEFT JOIN matchscores ON matches.match_id = matchscores.match_id LEFT JOIN team ON matchscores.team = team.team_id
where date = '$row_match_fixturesD1[date]' AND team.division = 'D1'
order by matches.match_id";
$match_dateD1 = mysql_query($query_match_dateD1, $db) or die(mysql_error());
$row_match_dateD1 = mysql_fetch_assoc($match_dateD1);
$totalRows_match_dateD1 = mysql_num_rows($match_dateD1);
No just from 1 giant query, but I have two sets of output i need.
I have tried from examples online but its not working, can you please take a look at the code for me.
Your code is currently executing 2 queries which produce 2 different result sets. Both are being retrieved from the same table which is why I asked if your queries needed combining so you are only working with a single result set. What exactly are you trying to retrieve from the database and what does your schema look like?
I need to display a set of tables with the Date of the matches
Then under that a table with all the matches for that date.
The there can only be matches played on that date
I need a text and previous buttons to move to the next date if thats possible if not then all the matches for the dates in the database must be on one page.
I have been tried for days and weeks now to get this done.
I have changed the site today but the GREEN heading should show the date and the matches should be under it/
This is the site currently: the page
Then you would build your query with "SELECT fields FROM tables & joins WHERE any clause GROUP BY date field ORDER BY date field". Does that make sense?
Basically you need to check with each loop whether the date field is the same as the previous loop. If not, you start a new table. There is no need for multiple loops or result sets like you have in the code you've posted.
You are still executing 2 separate queries for no reason and have a second loop which is not needed...I don't know to explain any clearer without coding it for you.
Psuedocode:
Query with 1 result here (using order by or group by as needed)
set your check date variable as null
loop here
is check date the same?
yes, continue existing table
no, start a new table
set check date variable to current field value
end loop
Reply With Quote
Bookmarks