For Each statement Maybe ?

dsnyder · March 14, 2010

I am working on a very simple pic gallery. Viewers can post comments on the pictures (saved to a seperate table). The code below is for the thumbnail view of pictures in a paticular Album. Under the pictures it shows # of Comments that have been posted on each picture. MY PROBLEM IS.. If a Picture has 0 Comments it will not show as 0.. It shows as the #of comments for the prior record Unless the pictures actually has comments.. Eg; If all pictures in the album have comments they all show correctly.. OR If all pictures Do Not have any comments then they will all show as 0 comments..

I thought perhaps I need some for each statement on the comments table? And if so can someone show me how that would be coded ? I am pretty new to php coding and learning as I go.. Any help would be greatly appreciatted.

<?php
require('config.php'); 
        $query= "SELECT * FROM pictures WHERE album = $albumid AND status = 'Approved'";
        $result = mysql_query($query);

        $num_records = mysql_num_rows($result); //Total number of records in result
        $num_per_row = 3; //Change to however many columns per row you desired
echo "<table width='550'>";
        $i = 1;
while ($row = mysql_fetch_array($result)) {
$thumb = $row[thumb];
$picid = $row[picid];
$path = $row[pic_path];
$caption = $row[caption];
$posted = $row[submited_by];
$edit ="<a href='edit_pic.php?picid=$picid' class='style3'>Edit</a>";
$delete ="<a href='delete_pic1.php?picid=$picid' class='style3'>Delete</a>";
$link = "<img src='http://www.jadindoffort.com/pictures/$path$thumb'
               alt='Thumb Nail Pic'>";

$query6 = "SELECT * FROM pic_comments  WHERE picid = '$picid'";
        $result6 = mysql_query($query6);
while ($row6 = mysql_fetch_array($result6)) {
$k = mysql_num_rows($result6);
}
if ($k>0) 
{ 
$piccom = "$k"; 
$thepiccom = "$piccom <a href='view_pic_comments.php?picid=$picid' class='style3'>Comments</a>"; 

}else{
$piccom = "0";  
$thepiccom = "$piccom Comments";

}

 if (($i == 1) || (($i > $num_per_row) && (($i % $num_per_row) == 1))) {
        echo "<tr>";
    }


echo "<td align='center' width='804'><font face='Verdana' size='2'><a href=\"javascript:doWin('view_pic.php?picid=$picid')\">$link</a><br><font color='#4D4D4D'>$caption</font><br>Posted By: <font color='#4D4D4D'>$posted</font><br>$thepiccom<br>$edit   $delete<br></font></TD>";

if (($i == $num_records) || (($i >= $num_per_row) && (($i % $num_per_row) == 0))) {

       //Fill in empty columns as necessary
       if (($i == $num_records) && ($i % $num_per_row != 0)) {
           $n = $num_per_row - ($i % $num_per_row);
           echo "<td colspan=\"' . $n . '\"></td>";
       }

       echo "</tr><tr>
				<td height='20'></td>
			</tr>";
    }

    $i ++;
   
} 


echo "</table>"; 
        ?>

DavidAM · March 14, 2010

Your second query ($query6) is completely unnecessary; and you are wasting resources bringing all the comments back and just looking at the number of rows (multiple times in a loop). It would be better to get the number of comments as part of the first query:

SELECT P.*, COUNT(C.*) AS CmtCount 
FROM pictures P LEFT JOIN comments C ON P.picid = C.picid
WHERE album = $albumid AND status = 'Approved'

dsnyder · March 14, 2010

DavidAM, thank you for your reply and your help, BUT now I am really confused

I am assumming you are saying in my query I would do:

$query= "SELECT P.*, COUNT(C.*) AS CmtCount FROM pictures P LEFT JOIN pic_comments C ON P.picid = C.picid WHERE album = $albumid AND status = 'Approved'";

I then get the following errors:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource on line 147

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource on line 151

Line 147 = $num_records = mysql_num_rows($result); //Total number of records in result

