Query Help!

[dekrelo]

Hi all,I need help for modeling and query from database. I'm makeing sport startistic site and have problem with head-to-head section. I have 2 tables: 1. players (id, player, country, dob,....) and 2. matches (id, player1, score1, player2, score2, tournament, season......). I have two dynamic list menus with players. User choose two player and click some button. The result have to be matches between selected player. I work with mysql and dreamweave, I try to pass through URL and pass through Form Variable but no result. Now I try to make many-to-many relation between tables, but need help with ideas for realisation, please!

[real_illusions]

Welcome to the forum,Best way is to start off with the finished html (with no php or mysql), and then work out from the beginning, slowly, where all the php fits in, where it needs to go, where it comes from etc...Rather than piecing together both the html and php together giving you some headaches.If theres any specific bit of code you get stuck on, then post it here and we'll try and help you towards a solution.

[dekrelo]

OK, thanks!I have 3 tables: 1. players (player_id, player, country, dob.....)2. matches (matches_id, player1, score1, player2, score2, tournament, season....)3. playerMatches (player_id, matches_id)I have many-to-many relationship. I need to query all matches between whoever two selected from user players.That is my question? Thanks!

[student101]

I have many-to-many relationship. I need to query all matches between whoever two selected from user players.

Sorry I don't think I understand, what is it that you want to do?query all matches between whoever two selected from user playersDo you want to select two from user players?

[justsomeguy]

What do you use the playerMatches table for? If the player IDs are already in the matches table, what do you need the other table for?

[dekrelo]

In table players I put info for every player. In table matches I put all matches between players.

[student101]

In table players I put info for every player. In table matches I put all matches between players.

What do you want to retrieve / display?Please post your complete tables;

players (player_id, player, country, dob)matches (matches_id, player1, score1, player2, score2, tournament, season)

You need to show scores?You may need a third table called for scores, not sure - need to know what you want from these tables?

[dekrelo]

User have to select from 2 dynamic drop down menus 2 players and see matches between selected 2 players. I make query:SELECT m.name,m.name2,frame,frame2FROM matches_db m JOIN players_db a ON m.name=a.name JOIN players_db b ON m.name2=b.nameWHERE m.name IN (pl1,pl2) AND m.name2 IN (pl1,pl2)but now I have problems with passing two player names from drop down menu to detailed page?

[student101]

Two players or two teams?Your above query doesn't relate to the table data you posted earlier.anyways, what results do you get?is your form using POST or GET as the method?You need to help me to help you, if you post half the data I can't help you.

[dekrelo]

OK, sorry!I have $pl1 and $pl2 from drop down menus in Master page and pass them in detailed page, where I have:SELECT m.name,m.name2,score,score2FROM matches_db m JOIN players_db a ON m.name=a.name JOIN players_db b ON m.name2=b.nameWHERE m.name IN ($pr1,$pr2) AND m.name2 IN ($pr1,$pr2)How to get thouse 2 variables in my query?I use post method.

[student101]

First off you can't have just any old variables as you go along...$pl1 OR $pr1 pick one, better yet what are the names of your selects (dropdown menus) use those.master.phppl1 and pl2 from drop down menus detail.php

$pl1 = $_POST['pl1']; // if pl1 is the name of the dropdown on master.php$pl2 = $_POST['pl2']; // if pl2 is the name of the dropdown on master.phpSELECT m.name,m.name2,score,score2FROM matches_db m JOIN players_db a ON m.name=a.name JOIN players_db b ON m.name2=b.nameWHERE m.name IN ($pl1,$pl2) AND m.name2 IN ($pl1,$pl2)

[justsomeguy]

It doesn't look like there's a reason to use a join here, the only data you are returning is from the matches table. You're not returning anything from any other tables, so why bother joining? This would return the same records:

$pl1 = mysql_real_escape_string($_POST['pl1']);$pl2 = mysql_real_escape_string($_POST['pl2']);$sql = "SELECT name,name2,score,score2FROM matches_dbWHERE name IN ('$pl1','$pl2') AND name2 IN ('$pl1','$pl2')";

