I am a beginner with php. I am using twitter-bootstrap to create a website. I would like to store the data submitted from a website dropdown menu into a MySQL database named datawebcollectiondb . I am able to store information in the db when the user submits input from an open text field, but not from a drop down menu. The database fills with 0s in all fields when the dropdown menu is submitted.
First the open text field that did work:
HTML file:
<html>
<body>
<form action="insert.php" method="post">
Number Please: <input type="text" name="directionp">
Another Number: <input type="text" name="timep">
Third Number: <input type="text" name="locationp">
Fourth Number: <input type="text" name="searchtimep">
<input type="submit">
</form>
</body>
</html>
PHP file that worked:
<?php
$con = mysql_connect("host", "username", "password");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("timeforparkingdb", $con);
$sql="INSERT INTO datawebcollection (directionp, timeofdayp, locationp, searchtimep)
VALUES
('$_POST[directionp]','$_POST[timep]','$_POST[locationp]','$_POST[searchtimep]')";
if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
echo "1 more record added =) ";
mysql_close($con);
?>
HTML file that did NOT work in tandem with above php file, other than populating db with 0s:
<html>
<form action="insert.php" method="post">
<b>DIRECTION:</b>
<select name="directionp">
<option value="1">Coming</option>
<option value="2">Going</option>
</select>
<b>TIME:</b>
<select name="timep">
<option value="0">12:00am - 1:00am</option>
<option value="1">1:00am - 2:00am</option>
<option value="2">2:00am - 3:00am</option>
<option value="3">3:00am - 4:00am</option>
<option value="4">4:00am - 5:00am</option>
<option value="5">5:00am - 6:00am</option>
<option value="6">6:00am - 7:00am</option>
<option value="7">7:00am - 8:00am</option>
<option value="8">8:00am - 9:00am</option>
<option value="9">9:00am - 10:00am</option>
<option value="10">10:00am - 11:00am</option>
<option value="11">11:00am - 12:00pm</option>
<option value="12">12:00pm - 1:00pm</option>
</select>
<b>LOCATION:</b>
<select name="locationp">
<option value="1">Zone One</option>
<option value="2">Zone Two</option>
<option value="3">Zone Three</option>
<option value="4">Zone Four</option>
<option value="5">Zone Five</option>
<option value="6">Zone Six</option>
<option value="7">Zone Seven</option>
<option value="8">Zone Eight</option>
<option value="9">Zone Nine</option>
<option value="10">Zone Ten</option>
<option value="11">Zone Eleven</option>
<option value="12">Zone Twelve</option>
<option value="13">Zone Thirteen</option>
</select>
<b>SEARCH TIME HERE:</b>
<select name="searchtimep">
<option value="0">Under 1 Minute</option>
<option value="1">1 Minute - 3 Minutes</option>
<option value="5">3 Minutes - 5 Minutes</option>
<option value="10">5 Minutes - 10 Minutes</option>
<option value="15">10 Minutes -15 Minutes</option>
<option value="20">15 Minutes - 20 Minutes</option>
<option value="25">20 Minutes - 25 Minutes</option>
<option value="30">25 Minutes - 30 Minutes</option>
<option value="35">30 Minutes - 35 Minutes</option>
<option value="40">35 Minutes - 40 Minutes</option>
<option value="45">40 Minutes - 45 Minutes</option>
<option value="50">45 Minutes - 50 Minutes</option>
<option value="55">50 Minutes - 55 Minutes</option>
<option value="60">55 Minutes - 1 HOUR</option>
<option value="0">Over 1 HOUR</option>
</select>
<button type="submit" class="btn btn-large btn-primary">Submit</button>
</form>
</html>
Original MySQL Database Structure:
Field Type Collation Attributes Null Default Extra Action
unique_entry_id int(11) No
dayp int(2) No
monthp int(2) No
yearp int(4) No
directionp int(1) No
timeofdayp int(2) No
locationp int(2) No
searchtimep int(2) No
What I see inside my database so far:
unique_entry_id dayp monthp yearp directionp timeofdayp locationp searchtimep
0 6 6 6 0 0 0 0
0 7 7 7 0 0 0 0
0 8 24 2345 0 0 0 0
0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0
The rows with values other than 0 are from when I ran the original code, and the rows with only 0s are from the dropdown form. How can I get the correct correlated option value to be the value stored into the database? I would have thought that the value assigned to the option, i.e. <option value="1">Zone One</option> <option value="2">Zone Two</option>, would have been the value submitted to the db.
What I saw on my chrome developer console under form data:
direction=2&time=5&location=5&searchtime=25
and still the database recorded all 0s.
Corrected MySQL Database Structure: Present Corrected Database Structure:
Field Type Collation Attributes Null Default Extra
unique_entry_id int(11) No
city_id varchar(50) utf8_general_ci No
datep date No
directionp int(1) No
timeofdayp time No
locationp int(2) No
searchtimep int(2) No
In order to pass integers and not text to the mySQL db from my php code I've removed the single quotes:
<?php
$con = mysql_connect("", "", "");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("timeforparkingdb", $con);
$sql="INSERT INTO datawebcollection (directionp, timeofdayp, locationp, searchtimep)
VALUES
($_POST[directionp],$_POST[timep],$_POST[locationp],$_POST[searchtimep])";
if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
echo "Thank you for your submission! 1 more record added =) ";
mysql_close($con);
?>
First step in order to avoid SQL injection I have modified my HTML to:
<form action="insert.php" id="timeforparking" method="post">
<input type="submit">
</form>
and:
DIRECTION:
<select form = "timeforparking" id="directionp">
<option value="1">Parking In Manhattan</option>
<option value="2">Leaving Manhattan</option>
</select>
TIME:
<select form = "timeforparking" id="timep">
<option value="0">12:00am - 1:00am</option>
<option value="1">1:00am - 2:00am</option>
<option value="2">2:00am - 3:00am</option>
<option value="3">3:00am - 4:00am</option>
<option value="4">4:00am - 5:00am</option>
<option value="5">5:00am - 6:00am</option>
<option value="6">6:00am - 7:00am</option>
<option value="7">7:00am - 8:00am</option>
<option value="8">8:00am - 9:00am</option>
<option value="9">9:00am - 10:00am</option>
<option value="10">10:00am - 11:00am</option>
<option value="11">11:00am - 12:00pm</option>
<option value="12">12:00pm - 1:00pm</option>
<option value="13">1:00pm - 2:00pm</option>
<option value="14">2:00pm - 3:00pm</option>
<option value="15">3:00pm - 4:00pm</option>
<option value="16">4:00pm - 5:00pm</option>
<option value="17">5:00pm - 6:00pm</option>
<option value="18">6:00pm - 7:00pm</option>
<option value="19">7:00pm - 8:00pm</option>
<option value="20">8:00pm - 9:00pm</option>
<option value="21">9:00pm - 10:00pm</option>
<option value="22">10:00pm - 11:00am</option>
<option value="23">11:00am - 12:00pm</option>
</select>
LOCATION:
<select form = "timeforparking" id="locationp">
<option value="1">Zone One</option>
<option value="2">Zone Two</option>
<option value="3">Zone Three</option>
<option value="4">Zone Four</option>
<option value="5">Zone Five</option>
<option value="6">Zone Six</option>
<option value="7">Zone Seven</option>
<option value="8">Zone Eight</option>
<option value="9">Zone Nine</option>
<option value="10">Zone Ten</option>
<option value="11">Zone Eleven</option>
<option value="12">Zone Twelve</option>
<option value="13">Zone Thirteen</option>
</select>
REPORT YOUR APPROXIMATE SEARCH TIME HERE:
<select form = "timeforparking" id="searchtimep">
<option value="0">Under 1 Minute</option>
<option value="1">1 Minute - 3 Minutes</option>
<option value="5">3 Minutes - 5 Minutes</option>
<option value="10">5 Minutes - 10 Minutes</option>
<option value="15">10 Minutes -15 Minutes</option>
<option value="20">15 Minutes - 20 Minutes</option>
<option value="25">20 Minutes - 25 Minutes</option>
<option value="30">25 Minutes - 30 Minutes</option>
<option value="35">30 Minutes - 35 Minutes</option>
<option value="40">35 Minutes - 40 Minutes</option>
<option value="45">40 Minutes - 45 Minutes</option>
<option value="50">45 Minutes - 50 Minutes</option>
<option value="55">50 Minutes - 55 Minutes</option>
<option value="60">55 Minutes - 1 HOUR</option>
<option value="0">Over 1 HOUR</option>
</select>
And I will have to do something to the php to avoid injection, and I do not undertand what I need to do for this yet, and I am presently researching this at http://php.net/manual/en/pdo.prepared-statements.php, and trying to apply the following to my case:
This example performs an INSERT query by substituting a name and a value for the named placeholders.
<?php
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);
// insert one row
$name = 'one';
$value = 1;
$stmt->execute();
// insert another row with different values
$name = 'two';
$value = 2;
$stmt->execute();
?>
It may be that some of my values are empty. Next I hope to check the POST parameters and print them out in the php code via Print out post values , specifically:
<?php
// loop through every form field
while( list( $field, $value ) = each( $_POST )) {
// display values
if( is_array( $value )) {
// if checkbox (or other multiple value fields)
while( list( $arrayField, $arrayValue ) = each( $value )) {
echo "<p>" . $arrayValue . "</p>\n";
}
} else {
echo "<p>" . $value . "</p>\n";
}
}
?>
output after submit:
blank white screen
Does this mean that I am trying to pass empty values to my db? And I am no longer receiving all 0s in my database as I know have a SQL error (or is that because I am passing blank values ,i.e. $_POST[locationp] and $_POST[searchtimep] are empty? is that the right conclusion? if so, why is it doing that?) with the updated PHP without single quotes; With the latest PHP [as seen above without single quotes, so it doesn't send as text] and the HTML above that now uses an ID tag for the form, I recieve:
"Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ',,)' at line 3"
when I ty to submit from the webpage. And it follows that Chrome is not displaying anything regarding form values, only "Request Headers" and "Response Headers" are listed.
My original question/post is still: storing values in a MySQL db from a drop down menu?
A related question that I was unable to glean a solution from: Inserting Data from dropdown into database with PHP
