[RESOLVED] Drop down populated by mysql to also show number of rows?

[suttercain]

I am trying to echo the amount of issues for each title in my drop down. Right now it is working as far as displaying each title as it is grouped by title. What I want to o is echa the amount of issue per each title

EXAMPLE
Title1 - 76 Results
Title2 - 189 Results
Title3 - 9 Results

So when they click the dopw down they actually see the number of results they'll get when they go to the display page.

<?php 
				  $result = mysql_query("SELECT title FROM comics GROUP BY title") or die(mysql_error());
 				  $options="";
				  echo "<select name='title'>";  

 				  while($row = mysql_fetch_array($result)){
				  $rows = mysql_num_rows($result);
 				  $title=$row["title"];
 				  echo "<option value='" . $title . "'>".$title. " (" . $rows . " Issues)</option>'";
 					}
				  echo "</select>";
				  ?>
				  <input type="submit" name="submit">
				  </form>

Right now it just echoed 363 issues next to each title in the drop down.

Any suggestions?

Thank you for your time.

SC

[ClarkF1]

I haven't tried this myself but it might work

<?php 
				  $result = mysql_query("SELECT title, COUNT(title) AS count FROM comics GROUP BY title") or die(mysql_error());
 				  $options="";
				  echo "<select name='title'>";  

 				  while($row = mysql_fetch_array($result))
{ 				  

$title=$row["title"];
 				  echo "<option value='" . $title . "'>".$title. " (" . $row['count'] . " Issues)</option>'";
 					}
				  echo "</select>";
				  ?>
				  <input type="submit" name="submit">
				  </form>

[bradgrafelman]

Also note that if you're using a MyISAM table, usint COUNT(*) instead might be faster (don't quote me on this, though).

[suttercain]

Hello,

Thank you for replying. I ran that code and got this error:

Parse error: parse error, unexpected T_VARIABLE in D:\wamp\www\Superman\comic_index.php on line 48

I thought it was because it was missing the { after the loop, but that didn't help.

SC

[bradgrafelman]

Glancing at his code snippet, I can't see any glaring mistakes, so could you post this section of your code and point out which line is #48 for us?

[suttercain]

Sure thing, and thank you for your time:

				  <form action="comic_index_view.php" method="post">
				  <?php 
				  $result = mysql_query("SELECT title, COUNT(title) AS count FROM comics GROUP BY title") or die(mysql_error());
                  $options="";
                  echo "<select name='title'>";  

                  while($row = mysql_fetch_array($result) 
                  $title = $row["title"];             //LINE 48
                  echo "<option value='" . $title . "'>".$title. " (" . $row['count'] . " Issues)</option>'";
                    }
                  echo "</select>";
                  ?>
                  <input type="submit" name="submit">
                  </form>

[bradgrafelman]

Note that it's easier to read PHP code when you use the [ PHP ] tags instead of the general 'code' tags - it adds syntax highlighting.

All you need to do is add a closing ')' parenthesis as well as an opening brace '{' for that while loop and it should work.

Then again, there's no need to create a new $title variable, and you could simplify that loop to:

echo '<select name="title">';  

while($row = mysql_fetch_array($result))
	echo '<option value="' . $row['title'] . '">' . $row['title'] . '</option>';
echo '</select>';

[suttercain]

Thanks. I am a newbie here and did not know about the colored syntax, but I will use it in the future. Thank you for your time.

[ClarkF1]

It was my fault........you must have copied the code and tried it before I corrected the syntax errors 😃

[bradgrafelman]

ClarkF1 wrote:

It was my fault........you must have copied the code and tried it before I corrected the syntax errors 😃

Sneaky, sneaky!

Nah, it's ok, because no one around here simply copies and pastes code without examining themselves, right? Right?