count help pleasse cheers.

[redarrow]

count not working how cheers.

[code]
$qu="select * from count(member_messages) as r where id='$id'";
$re=mysql_query($qu);

echo "<table width='200'border='4' bordercolor='black'><td align='center'><b>Messages:

<font color='red'>$r</b></font></td></table><br><br>";
[/code]

[Ninjakreborn]

I am only 50 percent sure but I think that in sql
select and from should be all caps.
and member_messages doesn't have the variable symbol in front of it.
Just some suggestions but I could be wrong.

[wildteen88]

Your SQL query is wrong. You are using count on the table name and not the table row. Your query should be this:
[code]$qu="SELLECT COUNT(*) FROM member_messages WHERE id='$id'";[/code]
If you want to use count on specific row do this:
[code]$qu="SELECT *, COUNT(field_name) AS num FROM member_messages WHERE id='$id'";[/code]
change where it says field_name with the actuall field name in your table.

[b]Businessman[/b]
SQL is forgiving if you use uppercase or lowercase for SQL keywords. I prefer to use uppercase with keywords in SQL queries.

[Ninjakreborn]

Ok so atleast hopefully he got the help he needed.

[poirot]

[!--quoteo(post=382230:date=Jun 10 2006, 07:36 AM:name=wildteen88)--][div class=\'quotetop\']QUOTE(wildteen88 @ Jun 10 2006, 07:36 AM) [snapback]382230[/snapback][/div][div class=\'quotemain\'][!--quotec--]
[code]$qu="SELECT *, COUNT(field_name) AS num FROM member_messages WHERE id='$id'";[/code]
change where it says field_name with the actuall field name in your table.

[b]Businessman[/b]
SQL is forgiving if you use uppercase or lowercase for SQL keywords. I prefer to use uppercase with keywords in SQL queries.
[/quote]
Won't that throw an error like:
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]#1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause [/quote]

But something like this should do:
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] *, [color=blue]COUNT[/color](field_name) [color=green]AS[/color] num [color=green]FROM[/color] [color=orange]member_messages[/color] [color=green]WHERE[/color] id[color=orange]=[/color][color=red]'$id'[/color]" GROUP BY field; [!--sql2--][/div][!--sql3--]

I prefer to use uppercase for keywords, but it's just "aesthetic". Kinda like indentation, you don't need it, but if you use it it makes the code more organized.

[.josh]

also businessman, column and table names do not use $ in front of them, if you are using their name. for example, it would be
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--]$sql [color=orange]=[/color] [color=red]"[span style=\'color:blue;font-weight:bold\']select[/color] * [color=green]from[/color] [color=orange]tablename[/color] [color=green]where[/color] name[color=orange]=[/color]'[/span]blah'"; [!--sql2--][/div][!--sql3--]
not
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--]$sql [color=orange]=[/color] [color=red]"[span style=\'color:blue;font-weight:bold\']select[/color] * [color=green]from[/color] [color=orange]$tablename[/color] [color=green]where[/color] $name[color=orange]=[/color]'[/span]blah'"; [!--sql2--][/div][!--sql3--]
... unless you were like, working with multiple tables, and your script was going to query a specific table, or maybe your script would query a column dependant on something or whatever:

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--]$columnname [color=orange]=[/color] [color=red]'name'[/color];
$tablename[color=orange]=[/color] [color=red]'tablename'[/color];
$sql [color=orange]=[/color] [color=red]"[span style=\'color:blue;font-weight:bold\']select[/color] * [color=green]from[/color] [color=orange]$tablename[/color] [color=green]where[/color] $columnname[color=orange]=[/color]'[/span]blah'";
[!--sql2--][/div][!--sql3--]

[joquius]

[!--quoteo(post=382230:date=Jun 10 2006, 03:36 PM:name=wildteen88)--][div class=\'quotetop\']QUOTE(wildteen88 @ Jun 10 2006, 03:36 PM) [snapback]382230[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Your SQL query is wrong. You are using count on the table name and not the table row. Your query should be this:
[code]$qu="SELLECT COUNT(*) FROM member_messages WHERE id='$id'";[/code]
If you want to use count on specific row do this:
[code]$qu="SELECT *, COUNT(field_name) AS num FROM member_messages WHERE id='$id'";[/code]
change where it says field_name with the actuall field name in your table.

[b]Businessman[/b]
SQL is forgiving if you use uppercase or lowercase for SQL keywords. I prefer to use uppercase with keywords in SQL queries.
[/quote]
If you needed the row number of a general query would it not be better to

[code]
$sql = "SELECT * FROM `table` WHERE `field` = '$value'";
$result = mysql_query ($sql) or die (mysql_error ());
$rows = mysql_num_rows ($result);
[/code]

I suppose a count is marginally better.

(this is of course if you want the row data and not just the number of rows)

In any case u have to take the $count_query result and do
$num_rows = mysql_result ($count_query, 0);

[.josh]

well, why make php do it when you can have sql do it? php specializes in manipulating the data. sql specializes in keeping track of it. so it is better for sql to tell you how many than php. and if you notice in the query, it is selecting the data also, not just getting the count.

[redarrow]

thanks every one but how do i get the num out cherrs

[code]
$qu="SELECT *, COUNT(messages) AS num FROM member_messages WHERE id='$id'";
$re=mysql_query($qu);


echo "<table width='200'border='4' bordercolor='black'><td align='center'><b>Messages:

<font color='red'>$num</b></font></td></table><br><br>";

[/code]

[joquius]

$qu="SELECT *, COUNT(messages) AS num FROM member_messages WHERE id='$id'";
$re=mysql_result (mysql_query ($qu), 0);

[redarrow]

[!--quoteo(post=382480:date=Jun 11 2006, 12:48 PM:name=joquius)--][div class=\'quotetop\']QUOTE(joquius @ Jun 11 2006, 12:48 PM) [snapback]382480[/snapback][/div][div class=\'quotemain\'][!--quotec--]
$qu="SELECT *, COUNT(messages) AS num FROM member_messages WHERE id='$id'";
$re=mysql_result (mysql_query ($qu), 0);
[/quote]

solved cheers.