[SOLVED] Is this possible with a drop down and MySQL?

[suttercain]

Hi guys, and thanks to the people who helped getting my drop down working.

 

I was wondering if this is possible before I start trying it.

 

I have a drop down that is populated by MySQL and grouped by the column 'title'. So when you get the drop down you see all the titles:

 

Action Comics

Batman

Green Lantern

Superman

 

Now is this possible, to number the results for each title in the drop down...

 

EXAMPLE:

Action Comics (843 Issues)

Batman (7 Issues)

Green Lantern (23 Issues)

Superman (634 Issues)

 

That way before the user clicks the submit they now how many issues of that title is in the database?

 

Here is the code I am using for the MySQL generation:

<form action="comic_index_view.php" method="post">
			  <?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)){
				  $title=$row["title"];
				  echo "<option value='" . $title . "'>".$title.'</option>';
					}
			  echo "</select>";
			  ?>
			  <input type="submit" name="submit">
			  </form>

 

 

Thanks for any answers or advice.

 

SC

[suttercain]

Duh....

 

I should have tried first

 

<form action="comic_index_view.php" method="post">
			  <?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>

[suttercain]

Nope, I thought it was working, but al it does is echo the same number for each comic title, 363... how would I do it for each comic title... foreach?

[Barand]

<?php
$result = mysql_query("SELECT title, COUNT(*) as numissues
                FROM comics 
                GROUP BY title") or die(mysql_error());
?>

[suttercain]

Hi Barand,

 

Thanks I have been playing with what you wrote here and I know it is something I am missing and not catching on to.

 

I tried:

<?php 
			  $result = mysql_query("SELECT title, COUNT(*) as numissues 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>";
			  ?>

 

And it still echos 363 Issues nect to each title.

 

So I removed the

$rows = mysql_num_rows($result);

and still had the same issue.

 

I also tried replacing numissues from the MySQL select stament with title and it changed the title "Action Comics" to the number of actual issues, which is what I am looking for without losing the title. So instead of reading Title Number of Issues it Reads Number of Issues #363. Also when I add the COUNT(*) and try to submit it, it messing up my results page and it doesn't echo anything.

[suttercain]

Chitty Chitty Bang Bang?

[Barand]

Sorry to keep you waiting and make you so impatient. I sometimes stop to eat. Perhaps I should have informed you first.

 

I have your solution but I think I'll also stop to sleep too.

 

[suttercain]

Chitty Chitty Bang Bang? = Impatience? I thought it was a light hearted reference to a Disney film. But who I am to know my own intentions and will?

 

None the less I thank you for your time Barrand, you led me on the right path and I was able to rectify the issue.

[HeyRay2]

Glad to hear you rectified the issue.

 

Just to ensure anyone looking at this thread is able to benefit from the solution, I'm assuming you solved the problem by using something along the lines of this:

 

<?php
   $result = mysql_query("SELECT title, COUNT(*) as numissues FROM comics GROUP BY title") or die(mysql_error());
   echo "<select name='title'>";  
   while($row = mysql_fetch_array($result)){
     $rows = $row["numissues"]; // Changed from $rows = mysql_num_rows($result);
     $title=$row["title"];
     echo "<option value='" . $title . "'>" . $title . " (" . $rows . " Issues)</option>'";
   }
   echo "</select>";
?>

 

[suttercain]

This is what I did do get the results:

 

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