SQL and Drop Down

@crazy8Nov 17.2007

Ok so I have a drop down menu in a form…

[code=php]<td class=”required”> <select name=”est”> <option value=”select”>- Select One -</option> <option value=”bars”>Bar</option> <option value=”clubs”>Club</option> <option value=”coffee_shops”>Coffee Shop</option> <option value=”resturants”>Resturant</option> </select> </td>[/code]

…and I have a db set up with tables called “bars”, “clubs”, “coffee_shops”, and “resturants”. How do I set up an sql insert statement to insert the data in the form into the corrisponding table.
For example the form gets filled out and the person selects the “Bar” option from the drop down and all the information in the form will be inserted into the “bars” table of the db.

Any ideas?
Thanks alot for the help

to post a comment

PHP

25 Comments(s) _↴

@crazy8authorNov 18.2007 — #Any idea at ll on the one?

Thanks alot

@jasonahouleNov 18.2007 — #Do you know how to insert the rest of the data? It would be exactly the same way...

[code=php]
 $query = "INSERT INTO ".$_GET['est']." VALUES('".$_GET['field1']."', '".$_GET['field2']."');";
 [/code]

@crazy8authorNov 19.2007 — #Well I did notice a minor typo at the end of your query statement but based off your example does this look correct to you as far as the whole statement goes?

[code=php]$query = "INSERT INTO ".$_GET['est']." (input1, input2, input3, input4, input5, input6)
 VALUES('".$_GET['input1']."', '".$_GET['input2']."', '".$_GET['input3']."', '".$_GET['input4']."',
 '".$_GET['input5']."', '".$_GET['input6']."')";[/code]

Incase it helps here is the whole form as of now...

[code=php]<div class = "nround">
 <div class = "ntop"><h2>Become a hotspot</h2></div>
 <div class = "nmiddle" align="left">
 <form name="step1" method="post" action="basic2.php">
 
 <?php include ('db_connect.php'); 
 
 $query = "INSERT INTO ".$_GET['est']." (comp_name, comp_address, comp_phone, comp_email, comp_web, comp_desc)
 VALUES('".$_GET['comp_name']."', '".$_GET['comp_address']."', '".$_GET['comp_phone']."', '".$_GET['comp_email']."',
 '".$_GET['comp_web']."', '".$_GET['comp_desc']."')";
 
 //if ($_REQUEST['bars'])
 //sql = "INSERT INTO bar (comp_name, comp_address, comp_phone, comp_email, comp_web, comp_desc)
 //VALUES ('$_POST[comp_name]','$_POST[comp_address]''$_POST[comp_phone]','$_POST[comp_email]','$_POST[comp_web]',
 //'$_POST[comp_desc]')";
 //if (!mysql_query($sql,$con))
 //	{ 
 //	die('Error: ' . mysql_error());
 //	}
 //	echo "Thank you for your submission.";
 ?>
 <table border="0" cellpadding="2" cellspacing="0" width="100%">
 <tbody><tr>
 <td colspan="2"><b>Please enter a valid e-mail address. You will need to confirm your e-mail address to activate your account.</b>
 </td>
 </tr>
 <tr>
 <td class="required">Company Name</td>
 <td class="required"><input type="text" name="comp_name" size="20" maxlength="15" 
 value="<?php if (isset($_POST['comp_name'])) echo $_POST['comp_name']; ?>" /></td>
 </tr>
 <tr>
 <td class="required">Establishment Type</td>
 <td class="required">
 <select name="est">
 <option value="select">- Select One -</option>
 <option value="bars">Bar</option>
 <option value="clubs">Club</option>
 <option value="coffee_shops">Coffee Shop</option>
 <option value="resturants">Resturant</option>
 </select>
 </td>
 </tr>
 <tr>
 <td class="required">Address</td>
 <td class="required"><input type="text" name="comp_address" size="30" maxlength="30" 
 value="<?php if (isset($_POST['comp_address'])) echo $_POST['comp_address']; ?>" /></td>
 </tr>
 <tr>
 <td class="required">Phone Number</td>
 <td><input type="text" name="comp_phone" size="20" maxlength="40" 
 value="<?php if (isset($_POST['comp_phone'])) echo $_POST['comp_phone']; ?>" /></td>
 <?php
 if (!preg_match('/^[0-9 ]+$/' , $_POST['comp_phone'])){ 
 $error_msg['comp_phone'] = '<div class=message>Invalid character in phone number.'; 
 }
 ?>
 </tr>
 <tr>
 <td class="required">Email Address</td>
 <td><input type="text" name="comp_email" size="30" maxlength="20" 
 value="<?php if (isset($_POST['comp_email'])) echo $_POST['comp_email']; ?>" /></td>
 </tr>
 <tr>
 <td class="required">Website Address</td>
 <td><input type="text" name="comp_web" size="30" maxlength="20" 
 value="<?php if (isset($_POST['comp_web'])) echo $_POST['comp_web']; ?>" /></td>
 </tr>
 <tr>
 <td class="required">Description</td>
 <td><textarea name="comp_desc" cols="30" rows="12" 
 value="<?php if (isset($_POST['comp_desc'])) echo $_POST['comp_desc']; ?>" ></textarea></td>
 </tr> 
 <tr>
 <td class="verdana11" align="right" width="125"><p></td>
 <td><input onClick="basic2.php" type="submit" name="submit" value="Go to step 2" /></td>
 </tr>
 </tbody>
 </table>
 
 </form>
 </div>
 <div class = "nbottom"></div>
 </div>[/code]

