Page 1 of 1

[SOLVED] Dynamica dropdown box

Posted: Sun Mar 06, 2005 4:41 pm
by Addos
Hi,
I wonder if anyone can help with me trying to dynamically populate a dropdown box using PHP. I have searched for over an hour or so and even looked here at the turorials but to no avail.
I have been working with this:

Code: Select all

<select name="Counties_ID">
	  <?PHP
	  $cat_array=get_locations()
	  foreach ($cat_array as $thiscat)
	  &#123;
	  echo '<option value="';
	  echo $thiscat&#1111;'Counties_ID'];
	  echo '"' ;
	  echo '>';
	  echo $thiscat&#1111;'Counties_Name'];
	  echo "</option>\n";
	  &#125;
       ?>
      </select>
My MySQL database has one table called ‘locations’ and the 2 columns in it are 'Counties_ID' and 'Counties_Name'.

But I’m so new to this that I’m not getting very far. I have done this in ColdFusion ok but I’m trying to transfer this CF site over to PHP so if anyone can point me in the right direction or tell me if I’m close or way off I’d be most grateful.
Thanks a mil
Brian

Posted: Sun Mar 06, 2005 6:29 pm
by feyd
have you tried this? How is get_locations() defined? (If need be, post its code. Remember to change/remove the username and password passed to mysq on connection, as well as the host name if different from localhost.)

Posted: Mon Mar 07, 2005 3:17 am
by Addos
Thanks for getting back to me. I’m not to sure I follow what you mean so I have posted most of the code from the page I’m working on. This has no passwords as I use a separate folder for these to connect to the database etc.
I simply want to populate a dropdown box with a list of placed that are in a table in my database.
I have done this with CF:

Code: Select all

<select name="Counties_ID">
        <option value="" selected>Search All</option>
        <cfoutput query="rsCounties">
          <option value="#rsCounties.Counties_ID#">#Counties_Name#</option>
        </cfoutput>
      </select>
But obviously doing it with PHP is a little more difficult for me anyway!

Thanks again
Brian

Code: Select all

<?php require_once('Connections/mydatabase.php'); ?>
<?php
mysql_select_db($database_mydatabase, $mydatabase);
$query_rstSearchLocations = "SELECT * FROM locations ORDER BY Counties_ID ASC";
$rstSearchLocations = mysql_query($query_rstSearchLocations, $mydatabase) or die(mysql_error());
$row_rstSearchLocations = mysql_fetch_assoc($rstSearchLocations);
$totalRows_rstSearchLocations = mysql_num_rows($rstSearchLocations);
?> 

<FORM ACTION="results.php" name="imtSearch" METHOD="POST">
<table width="100%" border="0" cellspacing="0" cellpadding="2">
<tr>
	  <td><select name="Counties_ID">
	  <?PHP
	  $cat_array=get_locations()
	  foreach ($cat_array as $thiscat)
	  &#123;
	  echo '<option value="';
	  echo $thiscat&#1111;'Counties_ID'];
	  echo '"' ;
	  echo '>';
	  echo $thiscat&#1111;'Counties_Name'];
	  echo "</option>\n";
	  &#125;
       ?>
      </select>
      Choose County</td>
    </tr><tr> <td></td>  </tr>	<tr> <td><input name="submit" type="submit" value="Search"></td>   </tr></table>
</form>

Posted: Mon Mar 07, 2005 5:53 am
by Jean-Yves
Here's a simple version, that you can customise at will:

Code: Select all

$query = \&quote;SELECT Counties_ID, Counties_Name FROM locations ORDER BY Counties_Name\&quote;;

$result = mysql_query($query) or die(mysql_error());

echo \&quote;<select name='selLocations' id='selLocations'>\n\&quote;;
while($dbRow = mysql_fetch_array($result)){
  echo \&quote;<option value='\&quote; . $dbRow&#1111;\&quote;Counties_ID\&quote;]  . \&quote;'>\&quote; . $dbRow&#1111;\&quote;Counties_Name\&quote;] .\&quote;</option>\n\&quote;;
}
echo \&quote;</select>\n\&quote;;
I have not checked the code in a live environement, so there may be typos/syntax errors. Also, I have not checked for an empty result set, but that is easy enough to add.

