[SOLVED] Post Counting Problems

[everlifefree]

Ok here's the code I have come up with so far and what I'd like to be able to do is count the number of posts by each user. The querys I would like to use out of the videos table to count them is: user. But currently all I can get it to count to is 1. So I know I did something wrong. Please Help...

// COUNT ALL USERS ///////////////////////////////////////////////////////////
$selectCount = myQ("SELECT SQL_CALC_FOUND_ROWS * 
FROM `[x]videos`
WHERE `user`='{$_GET["id"]}' 

");
$countRowsSelectCount = myQ("SELECT FOUND_ROWS()");
$countRowsResultCount = mysql_fetch_row($countRowsSelectCount);
$totalRowsCount = $countRowsResultCount[0];

$tpl -> AssignArray(array(
	"video.total" => $totalRowsCount,
	"dbCount.sample" => $totalRows,
));

[yaz]

Can't you do:

 

SELECT COUNT(id) AS TotalPosts FROM [x]videos WHERE `user`='{$_GET["id"]}'

 

ps: mysql_escape_real_string() those $_GET vars....

[timmah1]

This is what I use and it works fine

 

$item_count = mysql_fetch_assoc(mysql_query("SELECT count(*) as num FROM items WHERE username = '$user[username]'"));

[everlifefree]

would anyone mind writing the code the use with my variables in it because I am a little confused where to insert them.

[yaz]

$query = mysql_query("SELECT COUNT(id field of the table) AS total FROM `[x]videos` WHERE `user`='{$_GET["id"]}' ");

$result = mysql_fetch_assoc($query);

 

your total would be in this variable: $result['total']

 

Using a COUNT(id of table) is a litle better than using "*". If you have a table with a lot of fields and/or rows, using the star causes your database to slow down A LOT.

[everlifefree]

If I am think right then my id area counts up 1 for the first post ever by anyone and then 2 for the second post anyone ever posted and so on how exactly would I implent this is it's the right thing?

[everlifefree]

I just reliezed a flaw in my code of the first post it should actually be like this but I still have the same problem.

 

// COUNT VIDEO POSTS ///////////////////////////////////////////////////////////
$selectCount = myQ("SELECT SQL_CALC_FOUND_ROWS * 
FROM `[x]videos`
WHERE `user`='".me('id')."' 

");
$countRowsSelectCount = myQ("SELECT FOUND_ROWS()");
$countRowsResultCount = mysql_fetch_row($countRowsSelectCount);
$totalRowsCount = $countRowsResultCount[0];

$tpl -> AssignArray(array(
	"video.total" => $totalRowsCount,
	"dbCount.sample" => $totalRows,
));

[yaz]

The solutions we provided should still work. Just replace your variables.

[everlifefree]

ok, thanks last quest is do I need to input any into the "(id field of the table)" part or is that whats supposed to be there?

[timmah1]

I believe from looking at your code

(id field of the table) = videos

[yaz]

I guess depending on your table structure... generally my table field would look like this:

 

VideoID

VideoName

UserID

Date

 

So I would do:

 

SELECT COUNT(VideoID) AS Total FROM Videos WHERE UserId = '$me'

 

[nadeemshafi9]

instead of using a mysql database function use a normal count like a normal person what does found_rows() return ? if its a number you cant count it, whats the point of

 

// COUNT ALL USERS ///////////////////////////////////////////////////////////

$selectCount = myQ("SELECT SQL_CALC_FOUND_ROWS *

FROM `[x]videos`

WHERE `user`='{$_GET["id"]}'

 

it dosent do anything

[nadeemshafi9]

im sure this wont work

 

$countRowsSelectCount = myQ("SELECT FOUND_ROWS()");

$countRowsResultCount = mysql_fetch_row($countRowsSelectCount);

 

to count things in a table you do somthing like this:

 

$sql = "SELECT COUNT(*) AS whatever FROM table"

 

$result = mysql_query($sql);

 

$row = mysql_fetch_row($result);

 

echo $row[0]; // somtimes u can do echo $row['whatever '];

 

 

BASICALY what timmah1  said

[nadeemshafi9]

oh so u select ur records and then perform a mysql internal function which does the count, well u need to know what variable thats function puts the counted result into then u can use it, SELECT @variable

[nadeemshafi9]

TRY

 

$countRowsSelectCount = myQ("SELECT FOUND_ROWS() AS somthing");

$countRowsResultCount = mysql_fetch_row($countRowsSelectCount);

$totalRowsCount = $countRowsResultCount[0];

 

TRY

myQ("SELECT FOUND_ROWS()");

$countRowsSelectCount = myQ("SELECT @read_the_function_to_see_the_var");

$countRowsResultCount = mysql_fetch_row($countRowsSelectCount);

$totalRowsCount = $countRowsResultCount[0];

[everlifefree]

Here's the new code I tried:

 

// COUNT VIDEO POSTS ///////////////////////////////////////////////////////////
$query = mysql_query("SELECT COUNT(*) AS total FROM `[x]videos` WHERE `user`='".me('id')."'
$result = mysql_fetch_assoc($query);

$tpl -> AssignArray(array(
	"video.total" => $result,
));

 

But I get this error:

Parse error: syntax error, unexpected T_STRING in /home/sites/spadespace.com/public_html/modules/users/desktop.php on line 227

 

And line 227 is one of these two:

 

$query = mysql_query("SELECT COUNT(*) AS total FROM `[x]videos` WHERE `user`='".me('id')."'
$result = mysql_fetch_assoc($query);

[nadeemshafi9]

your missing a ; but try what i said before where i uses AS somthing

[everlifefree]

I tried both and neither worked one gave me nothing and the other return a 1 aswell was I supposed to put something special in the something spot?