Count From Mysql Won't Print Result?

[rocky48]

Hi

 

I have been improving my code and decided I would let the users know how many records of a particular Event there were when the SELECT query was run.

 

The SQL works fine, but when I add it into the display block I use to display the pre text and then the table no output from the count variable is displayed.

 

However I put an echo before the display block and it does output the correct number.

 

Here is a snippet of the code that is causing the problem:

$Count_sql = "SELECT Event, COUNT(Sub_Type) FROM Verses WHERE Event='".$_POST["Event_Type"]."'";
$Count_res = mysqli_query($mysqli, $Count_sql) or die(mysqli_error($mysqli));
while ($Count_Event = mysqli_fetch_array($Count_res)) {

 echo "There are ".$Count_Event['COUNT(Sub_Type)'];
}
//create the display string
$display_block = "
<p> The Event Type is: <b>
 ".$Event_Name."</b> </p>
[color=#ff0000]<p> Number of Verses for this event Type:<b> </p>
 ".$Count_Event['COUNT(Sub_Type)']."</b>  [/color]
<table width=\"70%\" cellpadding=\"3\" cellspacing=\"1\" border=\"1\" BGCOLOR=\"#87CEEB\" >
<tr>
<th>ID</th>
<th>VERSE</th>
<th>MOOD/SUB TYPE</th>
</tr>";

The sentence before is displyed but the ".$Count_Event['COUNT(Sub_Type)']." is not displayed.

Where am I going wrong?

[premiso]

Make it easier on yourself and use aliases. 

 

$Count_sql = "SELECT Event, COUNT(Sub_Type) as Sub_Type_Count FROM Verses WHERE Event='".$_POST["Event_Type"]."' GROUP BY Event";
$Count_res = mysqli_query($mysqli, $Count_sql) or die(mysqli_error($mysqli));
$Count_Event = mysqli_fetch_array($Count_res);
 
//create the display string
$display_block = "
<p> The Event Type is: <b>
  ".$Event_Name."</b> </p>
[color=#ff0000]<p> Number of Verses for this event Type:<b> </p>
  ".$Count_Event['Sub_Type_Count']."</b>  [/color]
<table width=\"70%\" cellpadding=\"3\" cellspacing=\"1\" border=\"1\" BGCOLOR=\"#87CEEB\" >
<tr>
<th>ID</th>
<th>VERSE</th>
<th>MOOD/SUB TYPE</th>
</tr>";

 

Does not answer your question, but yea, it is possible just makes for extra work and randomness at times. Go for the alias. 

 

EDIT:

I made some changes to your code, you seem to only be pulling one event, so added the group by which should only return 1 line, and removed the while loop as 1 row should only ever be returned. 

Edited December 6, 2012 by premiso

[Barand]

The reason for failure was your while loop. Once the end of the loop is reached, $Count_Event is set to false so it is no longer an array of row values

[rocky48]

Hi

I have edited as in reply 1 and it still does not output the count!

I removed the while loop, so why is it not outputting the value now????

[Christian F.]

Post the updated code, along with some example data. That'll be of great help in determining why things are happening.

Also, double-check that you've turned error reporting on in PHP, and that you're handling all of the MySQL errors. Jessica's signature (just search for one of her posts) has a lot of good information on how to debug your scripts, so I heartily recommend reading through those links.

[rocky48]

Thanks for the offer but it is now working OK!