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
////////////////
// COMMENTS //
////////////////
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).
1 Answer 1
From a quick glance of your code it looks like you are doing a lot in your PHP that you should be doing on the database with SQL.
I recommend that you look into Stored Procedures for MySQL, I think that a lot of what you are doing can be done using Stored Procedures on your database, then you can call those procedures from the PHP code to display the data that is retrieved.
In my experience it is easier to write SQL in SQL and not SQL in a server side language to run on the database. This would enhance the performance on your web server.
It would also give you the opportunity to view the data before making it visible on your site, this makes your data more secure in the long run and will alleviate stress trying to get your data to look right on the web page. This also compartmentalizes the database stuff from the webpage stuff.
Explore related questions
See similar questions with these tags.
WHEREclause you're using&&, while it should beAND. It may work onMySQL, but it's definitly not standard (there is not truth-table, nor is there a char-matrix in the official spec \$\endgroup\$EXPLAIN EXTENDEDbefore it, so you can see how the tables are actually joined. Another thing: don't useLIKEwhen 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 \$\endgroup\$