Jump to content

Query Help!


dekrelo
 Share

Recommended Posts

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!

Link to comment
Share on other sites

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.:)

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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?

Edited by student101
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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)

Edited by student101
Link to comment
Share on other sites

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')";

Link to comment
Share on other sites

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')";

Link to comment
Share on other sites

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);?>

Edited by student101
Link to comment
Share on other sites

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>

Edited by student101
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...