Programming Help

Web Development



What's Here?

  • Members: 132,687
  • Replies: 471,792
  • Topics: 73,154
  • Snippets: 2,541
  • Tutorials: 667
  • Total Online: 1,271
  • Members: 63
  • Guests: 1,208

Who's Online?

Welcome to Dream.In.Code
Getting PHP Help is Easy!

Join 132,687 PHP Programmers for FREE! Get instant access to thousands of PHP experts, tutorials, code snippets, and more! There are 1,271 people online right now. Registration is fast and FREE... Join Now!




COUNT(*) problem =/

 
 Reply to this topicStart new topic

COUNT(*) problem =/
JBrace1990
post 6 Jun, 2008 - 05:54 PM
Post #1


D.I.C Regular

Group Icon
Joined: 9 Mar, 2008
Posts: 474



Thanked 21 times

Dream Kudos: 350
My Contributions
php
           $sql = mysql_query("SELECT COUNT(*) AS total,id FROM adoptables GROUP BY id")or die(mysql_error());
           $sql2 = mysql_query("SELECT COUNT(*) AS total,owner_email FROM adoptables GROUP BY owner_email,owner_password")or die(mysql_error());
           $row = mysql_fetch_array($sql);
           $row2 = mysql_fetch_array($sql2);
           echo "Server Stats are as follows:</br>";
           echo "The site contains ".$row['total']." creatures under a total of ".$row2['total']." Accounts.</br>";


ok, I have this cde.... in the database, there are three rows.... they are as follows:
CODE
INSERT INTO `adoptables` (`id`, `monster_name`, `monster_type`, `monster_level`, `owner_email`, `owner_password`) VALUES
(1, 'JBrace1990', 'Admin', 21, 'email1', 'XXXX'),
(2, 'JBrace1990', 'Horse', 1, 'email1', 'XXXX'),
(3, 'Awesomeness', 'Horse', 1, 'email2', 'XXX');


NOTE: owner_email and owner_password are X'd out for a reason 8)

anyway, it should tell me there are 3 creatures and 2 accounts..... but it's not telling me a damn thing, it only says 1.... any ideas?
User is offlineProfile CardPM

Go to the top of the page

BetaWar
post 6 Jun, 2008 - 06:47 PM
Post #2


#include <soul.h>

Group Icon
Joined: 7 Sep, 2006
Posts: 1,987



Thanked 78 times

Dream Kudos: 1175
My Contributions
Well, the count() function in that context is different than counting the number of items in an array. To do what you are wanting to do, you would neet to change the code to look like this:


QUOTE
$sql = mysql_query("SELECT * FROM adoptables GROUP BY id")or die(mysql_error());
$sql2 = mysql_query("SELECT * FROM adoptables GROUP BY owner_email,owner_password")or die(mysql_error());
$row = mysql_fetch_array($sql);
$total = count($row);
$row2 = mysql_fetch_array($sql2);
$total2 = count($row2);
echo "Server Stats are as follows:</br>";
echo "The site contains ".$total." creatures under a total of ".$total2." Accounts.</br>";


If you want to use the count() function inside the sql statement it should look something like this (this is an example from some of my own code):

php
$result = mysql_query("select count(*) from halo3");  
    $total = mysql_result($result, 0, 0);


So you have to use the mysql_result() function:
http://us2.php.net/manual/en/function.mysql-result.php

Or, if you want you could do use:
php
$total = mysql_num_rows($row);


All of these methods will allow you to get the length of the returned array.

This post has been edited by BetaWar: 6 Jun, 2008 - 06:48 PM
User is offlineProfile CardPM

Go to the top of the page

JBrace1990
post 7 Jun, 2008 - 06:52 AM
Post #3


D.I.C Regular

Group Icon
Joined: 9 Mar, 2008
Posts: 474



Thanked 21 times

Dream Kudos: 350
My Contributions
1. mysql_num_rows is system intensive (and bad coding in general) when you get into larger applications and should be avoided wherever possible.. see, MySQL stores the number of rows by default, and COUNT(*) gets them... the GROUP BY groups them, which means that it should be working correctly, and efficiently... Read This for the reasons...
2. the php count() function counts the number of elements in an array, which us useless to me because with my queries there's several columns selected
3. I want to count the number of rows, not columns
4. The way i'm using count is perfectly fine, i've used it that way before on applications that work, i'm just not sure why it's not working this time where it has all the other times

so, does anyone know why i'm only getting 1 from the query? it works perfectly fine when I run it through PHPMyAdmin, but not through my actual php =/

[edit]ok, problem solved with the help of one of my coding friends... as of now, it displays correctly...
php
$sql = mysql_query("SELECT COUNT(id) AS total FROM adoptables")or die(mysql_error());
           $sql2 = mysql_query("SELECT COUNT(owner_email) as total_accounts FROM adoptables GROUP BY owner_password")or die(mysql_error());
           $row = mysql_fetch_array($sql);
           $row2 = mysql_fetch_array($sql2);
           echo "Server Stats are as follows:</br>";
           echo "The site contains <strong>".$row['total']."</strong> creatures under a total of <strong>".$row2['total_accounts']."</strong> Accounts.</br>";

[/edit]

This post has been edited by JBrace1990: 7 Jun, 2008 - 07:11 AM
User is offlineProfile CardPM

Go to the top of the page

Fast ReplyReply to this topicStart new topic
Time is now: 11/23/08 07:21AM
Invision Power Board v2.1.7 © 2008  IPS, Inc.
Licensed to: MediaGroup1

Live PHP Help!

PHP Tutorials

Reference Sheets

PHP Snippets

Bye Bye Ads

Free DIC T-Shirt

T-Shirt Example

Related Sites

Monthly Drawing

Thumb Drive

Partners

Top Contributors

Top 10 Kudos This Month