[student101]

fair enough, I just used what dekrelo posted, he seemed comfortable with what he posted; Yes, I should've posted a different version that would've been simpler.Thanks for pointing that out!

[dekrelo]

Thanks a lot both! I make this, but now, when choose 2 players in master page I have empty table in detailed page. I post my script:Master page:

<body><label>player1<select name="player1" id="player1">  <?phpdo {  ?>  <option value="<?php echo $row_player1['name']?>"><?php echo $row_player1['name']?></option>  <?php} while ($row_player1 = mysql_fetch_assoc($player1));  $rows = mysql_num_rows($player1);  if($rows > 0) {	  mysql_data_seek($player1, 0);	  $row_player1 = mysql_fetch_assoc($player1);  }?></select><br /><br />player2<select name="player2" id="player2">  <?phpdo {  ?>  <option value="<?php echo $row_player1['name']?>"><?php echo $row_player1['name']?></option>  <?php} while ($row_player1 = mysql_fetch_assoc($player1));  $rows = mysql_num_rows($player1);  if($rows > 0) {	  mysql_data_seek($player1, 0);	  $row_player1 = mysql_fetch_assoc($player1);  }?></select><br /><br /></label><p><a href="result.php">go</a></p><p>  <a href="formResult.php">  <label></label></a></p></body></html><?phpmysql_free_result($player1);?>

and detailed page:

$pr1 = mysql_real_escape_string($_POST['player1']);$pr2 = mysql_real_escape_string($_POST['player2']);mysql_select_db($database_sb, $sb);$query_matches = "SELECT m.name,m.name2,frame,frame2 FROM matches_db m JOIN players_db a ON m.name=a.name JOIN players_db b ON m.name2=b.name WHERE m.name IN ('$pr1','$pr2') AND m.name2 IN ('$pr1','$pr2')";

[student101]

Don't you need a second query for player2?Is this supposed to be duplicated?

 while ($row_player1 = mysql_fetch_assoc($player1));  $rows = mysql_num_rows($player1);  if($rows > 0) {	  mysql_data_seek($player1, 0);	  $row_player1 = mysql_fetch_assoc($player1);}

Edit;

How are you posting the select values?Where's your form?

Try this instead;

<body><form action="" method="post" name="myform" id="myform"><p><label>Player1</label><select name="player1" id="player1"><?phpdo {  ?><option value="<?php echo $row_player1['name']?>"><?php echo $row_player1['name']?></option><?php} while ($row_player1 = mysql_fetch_assoc($player1));  $rows = mysql_num_rows($player1);  if($rows > 0) {mysql_data_seek($player1, 0);$row_player1 = mysql_fetch_assoc($player1);}?></select></p><p><label>Player2</label><select name="player2" id="player2"><?phpdo {  ?><option value="<?php echo $row_player1['name']?>"><?php echo $row_player1['name']?></option><?php} while ($row_player1 = mysql_fetch_assoc($player1));  $rows = mysql_num_rows($player1);  if($rows > 0) {	  mysql_data_seek($player1, 0);	  $row_player1 = mysql_fetch_assoc($player1);}?></select></p><p>  <label>  <input type="submit" value="Go" />  </label></p></form></body></html><?phpmysql_free_result($player1);?>

[dekrelo]

I make this with Dreamweaver. I make form with Dreamweaver. The first code is written with Dreamweaver.

[student101]

Try use the last one I posted, what are your results?How does this work if they the same?

<option value="<?php echo $row_player1['name']?>"><?php echo $row_player1['name']?></option><?php} while ($row_player1 = mysql_fetch_assoc($player1));  $rows = mysql_num_rows($player1);  if($rows > 0) {	  mysql_data_seek($player1, 0);	  $row_player1 = mysql_fetch_assoc($player1);}?></select>

[dekrelo]

Thats work! Great! Thank you very much!!!!

[student101]

Thats work! Great! Thank you very much!!!!

Cool, glad you got it sorted.