PHP Code Snippets part 3 - Database information into Option/Dropdown Box

Here is my next PHP code snippet (as promised) where I am going to pull data from a MYSQL database to fill a dropdown box.

I have been asked to take it a step further and display the full information from the database that is chosen from the option box.

So here we go..
For now create a sample database..

I am going to put some names and numbers as an example to help demonstrate the case..

CREATE TABLE `sample` (
`id` int(6) NOT NULL auto_increment,
`firstname` varchar(50) NOT NULL default '',
`color` varchar(50) NOT NULL default '',
`number` varchar(25) NOT NULL default '',
PRIMARY KEY  (`id`),
UNIQUE KEY `id` (`id`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

so thats the table created, now to put some data into it.

INSERT into sample VALUES('1','bob','red','86');
INSERT into sample VALUES('2','ben','blue','6');
INSERT into sample VALUES('3','bill','green','8');
INSERT into sample VALUES('4','jon','pink','44');
INSERT into sample VALUES('5','mike','yellow','58');

So that’s a small database made with a few different values in it.
This demo is not for any set purpose other than to provide you with an example
on how to achieve what is needed.
First of all you are going to need to connect to your database..
You should have your usernames and passwords all setup so replace the stars with your data.

Now providing you have filled out all your information correctly you should be able to connect to the database ready to get a result.
I personally would save this as its on php file called connect.db.php or similar. Everytime you need to you can call upon the file rather than retype the connection lines over and over again.

So thats the basic database and table and connection all setup so now for the main file..

   
   Example Of PHP Snippet 3 - South Coast Web
   

To briefly cover what has happened…
We have connected to the database.
We have done a sql query on that database to get all values from the sample table.
If $num = 0 it means no data was found so it displays a message not an option box.
If we have a result
We have made a form that when submitted reloads itself…
We have included a hidden data of submitted = ‘yes’
(You will see why in next step)
We have created a SELECT / DROPDOWN box that ‘onChange’ will automatically submit the form.
The select box is populated with the firstname field from our sample table.

So what happens next ???

You choose a name from the drop down option box,
this sumits the form and sends 2 variables back to the same page..
The variables are : submitted with a value of yes
and : changedata with a value of the chosen name.

Next step is to display the information :

ID : ".$data['id'];
   echo "
Name : ".$data['firstname'];
   echo "
Favourite Colour : ".$data['color'];
   echo "
Favourite Number : ".$data['number'];
   echo "

Please select a different name to view their details";
} else {
   // if form is not submitted
   echo "Please select a name from the list to view data";
}
?>

So if you have chosen Bob from the list the result should look like :

ID : 1
Name : Bob
Favourite Colour : Red
Favourite Number : 86

Please select a different name to view their details

So there you have it…

I hope you have understood this tutorial and please feel free to leave a comment/improvements
you may have.