Line 151 = while ($row = mysql_fetch_array($result)) {

Would you mind showing me how the code should be incorporated into what I have below. Get me pass these Errors... NOTE: "thepiccom"

= if 0 comments no link to view if there are comments shows link to view the comments (I want to keep this part)

        $query= "SELECT P.*, COUNT(C.*) AS CmtCount FROM pictures P LEFT JOIN pic_comments C ON P.picid = C.picid WHERE album = $albumid AND status = 'Approved'";
        $result = mysql_query($query);

        $num_records = mysql_num_rows($result); //Total number of records in result
        $num_per_row = 3; //Change to however many columns per row you desired
echo "<table width='550'>";
        $i = 1;
while ($row = mysql_fetch_array($result)) {
$thumb = $row[thumb];
$picid = $row[picid];
$path = $row[pic_path];
$caption = $row[caption];
$posted = $row[submited_by];
$edit ="<a href='edit_pic.php?picid=$picid' class='style3'>Edit</a>";
$delete ="<a href='delete_pic1.php?picid=$picid' class='style3'>Delete</a>";
$link = "<img src='http://www.jadindoffort.com/pictures/$path$thumb'
               alt='Thumb Nail Pic'>";

$CmtCount = $k;
if ($k>0) 
{ 
$piccom = "$k"; 
$thepiccom = "$piccom <a href='view_pic_comments.php?picid=$picid' class='style3'>Comments</a>"; 

}else{
$piccom = "0";  
$thepiccom = "$piccom Comments";

}

I appreciate any additional help you can offer.

DavidAM · March 16, 2010

That is an indication that the query failed. There could be a syntax error. Try a couple of things:

1) Modify the SQL to use the table aliases in the WHERE clause:

 $query= "SELECT P.*, COUNT(C.*) AS CmtCount 
FROM pictures P LEFT JOIN pic_comments C ON P.picid = C.picid 
WHERE P.album = $albumid AND P.status = 'Approved'";

2) Right after executing the query, check the result and show the error and the SQL:

        $result = mysql_query($query);
        if ( $result === false) { 
                echo mysql_error();
                echo '<BR>' . $query;
        }

we can work on any errors revealed by #1 above.

In any case, you'll need to change this line:

$CmtCount = $k;

to this:

$k = $row['CmtCount'];

By the way, are you getting warnings about these lines:

 $thumb = $row[thumb];
$picid = $row[picid];
$path = $row[pic_path];
$caption = $row[caption];
$posted = $row[submited_by];

because, unless you have "thumb", "picid", etc defined as constants they should be in quotes. If you are not getting warnings, you should turn on error reporting at the beginning of every page during development so you can see any problems that are reported:

error_reporting(E_ALL);

dsnyder · March 21, 2010

Hi DavidAM,

Thank you very much for your continued help. Your query works fine. It gets the accurate count on the comments for the pictiure. I setup a little test page with just the query Eg;

 <?php
require('config.php');
$query= "SELECT P.*, COUNT(C.*) AS CmtCount FROM pictures P LEFT JOIN pic_comments C ON P.picid = C.picid WHERE P.album = $albumid AND P.status = 'Approved'";
$k = $row['CmtCount'];
if ($k>0) { 
$piccom = "$k"; 
$thepiccom = "$piccom <a href='view_pic_comments.php?picid=$picid' class='style3'>Comments</a>"; 
}else{
$piccom = "0";  
$thepiccom = "$piccom Comments";
}
echo"$thepiccom";
?>

This works Fine..

However when I use it in my code I am getting the following errors.

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/jadindof/public_html/admin/pictures/pictures.php on line 148

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/jadindof/public_html/admin/pictures/pictures.php on line 152

CURRENT CODE =

<?php
require('config.php');

        $query= "SELECT P.*, COUNT(C.*) AS CmtCount FROM pictures P LEFT JOIN pic_comments C ON P.picid = C.picid WHERE P.album = $albumid AND P.status = 'Approved'";
        $result = mysql_query($query);

        $num_records = mysql_num_rows($result); //Total number of records in result
        $num_per_row = 3; //Change to however many columns per row you desired
echo "<table width='550'>";
        $i = 1;
while ($row = mysql_fetch_array($result)) {
$thumb = $row[thumb];
$picid = $row[picid];
$path = $row[pic_path];
$caption = $row[caption];
$posted = $row[submited_by];
$edit ="<a href='edit_pic.php?picid=$picid' class='style3'>Edit</a>";
$delete ="<a href='delete_pic1.php?picid=$picid' class='style3'>Delete</a>";
$link = "<img src='http://www.jadindoffort.com/pictures/$path$thumb'
               alt='Thumb Nail Pic'>";

$k = $row['CmtCount'];
if ($k>0) 
{ 
$piccom = "$k"; 
$thepiccom = "$piccom <a href='view_pic_comments.php?picid=$picid&albumid=$albumid&catid=$catid' class='style3'>Comments</a>"; 

}else{
$piccom = "0";  
$thepiccom = "$piccom Comments";

}

 if (($i == 1) || (($i > $num_per_row) && (($i % $num_per_row) == 1))) {
        echo "<tr>";
    }


echo "<td align='center' width='804'><font face='Verdana' size='2'><a href=\"javascript:doWin('view_pic.php?picid=$picid')\">$link</a><br><font color='#4D4D4D'>$caption</font><br>Posted By: <font color='#4D4D4D'>$posted</font><br>$thepiccom<br>$edit   $delete<br></font></TD>";

if (($i == $num_records) || (($i >= $num_per_row) && (($i % $num_per_row) == 0))) {

       //Fill in empty columns as necessary
       if (($i == $num_records) && ($i % $num_per_row != 0)) {
           $n = $num_per_row - ($i % $num_per_row);
           echo "<td colspan=\"' . $n . '\"></td>";
       }

       echo "</tr><tr>
				<td height='20'></td>
			</tr>";
    }

    $i ++;
   
} 


