select count loop

cmpennington

I am having trouble figuring out a way to count the number of condolences that are left for a family member who has died. In other words my program lists the names of people who have died. When you select that person, his unique ID is passed and all of the condolences for him are selected and printed on screen.

What I would like to do is to know how many (the number) of condolences that are online for the deceased without having to select each person to find out.

The problem is that the list of each deceased person is created dynamicall in a loop, as well as the condolences.

Here is most of the code I have been using:

echo "<p align=center><font size=6 color=green><b>Condolence System</b></font></p>";
    // show employee list

   $result = mysql_query("SELECT * FROM employees ORDER BY date DESC LIMIT 7",$db);


 if ($myrow = mysql_fetch_array($result)) {




  // display list if there are records to display
echo "<CENTER><TABLE CELLPADDING=5 CELLSPACING=0 WIDTH=500><TR BGCOLOR=#000000><TD ALIGN=left WIDTH=150><B><FONT FACE=Garamond SIZE=3 COLOR=#FFFFFF>Last Name</FONT></B></TD><TD ALIGN=CENTER WIDTH=150><B><FONT FACE=Garamond SIZE=3 COLOR=#FFFFFF>Full Name</FONT></B></TD><TD ALIGN=center WIDTH=150><B><FONT FACE=Garamond SIZE=3 COLOR=#FFFFFF>Date</FONT></B></TD></TR><br>";
      do {
$query1 = "select count(*) FROM condolenceone WHERE cond_id='$cond_id'"; 
$numguests1 = mysql_query($query1) or die("Select Failed!");
$numguest1 = mysql_fetch_array($numguests1);
$no2 = $numguest1[0];

    printf("<tr><td align=left>%s</td><td align=center><a href=\"%s?id=%s&Fullname=%s\">%s</a></td><td align=center>%s</td></tr>\n", $myrow["last"], $PHP_SELF, $myrow["id"], $myrow["Fullname"], $myrow["Fullname"], $numguest1[0]);
  } while ($myrow = mysql_fetch_array($result));
} else {




// no records to display

  echo "Sorry, no records were found!";	

}

}

bastien

select count(distinct(condolensces)) from condolenceone group by id

cmpennington

It didn't seem to work, maybe if I give you a clearer picture you can help me to find my way through this:

Here is the database:

Database name: condolenceone

id -> is the name of the deceased person that they are going to leave a message for.

cond_id -> is an auto-increment id to keep all of the condolences seperate.

I am trying to use the code above to count the number of condolences left for #144 and #146 and #154, etc.. and have them print into this portion of the code that when it works would look as such.

                                      -Condolences-

LOCATION DECEASED CONDOLENCES ONLINE

Hartsville John Doe(⁾ 3
Columbia James Smith 2
LAs Vegas Randy Jackpot 0
New York Larry Henson 0

    ------Please select a deceased name to view condolences----

[submit button]

Hope this gives you some better info as to what I'm shooting for!
And thanks for helping on this - cmpennington

notionlogic

Doesn't this work?

select id, count(*) from condolenceone group by id

Or have I misunderstood the requirements?

cmpennington

I keep getting the select failed error.

Is this code right?

$query1 = mysql_query("select id, count(*) from condolenceone group by id",$db);
$numguests1 = mysql_query($query1) or die("Select Failed!");
$numguest1 = mysql_fetch_array($numguests1);
$no2 = $numguest1[0];
echo $no2;

HEEEELLLLPPP!!!

cmpennington

Any sugestions? Anyone?😕

Mce

Please replace the die("Select Failed") with die(mysql_error()) and post the resulting error message

Thanks,
-Mce

cmpennington

You have an error in your SQL syntax near 'Resource id #3' at line 1

Mce

Originally posted by cmpennington
You have an error in your SQL syntax near 'Resource id #3' at line 1

Umm...wow never seen that one...
Try this instead of what I said before
die(mysql_error()."<br>STMT: $query1<br>\n")

The output of the tho together should be more telling of the problem.

-Mce

cmpennington

What is this error?

You have an error in your SQL syntax near 'Resource id #3' at line 1
STMT: Resource id #3

Lars_Berg

$query1 = mysql_query("select id, count(*) from condolenceone group by id",$db) or die("Query failed with " . mysql_error()); 

while ($numguest1 = mysql_fetch_array($query1)) { 
echo $numguest1[0] . " has " . $numguest1[1] . " condoleances <br>"; 
}

Mce

Ok I see the problem now....

this line
$query1 = mysql_query("select id, count(*) from condolenceone group by id",$db);

should be
$query1 = "select id, count(*) from condolenceone group by id";

and this one
$numguests1 = mysql_query($query1) or die("Select Failed!");

should be
$numguests1 = mysql_query($query1,$db) or die("Select Failed: " . mysql_error());

$numguest1 = mysql_fetch_array($numguests1);
$no2 = $numguest1[0];
echo $no2;

The first line was calling mysql_query therefore making $query1 a result resource identifier then on the next line you were calling mysql_query with the $query whict was a resource identifier and not an SQL statement.

-Mce