selecting data from mysql

Question

0.00/5 (No votes)

See more:

I have an issue in select query. I want to select total_comment From comment table and show it under each post.

What I Try

SQL

SELECT SUM(total_comment) AS comment
FROM   user_comment
       INNER JOIN post
               ON user_comment.image_id = post.id
WHERE  status = 0

And

SELECT SUM(total_comment) AS comment
FROM user_comment
WHERE status = 0

Problem:

I am not able to get the total comment posted on each post... The result i get is wrong.

Result Pic..

http://i.imgur.com/o40sw3T.png[^]

Or

http://i.imgur.com/16Ln2ds.png[^]

But I Want Like...

http://i.imgur.com/6bxH3eB.png[^]

My php code is

PHP

<?php
session_start();
include 'conn.php';
if(!isset($_SESSION['user']))
{
    header('location:signin.php');
}



$smt=$conn->prepare("SELECT * FROM post,images WHERE post.Id=images.Id");
$smt->execute();
$smtt=$conn->prepare("SELECT * FROM post,images WHERE post.Id=images.Id");
$smtt->execute();
$myres=$smtt->fetch(PDO::FETCH_OBJ);
$imgid=$myres->Id;
$qry=$conn->prepare("SELECT COUNT(user_comment.Total_Comment) AS comment FROM user_comment INNER JOIN post ON user_comment.Image_Name = post.File_Name WHERE user_comment.Image_Id =post.Id");
$qry->execute();
$result=$qry->fetch(PDO::FETCH_OBJ );
$total_coment=$result->comment;
?>
<?php include 'header.php';?>
<?php
if(isset($_SESSION['user']))
{
    include 'nav.php';
}
else
{
    include 'nav-simple.php';
}
?>

    <div class="container-fluid">
    <?php include 'right_sidebar.php';?>
<div class="main-container-top" id="masonry-grid">
    <?php while($rows=$smt->fetch(PDO::FETCH_OBJ)): ?>

        <div class="col-md-3 grid-item post-col">
<img src="image/<?php echo $rows->Image_Name;?>" data-echo="image/<?php echo $rows->Image_Name;?>" class="post-image"/>

            <h5>Post On  <?php echo $rows->Post_Date;?> <span class="pull-right">By <?php echo $rows->Post_By; ?></span> <span class="pull-right">Total Comment <?php echo $total_coment;?></span></h5>

            <a href="post-description.php?id=<?php echo $rows->Id ?>"> <h4><?php echo $rows->Post_Title;?></h4> </a>
            <p>
                <?php echo $rows->Post;?>
            </p>

        </div>


    <?php endwhile;?>
</div>
    </div>

<?php include 'footer-content.php';?>
<?php include 'footer.php';?>

Note:

When i run the query In the post description the it would work fine, but in index page it will not...

My post-description.php code is

PHP

<?php
session_start();
include 'conn.php';
$pic_id='';
if(isset($_GET['id']))
{
    $pic_id=$_GET['id'];
}
$comv=$conn->prepare("SELECT * FROM user_comment WHERE user_comment.Image_Id='".$pic_id."'  AND user_comment.Status=0 AND user_comment.Comment_Status=1");
$comv->execute();
$fimg=$conn->prepare("SELECT Image_Name From Images WHERE Id='".$pic_id."'");
$fimg->execute();
$gimg=$fimg->fetch(PDO::FETCH_OBJ);
$pro=$conn->prepare("SELECT Profile_Picture FROM user_registration WHERE User_Name='".$_SESSION['user']."'");
$pro->execute();
$prof_img=$pro->fetch(PDO::FETCH_OBJ);
$smt=$conn->prepare("SELECT * FROM post,images WHERE post.Id = images.Id AND post.Id='".$pic_id."'");
$smt->execute();
$qry=$conn->prepare("SELECT COUNT(Total_Comment) AS comment FROM user_comment WHERE Status=0 AND Image_Id ='".$pic_id."'");
$qry->execute();
$result=$qry->fetch(PDO::FETCH_OBJ );
$total_coment=$result->comment;
?>
<?php include 'header.php';?>

<?php include 'nav.php';?>

<div class="container-fluid">
    <?php include 'right_sidebar.php';?>
<div class="col-md-1"></div>

    <div class="col-md-9 main-container-top container">
<?php while($rows=$smt->fetch(PDO::FETCH_OBJ)):?>
        <div class="media col-md-12 description-post">


           <img src="image/<?php echo $rows->Image_Name;?>" alt="<?php echo $rows->Image_Name;?>" class="img-rounded img-responsive media-left img-description"/>
            <div class="media-body">
               <h4 class="h4 description-heading"><?php echo $rows->Post_Title;?> <small class="pull-right"> <?php echo $total_coment;?></small></h4>
                <p class="post-text text-justify text-info">

                    <?php echo $rows->Post;?>
            </div>
