Can't insert data with PDO [Branched from: Insert and Update Server Behaviors in the Same Form]

[ElizabethGailLittle]

I feel as if I'm fighting my way around a paper bag trying to insert a record.  I've recently converted from MySQL to PDO, which may not be relevant.  I'm not trying to write some data update routines, and have started with insert.  I've tried the example in your PHP Solutions Edition Two, pp 361-363 but I can't get a record written.

This is a database I've maintained from the host server using phpMyAdmin.  I'm displaying the data on the site just fine, so I assume my connection script is ok.  However, nothing I've tried has gotten an insert recorded.  I've tried going back to basics, and it still doesn't work.  This is my current code.  Something is wrong with my $sql= statement, and I can't identify the problem.  Please help!

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

  try {

  // create SQL

  $sql = "INSERT INTO Homepage_text (textpage, h_date, h_seq, h_col, p_heading, p_text, h_hide) VALUES ($_POST['textpage'], $_POST['h_date'], $_POST['h_seq'], $_POST['h_col'], $_POST['p_heading'], $_POST['p_text'], $_POST['h_hide'])";

  $sainttim->execute($sql);

  echo 'New record created successfully';

  }

  catch(PDOException ($e)

  {

  echo $sql. "<br />". $e->getMessage();

  }

}

[David_Powers · Accepted solution]

There are several things wrong with your code:

• You are using associative array elements inside a double quoted string. That will cause a parse error.
• The values you are trying to insert into the database are mostly (if not all) text fields. If you're using a literal SQL query, text fields need to be wrapped in quotes.
• You're trying to use the execute() method with a literal SQL query. In PDO, execute() works only with a prepared statement. To execute a literal SQL query, you have to use the exec() method.
• Passing values from the $_POST array directly into the database without any sort of validation and without escaping quotes or other characters is just asking for trouble.

Follow the examples in the book, and use a prepared statement. It will fix all those issues quickly and easily.

[ElizabethGailLittle]

I did go back to your book, and got it working.  I did have trouble with the Header statement and finally used a JavaScript advised in this forum:

<script type="text/javascript">

window.location = "<?=$URL?>";

</script>

The output statement that was objected to was the opening <?php in the include file which connects to the database.  Since nothing can  happen without that, I don't understand how the Header statement ever works!  Connect to the database, display data, input data, write data, and THEN redirect.

All the php code to write the record precedes the <!DOCTYPE, so I don't know what the output is.

Thank you so much for your response!  I'm moving on to a delete routine after filling my test database with test inserts!  I'm having some trouble with that, too, but highlighting your book like crazy...

[David_Powers]

The "headers already sent" error can be caused by a number of things:

• Whitespace before the opening PHP tag of an include file
• Whitespace after the closing PHP tag of an include file
• Using the BOM (byte-order mark) at the beginning of a PHP file
• Outputting any content to the browser before calling the header() or setcookie() function

I suspect you've probably got a closing PHP tag in the database connection file, and that there's a space or some new lines after the closing tag. When an include file contains only PHP code, it's best to omit the closing PHP tag. The closing tag is optional if a file contains only PHP code.

[ElizabethGailLittle]

That did it!  I removed the closing php tag from the connection include file, and the redirect works as expected!

Thanks for your help.  I'm now going through your php Object-Oriented Solutions.  I developed objects/classes many years ago when I was writing application software in Turbo Pascal for DOS!  Then in Borland Delphi for Windows. (And along the way, BASIC, RPG, Lotus Notes.)  I retired in 2001, so the web stuff I'm doing now is my re-entry to coding, and I'm absolutely loving it.  I've just read the first few chapters, and finally understand the  -> syntax I've been using in the PDO operations.

I notice that the Dreamweaver environment is not the best for developing OOP solutions.  I'll use it until I have a problem.  Still have to finish my data maintenance routines, but can certainly see the advantage in writing objects to handle some of those tasks.

Thanks again!