Genesis5150
asked on
PHP Code not inserting into MySQL
Could someone please help me. I can't figure out why this code is not inserting into the database. All the values are filled in. Another question is how do I convert the dates from 00-00-0000 to 0000-00-00 so it can be inputted into the database correctly.
Thanks
The Form:
Thanks
<?php
error_reporting(E_ALL);
if(isset($_POST['submit']))
{
$fielderror = "";
if( $_POST["percent"] == "" || $_POST["spots"] == "" || $_POST["cancel"] == "" || $_POST["start"] == "" || $_POST["finish"] == ""){
//$errorTrace .= "after <br>";
$fielderror="Please fill the fields annotated with an asterisk<br>";
$doNotSubmit = true;
}
if( !is_numeric($_POST["percent"]) /*!(preg_match('#^\d+(\.(\d{2}))?$#',($_POST["amount"])))*/ ){
$fielderror="Only Numbers In The Deposit Field";
$doNotSubmit = true;
}
if( $_POST['spots'] != "" ){
if (!is_numeric($_POST['spots'])) {
$fielderror .="Only Numbers In The Spots Field";
$doNotSubmit = true;
}
}
if( $_POST['cancel'] != "" ){
if (!is_numeric($_POST['cancel'])) {
$fielderror .="Only Numbers In The Cancelation Field";
$doNotSubmit = true;
}
}
if( $doNotSubmit == false ) {
$bd = mysql_connect("localhost","####_####","####");
if (!$bd){die('Could not connect: ' . mysql_error());}
mysql_select_db("####",$bd);
$username = $_SESSION["jigowatt"]["username"];
$sql = "INSERT INTO bookings (username, title, percent, spots, mini, cancel, start, finish, date_created) VALUES ('".$username."', '".mysql_real_escape_string($_POST['title'])."', '".mysql_real_escape_string($_POST['percent'])."', '".mysql_real_escape_string($_POST['spots'])."', '".mysql_real_escape_string($_POST['mini'])."', '".mysql_real_escape_string($_POST['cancel'])."', '".mysql_real_escape_string($_POST['start'])."', '".mysql_real_escape_string($_POST['finish'])."', NOW(), ')";
if(mysqli_query($bd, $sql)){
echo "Records added successfully.";
} else{
echo "ERROR: Could not able to execute $sql. " . mysqli_error($bd);
}
// Close connection
mysqli_close($link);
}
}
?>The Form:
<form action="index.php" id="dates" method="post" name="dates" class="sky-form" onSubmit="return Blank_TextField_Validator()" enctype='multipart/form-data' />
<div class="row">
<fieldset>
<section class="col col-6">
<span style="background-color:#0000cd; font-size:16px; color:white; padding:5px;">Select Unit</span>
<label class="select">
<?php
$sql = "SELECT * FROM units WHERE `username` = '".$_SESSION['jigowatt']['username']."'ORDER BY date_created DESC";
$result = mysql_query($sql);
echo "<select name='title'>";
while ($row = mysql_fetch_array($result)) {
echo "<option value='" . $row['title'] . "'>" . $row['title'] . "</option>";
}
echo "</select>";
?>
<i></i>
</label>
</section>
</div>
<div class="row">
<section class="col col-3">
<span style="background-color:#0000cd; font-size:16px; color:white; padding:5px;">*Percentage of Deposit</span>
<label class="input">
<i class="icon-prepend fa fa-bar-chart-o"></i>
<input type="text" name="percent" id="percent" placeholder="100%" value="<?php print isset($_POST['percent']) ? strip_tags($_POST['percent']) : ""; ?>">
</label>
</section>
</div>
<div class="row">
<section class="col col-2">
<span style="background-color:#0000cd; font-size:16px; color:white; padding:5px;">*How Many Spots?</span>
<label class="input">
<i class="icon-prepend fa fa-bed"></i>
<input type="text" name="spots" id="spots" placeholder="Available Spots" value="<?php print isset($_POST['spots']) ? strip_tags($_POST['spots']) : ""; ?>">
</label>
</section>
<section class="col col-2">
<span style="background-color:#0000cd; font-size:16px; color:white; padding:5px;">*Minimum Days?</span>
<label class="input">
<i class="icon-prepend fa fa-calendar-o"></i>
<input type="mini" name="mini" id="mini" placeholder="Minimum" value="<?php print isset($_POST['mini']) ? strip_tags($_POST['mini']) : ""; ?>">
</label>
</section>
<section class="col col-4">
<span style="background-color:#0000cd; font-size:16px; color:white; padding:5px;">*How Many Days Before Cancellation?</span>
<label class="input">
<i class="icon-prepend fa fa-times"></i>
<input type="text" name="cancel" id="cancel" placeholder="Days Before Cancellation" value="<?php print isset($_POST['cancel']) ? strip_tags($_POST['cancel']) : ""; ?>">
</label>
</section>
</div>
<div class="row">
<section class="col col-2">
<span style="background-color:#0000cd; font-size:16px; color:white; padding:5px;">*Select Start Date</span>
<label class="input">
<i class="icon-prepend fa fa-calendar"></i>
<input type="text" name="start" id="start" placeholder="Start date" value="<?php print isset($_POST['start']) ? strip_tags($_POST['start']) : ""; ?>">
</label>
</section>
<section class="col col-2">
<span style="background-color:#0000cd; font-size:16px; color:white; padding:5px;">*Select Finish Date</span>
<label class="input">
<i class="icon-prepend fa fa-calendar"></i>
<input type="text" name="finish" id="finish" placeholder="Finish date" value="<?php print isset($_POST['finish']) ? strip_tags($_POST['finish']) : ""; ?>">
</label>
</section>
</div>
</fieldset>
<footer>
<button input name='submit' type='submit' value='submit' class="button">Submit</button>
</footer>
</form> SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
$bd = mysql_connect("localhost","####_####","####");
if (!$bd){die('Could not connect: ' . mysql_error());}
mysql_select_db("####",$bd);
$username = $_SESSION["jigowatt"]["username"];
$sql = "INSERT INTO bookings (username, title, percent, spots, mini, cancel, start, finish, date_created) VALUES ('".$username."', '".mysql_real_escape_string($_POST['title'])."', '".mysql_real_escape_string($_POST['percent'])."', '".mysql_real_escape_string($_POST['spots'])."', '".mysql_real_escape_string($_POST['mini'])."', '".mysql_real_escape_string($_POST['cancel'])."', '".mysql_real_escape_string($_POST['start'])."', '".mysql_real_escape_string($_POST['finish'])."', NOW(), ')";
if(mysqli_query($bd, $sql)){ // <--- riiiight there it is 
Frank is right, you can not mix 'mysql' and 'mysqli' functions. They are from completely different libraries and do not work together.
This article will show you most of the basics of using MySQLi or PDO in a consistent and successful manner. Do not use MySQL functions ever - it's the 1995 way of doing things and PHP is removing those functions from the language, so just don't go there at all.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
Date/time values in are described in this article. Executive summary: You really want to understand and use the ISO-8601 format for all internal representations, all the time.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
Date/time values in are described in this article. Executive summary: You really want to understand and use the ISO-8601 format for all internal representations, all the time.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok so I changed my code to fit MySQLi to this:
But now I'm getting this error:
Notice: Undefined variable: doNotSubmit in C:\xampp\htdocs\user\booki
Error This happen : (1064) 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 1
error_reporting(E_ALL);
if(isset($_POST['submit']))
{
$fielderror = "";
if( $_POST["percent"] == "" || $_POST["spots"] == "" || $_POST["cancel"] == "" || $_POST["mini"] == "" || $_POST["start"] == "" || $_POST["finish"] == ""){
$fielderror="Please fill the fields annotated with an asterisk<br>";
$doNotSubmit = true;
}
if( !is_numeric($_POST["percent"]) /*!(preg_match('#^\d+(\.(\d{2}))?$#',($_POST["amount"])))*/ ){
$fielderror="Only Numbers In The Deposit Field";
$doNotSubmit = true;
}
if( $_POST['spots'] != "" ){
if (!is_numeric($_POST['spots'])) {
$fielderror .="Only Numbers In The Spots Field";
$doNotSubmit = true;
}
}
if( $_POST['cancel'] != "" ){
if (!is_numeric($_POST['cancel'])) {
$fielderror .="Only Numbers In The Cancelation Field";
$doNotSubmit = true;
}
}
if( $_POST['mini'] != "" ){
if (!is_numeric($_POST['mini'])) {
$fielderror .="Only Numbers In The Minimum Stay Field";
$doNotSubmit = true;
}
}
if( $doNotSubmit == false ) {
$dbhost = 'localhost';
$dbuser = '####';
$dbpass = '####';
$dbname = '####'; // Database name
$tbl_name = 'bookings'; // Table name
$conn = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
$username = $_SESSION["class"]["username"];
// check connection
if ($conn->connect_error) {
trigger_error('Database connection failed: ' . $conn->connect_error, E_USER_ERROR);
}
$title = mysqli_real_escape_string($conn, $_POST['title']);
$percent = mysqli_real_escape_string($conn, $_POST['percent']);
$spots = mysqli_real_escape_string($conn, $_POST['spots']);
$mini = mysqli_real_escape_string($conn, $_POST['mini']);
$cancel = mysqli_real_escape_string($conn, $_POST['cancel']);
$start = mysqli_real_escape_string($conn, $_POST['start']);
$finish = mysqli_real_escape_string($conn, $_POST['finish']);
$insert_row = $conn->query("INSERT INTO bookings (username, title, percent, spots, mini, cancel, start, finish, date_created) VALUES ('".$username."','".$title."','".$percent."','".$spots."','".$mini."','".$cancel."','".$start."','".$finish."', NOW(), ')");
if($insert_row){
print 'Success! <br />';
}else{
die('Error This happen : ('. $conn->errno .') '. $conn->error);
}
}
}
?>But now I'm getting this error:
Notice: Undefined variable: doNotSubmit in C:\xampp\htdocs\user\booki
Error This happen : (1064) 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 1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you Ray and yes I will read up on PHP coding. I picked up Murach's PHP and MySQL book and stated reading it 2 weeks ago. Also the syntax error was too many ")"s Changed it to this:
$insert_row = $conn->query("INSERT INTO bookings (username, title, percent, spots, mini, cancel, start, finish, date_created) VALUES ('".$username."','".$title
$insert_row = $conn->query("INSERT INTO bookings (username, title, percent, spots, mini, cancel, start, finish, date_created) VALUES ('".$username."','".$title
What's handy and cleaner is to use double quotes like this:
... and also remember -> when in doubt, echo it out!
$insert_row = $conn->query("INSERT INTO bookings (username, title, percent, spots, mini, cancel, start, finish, date_created) VALUES ('{$username}', '{$title}', '{$percent}' ,'{$spots}', '{$mini}', '{$cancel}', '{$start}', '{$finish}', NOW() )");... and also remember -> when in doubt, echo it out!
$insert_row = $conn->query("INSERT INTO bookings (username, title, percent, spots, mini, cancel, start, finish, date_created) VALUES ('{$username}', '{$title}', '{$percent}' ,'{$spots}', '{$mini}', '{$cancel}', '{$start}', '{$finish}', NOW() )");
echo $insert_row; // take this output and run it on the MYSQL command line or through another query utility.. I like HeidiSQL and SequelPro depending on mac or windows.
exit;
ASKER