Thanks mucu for your help

@jasonahouleNov 19.2007 — #Looks pretty good, although I don't see the typo that you were talking about. If you mean the semi-colon, then that is not a typo. It signifies the end of a SQL statement just as it does in PHP.

@LuboxNov 19.2007 — #I guess you would need to use $_POST instead of $_GET since your form method is post...

@crazy8authorNov 19.2007 — #Jason - I was talking about this ;";[/QUOTE] I thought it was a typo just because I have only seen "; after sql statements. Ill add it back in since its not a typo, my bad ?

Lubox - So then with all the "$_GET" in the sql statement need to be changed to "$_POST" then?

NOTE: I dont have any POST OR GET in my drop down menu, does there need to be any for it to work the way I want it to?

Thanks again

@jasonahouleNov 19.2007 — #Oops. Good call Lubox.

crazy8 -

The POST is set here

<form name="step1" method="post" action="basic2.php">

You could also use method="get" but when submitting forms like this it should definitely be post. So yes, you will need to change all your $_GET to $_POST. Sorry, that was my bad.

@crazy8authorNov 19.2007 — #well I did try that and still my db is empty. Im not sure if this $query = "INSERT INTO ".$_GET['est']."[/QUOTE] is working correctly or not. When i hit submit the page changes to load the "step2" form but nothing that I entered into the "step1" form gets inserted into the db.

Any ideas?

@knowjNov 19.2007 — #echo your query when you update then you will see exactly whats going in/out

@jasonahouleNov 19.2007 — #Stupid question, are those your table names? bars, coffee_shops, clubs, restaurants? Also, all your tables have the same fields? If so, it may be easier to just use one table and add a "type" column where you define whether it is a bar, club, etc.

@crazy8authorNov 19.2007 — #yes those are my table names and yes they all have the same fileds. Well if you think it would be better to have one table with a "type" coulmn, is there anything special I need to set that column up in the db? how would I write out the sql query and anything else to do it that way?

@jasonahouleNov 19.2007 — #I would just add another varchar() column to the table (<-called comp below) called type (or whatever you want it to be). Modify the insert script to be

<i>
 </i>$query = "INSERT INTO comp (comp_name, comp_address, comp_phone, comp_email, comp_web, comp_desc, comp_type)
 VALUES('".$_POST['comp_name']."', '".$_POST['comp_address']."', '".$_POST['comp_phone']."', '".$_POST['comp_email']."',
 '".$_POST['comp_web']."', '".$_POST['comp_desc']."', '".$_POST['comp_type']."')";

@crazy8authorNov 19.2007 — #This is weird I am clueless as to why this isnt working. i even have my db connection code in the script now and for some reason i can not get this to work.

This is what I got now...

