[SOLVED] Putting results from Mysql query into a pulldown box.

[BobcatM]

Hello all,

 

I am trying to figure out a way to run a Mysql query and based on that put the results into a drop down box so the user can click one.

 

Where I have dat1-dat4 - There will be the first three letters of the location.

The place I am trying to do this is: location

 

The reason for this I am going to make another form to update the list as new clients are added.

 

Thanks as always for the help.

 

 

 

form name ="insertjob" action="insertjob.php" method="post" onSubmit="return checkform(this);">
  <p><strong>Please Fill out the form.<br>
    Your Job# will be generated and displayed on the next page. </strong></p>
  <table width="446" border="1">
  <tr>
    <td width="138" align="left" nowrap><strong>Date:</strong></td>
    <td width="292"> 
<SCRIPT LANGUAGE="JavaScript" SRC="/warlord/desktop/CalendarPopup.js"></SCRIPT>
<SCRIPT LANGUAGE="JavaScript">document.write(getCalendarStyles());</SCRIPT>
<SCRIPT LANGUAGE="JavaScript">
var cal5 = new CalendarPopup();
cal5.showNavigationDropdowns();
</SCRIPT> 
   <INPUT TYPE="text" NAME="date" SIZE=25>
<A HREF="#" onClick="cal5.select(document.forms['insertjob'].date,'anchor','MM/dd/yyyy'); return false;" NAME="anchor" ID="anchor">Select Date</A>   </td>
  </tr>
  <tr>
    <td align="left" nowrap><strong>Location:</strong></td>
    <td><select name="location">
    <option value=""></option>  
    <option value="dat1">First row from DB</option>
    <option value="dat2">Second row From DB</option>    
    <option value="dat3">Third row from DB.</option>
    <option value="dat4">Fourth row from DB.</option>
    </select></td>  </tr>
  <tr>
    <td align="left" nowrap><strong>Division:</strong></td>
    <td><select name="division">
        <option value=""></option>
        <option value="Welding">Welding</option>
    </select></td>
  </tr>
  <tr>
    <td align="left" nowrap><strong>Your Name: </strong></td>
    <td>	
<input type="text" name="name">	</td>
  </tr>
  <tr>
    <td align="left" nowrap><strong>PO Number:</strong></td>
    <td><input type="text" name="ponum"></td>
  </tr>
  <tr>
    <td align="left" nowrap><strong>Equipment #:</strong></td>
    <td><input type="text" name="equipnum"></td>
  </tr>
  <tr>
    <td align="left" nowrap><strong>Approx. PO Amount: </strong></td>
    <td><input type="text" name="poamount"></td>
  </tr>
  <tr>
    <td align="left" nowrap><strong>Description:</strong></td>
    <td><TEXTAREA NAME="description" ROWS=3 COLS=40></TEXTAREA></td>
  </tr>
</table>
<input type="Submit"><input type="reset">
</form>	

 

-Bob

[craygo]

<select name="location">
<option value=""></option>
<?php
$sql = "SELECT SUBSTRING(`datfield`,1,3) AS `dat`, `name` FROM `tablename`";
$res = mysql_query($sql) or die(mysql_error());
while($r = mysql_fetch_assoc($res)){
echo "<option value=\"".$r['dat']."\">".$r['name']."</option>\n";
}
?>
</select>

 

I grabbed another field and just called it name which would be a friendly name to the user. Replace all table and field names with your own

Ray

[BobcatM]

craygo - Thanks for the reply!

 

You answered excatly what I asked, but me reading it again I messed up what I was asking.

I was refering to the first letter of each word, but I was wrong. The user who adds a new job will actually define what the letters are and what the option will be.

 

 

I am going to have a form, where the user will say enter:

 

Job Location: Equistar LaPorte

3 Letter Identifier: ELP

 

The Italics will be what the user enters & this will be stored in the DB. I need a query that will somehow pull the data out of the database to display in the drop down box.

 

So where dat1-dat4 is - that will be the location of ELP, and the option the user will see will be Equistar LaPorte.

 

Thanks so much and sorry I messed up.

[craygo]

well if the 3 letter code is going into it's own field then just change the query to grab the 2 fields

 

$sql = "SELECT `identifier`, `joblocation` FROM `tablename`";

 

Not sure if you want to return all the row or just 4 but if you want just 4

 

$sql = "SELECT `identifier`, `joblocation` FROM `tablename` LIMIT 4";

 

Ray

[BobcatM]

I have got the form completed to insert the job into the DB.

 

But I still can't get it to pull the info out of the DB.

 

This is the one section I am working on.

 

    <td><select name="location">
           <option value=""></option>  
<?php
$sql = "SELECT 'locpref', 'loc' FROM  `location`";
$res = mysql_query($sql) or die(mysql_error());
while($r = mysql_fetch_assoc($res)){
echo "<option value=\"".$r['locpref']."\">".$r['location']."</option>\n";
}
?>
           </select>

 

When I load the page, it does not display anything but a ton of white space. Like it knows the length it needs to be, but will not show anything.

 

Thanks.

[BobcatM]

I figured it out.

 

<select name="location">
    <option value=""></option>  
<?php
$sql = "SELECT locpref, loc FROM  location";
$res = mysql_query($sql) or die(mysql_error());
while($r = mysql_fetch_assoc($res)){
echo "<option value=\"".$r['locpref']."\">".$r['loc']."</option>\n";
}
?>
</select>

 

Just for those that stumble accross this.

 

Thanks for the help craygo .

[craygo]

table names and field names use back ticks `

 

values use single quotes '

 

Just for future reference

 

Ray