How to insert a record into a MySQL database table?

Report · Oct 02, 2015

I'm writing some input routines for database maintenance. I've started with the insert using PDO. I've tried examples in Dave Powers' PHP Solutions book, and I've tried examples in a response from Rob Hecker2 from several months ago. I just cannot understand the prepare and bindValue or bindParam.

The following code is what I've got right now. home_key is set to "" because it's an auto increment field. The other values come from my input form. What am I doing wrong?

if (isset($_POST['insert']))

{

$insertSQL = $sainttim->prepare('INSERT INTO homepage_text

SET home_key=:home_key, textpage= :textpage, h_date = :h_date, h_seq = :h_seq, h_col = :h_col, p_heading = :p_heading, p_text = :p_text, h_hide = :h_hide');

// bind parameters, execute statement

$insertSQL->bindValue('home_key', $_POST['home_key'];

$insertSQL->bindValue('textpage', $_POST['textpage'];

$insertSQL->bindValue('h_date', $_POST['h_date'];

$insertSQL->bindValue('h_seq', $_POST['h_seq'];

$insertSQL->bindValue('h_col', $_POST['h_col'];

$insertSQL->bindValue('p_heading', $_POST['p_heading'];

$insertSQL->bindValue('p_text', $_POST['p_text'];

$insertSQL->bindValue('h_hide', $_POST['h_hide'];

// execute & get # affected rows

try {

$insertSQL->execute() or $response = "<p style='color:red'>INSERT FAILED!</p>";

} catch(PDOException $e)

{ echo $e; }

$OK = $insertSQL->rowCount();

// redirect if successful or display error

if ($OK)

{

header('Location: ', $insertGoTo);

exit;

}

} /* if isset INSERT */

else {

$query_rstextpage = "SELECT Homepage_text.home_key, Homepage_text.textpage, Homepage_text.h_date, Homepage_text.h_seq, Homepage_text.p_heading, Homepage_text.p_text FROM Homepage_text";

$rstextpage = $sainttim->query($query_rstextpage);

$totalRows_rstextpage = $rstextpage->rowCount();

}

Report · Oct 15, 2015

I see from a later thread that you have solved this problem. For the benefit of anyone else looking at this, the main problem lies in the SQL. The syntax for INSERT is as follows:

INSERT INTO table_name (column_name, column_name) VALUES (value, value)

SET is used for UPDATE.

When a column is set to auto_increment, there is no need to include it in the INSERT statement.

Report · Oct 15, 2015

I am still looking for the problem with my redirect, however. And I'm puzzled by some of the code in your example for the record update. Don't go away, please!