echo "</table>"; 
        ?>

SO Errors are Line: $num_records = mysql_num_rows($result); AND LINE: while ($row = mysql_fetch_array($result)) {

YOU ASKED: If I was getting errors for: $thumb = $row[thumb]; $picid = $row[picid]; etc, etc. NO I am Not

I Tried a bunch of different things, but have had no luck

Your continued help is greatly appreciatted.

DavidAM · March 22, 2010

That error indicates that $result is not a mysql resource. That means the call to mysql_query() failed. There is probably an error in the SQL statement. You can add a line after the call to display the error:

if ($result === false) { echo mysql_error(); }

to have the error message printed. Or you can echo the sql before calling it to see what it is:

echo $query;

The you can copy and paste into the query browser (or mysqlAdmin) to see if the query works and returns what you expect. It seems like I sometimes have trouble with COUNT(*), and have to specifically name a column -- COUNT(C.id) -- or something.

I don't see how the test page you did could work, you never execute the query. Maybe you just left the line out when you posted it?

Do you have error reporting on? add a line at the top of the page that says

error_reporting(E_ALL);

so you can see them (at least during development).

$thumb = $row[thumb];
// that line should be
$thumb = $row['thumb'];

you should get a warning that "thumb" is not a defined constant, and assuming you meant the literal string 'thumb' which is what you meant and so it works. But it is bad practice, you should quote those literals instead of letting the PHP engine make assumptions.

dsnyder · March 23, 2010

Hey DavidAM,

Again Thank You for your continued help with this. I did as you suggested and have gone through ALL my code and changed all $thumb = $row[thumb]; to

$thumb = $row['thumb'];

I also as you suggestted added to my test page.

if ($result === false) {

echo mysql_error();

}

echo $query;

Here is the error I am getting:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/jadindof/public_html/admin/pictures/testing2.php on line 7

0 CommentsYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) AS CmtCount FROM pictures P LEFT JOIN pic_comments C ON P.picid = C.picid WHE' at line 1SELECT P.*, COUNT(C.*) AS CmtCount FROM pictures P LEFT JOIN pic_comments C ON P.picid = C.picid WHERE P.album = 14 AND P.status = 'Approved'

Here is the code I have that is causing the Error.

<?php
error_reporting(E_ALL);

require('config.php');
$query= "SELECT P.*, COUNT(C.*) AS CmtCount FROM pictures P LEFT JOIN pic_comments C ON P.picid = C.picid WHERE P.album = $albumid AND P.status = 'Approved'";
$result = mysql_query($query);
$row = mysql_fetch_array($result);
$k = $row['CmtCount'];
if ($k>0) { 
$piccom = "$k"; 
$thepiccom = "$piccom <a href='view_pic_comments.php?picid=$picid' class='style3'>Comments</a>"; 
}else{
$piccom = "0";  
$thepiccom = "$piccom Comments";
}
echo"$thepiccom";
if ($result === false) { 
echo mysql_error(); 
}
echo $query;
?>

DavidAM · March 24, 2010

Yeah, It looks like it does not like the COUNT(C.*). I have had problems with mySql not liking to count the *. Pick a column in the pic_comments table (if you have a unique ID, use it), and change it to count that column; like COUNT(C.id) or COUNT(C.cmt_id) or whatever the column name is. If that does not work, then try copying the SQL and paste it in your Query Browser, or mySqlAdmin. See if you can track down the problem from there.

Sign In

For Each statement Maybe ?

Recommended Posts

dsnyder

Link to comment

Share on other sites

DavidAM

Link to comment

Share on other sites

dsnyder

Link to comment

Share on other sites

DavidAM

Link to comment

Share on other sites

dsnyder

Link to comment

Share on other sites

DavidAM

Link to comment

Share on other sites

dsnyder

Link to comment

Share on other sites

DavidAM

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information