Comments php and sql help needed.

[Davie33]

Need abit of help coding this for game comments please.
Here is my code.

<!-----------Comments-------------->
<div class="comments">
<div class="game-name"> <img src="<?php echo $setting['siteurl'].'templates/'.$setting['theme'].'/skins/'.$setting['skin'];?>/images/sd-sml.png" />  Comments
</div>    
    <?php
    $query = yasDB_select("SELECT count(id) AS count FROM comments WHERE gameid = $id");
    $result = $query->fetch_array(MYSQLI_ASSOC);
    $total = $result['count'];
    if($total == 0) {
        echo '<center>This game has no comments, be the first to add one!</center>';
    } else {
        $query = yasDB_select("SELECT comments.name,comments.comment,comments.gameid,user.id,user.username,user.avatarfile,user.useavatar FROM comments LEFT JOIN user ON comments.name = user.username WHERE gameid = $id ORDER BY 'id' DESC LIMIT 5");
        while($row = $query->fetch_array(MYSQLI_ASSOC)) {        
            $text = $row['comment'];
            $username = $row['name'];
            if ( $row['useavatar'] == '1' ) {
            $avatarimage = $setting['siteurl'] . 'avatars/' . $row['avatarfile'];
            } else {
            $avatarimage = $setting['siteurl'] . 'avatars/useruploads/noavatar.JPG';
            }
            if ($setting['seo'] == 'yes') {
            $memberlink = $setting['siteurl'].'showmember/'.$id.'.html';
            } else {
            $memberlink = $setting['siteurl'] . 'index.php?act=showmember&id='.$id ;
            }        
            echo '<div class="cm-box"><a href="'.$memberlink.'"><img src="'.$avatarimage.'" align="center" class="c-img" title="'.$username.'"></a><div class="cmmnts"><p class="usr-cmnt">' . $text . '</p></div></div>';
        }
    }
    $query->close();
    if (isset($_POST['comment'])) {
        $comment = $_POST['comment'];
    } else {
        $comment = '';
    }
    ?>
</div>

As you will see the gameid = $id is giving the url link to members profile when clicking on a avatar from the game comments.

This is giving me the problem WHERE gameid = $id ORDER BY '$id' DESC LIMIT 5");

Because of $id in sql is messing with the url as showing here.

            if ($setting['seo'] == 'yes') {
            $memberlink = $setting['siteurl'].'showmember/'.$id.'.html';
            } else {
            $memberlink = $setting['siteurl'] . 'index.php?act=showmember&id='.$id ;
            }

So instead of the member id it gives the game id in url
it shows the avatar but link id is from the game id which we still need in sql
How can i fix this ?.

[Ch0cu3r]

You need to get the users user id from the query result ($row)

 

The variable

 

    $row['id']

 

will contain the users id. So to link to the members profile you'd use

            if ($setting['seo'] == 'yes') {

            $memberlink = $setting['siteurl'].'showmember/'.$row['id'].'.html';

            } else {

            $memberlink = $setting['siteurl'] . 'index.php?act=showmember&id='.$row['id'];

            }

If you want to assign $row['id'] to a variable then name that variable to something else that is not called id. Like

 

     $member_id = $row['id'];

Edited November 8, 2013 by Ch0cu3r

[Davie33]

Works on localhost just need to test it on server.

[Davie33]

Thank you for the help it works great

[Davie33]

Hi thought i would keep this in same topic as am using this code but what i would like to know is.

After every 2nd comment is made the avatar should be placed on the opposite side.

 

I have the css divs made for 2nd comment which is.....

 

<div class="cm-box">
<div class="scnd-cmnt">
<p class="usr-cmnt-2">Comment msg</p>
</div>
<a href="profile.html"><img src="images/Fast_Food_Bar.jpg" border="0" class="c-img"></a>
</div>

 

So the comments look like this.....

 

avatar comment
comment avatar
avatar comment

 

How can i do this ?.

Edited November 9, 2013 by Davie33

[Ch0cu3r]

before the while loop add   $i = 0;

 

Then when displaying the comments you'd use this code

if($i % 2 == 0)
{
    // html code for displaying comment with avater on the left
}
else
{
    // html code for displaying comment with avater on the right
}
$i++;

[Davie33]

Thanks for this worked gr8t thanks again .