inserting row count from users in table

[abda53]

i have a table, where it lists information about my users.. kinda like..

user count | edit functions | user name | user email | status | date created

this is for a image hosting site, and im trying to add a column where i can see how many images each user has uploaded (which is stored in the database)

this is what i have right now...

<table width="100%" cellpadding="4" cellspacing="0">
<tr>
<td align="center"><font face="verdana" size="1"><b></b></font></td>
<td align="center"><font face="verdana" size="1"><b>Edit</b></font></td>
<td><font face="verdana" size="1"><b>Name</b></font></td>
<td><font face="verdana" size="1"><b>E-mail</b></font></td>
<td align="center"><font face="verdana" size="1"><b>Status</b></font></td>
<td align="center"><font face="verdana" size="1"><b>Images</b></font></td>
<td align="center"><font face="verdana" size="1"><b>Created</b></font></td>
</tr>

<?php
$count = 0;
$query = mysql_query("SELECT * FROM member");
while($data = mysql_fetch_object($query, $db)) {
$count++;
if(($thisrow%2) == 0) {
$color = "#EEEEEE";
$thisrow++;
}
else {
$color = "#FFFFFF";
$thisrow++;
}

$temp = split("/", $data->created_on);
$date = $temp[1] . "/" . $temp[2] . "/" . $temp[0];
?>

<tr bgcolor="<?php print($color); ?>">
<td align="center"><font face="verdana" size="1"><?php print($count); ?></font></td>
<?php if($data->member_status == "D") { ?>
<td align="center"><font face="verdana" size="1"><a href="javascript:jp_activate('<?php print($data->member_name); ?>','<?php print($data->member_id); ?>');">activate</a></font></td>

<?php } ?>

<?php if($data->member_status == "A") { ?>

<td align="center"><font face="verdana" size="1"><a href="javascript:jp_delete('<?php print($data->member_name); ?>','<?php print($data->member_id); ?>');">delete</a></font></td>

<?php } ?>

<?php if($data->member_status == "I") { ?>

<td align="center"><font face="verdana" size="1"><a href="javascript:jp_activate('<?php print($data->member_name); ?>','<?php print($data->member_id); ?>');">activate</a>&nbsp;<a href="javascript:jp_delete('<?php print($data->member_name); ?>','<?php print($data->member_id); ?>');">delete</a></font></td>

<?php } ?>

<td><font face="verdana" size="1"><?php print($data->member_name); ?></font></td>
<td align="left"><font face="verdana" size="1"><?php print($data->member_email); ?></font></td>
<td align="center"><font face="verdana" size="1"><?php print($data->member_status); ?></font></td>
<td align="center"><font face="verdana" size="1"><?php //code here for image count ?></font></td>
<td align="center"><font face="verdana" size="1"><?php print($date); ?></font></td>

</tr>
<?php } ?>
</table>

the user information is in a database table called "members" and the image info stuff is called "images"

im trying to like.. print the row count of the images per user, but im not able to get it right.. ive read everything i could to try to fix it and understand it more, so please dont give me a link to mysql.com ive been there.. 😕

if you can give me any ideas, suggestions, or examples, or point me in the right direction.. that would be great.. thanks and if you can help me out, ill hook you up with a free account on my site 😉 lol

[nnichols]

Try this -

SELECT member.*, COUNT(images.USER_ID)
FROM member, images
WHERE member.USER_ID=images.USER_ID
GROUP BY images.USER_ID

this assumes that you have USER_ID (or similar field) as FK in the images table.

Hope this helps 😉

[abda53]

thanks.. i went with that, and now im kinda stuck.. heres part of my database.. i believe my query is right now. its showing the right results on myadmin thing.. but when i put it into php, im just pulling out the first result, and showing that on all the rows..

database "image"...
image_member_id (this part is image->member id)

database "member"...
member_id (this is member id.. the same as image_member_id)

<?
$query1 = mysql_query("SELECT member.member_id, COUNT(image.image_member_id) FROM member, image WHERE member.member_id=image_member_id GROUP BY member_id ORDER BY member_id asc");

$data1 = mysql_result($query1, $db);
?>

im not sure if im trying to pull it out correctly, with the mysql_result..

also i havent gotten to it yet, but when i use that query above in my admin thing, im showing the member/image id on the left, so i took it out the member.member_id like below.. not sure if that would affect it any..

SELECT COUNT(image.image_member_id) FROM member, image WHERE member.member_id=image_member_id GROUP BY member_id ORDER BY member_id asc

if you can give me any ideas, thatd be great

[nnichols]

Try something like this -

$sql = "SELECT member.member_id, COUNT(image.image_member_id) AS image_count 
FROM member, image 
WHERE member.member_id=image.image_member_id 
GROUP BY member_id 
ORDER BY member_id asc";

// it's good to add some debug in case it doesn't work
$result = mysql_query($sql) or die('Query failed.<br>MySQL Error: ' . mysql_error() . '<br>Query: ' . $sql);

// use something like this to loop through the rows returned
while ($row = mysql_fetch_assoc($result)) {
echo 'Member ID: ' . $row['member_id'];
echo '<br>Images: ' . $row['image_count'] . '<br><br>';

If you aren't going to select more columns from the 'member' table you should get rid of the JOIN and just use -

$sql = "SELECT image_member_id, COUNT(image_member_id) AS image_count 
FROM image 
GROUP BY image_member_id 
ORDER BY image_member_id asc";

Hope this helps 😉

[abda53]

alright..
with some tweaking.. this is what i ended up with..

<td align="center"><font face="verdana" size="1">
<?
$sql1 = "SELECT COUNT(image_member_id) AS image_count FROM image GROUP BY image_member_id ORDER BY image_member_id asc";

$result = mysql_query($sql1) or die('' . mysql_error() . $sql);

while ($row = mysql_fetch_assoc($result)) 
{
?>

<?php print 'Images: ' . $row['image_count'];
}
?></font></td>

and this is what i come out with..
1 | delete | eric | [e-mail] | A | Images: 2 Images: 13 Images: 4 Images: 65 Images: 11 Images: 1 | 01/05/2003

thats how it is in the table.. all going left to right.. umm.. like how i described it earlier.. but it SHOULD go like..
2
13
4
65
11
1

with 2 going to the first peron on the list, 13 the next, etc.. in the table...

what am i doing wrong now?

[nnichols]

Post your code in full, as it is now and an example of what you want it to output. At the moment you are using two queries (I assume) when you can do it easily and more efficiently with one.

[abda53]

ok.. ill upload the page in a zip that im trying to work on.. how it is right now (with the mods i made with your help..)
in the zip will be an image of how the page looks right now with it, so you can see what im talkin about

i just relooked at the database, and the image_member_id points the url to the user number, and thats what is pulled out in the query, but with that query i was pulling out 6 records, and i have 8 members.. im not sure, but is there a way to do a query and be like.. SELECT COUNT(image_member_id) AS image_count FROM image GROUP BY image_member_id ORDER BY image_member_id asc... and have it so that if there are no results for the image id to show just a 0?

the example you gave me earlier shows the id->image count..
SELECT member.member_id, COUNT(image.image_member_id) AS image_count FROM member, image

WHERE member.member_id=image.image_member_id GROUP BY member_id ORDER BY member_id asc

member_id image_count

1 2
2 13
4 4
6 95
7 11
8 1

but you can see how id 3 and 5 have no images, so its left out. i would think that would mess up the query when putting it into a table.. i dont know if im making much sense.. im gonna try to research it.. if you have any ideas, thatd be great..

thanks

[abda53]

[nnichols]

Have you checked your PMs?