2
\$\begingroup\$

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).

\$\endgroup\$
  • \$\begingroup\$ 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 \$\endgroup\$ – Elias Van Ootegem Sep 11 '13 at 8:30
  • \$\begingroup\$ 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? \$\endgroup\$ – FooBar Sep 11 '13 at 8:59
  • \$\begingroup\$ 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 \$\endgroup\$ – Elias Van Ootegem Sep 11 '13 at 9:03
  • \$\begingroup\$ 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? \$\endgroup\$ – FooBar Sep 11 '13 at 9:29
  • \$\begingroup\$ 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). \$\endgroup\$ – Elias Van Ootegem Sep 11 '13 at 9:33
2
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.