Page 1 of 1

Please Help! Submitting MySQL query from dropdown box

Posted: Mon Sep 26, 2011 11:46 am
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

Re: Please Help! Submitting MySQL query from dropdown box

Posted: Mon Sep 26, 2011 10:40 pm
by mayasorceress
what does it say when you run the script?

Re: Please Help! Submitting MySQL query from dropdown box

Posted: Tue Sep 27, 2011 12:17 am
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?

Re: Please Help! Submitting MySQL query from dropdown box

Posted: Tue Sep 27, 2011 1:34 pm
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?

Re: Please Help! Submitting MySQL query from dropdown box

Posted: Tue Sep 27, 2011 5:16 pm
by phpnoob1
yes.. is says:

Column count doesn't match value count at row

Any thoughts?

Re: Please Help! Submitting MySQL query from dropdown box

Posted: Tue Sep 27, 2011 7:23 pm
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?

Re: Please Help! Submitting MySQL query from dropdown box

Posted: Wed Sep 28, 2011 3:11 pm
by phpnoob1
w00h00 it works!! thank you so much!!!

Re: Please Help! Submitting MySQL query from dropdown box

Posted: Thu Sep 29, 2011 4:08 pm
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.