Hi all,
I'm trying to create a form that will insert into a mysql database.
The code for the php file is below:
PHP Code:
<?
//set database connection parameters
$hostname = "127.0.0.1:3307"; // database server name
$db_user = "testuser"; // change to your database password
$db_password = "testpass"; // change to your database password
$database = "testdb"; // provide your database name
$db_table = "form1"; // leave this as is
if(isset($_REQUEST['submit']))
{
//inserting data order
$sql = "INSERT INTO $db_table(EmployeeName,EmployeeRef,WeeksPerYear,MonTownFrom,MonPostcodeFrom,MonTownTo,MonPostcodeTo,MonMiles,TueTownFrom,TuePostcodeFrom,TueTownTo,TuePostcodeTo,TueMiles,WedTownFrom,WedPostcodeFrom,WedTownTo,WedPostcodeTo,WedMiles,ThuTownFrom,ThuPostcodeFrom,ThuTownTo,ThuPostcodeTo,ThuMiles,FriTownFrom,FriPostcodeFrom,FriTownTo,FriPostcodeTo,FriMiles,SatTownFrom,SatPostcodeFrom,SatTownTo,SatPostcodeTo,SatMiles,SunTownFrom,SunPostcodeFrom,SunTownTo,SunPostcodeTo,SunMiles,SumMiles,MonCheck,TueCheck,WedCheck,ThuCheck,FriCheck,SatCheck,SunCheck,MonHours,TueHours,WedHours,ThuHours,FriHours,SatHours,SunHours,SumHours,MonTravel,TueTravel,WedTravel,ThuTravel,FriTravel,SatTravel,SunTravel,MonMealCheck,TueMealCheck,WedMealCheck,ThuMealCheck,FriMealCheck,SatMealCheck,SunMealCheck,MonEveningMealCheck,TueEveningMealCheck,WedEveningMealCheck,ThuEveningMealCheck,FriEveningMealCheck,SatEveningMealCheck,SunEveningMealCheck,MonOvernightCheck,TueOvernightCheck,WedOvernightCheck,ThuOvernightCheck,FriOvernightCheck,SatOvernightCheck,SunOvernightCheck,DeclarationCheck,ip,created)
VALUES ('$_POST['EmployeeName']','$_POST['EmployeeRef']','$_POST['WeeksPerYear']','$_POST['MonTownFrom']','$_POST['MonPostcodeFrom']','$_POST['MonTownTo']','$_POST['MonPostcodeTo']','$_POST['MonMiles']','$_POST['TueTownFrom']','$_POST['TuePostcodeFrom']','$_POST['TueTownTo']','$_POST['TuePostcodeTo']','$_POST['TueMiles']','$_POST['WedTownFrom']','$_POST['WedPostcodeFrom']','$_POST['WedTownTo']','$_POST['WedPostcodeTo']','$_POST['WedMiles']','$_POST['ThuTownFrom']','$_POST['ThuPostcodeFrom']','$_POST['ThuTownTo']','$_POST['ThuPostcodeTo']','$_POST['ThuMiles']','$_POST['FriTownFrom']','$_POST['FriPostcodeFrom']','$_POST['FriTownTo']','$_POST['FriPostcodeTo']','$_POST['FriMiles']','$_POST['SatTownFrom']','$_POST['SatPostcodeFrom']','$_POST['SatTownTo']','$_POST['SatPostcodeTo']','$_POST['SatMiles']','$_POST['SunTownFrom']','$_POST['SunPostcodeFrom']','$_POST['SunTownTo']','$_POST['SunPostcodeTo']','$_POST['SunMiles']','$_POST['SumMiles']','$_POST['MonCheck']','$_POST['TueCheck']','$_POST['WedCheck']','$_POST['ThuCheck']','$_POST['FriCheck']','$_POST['SatCheck']','$_POST['SunCheck']','$_POST['MonHours']','$_POST['TueHours']','$_POST['WedHours']','$_POST['ThuHours']','$_POST['FriHours']','$_POST['SatHours']','$_POST['SunHours']','$_POST['SumHours']','$_POST['MonTravel']','$_POST['TueTravel']','$_POST['WedTravel']','$_POST['ThuTravel']','$_POST['FriTravel']','$_POST['SatTravel']','$_POST['SunTravel']','$_POST['MonMealCheck']','$_POST['TueMealCheck']','$_POST['WedMealCheck']','$_POST['ThuMealCheck']','$_POST['FriMealCheck']','$_POST['SatMealCheck']','$_POST['SunMealCheck']','$_POST['MonEveningMealCheck']','$_POST['TueEveningMealCheck']','$_POST['WedEveningMealCheck']','$_POST['ThuEveningMealCheck']','$_POST['FriEveningMealCheck']','$_POST['SatEveningMealCheck']','$_POST['SunEveningMealCheck']','$_POST['MonOvernightCheck']','$_POST['TueOvernightCheck']','$_POST['WedOvernightCheck']','$_POST['ThuOvernightCheck']','$_POST['FriOvernightCheck']','$_POST['SatOvernightCheck']','$_POST['SunOvernightCheck']','$_POST['DeclarationCheck']','$_POST['ip']','$_POST['created']')";
//declare in the order variable
if($result = mysql_query($sql ,$db))
{
echo = "<br>Your Mileage form has been submitted successfully";
}
else{
echo = "<br>Your Mileage form has not been submitted successfully. Please correct any errors and try again."
"ERROR: ".mysql_error();
}
}
?>
My problem is:
When I click submit on the form, the only thing I get is:
Your Mileage form has been submitted successfully"; } else{ echo = "
Your Mileage form has not been submitted successfully. Please correct any errors and try again." "ERROR: ".mysql_error(); } } ?>
Obviously my syntax is wrong somewhere, but I simply can't find where!
I've tried everything and pulling my hair out is next.
I'd appreciate someone giving me a hand with this!
You're trying to embed variables (namely post) into a string, but when you do this quotes around the array index are NOT correct.
Note you should NEVER place external data directly into a SQL query like that, else you will be open to SQL Injection
Also note, the entire mysql_* library is out of date and to be deprecated. You shjould instead use PDO or mysqli. See API choosing for more information.
For my personal tastes, I'll leave the quotes in there around the array indexes just to be consistent throughout the app. To do that, however, you either have to use "complex" variable notation (wrapping the entire array variable in curly braces) or use concatenation -- or use sprintf() and add the variables as the additional arguments.
PHP Code:
// both (ugh!) complex notation and concatenation:
$sql = "test {$_GET['foo']} test " . $_GET['bar'] . " test";
// or use sprintf with place-holders:
$sql = sprintf(
"test %s test %s test",
mysql_real_escape_string($_GET['foo']),
mysql_real_escape_string($_GET['bar'])
);
"Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
~ Terry Pratchett in Nation
Thanks guys.
I've taken your advice and used mysqli instead of the (previously unbeknownst to me) depreciated mysql_connect() method.
Below is my new code.
Is this any good?
(splitting it into two posts because the forum doesn't like the length)
PHP Code:
<?
//set database connection parameters $hostname = "127.0.0.1:3307"; // usually is localhost, but if not sure, check with your hosting company, if you are with webune leave as localhost $db_user = "testuser"; // change to your database username $db_password = "testpw"; // change to your database password $database = "testdb"; // provide your database name $db_table = "form1"; // leave this as is
//database connection $db = new mysqli($hostname, $db_user, $db_password, $database); if($db->connect_errno > 0) { die('Unable to connect to database [' . $db->connect_error . ']'); } mysqli_report(MYSQLI_REPORT_ERROR);
Try using <?php (instead of <?). (Looks like it's not being parsed as PHP, just outputting (malformed) HTML.)
"Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
~ Terry Pratchett in Nation
If you're using the MySQLi extension, then the mysql_*() function won't know anything about that DB connection, and so won't work. In any case, if you are using bound parameters for all the inputs, then you do not want to escape them separately anyway, since the bind_param() method automatically takes care of that, as doing so would result in double escaping. (However, it looks like you'll need a lot more place-holders in its first parameter: one for each place-holder to be bound).
"Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
~ Terry Pratchett in Nation
Thanks very much NogDog, I think i'm starting to get somewhere now!
I've commented out the mysql_real_escape_string piece and now it's progressing past that error.
The only thing that I don't seem to be handling correctly now are the checkboxes in my form.
I've got all the inputs looking for whether they are set or not by the master:
PHP Code:
if(isset($_POST['submit']))
However, this isn't handling the checkboxes.
I get:
Notice: Undefined index: MonCheck
and so on, for all checkbox values.
by looking online, I believe i'd like to handle the checkbox insertion by either using a 1 or a 0 if they are checked or unchecked respectively.
I can see that this can be typically done by:
PHP Code:
$MonCheck = (isset($_POST['MonCheck'])) ? 1 : 0;
If this is the way to do it, I can't see how to handle that within my existing code due to the fact that i'm running an isset for all inputs as opposed to doing it for each input individually :
Checkboxes are only POST when they are checked. So even though you check if the form was submitted, which you should do differently btw (see: here), the $_POST['CheckBox'] will not be set unless it was checked, that's why the suggested way to check for it is with isset on the field itself.
If you have 85 checkboxes, in a way yes. You could however name those checkboxes something like name="Options[]" Value="unique values" and then just check the $_POST['Options'] is set and loop through looking for values, you could then have a predefined array of possible values, and then loop through them to determine which ones were checked and which ones weren't. I'm not going to do an 85 point but here's an example of what I mean:
I've refined (or is it bloated?) the code now to the below and fixed other errors that I spotted along the way.
The only issue i'm facing at the moment is that the insert doesn't seem to insert the date/time that I have set by a variable.
The error is:
Warning: mysqli_stmt::execute() [<a href='mysqli-stmt.execute'>mysqli-stmt.execute</a>]: (23000/1048): Column 'created' cannot be null
Obviously this is the target mysql server notifying me that the destination column is not nullable (which is fine) and the underlying issue is that it's not being given the date/time correctly.
My code is now as below:
PHP Code:
<?php
//set database connection parameters
$hostname = 'localhost:3307'; // usually is localhost, but if not sure, check with your hosting company, if you are with webune leave as localhost
$db_user = 'testuser'; // change to your database username
$db_password = 'testpass'; // change to your database password
$database = 'test'; // provide your database name
//$db_table = 'testform'; // leave this as is
//database connection
$db = new mysqli($hostname, $db_user, $db_password, $database);
if($db->connect_errno > 0)
{
die('Unable to connect to database [' . $db->connect_error . ']');
}
mysqli_report(MYSQLI_REPORT_ERROR);
Reply With Quote
Bookmarks