Jump to content

count help pleasse cheers.


redarrow

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]
Link to comment
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.
Link to comment
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.
Link to comment
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--]
Link to comment
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);
Link to comment
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]
Link to comment
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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.