Jump to content

Archived

This topic is now archived and is closed to further replies.

redarrow

count help pleasse cheers.

Recommended Posts

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]

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
[!--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.

Share this post


Link to post
Share on other sites
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--]

Share this post


Link to post
Share on other sites
[!--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);

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
$qu="SELECT *, COUNT(messages) AS num FROM member_messages WHERE id='$id'";
$re=mysql_result (mysql_query ($qu), 0);

Share this post


Link to post
Share on other sites
[!--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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.