Hope that helps.

Posted: Mon Mar 07, 2005 6:52 am
by Addos
Thanks very much Jean, this works a treat once I tweaked the query texts a bit.

The only one thing I notice is that the result leaves out the first row of the table which has an ID of 1 for example I have 1 table with two columns similar to this in my database:

ID Locations

ID_1 Location 1
ID_2 Location 2
ID_3 Location 3
ID_4 Location 4
ID_5 Location 5

But what displays in the browser is only:
Location 2
Location 3
Location 4
Location 5

Any ideas as to why this is?

Thanks a mil
Brian

Posted: Mon Mar 07, 2005 7:15 am
by Jean-Yves
Can you post your revised code?

The only thing that I can think of off the top of my head is that you might have set LIMIT 1,9999 or similar on the SQL - this should be 0,9999 (or whatever top value you want)? Unlikely though.

Posted: Mon Mar 07, 2005 7:35 am
by Addos
Thanks Jean,
Here is the full code that I have set up and as I say it does work but just with the missing row.
Thanks so much
Brian

Code: Select all

<?php require_once('Connections/database.php'); ?>
<?php
mysql_select_db($database_database, $database);
$query_rstSearch = "SELECT * FROM Instruments Order by Instruments_ID ASC";
$rstSearch = mysql_query($query_rstSearch, $imtdatabase) or die(mysql_error());
$row_rstSearch = mysql_fetch_assoc($rstSearch);
$totalRows_rstSearch = mysql_num_rows($rstSearch);

mysql_select_db($database_database, $database);
$query_rstSearchLocations = "SELECT * FROM locations ORDER BY Counties_ID ASC";
$rstSearchLocations = mysql_query($query_rstSearchLocations, $database) or die(mysql_error());
$row_rstSearchLocations = mysql_fetch_assoc($rstSearchLocations);
$totalRows_rstSearchLocations = mysql_num_rows($rstSearchLocations);
?> 

<FORM ACTION="results.php" name="imtSearch" METHOD="POST">
<table width="100%" border="0" cellspacing="0" cellpadding="2">
    <tr>
      <td><input type="radio" name="School" value="Yes">School</td>
    </tr>
	<tr>
      <td><input type="radio" name="School" value="No">Teacher</td>
    </tr>
	<tr>
	  <td><input type="radio" name="School" value="Both" checked>Both</td>
    </tr>
	<tr>
	  <td><?PHP echo "<select name='Counties_ID' id='Counties_ID'>\n"; 
		while($dbRow = mysql_fetch_array($rstSearchLocations))&#123; 
  		echo "<option value='"
		. $dbRow&#1111;"Counties_ID"]  
		. "'>" . $dbRow&#1111;"Counties_Name"]
		."</option>\n"; 
		&#125; echo "</select>\n"; 
		?>
      Choose County</td>
    </tr>
	<tr>
	  <td><?PHP echo "<select name='Instruments_ID' id='Instruments_ID'>\n"; 
		while($dbRow = mysql_fetch_array($rstSearch))&#123; 
  		echo "<option value='"
		. $dbRow&#1111;"Instruments_ID"]  
		. "'>" . $dbRow&#1111;"Instruments_Name"]
		."</option>\n"; 
		&#125; echo "</select>\n"; 
		?>
      Choose Instrument</td>
    </tr>
	<tr>
	  <td></td>
    </tr>
	<tr>
	  <td><input name="submit" type="submit" value="Search"></td>
    </tr>
</table>
</form>

Posted: Mon Mar 07, 2005 8:17 am
by Jean-Yves
You have an additional row fetch just after you get the result set:

Code: Select all

$row_rstSearchLocations = mysql_fetch_assoc($rstSearchLocations);
So the recordset pointer has moved to the second record at this point, hence the missing row in your drop down.

Cheers,
Jean-Yves

Posted: Mon Mar 07, 2005 8:18 am
by Jean-Yves
Forgot to add: that extra line is probably a leftover from earlier code, so you can drop it now.

Posted: Mon Mar 07, 2005 12:53 pm
by Addos
Thanks very much Jean, this is perfect.
Much appreciated
Brian :wink:

Posted: Tue Mar 08, 2005 3:13 am
by Jean-Yves
My pleasure. :)