Query Failed: Drop down menu in php web page
Below is a form for adding a Book to a table called books.
I currently have a drop down menu that allows a user to pick an existing module however when I submit the form the below error appears...
"Query failed: SQLSTATE[21S01]: Insert value list does not match column list: 1136 Column count doesn't match value count at row 1 "
<form action="<?php echo $_SERVER['PHP_SELF'] ?>" method="post">
Book Code: <input type="text" name="fieldOne" />
Book Title: <input type="text" name="fieldTwo" />
Author: <input type="text" name="fieldThree" />
Second Author: <input type="text" name="fieldFour" />
Publisher: <input type="text" name="fieldFive" />
Publish Year: <input type="year" name="fieldSix" />
<!-- Module Code: <input type="text" name="fieldEight" />-->
<?php
include_once "includes/database.inc.php";
mysql_connect($host, $mysqlusername, $mysqlpassword, $mysqldatabase) or die(mysql_error());
mysql_select_db($mysqldatabase) or die(mysql_error());
$query = "SELECT mod_id FROM modules ORDER BY mod_id DESC";
$result = mysql_query($query) or die(mysql_error()."[".$query."]");
?>
<select name="fieldEight">
<?php
while ($row = mysql_fetch_array($result))
{
echo "<option value=".$row['mod_id'].">".$row['mod_id']."</option>";
}
?>
<input type="submit" name="submit" value="Add/Edit Book" />
Below is the code for querying the database/inserting user inputs into Books table setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch (PDOException $e) { echo "Connection Failed: " . $e->getMessage(); }
try {
//if the fields are set, get data from them
if ((isset($_POST["fieldOne"])) &&
(isset($_POST["fieldTwo"]))&&
(isset($_POST["fieldThree"])) &&
(isset($_POST["fieldFour"])) &&
(isset($_POST["fieldFive"])) &&
(isset($_POST["fieldSix"])) &&
(isset($_POST["fieldEight"]))
)
{
$plainTextOne = strtoupper(trim($_POST["fieldOne"]));
$plainTextTwo = ucfirst(strtolower(trim($_POST["fieldTwo"])));
$plainTextThree = ucfirst(strtolower(trim($_POST["fieldThree"])));
$plainTextFour = ucfirst(strtolower(trim($_POST["fieldFour"])));
$plainTextFive = ucfirst(strtolower(trim($_POST["fieldFive"])));
$plainTextSix = ucfirst(strtolower(trim($_POST["fieldSix"])));
$plainTextEight = ucfirst(strtolower(trim($_POST["fieldEight"])));
$disallow = array('"', '-', "'");
$one = str_replace($disallow, '', $plainTextOne);
$two = str_replace($disallow, '', $plainTextTwo);
$three = str_replace($disallow, '', $plainTextThree);
$four = str_replace($disallow, '', $plainTextFour);
$five = str_replace($disallow, '', $plainTextFive);
$six = str_replace($disallow, '', $plainTextSix);
$eight = str_replace($disallow, '', $plainTextEight);
} else {
$one = "";
$two = "";
$three = "";
$four = "";
$five = "";
$six = "";
$eight = "";
}
//if the fields are not all complete, ask user to complete them
//NOTE that user is not required to enter a second Author (Author2 can be NULL)
if ($one === "" || $two === "" || $three === ""
|| $five === "" || $six === "") {
echo "<br><br>Please complete all the specified fields to log a new Book or to modify an existing Book's details.</br></br>";
} else {
//begin checks on user input
$proceed = true;
$overwrite = false;
//check to see if there already is a book using this book code
$sql = "SELECT * FROM books
WHERE book_id = "$one"";
$check = $conn->query($sql);
//if a book with the associated code already exists, overwite with new data
if ($check->rowCount() > 0) {
$overwrite = true;
}
//check length of module title
if (strlen($two) > 50) {
$proceed = false;
echo "<br><p style = "color:blue;"> Book Title is limited to 50 characters. </p></br>";
}
// if the data is valid and a book is using the given code, update the row in the book table using the user input data.
if ($overwrite == true && $proceed == true) {
$sql =
"UPDATE books
SET book_title = "$two", author1 = "$three", author2 = "$four", publisher = "$five", pub_year = "$six", mod_id = "$eight"
WHERE book_id = "$one" ";
$conn->query($sql);
echo "<br><p style = "color:green;">Book with Book Code $one has been editied. Please refer to the table to confirm the changes.</p> </br></br>";
} else if ($overwrite == false && $proceed == true) {
// if the data is valid, generate the row in the module table using the user input data.
$sql =
"INSERT INTO books (`book_id`, `book_title`,
`author1`, `author2`, `publisher`,
`pub_year`, `keywords`, `mod_id`)
VALUES("$one", "$two", "$three", "$four", "$five", "$six" , "$eight")";
$conn->query($sql);
echo "<br><p style = "color:green;">Book with Book Code $one has been added.</p></br></br>";
//clear the variables
$one = "";
$two = "";
$three = "";
$four = "";
$five = "";
$six = "";
$eight = "";
}
}
//end try
} catch (PDOException $e) {
echo "Query failed: " . $e->getMessage();
}
//close the connection
$conn = null;
?>
Any idea why I am getting the error for query?
You are getting error in the insert query because .. The columns provided is 8 , but you are inserting data only for 7 columns.
See here
$sql = "INSERT INTO books (`book_id`, `book_title`,
`author1`, `author2`, `publisher`,
`pub_year`, `keywords`, `mod_id`)
VALUES("$one", "$two", "$three", "$four", "$five", "$six" , "$eight")";
//.....$seven is missing
上一篇: 当我的查询有错误时无法捕获PDOException，为什么？
下一篇: 查询失败：在php网页下拉菜单