using two tables to create one output

Codes here !

Moderators: macek, egami, gesf

macd926
New php-forum User
New php-forum User
Posts: 1
Joined: Thu Sep 13, 2012 5:23 am

using two tables to create one output

Postby macd926 » Thu Sep 13, 2012 5:41 am

Hi Guys,

i am trying to create a website for my athletics team with one of the pages letting the athletes create and update their profiles themselves, thus me not having to do it.
the idea being they can upload a picture, write a little blurb about themselves and enter their events and personnel bests.
i have set it in my database so one table collects their name, date joined etc and their blurb and gives them the id but as they may do more than one event i guessed i need a second table to collect their events and pb's and matches these to their id's.

the code i am using is;

Code: Select all

$athleteDisplay = '';
$disaplin = '';
$best = '';
   //while loop 1 that runs through the whole script and builds the list (closed at the end)
   $sql = mysql_query("SELECT * FROM members WHERE email_activated='1' ORDER BY last_name");
   while($row = mysql_fetch_array($sql)){
   $id = $row["id"];
   $firstname = $row["first_name"];
   $lastname = $row["last_name"];
   $bio_body = $row["bio_body"];
   $check_pic = "members/$id/image01.jpg";

   ////while loop 2
   $sql_event = mysql_query("SELECT event, pb FROM events WHERE id='$id' ORDER BY date LIMIT 11");
   while($row = mysql_fetch_array($sql_event)){
   $event = $row["event"];
   $pb = $row["pb"];
   $disaplin .= "$event <br/>";
   $best .= "$pb <br/>";
   }////stop while loop 2   
   if (file_exists($check_pic)) { ///if else pic check
       $user_pic = "<img src=\"members/$id/image01.jpg\" width=\"150px\" height=\"150px\" border=\"0\" />";
   } else {
      $user_pic = "<img src=\"members/0/image01.jpg\" width=\"150px\" height=\"150px\" border=\"0\" />";
   } ///close if else pic check   

$athleteDisplay .= '<table border="0" align="left" cellpadding="6" width="600">
               <tr> <td width="111">
               <tr><td valign="top"> ' . $user_pic . ' </td><td width="659" align="left" valign="top"><table width="60%">
               <tr><td><strong> ' . $firstname . ' ' . $lastname . ' </strong></td></tr><tr><td><strong>Blurb:</strong><br /> ' . $bio_body . ' </td></tr>
               <tr><td><strong>Event</strong></td><td><strong>PB</strong></td></tr>
               <tr><td> ' . $disaplin . ' </td><td> ' . $best . ' </td></tr>
               </table></td></tr></td></tr></table>  ';
}//closing the main while loop (loop 1)


obviously later down the page i use <?php print "$athleteDisplay";?> to display it on the webpage.

it is close to working except for if you look at the web page http://www.readingacmen.hostzi.com/athlete_profile.php it adds the athletes at the top of the lists pb's to the next athlete down as well as their own pb's. i cant work this out and have spent many ours trying.
i have tried using JOIN and LEFT JOIN to link the tables however this creates a profile for every event the athlete does as well as adding all the athletes above in the lists events and pb's also.

i am guessing the issue might be in the athlete display somewhere but am now stumped.

any help would be much apreaciated.

thanks

Return to “mySQL & php coding”

Who is online

Users browsing this forum: No registered users and 3 guests