[code=php]<?php
 //This sets the database access information as constants
 define ('DB_USER', 'root');
 define ('DB_PASSWORD', '');
 define ('DB_NAME','hotspot');
 define ('DB_HOST', 'localhost');
 
 //Establish connection to the databse and some error handeling
 $dbc = mysql_connect (DB_HOST, DB_USER, DB_PASSWORD)
 or die ('Could not connect to MySQL: ' .mysql_error());
 mysql_select_db(DB_NAME) OR die('Could not select the database: ' .mysql_error() );
 
 $query = "INSERT INTO establishment (comp_name, comp_address, comp_phone, comp_email, comp_web, comp_desc, comp_type)
 VALUES('".$_POST['comp_name']."', '".$_POST['comp_address']."', '".$_POST['comp_phone']."', '".$_POST['comp_email']."',
 '".$_POST['comp_web']."', '".$_POST['comp_desc']."', '".$_POST['comp_type']."')";
 ?>[/code]

I must be missing something with the db stuff since I still get sent to "step2" but nothing from "step1" is inserted into the db...

@jasonahouleNov 19.2007 — #Where are you executing the query?

@crazy8authorNov 19.2007 — #This is all being done in the step1 form. Here is what it looks like now...

[code=php]<div class = "nround">
 <div class = "ntop"><h2>Become a hotspot</h2></div>
 <div class = "nmiddle" align="left">
 <form name="step1" method="post" action="basic2.php">
 
 <?php
 //This sets the database access information as constants
 define ('DB_USER', 'root');
 define ('DB_PASSWORD', '');
 define ('DB_NAME','hotspot');
 define ('DB_HOST', 'localhost');
 
 //Establish connection to the databse and some error handeling
 $dbc = mysql_connect (DB_HOST, DB_USER, DB_PASSWORD)
 or die ('Could not connect to MySQL: ' .mysql_error());
 mysql_select_db(DB_NAME) OR die('Could not select the database: ' .mysql_error() );
 
 $query = "INSERT INTO establishment (comp_name, comp_address, comp_phone, comp_email, comp_web, comp_desc, comp_type)
 VALUES('".$_POST['comp_name']."', '".$_POST['comp_address']."', '".$_POST['comp_phone']."', '".$_POST['comp_email']."',
 '".$_POST['comp_web']."', '".$_POST['comp_desc']."', '".$_POST['comp_type']."')";
 ?>
 <table border="0" cellpadding="2" cellspacing="0" width="100%">
 <tbody><tr>
 <td colspan="2"><b>Please enter a valid e-mail address. You will need to confirm your e-mail address to activate your account.</b>
 </td>
 </tr>
 <tr>
 <td class="required">Company Name</td>
 <td class="required"><input type="text" name="comp_name" size="20" maxlength="15" 
 value="<?php if (isset($_POST['comp_name'])) echo $_POST['comp_name']; ?>" /></td>
 </tr>
 <tr>
 <td class="required">Establishment Type</td>
 <td class="required">
 <select name="est">
 <option value="select">- Select One -</option>
 <option value="bars">Bar</option>
 <option value="clubs">Club</option>
 <option value="coffee_shops">Coffee Shop</option>
 <option value="resturants">Resturant</option>
 </select>
 </td>
 </tr>
 <tr>
 <td class="required">Address</td>
 <td class="required"><input type="text" name="comp_address" size="30" maxlength="30" 
 value="<?php if (isset($_POST['comp_address'])) echo $_POST['comp_address']; ?>" /></td>
 </tr>
 <tr>
 <td class="required">Phone Number</td>
 <td><input type="text" name="comp_phone" size="20" maxlength="40" 
 value="<?php if (isset($_POST['comp_phone'])) echo $_POST['comp_phone']; ?>" /></td>
 <?php
 if (!preg_match('/^[0-9 ]+$/' , $_POST['comp_phone'])){ 
 $error_msg['comp_phone'] = '<div class=message>Invalid character in phone number.'; 
 }
 ?>
 </tr>
 <tr>
 <td class="required">Email Address</td>
 <td><input type="text" name="comp_email" size="30" maxlength="20" 
 value="<?php if (isset($_POST['comp_email'])) echo $_POST['comp_email']; ?>" /></td>
 </tr>
 <tr>
 <td class="required">Website Address</td>
 <td><input type="text" name="comp_web" size="30" maxlength="20" 
 value="<?php if (isset($_POST['comp_web'])) echo $_POST['comp_web']; ?>" /></td>
 </tr>
 <tr>
 <td class="required">Description</td>
 <td><textarea name="comp_desc" cols="30" rows="12" 
 value="<?php if (isset($_POST['comp_desc'])) echo $_POST['comp_desc']; ?>" ></textarea></td>
 </tr> 
 <tr>
 <td class="verdana11" align="right" width="125"><p></td>
 <td><input onClick="basic2.php" type="submit" name="submit" value="Go to step 2" /></td>
 </tr>
 </tbody>
 </table>
 
 </form>
 </div>
 <div class = "nbottom"></div>
 </div>[/code]

