UPDATE: Since the first issue was fixed (thank you - I kept the first question at the bottom for you to see), the proper connections are now pulling in fine. The problem is it is only pulling in those photos that have connections. How would I pull all photos no matter if they have connections or not?
Adding a LEFT JOIN instead of JOIN seems to pull in all photos, except it's adding "connections" to photos which don't have any...kind of weird.
This is hard to explain but I will do my best. First, let me apologize for the lengthy title, I just tried to make it as detailed as possible.
What this is about is displaying photos (user_album2 table) in a gallery. Each photo will have "connections" (from the user_connected table) displayed. An example of the user_connected table would be:
fk_pic_id | fk_user_id | fk_user_id_conn
Each of these columns are INT's, pulling the photos id #, user who made the connections id #, and the connected users id #.
All of the "get_" functions you see in the following code are simply functions pulling the users information (so if it's "get_profession" it will get the profession id #).
$query = mysql_query("SELECT a.pic_id, a.fk_user_id as uid, a.picture_number, a.picture_name, a.id, a.featuredphoto, c.fk_pic_id, GROUP_CONCAT(CONVERT(c.fk_user_id_conn, CHAR(8))) as cuids
FROM user_album2 a LEFT JOIN user_connected c ON a.pic_id = c.fk_pic_id GROUP BY a.pic_id ORDER BY RAND() LIMIT 40") or die(mysql_error());
while ($row = mysql_fetch_array($query)) {
$pic_id = $row['pic_id'];
$userid = $row['uid'];
$photonum = $row['picture_number'];
$photo = $row['picture_name'];
$photoid = $row['id'];
$featured = $row['featuredphoto'];
$photogid = $row['fk_photog_id'];
$modelid = $row['fk_model_id'];
$cuids = explode(',',$row['cuids']);
if (get_profession($userid) == 1) {
$modeltag = get_name($userid);
$modelpic = get_photo($userid);
$modelLink = get_profile_link($userid);
foreach ($cuids as $c) {
$cprof = get_profession($c);
if ($cprof == 2) {
$photogtag = get_name($c);
$photogpic = get_photo($c);
$photogLink = get_profile_link($c);
}
}
} elseif (get_profession($userid) == 2) {
$photogtag = get_name($userid);
$photogpic = get_photo($userid);
$photogLink = get_profile_link($userid);
foreach ($cuids as $c) {
$cprof = get_profession($c);
if ($cprof == 1) {
$modeltag = get_name($c);
$modelpic = get_photo($c);
$modelLink = get_profile_link($c);
}
}
}
$classMargin = $modeltag && $photogtag ? ' add-bot-margin':'';
$myreturn .= '<li>'
.'<div class="inner">'
.'<img border="0" alt="" src="'. $baseurl . $photo .'&w=188&h=150&zc=1" />'
.'<div class="details">'
.'<a href="'. $baseurl .'photos/'. $userid .'/'. $photoid .'/'. $photonum .'" class="img-link">view image <strong>here</strong></a>'
.'<div class="info-contain-details">';
if ($modeltag) {
$myreturn .= '<div class="details-info model-info'. $classMargin .'">'
.'<a href="'. $baseurl . $modelLink .'" class="link-image"><img border="0" alt="" src="'. $baseurl .'img.php?src=/memberpictures/'. $modelpic .'&w=30&h=30&zc=1" /></a>'
.'<div class="photo-info">'
.'<a href="'. $baseurl . $modelLink .'" class="link-view-more">view more from</a>'
.'<a href="'. $baseurl . $modelLink .'" class="link-username">'. $modeltag .'</a>'
.'</div>'
.'</div>';
}
if ($photogtag) {
$myreturn .= '<div class="details-info photog-info">'
.'<a href="'. $baseurl . $photogLink .'" class="link-image"><img border="0" alt="" src="'. $baseurl .'img.php?src=/memberpictures/'. $photogpic .'&w=30&h=30&zc=1" /></a>'
.'<div class="photo-info">'
.'<a href="'. $baseurl . $photogLink .'" class="link-view-more">view more from</a>'
.'<a href="'. $baseurl . $photogLink .'" class="link-username">'. $photogtag .'</a>'
.'</div>'
.'</div>';
}
$myreturn .= '</div>'
.'<span class="details-bg"></span>'
.'</div>'
.'</div>'
.'</li>';
}
FIXED: [For some reason I cannot for the life of me get the profession part to show up properly. It should be: If the owner of the photo's profession is a model then it should check for connections of that photo that = a photographer (model = 1, photographer = 2). I have a few rows in the 'user_connected' table to the same photo, but it keeps showing the same 2 users (both "models") when it should be one model and one photographer and that's it.] - was missing an "=" sign in the $cfprof if statement, DOH!
If you need any other information please let me know, and as always any help is greatly appreciated!
Fixed. The issue was inside the foreach loops, I needed to have an ELSE statement setting the photogtag/modeltag's to FALSE as well.
if ($cprof == 2 && $c != '') {
$photogtag = get_name($c);
$photogpic = get_photo($c);
$photogLink = get_profile_link($c);
} else {
$photogtag = false;
}