Please Help! Submitting MySQL query from dropdown box

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
phpnoob1
Forum Newbie
Posts: 4
Joined: Mon Sep 26, 2011 11:10 am

Please Help! Submitting MySQL query from dropdown box

Post by phpnoob1 »

I am new to PHP. I'm sure that this is a very basic problem, but it's been driving me crazy. Please Help!

The form in my html document looks like this:

---------------------------------------------------------------------

Code: Select all

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
<body>
<form action="msql_connect2.php" method="post">
  <p>First Name:
  <input type="text" name="first_name" /><br /><br />
    Last Name: <input type="text" name="last_name" /><br /><br />  
    <label for="list">List:</label>
    <select name="list" id="list">
      <option value="bob">bob</option>
      <option value="jo">jo</option>
      <option value="jane">jane</option>
    </select>
<br /><br />
    <input type="submit" />
<br />
</form>
</body>
</html>
-----------------------------------------------------

My php doc (msql_connect2.php) looks like this:

---------------------------------------------------

Code: Select all

<?php

	$db_host = "localhost";
	$db_username = "user";
	$db_pass = "password";
	$db_name = "database";

$connect = mysql_connect ("$db_host","$db_username","$db_pass") or die("Could not connet to MySQL");
mysql_select_db("$db_name") or die ("No database");

if($connect){
	echo "yes";
}else{
echo "no";
}
	$first_name = $_POST["first_name"];
	$last_name = $_POST["last_name"];
	$list = $_POST["list"];

mysql_select_db("$database", $connect);

if($connect){
	echo "connected to db";
}else{
echo "no not connected to db";
}

mysql_query("INSERT INTO basic (first_name, last_name, list) VALUES ('$first_name', '$last_name', '$list')");

?>
--------------------------------------------

1. I have the database set up in MySQL.
2. If I input: mysql_query("INSERT INTO basic (first_name, last_name) VALUES ('$first_name', '$last_name')"); the database creates a new entry when the form is filled out for "First name and Last Name"
3. The database will not create a new entry if I include a query for list as shown in the php example above.
4. When logged into phpMyAdmin I am able to create a database entry which includes whatever option I choose from "list"
5. How can I make it so that when a user fills out the form and selects an option from the list, their choice is inserted into the form "basic"?

Any help would be very much appreciated! Thank you in advance:o)

Jane
Last edited by Benjamin on Wed Sep 28, 2011 6:07 pm, edited 1 time in total.
Reason: Added [syntax=php|sql|css|javascript] and/or [text] tags.
mayasorceress
Forum Newbie
Posts: 1
Joined: Mon Sep 26, 2011 10:23 pm

Re: Please Help! Submitting MySQL query from dropdown box

Post by mayasorceress »

what does it say when you run the script?
phpnoob1
Forum Newbie
Posts: 4
Joined: Mon Sep 26, 2011 11:10 am

Re: Please Help! Submitting MySQL query from dropdown box

Post by phpnoob1 »

it doesnt give me any kind of error message.. just nothing happens.. thoughts? someone here must make these kinds of forms every day?
mrcoffee
Forum Commoner
Posts: 31
Joined: Tue Nov 10, 2009 3:03 pm
Location: Wyoming, USA

Re: Please Help! Submitting MySQL query from dropdown box

Post by mrcoffee »

Your code looks like it should work, which leads me to believe that the column "list" either does not exists or is of the wrong type.

Try inserting:

Code: Select all

echo mysql_error();
after your mysql_query statement.

Does that reveal anything?
phpnoob1
Forum Newbie
Posts: 4
Joined: Mon Sep 26, 2011 11:10 am

Re: Please Help! Submitting MySQL query from dropdown box

Post by phpnoob1 »

yes.. is says:

Column count doesn't match value count at row

Any thoughts?
mrcoffee
Forum Commoner
Posts: 31
Joined: Tue Nov 10, 2009 3:03 pm
Location: Wyoming, USA

Re: Please Help! Submitting MySQL query from dropdown box

Post by mrcoffee »

That's a bit of a mystery to me, as the code you posted shows three columns and three values. You would see that error for trying something like this: mysql_query("INSERT INTO example (one,two) values (1,2,3)")
Would you please double check your code? Perhaps you have either "...basic (first_name, last_name) VALUES ('$first_name','$last_name','$list')" or "...basic (first_name, last_name, list) VALUES ('$first_name','$last_name')"?

Aside from that I can think of some extreme scenarios where you might get that error, but none very probable.

If there are indeed three columns and three values, would you try replacing your mysql_query statement (the one line) with these 4 lines:

Code: Select all

$first_name = mysql_real_escape_string($_POST['first_name']);
$last_name = mysql_real_escape_string($_POST['last_name']);
$list = mysql_real_escape_string($_POST['list']);
mysql_query("INSERT INTO basic (`first_name`, `last_name`, `list`) VALUES ('{$first_name}', '{$last_name}', '{$list}')");
Do you still get the same error or another error trying this code?
phpnoob1
Forum Newbie
Posts: 4
Joined: Mon Sep 26, 2011 11:10 am

Re: Please Help! Submitting MySQL query from dropdown box

Post by phpnoob1 »

w00h00 it works!! thank you so much!!!
mrcoffee
Forum Commoner
Posts: 31
Joined: Tue Nov 10, 2009 3:03 pm
Location: Wyoming, USA

Re: Please Help! Submitting MySQL query from dropdown box

Post by mrcoffee »

Glad to hear. Let me briefly explain what I was attempting to rule out with the code I provided.

I guessed that one of three things was happening (the last not at all likely, but I thought it was worth mentioning).

First was a simple typo. Those are common and easy to overlook.

Second was to surround the column names with backticks (`). These tell MySQL that you're providing the name of an object (e.g., for a table or column). This is not always necessary, but may be for certain keywords. For example, you would need backticks if you had a table named "select", as "SELECT * FROM select" would cause an error while "SELECT * FROM `select`" is okay. I don't think this was the case with your query, however, as your column names seem okay ("list" is a keyword but shouldn't have to be escaped, unless perhaps you have an older version of MySQL - I'm not sure).

Finally, I ensured each variable was escaped using mysql_real_escape_string(), if by chance you were entering something containing comma surrounded by two single quotes, which would result in your database thinking you were trying to enter an extra column:

Code: Select all

$first_name = $_POST['first_name']; // suppose this is: ',' (literally a single quote, a comma, and a single quote)
mysql_query("INSERT INTO basic (first_name, last_name, list) VALUES ('$first_name', '$last_name', '$list')");
// would evaluate as: INSERT INTO basic (first_name, last_name, list) VALUES ('first name','','','list value');
// worse, someone could instead submit something like: '); DROP TABLE (users... thereby deleting your users table!
// myslql_real_escape_string escapes the quotes:
$first_name = mysql_real_escape_string($_POST['first_name']);
mysql_query("INSERT INTO basic (first_name, last_name, list) VALUES ('$first_name', '$last_name', '$list')");
// would evaluate as: INSERT INTO basic (first_name, last_name, list) VALUES ('first name','\',\'','list value'); so the value is inserted as entered
If you're not familiar with mysql_real_escape_string, I would strongly suggest looking it up in the manual. While some applications might not need it, the information about its use is extremely important.

Cheers.
Post Reply