calling from two tables in the database to give one output

[macd926]

Hi guys, i'm all new to this so hope i am putting this in the write place.

 

i am trying to create a site where members of my team can update their own profiles instead of me doing it.

the idea being each athlete can add a photo, a little 'blurb' about them selfs and add the events they do along with their personal bests.

as each athlete could do more than one event, the only way i could think of working it was to have two tables. a 'members' table to get the details of the athlete and the blurb, then an 'events' table to record the events and the pb's.

 

here is the code i am using;

 

<?php
$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 mem_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)
?>

what is happening is, the pic and blurb are being displayed fine but when it comes to the events and pb's it seems to be having trouble asigning the write ones to each profile. it does sort of find the write event and pb but for each profile that comes before on the website it adds their event and pb also.

 

the webpage if you need to look is - www.readingacmen.hostzi.com

 

any help you can offer will be much apreciated, i have spent ages trying to work this out and am starting to wonder if it is even possible.

if you need any further info just let me know.

 

thanks.

[Jessica]

Don't run queries inside of loops. You'll need to use a JOIN

[macd926]

thanks, i am quite new to codeing so have never heard of JOIN before.

 

after a bit of research it seemed LEFT JOIN is the code i need to use but i seem to be getting something wrong.

i get this error message;

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN eventsON members.id = events.id' at line 1

 

i have changed the code to;

$query = "SELECT members.first_name, members.last_name, members.bio_body, events.event, events.pb".
"FROM members LEFT JOIN events".
"ON members.id = events.id";

$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){ 
echo $id = $row["id"];
echo $firstname = $row["first_name"];
echo $lastname = $row["last_name"];
echo $bio_body = $row["bio_body"];
echo $check_pic = "members/$id/image01.jpg";
echo $event = $row["event"];
echo $pb = $row["pb"];
echo $disaplin .= "$event <br/>";//building mini lists for each profile slot for the main loop
echo $best .= "$pb <br/>";//building mini lists for each profile slot for the main loop

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	
echo $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>  ';
}
?>

i am not sure what i am now doing wrong, like i said i had never heard of any sort of JOIN before today so if you could help that would be bril.

 

thanks

[DavidAM]

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN eventsON members.id = events.id' at line 1

 

$query = "SELECT members.first_name, members.last_name, members.bio_body, events.event, events.pb".
"FROM members LEFT JOIN events".
"ON members.id = events.id";

 

Notice in the error message there is no space between "events" and "ON". In your code, you have concatenated the "FROM" clause and the "ON" clause without any space between them. So the value of $query is:

SELECT members.first_name, members.last_name, members.bio_body, events.event, events.pbFROM members LEFT JOIN eventsON members.id = events.id"

There is also no space between "events.pb" and "FROM".

 

When using concatenation to build a query, you should always either end every line with a space or start every line with a space. Pick one style and use it every time. Spaces are cheap and usually cause no trouble. If you can't find any, I have some extras in the other room I'll be glad to send to you.

 

$query = "SELECT members.first_name, members.last_name, members.bio_body, events.event, events.pb ".
"FROM members LEFT JOIN events ".
"ON members.id = events.id";

[macd926]

ah i see, thank you, sorted out the syntax problem, now to figure out how to use JOIN with my original issue.

 

thanks

[DavidAM]

You might need to show us your current code. However, I suspect the problem is that you are not clearing out the variables when the athlete changes. When using a JOIN, you can get multiple rows for the same (in this case) athlete, showing different (in this case) disciplines. You need to track which athlete you are dealing with and break-up the output when it changes. Something like:

 

// This is psuedo-code, it WILL NOT RUN
$currentID = null;
while ($row = fetch_assoc($queryResource)) {
  if ($currenID != $row['id']) {
    if (!empty($currentID)) {
     # output the data for the previous ID
    }
    $currentID = $row['id];
    $baseData = // Collect the name and stuff that does not change
    $moreData = ''; // Clear the variable holding the additional stuff
  }
  $moreData .= $row['whatever'];
}
// At this point we have not output the last set of data, so ...
if (!empty($currentID)) {
# output the data for the previous ID
}

[macd926]

sorry i might be being a bit dim here but can you explain what you mean by ' # output the data for the previous ID'

 

am i correct in thinking that what your script is saying is if the id is already in use then do not re-use just add the $moredata?

[Jessica]

That's a comment, telling you what the code should do.

And yes, that is what it means.