now I dont know if it would cause any issues or not but this form gets inserted to the page. i have another php script that is for the page itself then I just do an

<?php include ('step1.php'); ?>

@jasonahouleNov 19.2007 — #Ok, but you still aren't executing the query. Where is the following line?

[code=php]
 $result = mysql_query($query);
 [/code]

@crazy8authorNov 19.2007 — #Oh for the love of everything holy...I cant beleive I missed that...<takes out his helmit and puts it on>

Thanks ill do that and be sure I dont have any other issues.

UPDATE: well it still isnt inserting. for some reason also with the changes I made to the sql query wouldnt I have to change the name of the form to something different or is that fine the way it is?

@crazy8authorNov 20.2007 — #Well I decided to play with the sql stuff a bit to see what I could do. Well I got this and its obvious that it is connecting...here is the code...

[code=php]<?php
 $con = mysql_connect("localhost","root",""); //Replace with your actual MySQL DB Username and Password
 if (!$con) 
 { 
 die('Could not connect: ' . mysql_error()); 
 } 
 mysql_select_db("hotspot", $con); //Replace with your MySQL DB Name
 $comp_name=mysql_real_escape_string($_POST['comp_name']);
 $comp_address=mysql_real_escape_string($_POST["comp_address"]);
 $comp_phone=mysql_real_escape_string($_POST["comp_phone"]);
 $comp_email=mysql_real_escape_string($_POST['comp_email']);
 $comp_web=mysql_real_escape_string($_POST["comp_web"]);
 $comp_desc=mysql_real_escape_string($_POST["comp_desc"]);
 
 $sql="INSERT INTO establishment (comp_name, comp_address, comp_phone, comp_email, comp_web, comp_desc, comp_type) VALUES ('$comp_name','$comp_address','$comp_phone','$comp_email','$comp_web','$comp_desc')"; 
 
 if (!mysql_query($sql,$con)) {
 die('Error: ' . mysql_error()); 
 } 
 echo "The form data was successfully added to your database."; 
 mysql_close($con);
 ?>[/code]

The issue I am having with this is Im getting this error...
Error: Column count doesn't match value count at row 1[/QUOTE]
i have never seen an error like this one. what is it, what does it mean, and how can i fix it?

@crazy8authorNov 20.2007 — #Ok through some headache and much much much rewriting of the dbconnect script I finally found something that will work to do the insert...this is what I am using.

[code=php]<?php
 $username="root";
 $password="";
 $database="hotspot";
 
 $con = mysql_connect(localhost,$username,$password);
 mysql_select_db($database) or die( "Unable to select database");
 
 $sql="INSERT INTO establishment (type, comp_name, comp_address, comp_phone, comp_desc, comp_email, comp_web)
 VALUES
 ('$_POST[type]','$_POST[comp_name]','$_POST[comp_address]','$_POST[comp_phone]','$_POST[comp_desc]',
 '$_POST[comp_email]','$_POST[comp_web]')";
 
 if (!mysql_query($sql,$con))
 {
 die('Error: ' . mysql_error());
 }
 
 mysql_close();
 ?>[/code]

Now the only issue I can see right now with this is if the page gets refreshed and i think if you hit the back button, I beleive in both cases a "blank" record gets added to the db. How can I keep that from happeneing?

