Populate form from another mysql table?

[twoclones]

I know how to do this in Access but the PHP method is escaping me.  Maybe I don't know the proper term to search for?

 

I have a Contacts table and other tables like Events.  When a user enters a new event, I'd like them to select the event contact from a drop down list containing the names from the Contacts table. How?

 

A tutorial link would be great..

 

 

Butch

[roopurt18]

You need to pull the data from the contacts table using a SELECT query.

 

You then loop over the results and use each result to create an < option> item in a < select> input.

 

This is the bare minimum you'd need to do and it makes some assumptions, such that DB_HOST, DB_USER, and DB_PASS are all defined as well as assumptions about your database table.

<?php
mysql_connect(DB_HOST, DB_USER, DB_PASS);
$sql = "SELECT * FROM contacts WHERE 1";
$q = mysql_query($sql);
if($q){
  echo "<select>";
  while($row = mysql_fetch_assoc($q)){
    echo "<option value=\"{$row['fld1']}\">{$row['fld2']}</option>";
  }
  echo "</select>";
}
?>

[twoclones]

Thanks for the code!  I fought a battle or two with it before realizing I was taking it too literal.  One thing I still don't understand is the WHERE statement.  Is the Where 1 just an example or will it cause problems if I remove it?  Below is what I am using at the moment.

 

#get configuration info and open database
include 'library/config.php'; 
include 'library/opendb.php'; 

#create the query
$sql = "SELECT * FROM contacts WHERE 1";

#execute query
$q = mysql_query($sql);
if($q){
  echo "<select>";
  while($row = mysql_fetch_assoc($q)){
    echo "<option value=\"{$row['ID']}\">{$row['fname']} {$row['lname']} </option>";
  }
  echo "</select>";
}
#and close the database connection 
include 'library/closedb.php'; 

 

Thanks again,

Butch

[jcbarr]

That is just an example. If youw ant to select everything then remove it and it will return every row in the table. If you have a specific thing you are looking for then you can set the condition with the WHERE statement.

 

SELECT * FROM contacts WHERE column='value'

[twoclones]

Cool.  I've got that all working as I'd hoped. I've tried several things but can't get the data to display the way I need to.

 

I've got an Events table with a contact_ID column containing the ID number from the Contacts table. 

I have a script to display the contact fname, lname when I give it an ID number. 

I have a script to display the event information including the contact_ID.

 

How do I combine these so that my Event search displays the fname, lname from the Contacts table where I currently show the contact_ID?

 

Here is the code for displaying my Events

include 'library/config.php'; 
include 'library/opendb.php'; 

$query  = "SELECT * FROM events WHERE $field LIKE '%$name%' ORDER BY $order"; 

$result = mysql_query($query) or die('Error, query failed'); 

echo "<ol>";
// print the event data 
while($row = mysql_fetch_array($result)) 
{ 
    echo "<li><br>{$row['event_name']} <br>" .
	"Contact ID: {$row['contact_ID']} <br>" . 
	 "{$row['start_date']} <br>" .
	 "{$row['end_date']} <br>" .
	 "{$row['location']} <br>" .
	 "{$row['venue']} <br>" .
	 "{$row['sponsor']} <br>" .
	 "{$row['promotion']} <br>" .
	 "{$row['remarks']} ";
} 
echo "</ol>";

 

This code displays the contact info:

include 'library/config.php'; 
include 'library/opendb.php'; 

$query = "SELECT fname, lname FROM contacts WHERE ID = '16'"; 

$result = mysql_query($query) or die('Error, query failed'); 

echo "<ol>";

while($row = mysql_fetch_array($result)) 
{ 
    echo "<li>{$row['fname']} {$row['lname']}";
} 
echo "</ol>";

 

I'm over my head just far enough to need  a snorkle!

 

Butch