<?php endwhile;?>
        </div>

        <br/>

            <div class="media col-md-12 comment-section">
<?php
if(isset($_SESSION['comment-error']))
{
?>
    <span class="alert alert-warning col-md-6 container col-md-offset-3"><?php echo $_SESSION['comment-error']; ?></span>
    <?php
}
unset($_SESSION['comment-error']);
 if(isset($_SESSION['comment-success']))
{
?>
    <span class="alert alert-success col-md-6 container col-md-offset-3"><?php echo $_SESSION['comment-success']; ?></span>
    <?php
}
unset($_SESSION['comment-success']);
    ?>
                <br/>



                <?php while($gcom=$comv->fetch(PDO::FETCH_OBJ)):?>
                <span class="col-md-1 comment-pic"><img src="profile%20picture/<?php echo $gcom->Profile_Picture;?>" alt="post image" class="img-thumbnail img-responsive comment-img"/></span>
                <div class="media-body  comment-head col-md-10">
                    <h6 class="h6"><a href="#"> <?php echo $gcom->User_Name;?> </a> on <?php echo $gcom->On_Time;?></h6>
                    <p class="comment"><?php echo $gcom->Comment;?></p>
                </div>
                    <?php endwhile;?>
            </div>

        <br/>
        <div class="col-md-12 container">
            <h4 class="description-heading h4 text-muted">Share your thought</h4>
            <br/>
            <form action="comment.php" method="post" class="col-md-12" id="commentForm">
                <textarea name="comment" id="" cols="100" rows="5" placeholder="Your comment"></textarea>
                <input type="hidden" name='picture-name' value="<?php echo $gimg->Image_Name;?>"/>
                <input type="hidden" name="profile-pic" value="<?php echo $prof_img->Profile_Picture;?>"/>
                <input type="hidden" name="pic-id" value="<?php echo $pic_id;?>"/>
                <input type="hidden" name="image-id" value="<?php echo '?id='.$pic_id;?>"/>
                <br/>
                <input type="submit" value="Post" name="cmsg" id="" class="btn btn-info"/>
                <br/>
            </form>
        </div>
        <br/>



</div>

</div>
<?php include 'footer-content.php';?>
 <?php include 'footer.php';?>

Posted 15-Dec-14 8:26am

msz900

Updated 16-Dec-14 6:57am

v3

Add a Solution

Comments

PIEBALDconsult 15-Dec-14 14:37pm

Maybe more information about the tables and some sample data wuold be in order? Use Improve question.

barneyman 15-Dec-14 23:21pm

was going to suggest you use COUNT instead of SUM, but your PHP does ... so i'm now not sure what you expect, and what you get

msz900 16-Dec-14 6:41am

i expect to get each post result, for example you can see in the bold text above "What i want" but i get total comment 3 or total comment 0 under all post.

syed shanu 17-Dec-14 4:21am

I think you need to use the Grou by in your select query.
example : Iam not sure about your select key field so check for your needed group by field and change according to it.

SELECT SUM(total_comment) AS comment
FROM user_comment
INNER JOIN post
ON user_comment.image_id = post.id
WHERE status = 0
group By user_comment.image_id

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

/\jmot · Answer 1 · 2014-12-15T20:24:00

Solution 1

it's not the problem.
you are getting the Correct result. i think you just want to show the result in a specific way.
so just change the look or design you working on.

You are Using.

SQL

SELECT SUM(total_comment) AS comment
FROM   user_comment
       INNER JOIN post
               ON user_comment.image_id = post.id
WHERE  status = 0

you are getting.
Total comment: 3 as a result.

so what do you really want next???

Posted 15-Dec-14 20:24pm

/\jmot

Comments

msz900 16-Dec-14 6:39am

I want that the query will get each post comment, but it will show total_comment 3 under every post even there is no comment on post.

syed shanu 17-Dec-14 4:21am

I think you need to use the Grou by in your select query.
example : Iam not sure about your select key field so check for your needed group by field and change according to it.

SELECT SUM(total_comment) AS comment
FROM user_comment
INNER JOIN post
ON user_comment.image_id = post.id
WHERE status = 0
group By user_comment.image_id

msz900 17-Dec-14 8:37am

i already use group by but the result is same...

/\jmot 16-Dec-14 7:11am

so,whats your table structure?? what's your expected result??
plzz update your question.

syed shanu 17-Dec-14 19:14pm

yes it seems you have missed the user id in your table as you want to get the comment group by userid by this you can get sum of comments by user.

msz900 17-Dec-14 22:09pm

please show example code

selecting data from mysql

1 solution

Solution 1

Add your solution here

Preview 0

selecting data from mysql

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us