Mr Chris Posted June 5, 2007 Share Posted June 5, 2007 Working with a normalized database Hi all, Here’s my problem. What’s happening here is I’m working with a normalized database with 2 tables: results result_id home_team_id home_score away_score away_team_id teams team_id team_name so what I want to do is run two queries. So the first on the results table to get all the results from the database: So I can say: home_team_id 2-1 away_team_id Then for example if home_team_id=1 and away_team_id=2 I could run these two queries on the same form to find out the team_name: // Query to get the home team name $result2 = mysql_query("SELECT teams.team_name as away_teams FROM reports, teams WHERE reports.away_team_id = teams.team_id"); // Query to get the away team name $result2 = mysql_query("SELECT teams.team_name as home_teams FROM reports, teams WHERE reports.home_team_id = teams.team_id"); So this can’t be done using my code below? <?php // login stuff $msg = ""; if(isset($_POST['Submit'])){ $total = $_POST['total']; $repids = $_POST['rep_id']; foreach($repids as $id){ mysql_query("DELETE FROM reports WHERE report_id='$id'"); } $msg = count($repids) . " reports(s) deleted!"; } //This is my first query. I want to add another one, but don’t understand how! $result = mysql_query("SELECT teams.team_name as home_teams FROM reports, teams WHERE reports.home_team_id = teams.team_id"); $num = mysql_num_rows($result); $n = 0; ?> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title>Test</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> </head> <body> <?php echo $msg?> <br / > <form name="form1" method="post" action=""> <?php while($row = mysql_fetch_array($result)){ $n++; ?> <input type="checkbox" name="rep_id[]" id="rep_id[]" value="<?php echo $row['report_id'];?>"> <?php echo $row['home_teams'];?> - <?php echo $row['away_teams'];?> <br /> <?php } ?> <input type="submit" name="Submit" value="Delete" class="button"> <input name="total" type="hidden" id="total" value="<?php echo $n?>"> </form> </body> </html> Thanks Chris Quote Link to comment https://forums.phpfreaks.com/topic/54291-solved-working-with-a-normalized-database/ Share on other sites More sharing options...
trq Posted June 5, 2007 Share Posted June 5, 2007 One of the main points for using database normalization is to minimize queries by relating data. If you can explain little clearer exactly what it is you want, we may be able to help you write one query to achieve it. Quote Link to comment https://forums.phpfreaks.com/topic/54291-solved-working-with-a-normalized-database/#findComment-268434 Share on other sites More sharing options...
Mr Chris Posted June 5, 2007 Author Share Posted June 5, 2007 Thanks. Basically I have two tables as you can see. The football teams table contains teams with id's so for example it holds: team_id | team_name 1 | Harchester United 2 | Manchester United 3 | Arsenal Then in my results table each of these teams play each other: result_id | home_team_id | home_score | away_score | away_team_id 1 | 3 | 4 | 0 | 1 So in effect by referencing that against my teams table the result would be: Arsenal 4-0 Harchester United If you could help me with one single query for both that would be great. Many Thanks Chris Quote Link to comment https://forums.phpfreaks.com/topic/54291-solved-working-with-a-normalized-database/#findComment-268600 Share on other sites More sharing options...
Barand Posted June 5, 2007 Share Posted June 5, 2007 Join twice to the teams table, on the home teams id and on the away team id. Give the teams table a different alias for each join. SELECT th.team_name, ta.team_name, r.home_score, r.away_score FROM results r INNER JOIN teams th ON r.home_team_id = th.team_id INNER JOIN teams ta ON r.away_team_id = ta.team_id Quote Link to comment https://forums.phpfreaks.com/topic/54291-solved-working-with-a-normalized-database/#findComment-268706 Share on other sites More sharing options...
Mr Chris Posted June 6, 2007 Author Share Posted June 6, 2007 Perfect - thank you Quote Link to comment https://forums.phpfreaks.com/topic/54291-solved-working-with-a-normalized-database/#findComment-269131 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.