# Correct use of my JOIN and arrays to fetch the data?

After learning a lot about programming, I've decided to write some code pertaining to scripting and use of different functions. I've come to a point where I'd like others to verify my code for correctness. Is there anything I can do better, different, or easier, to make my code more efficient?

My main goal is to determine whether or not my functions are working correctly. It works perfectly right now, but I'd also like to join new tables to get additional information to the $posts array. I find it very hard to implement this easily. For instance, I'd like to retrieve all likes from table: likes where like_entity = post_id. The code works, but it feels stuck to me. Any ideas / feedback? // Get wall // Returns posts array with comments function GetWall($entity='') {
global $tbl_prefix;$sql = "
SELECT
p.*,
c.post_id AS comment_id,
c.post_message AS comment_message,
c.post_author AS comment_author,
c.post_date AS comment_date,
c.post_deleted AS comment_deleted,
u.name AS post_author_name,
u2.name AS comment_author_name,
lp.like_entity AS post_like,
lc.like_entity AS comment_like
FROM " . $tbl_prefix . "posts p LEFT JOIN " .$tbl_prefix . "posts c ON c.post_comment = p.post_id
LEFT JOIN " . $tbl_prefix . "users u ON u.user_id = p.post_author LEFT JOIN " .$tbl_prefix . "users u2 ON u2.user_id = c.post_author
LEFT JOIN " . $tbl_prefix . "likes lp ON lp.like_entity = p.post_id && lp.like_author = '" . UserId() . "' LEFT JOIN " .$tbl_prefix . "likes lc ON lc.like_entity = c.post_id && lc.like_author = '" . UserId() . "'

WHERE ";

if($entity) {$sql .= "p.post_receiver = '$entity' && "; }$sql .= "p.post_deleted = '0' && p.post_comment IS NULL
GROUP BY p.post_id, comment_id
ORDER BY p.post_date DESC
";

$query = doQuery($sql);

$posts = array();$catchedComments = array();

while($row = mysql_fetch_assoc($query)) { // loop query

if($row['post_like']) {$user_likes_post = 1;
}

if($row['comment_like']) {$user_likes_comment = 1;
}

if(!array_key_exists($row['post_id'],$posts)) { // if wall post is not yet created
////////////////
////////////////

if($row['comment_id'] && !$row['comment_deleted']) { // if comment exists on current
$comment = array(array( 'id' =>$row['comment_id'],
'message' => $row['comment_message'], 'author' =>$row['comment_author'],
'author_name' => $row['comment_author_name'], 'date' =>$row['comment_date'],
'user_likes' => $row['comment_like'] )); } else { // no comments for current post$comment = array();
}

////////////////
// POST ARRAY //
////////////////

// Create post ARRAY
$posts[$row['post_id']] = array(
'message' => $row['post_message'], 'author' =>$row['post_author'],
'author_name' => $row['post_author_name'], 'date' =>$row['post_date'],
'comments' => $comment, 'user_likes' =>$row['post_like']
);

} else { // if post is created, assume this fetch it is comment/like data
foreach($posts[$row['post_id']] as $k =>$v) { // loop post through and scan for arrays
if($k == 'comments') { // get comments if(!array_key_exists($row['comment_id'], $v)) { // check if comment is created (with$post array)
if(!$comment[$row['comment_id']]) { // check if comment is created (with comment array)
if(!$row['comment_deleted']) { // check if comment is deleted // Create array with comment data$comment[$row['comment_id']] = array( 'id' =>$row['comment_id'],
'message' => $row['comment_message'], 'author' =>$row['comment_author'],
'author_name' => $row['comment_author_name'], 'date' =>$row['comment_date'],
'deleted' => $row['comment_deleted'], 'user_likes' =>$row['comment_like']
);

// Push to comment array
array_push($posts[$row['post_id']]['comments'], $comment[$row['comment_id']]);
}
}
}
}
} // end foreach
} // end comment/like data section
} // end while

// Returns array with data
return \$posts;
} // end function


I use the function to retrieve wall posts, and all associated comments to them, and then output it to an array. The associated likes, I get externally from a jQuery code (and other MySQL fetch).

• In your WHERE clause you're using &&, while it should be AND. It may work on MySQL, but it's definitly not standard (there is not truth-table, nor is there a char-matrix in the official spec – Elias Van Ootegem Sep 11 '13 at 8:30
• Hi there! Thanks for your answer - but can you tell; is it the right way I'm doing it? I'm about to join another table with even more information (for every like that exists on the current post/comment in the posts table), and that will create probably additionally 500% more rows (depending on the amount of likes), and it feels like that is very unnecessary. Any advice? – FooBar Sep 11 '13 at 8:59
• When writing queries, execute your queries, but add EXPLAIN EXTENDED before it, so you can see how the tables are actually joined. Another thing: don't use LIKE when you don't have to (it's slow). Add your table shema's and storage engines + collation to your question, because looking at the query alone is not going to lead to a definitive answer. And yes, 6 joins is a bit much (not uncommon, though), and you're likely to be able to improve on your query – Elias Van Ootegem Sep 11 '13 at 9:03
• I have been advised to convert my database to a relational database, any recommendations on this? I have already build up a lot of tables in my database, but it seems as it is the most recommended. Could I benefit from it, for example on above code? – FooBar Sep 11 '13 at 9:29
• They already look relational to me (relational tbls are tables that express a relation between the data by storing the id of record X in tbl1 in a field in tbl2 and vice-versa). – Elias Van Ootegem Sep 11 '13 at 9:33