Also I cant seem to get the type thing to work. This is what the drop down looks like now. What do I need to do to get this working (the selected option to be inserted into the db, in the "type" coulumn)?

Thanks again

@jasonahouleNov 20.2007 — #You should do some validation on your data before you attempt to insert it. Check to see if values are null or empty strings.

Also, your code references $_POST['type'] but your markup has a select named 'est'. Does the script you have actually insert data into the database? I have never attempted to do it this way.

'$_POST[type]'

is usually written

'".$_POST['type']."'

@crazy8authorNov 20.2007 — #I agree I do need validation. So how do I do it to make sure I dont have empty input boxes, setup some to accept only letters and some to accept only numbers?

I changed the name of the drop down and did the appropriate edit in the db connection script to comp_type. Here is my drop down now...

[code=php]<select name="comp_type">
 <option selected>- Select One -</option>
 <option value="bars">Bar</option>
 <option value="clubs">Club</option>
 <option value="coffee_shops">Coffee Shop</option>
 <option value="resturants">Resturant</option>
 </select>[/code]

But I still cant get the selected option value to get inserted into the "comp_type" field into the db. Any ideas?

Yes the insert actually does work. Perhaps ill change the POST comments to the format your recomending.

@jasonahouleNov 20.2007 — #First, your script references $_POST['type'] but the name of the element is "comp_type".

Your validation should take place in two places. The first should be written in javascript and should not submit the form unless all fields have data. The second should take place on the server side and should redirect the user back to the form if anything is missing.

[code=php]
 if($_POST['comp_type'] == NULL || $_POST['comp_type'] == "- Select One -" || $_POST['comp_type'] == '') {
 header('location:myform.php');
 }
 [/code]

You would most likely want to put the values submitted back into the form. There are a couple ways to do that. You can store the data in the session, post it back to that page, or pass it in the URL. The session is probably the easiest option.

@crazy8authorNov 20.2007 — #Do I just repeat this for every option in my drop down? for example...

[code=php]if($_POST['comp_type'] == NULL || $_POST['comp_type'] == "bar" || $_POST['comp_type'] == '') { 
 header('location:myform.php');[/code]

[code=php]if($_POST['comp_type'] == NULL || $_POST['comp_type'] == "club" || $_POST['comp_type'] == '') { 
 header('location:myform.php');[/code]

[code=php]if($_POST['comp_type'] == NULL || $_POST['comp_type'] == "resturant" || $_POST['comp_type'] == '') { 
 header('location:myform.php');[/code]

@jasonahouleNov 20.2007 — #No...that is definitely not right. The code you have will send you back to the form no matter what you do.

What are you trying to do? You are trying to make sure the user has submitted valid data, right? Your data is being sent to the page in the $_POST variable. You want to make sure the user changed the select box to a valid value. "- Select One -" is not a valid value. A null value is not valid. An empty string is not valid.

You can also do it the exact opposite way. You can make sure that it IS one of the values you want.

I think you need a better understanding of how PHP works and coding in general. I would look into PHP and Javascript tutorials to get a better understanding of how things work.

@crazy8authorNov 23.2007 — #sorry here is the whole thing I have now...

$username="root"

$password=""

$database="hotspot"

$con = mysql_connect(localhost,$username,$password);

mysql_select_db_$database or die( "Unable to select database");

$query = "INSERT INTO comp (comp_name, comp_address, comp_phone, comp_email, comp_web, comp_desc, comp_type)

VALUES('".$_POST['comp_name']."', '".$_POST['comp_address']."', '".$_POST['comp_phone']."', '".$_POST['comp_email']."',

'".$_POST['comp_web']."', '".$_POST['comp_desc']."', '".$_POST['comp_type']."')";

if (!mysql_query_$sql,$con))

{

die('Error: ' .mysql_error());

}

mysql_close();

Also in #PHP _↴

Email Format imagettftext problem Help upload file from Form.

Success!

Help @crazy8 spread the word by sharing this article on Twitter...

Tweet This

SQL and Drop Down

25 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

SQL and Drop Down

25 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

25 Comments(s) _↴

Also in #PHP _↴