Jump to content


Photo

retrieving data from more than one table


  • Please log in to reply
1 reply to this topic

#1 dr_overload

dr_overload
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 29 May 2006 - 07:29 PM

I currently use this script to retrieve data from the one table

<?php
mysql_connect("**", "**", "**") or die(mysql_error());
mysql_select_db("**") or die(mysql_error());
$data = mysql_query("SELECT * FROM world_cup WHERE username ='$username'")
or die(mysql_error());
while($info = mysql_fetch_array( $data ))
if ( $info['a'].$info['b'] == $info['c'].$info['d'] ) {
echo $info['e'];
?>

What would I have to change in the script if 'a' and 'b' were in a second table. ie world_cup_scores?

Any help is most appreciated



#2 lead2gold

lead2gold
  • Members
  • PipPipPip
  • Advanced Member
  • 164 posts
  • LocationOttawa, On

Posted 29 May 2006 - 07:52 PM

[!--quoteo(post=378177:date=May 29 2006, 03:29 PM:name=dr_overload)--][div class=\'quotetop\']QUOTE(dr_overload @ May 29 2006, 03:29 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
I currently use this script to retrieve data from the one table

<?php
mysql_connect("**", "**", "**") or die(mysql_error());
mysql_select_db("**") or die(mysql_error());
$data = mysql_query("SELECT * FROM world_cup WHERE username ='$username'")
or die(mysql_error());
while($info = mysql_fetch_array( $data ))
if ( $info['a'].$info['b'] == $info['c'].$info['d'] ) {
echo $info['e'];
?>

What would I have to change in the script if 'a' and 'b' were in a second table. ie world_cup_scores?

Any help is most appreciated
[/quote]
Assuming you'll be linking the second table to the first table by some type of id:
Table1: world_cup
 table_id INTEGER(20),
 info_c    <whatever>,
 info_d    <whatever>,
 info_e    <whatever>

Table2: world_cup_scores
 table_id INTEGER(20), -- this value will reference Table1 table_id as a foreign key
 info_a  <whatever>,
 info_b <whatever>

your select statement:
 $sql = " SELECT world_cup.*,world_cup_scores.* FROM world_cup";
 $sql .= " LEFT JOIN world_cup_scores ON world_cup.table_id = world_cup_scores.table_id"
 $sql .= " WHERE username ='$username'";

The above will retrieve all the work cup games even if there are no scores associated to those games yet.








0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users