i am very new to php mysql, i am using the following php from to input data. my var_dump shows all the values i have entered in the form correctly. database connection was also successful. but the data are not inserted into the MariaDB table. i have cross-checked that the table structure, values and data types are matching. everything seem to be ok but the database table was never updated with the data . any help would be much appreciated ! thanks in advance.
dropdown-form.php
=================
<?php
include('database.php');
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Chiefdom Zambia</title>
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/water.css@2/out/water.min.css">
<style>
.container
{
border-radius: 5px;
background-color: #f2f2f2;
padding: 20px;
}
.col-25
{
float: left;
width: 25%;
margin-top: 6px;
}
.col-75
{
float: left;
width: 75%;
margin-top: 6px;
}
/* Clear floats after the columns */
.row:after {
content: "";
display: table;
clear: both;
}
/* Responsive layout - when the screen is less than 600px wide, make the two columns stack on top of each other instead of next to each other */
@media screen and (max-width: 600px) {
.col-25,
.col-75,
input[type=submit] {
width: 100%;
margin-top: 0;
}
}
<div >
<div >
<form method="post" action="dropdown-form.php">
<legend style="text-align: center"> Land Info </legend>
<legend style="text-align: center"> Country : Zambia </legend>
<div >
<select name="pro_id" id="country">
<option value="pro_id">Select your Province here</option>
<?php
$countryData = "SELECT id, name from countries";
$result = mysqli_query($conn, $countryData);
if (mysqli_num_rows($result) > 0) {
while ($arr = mysqli_fetch_assoc($result)) {
?>
<option value="<?php echo $arr['id']; ?>">
<?php echo $arr['name']; ?>
</option>
<?php
}
}
?>
</select>
</div>
<div >
<select name="dist_id" id="state">
<option value="dist_id">Select your District here</option>
</select>
</div>
<div >
<select name="chief_id" id="city">
<option value="chief_id">Select your Chiefdom here </option>
</select>
</div>
<input type="text" placeholder="Enter Zone Number " name="zone_no">
<input type="text" placeholder="Enter Zone Name " name="zone_name">
<input type="text" placeholder="Enter Locality" name="locality">
<legend style="text-align: center"> Farmer Info </legend>
<input type="text" placeholder="Enter Name of the Farmer" name="farmer_name"></input>
<input type="text" placeholder="Enter NRC Number of the Farmer " name="nrc_no">
<input type="text" placeholder="Enter Contact Number of the Farmer " name="contact_no">
<input type="text" placeholder="Land Holding in Hectare (Ha)" name="land_holding">
<legend style="text-align: center"> Crop Info </legend>
<select name="crops_no" id="form-selector">
<option value="0">Select Total Number of Crops </option>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
</select>
<br>
<button>submit</button>
</form>
</div>
</div>
</body>
</html>
<?php
$pro_id = filter_input(INPUT_POST, "pro_id", FILTER_VALIDATE_INT);
$dist_id = filter_input(INPUT_POST, "dist_id", FILTER_VALIDATE_INT);
$chief_id = filter_input(INPUT_POST, "chief_id", FILTER_VALIDATE_INT);
$zone_no = $_POST["zone_no"];
$zone_name = $_POST["zone_name"];
$locality = $_POST["locality"];
$farmer_name = $_POST["farmer_name"];
$nrc_no = $_POST["nrc_no"];
$contact_no = $_POST["contact_no"];
$land_holding = $_POST["land_holding"];
$crops_no = filter_input(INPUT_POST, "crops_no", FILTER_VALIDATE_INT);
ini_set('display_errors', 1); ini_set('log_errors',1); error_reporting(E_ALL); mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$sql = "INSERT INTO farmer (' province`, `district`, `chiefdom`, `zone no`, `zone name`,`local`, `farmer name`, `nrc no`, `mobile no`, `land holding`, `no of crops`, ) VALUES ( ?,?,?,?,?,?,?,?,?,?,?)";
$stmt = mysqli_stmt_init($conn);
if (!mysqli_stmt_prepare($stmt, $sql)) {
die(mysqli_error($conn));
}
mysqli_stmt_bind_param(
$stmt,
"iiisssssssi",
$pro_id,
$dist_id,
$chief_id,
$zone_no,
$zone_name,
$locality,
$farmer_name,
$nrc_no,
$contact_no,
$land_holding,
$crops_no
);
if (mysqli_stmt_execute($stmt)) {
echo "Record added successfully";
} else {
echo "Error: " . mysqli_error($conn);
}
mysqli_stmt_close($stmt);
database.php
============
$dbHost = "localhost";
$dbUsername = "root";
$dbPassword = "";
$dbName = "ddl";
$conn = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);
if ($conn->connect_error)
{
die("Connection failed: " . $conn->connect_error);
}
if (mysqli_connect_errno()) {
die("Connection Error: " . mysqli_connect_error());
}
` scenarios were:
i have checked the database connection for errors. data type and values are perfect.table column and sql query are matching. manual entry of sql query accepts the data and updated in the table.
i got the following error:
Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' province, district, chiefdom, zone no, zone name,local, farmer ...' at line 1 in dropdown-form.php:255 Stack trace: #0 dropdown-form.php(255): mysqli_stmt_prepare(Object(mysqli_stmt), 'INSERT INTO far...') #1 {main} thrown in dropdown-form.php on line 255
CodePudding user response：
It looks like you are using a back tick around the field names rather than a straight quote. I would recommend using an editor that does syntax highlighting (there are lots of free ones) so these issues will jump to your eyes. This will bring your focus to the parts that matter more.
CodePudding user response：
The MySQL syntax here is incorrect, which is precisely where the error spotted a problem:
INSERT INTO farmer (`province`, `district`, ...
-- ^ -- Fixed
Do pay extremely close attention to what MySQL says in its error messages. It is usually pinpointing the exact problem, and it did here, to the character.