Retrieve and display comments/queries from database

Question

I'm developing in php/sql a web application where users will be able to post items that they'd like to sell ( kinda like ebay ). I want non-members to be able to comment on the items or ask queries about items.

My problem is I want to display each item as well as any comment/query made about that item, in a similar manner as the way Facebook wall works.

I want to "append comments"(if any) to each item. The comments table is linked to the items table via column item_id. And the items table is linked to users table via column user_id. I have left joined users table with items table to display item details, i tried to left join comments table as well so that there are 3 joined tables.

That fails because no comments are displayed and only one item is displayed, despite there being multiple entries in each table. Here is the code i,m using.

$database->query
    ('
    SELECT sale.*, query.*, users.id AS userid, users.username as user 
    FROM sale 
    LEFT JOIN users ON sale.user_id = users.id 
    LEFT JOIN query on sale.id = query.item_id
    where category = "$category" ORDER BY sale.id DESC
    ');

    $show = " "; //variable to hold items and comments

    if ($database->count() == 0) {

      // Show this message if there are no items  

      $show .= "<li class='noitems'>There are currently no items to display.</li>" ; 

    } else {

        $show .= "<li>";

        while ( $items = $database->statement->fetch(PDO::FETCH_ASSOC) )  
        {

            $show .= "
                //show item details in html
               ";

            while( $query = $database->statement->fetch(PDO::FETCH_ASSOC) )
            {

                $show .= "
                  //show queries below item details
                        ";                    
            }     

      $show .= "</li>" ; 
        }

You just need a comment table, with a link to the itemID, surely? If you're already getting item details displayed, you've already got code that will let you read and write to the database. — andrewsi, Sep 17 '12 at 18:38
u need to append comments (probably ) inside the main body, while using ajax to save the comments in the comment table, with a link to item (as @andrewsi mentioned) — Teena Thomas, Sep 17 '12 at 18:42
Yes, @andrewsi i do have code to read and write to the db, and i am already displaying item details. Trouble starts when i want to "append comments"(if any) to each item as you point out. The comments table is linked to the items table via column item_id. And the items table is linked to users table via column user_id. I have left joined users table with items table to display item details, i tried to left join comments table as well so that there are 3 joined tables but that fails because all comments get listed below each item rather than just the comments about that item. — user1678293, Sep 18 '12 at 15:17
@user1678293 - could you edit your question to include the table design and the SQL you're trying, please? — andrewsi, Sep 18 '12 at 15:20

score 0 · Answer 1 · edited May 23 '17 at 12:24

Welcome to Stackoverflow!

I recommend you taking a look at pdo. If you are already using mysql_ functions, then I recommend you switch. More on that can be found here.

Now that your pointed to the direction of to what functions to use when connecting/running queries, you now should create your tables. I use phpmyadmin for managing my database, I find it very good, but it's up to you what you use. Once you've decided on the service you use to manage your database, you should then learn how to use it by doing some google searches.

Now you need to set up your table and structure it correctly. If you say you're having items, then you should make a table called items. Next create the columns to the properties of the items. Also I recommend reading about Database Normalization, which is a key aspect of setting up your SQL tables Etc.

Once you have everything set up, you've connected to your database successfully Etc. You now need to set up the "Dynamic Page". What I mean by this is, there's only one page, say called 'dynamic', then a variable is passed to the url. These are called GET HTTP requests. Here's an example of what one would look like: http://example.com/item?id=345.

If you've noticed, you'll see the ? then the id variable defined to 345. You can GRAB this variable from the url by accessing the built in PHP array called $_GET[]. You can then type in your variable name you want to fetch into the []'s. Here's an example.

<?php
$data = $_GET['varname']; // get varname from the url
if(isnumeric($data)){ // is it totally made out of numbers?
    $query = "SELECT fieldname FROM table WHERE id=:paramname";
    $statement = $pdo->prepare($query); // prepare's the query
    $statement->execute(array(
        'paramname'=>$data // binds the parameter 'paramname' to the $data variable.
    ));
    $result = $statement->fetch(PDO::FETCH_ASSOC); // grabs the result of the query
    $result = $result['fieldname'];
    echo 'varname was found in the database with the id equal to:'.$data.', and the             result being: '.$result;
}
?>

So now you can see how you can grab the variable from the url and dynamically change the content with-in the page from that!

Hope this helped :)

Thank you for your response, but i am afraid i may have been vague with my question, See the edited question above where i have included my code. Thank you. — user1678293, Sep 18 '12 at 16:51

Retrieve and display comments/queries from database

1 Answers1