Simple PDO write not working

Question

I'm trying to get a simple PDO insert to work. I have successfully created a tabled named mydb10 using PDO, and the next part I want to do is insert data into that table. Running the script does not return any errors (PDO error mode exception is enabled), but the table still contains null values.

I'm using a local server to run the PHP file, and am connecting to an Amazon RDS database. Currently all inbound traffic through SSH, HTTP, HTTPS, and MYSQL is allowed through the database's security group

$link = new PDO("mysql:host=$dbhost;dbname=$dbname",$username,$password);

$statement = $link->prepare("INSERT INTO mydb10 (selectedMain, selectedSide)
    VALUES(:selectedMain, :selectedSide)");

$statement->execute(array(
    "selectedMain" => "test",
    "selectedSide" => "test2"
));

This might be silly, but I've been stuck for a while now and any help is appreciated. If you'd like any more information, let me know. I'm trying to utilize PHP in my app, but can't even get this simple test to work, so it's a bit discouraging.

EDIT # 1

This snippet is part of a larger file. I am able to successfully connect to the database with my credentials and create new tables on the server. I do have PDO error reporting enabled in exception mode, and it has helped me work past syntax errors, but I am no longer getting any errors when I run the code. There are also no errors on the MYSQL server log.

I can provide any additional information that may be useful in debugging if desired.

Q: Are there any errors in your mySQL log? Or [PDO::errorInfo()](http://php.net/manual/en/pdo.error-handling.php)? If so, please copy/paste them into your post. ALSO: look at this post: [PHP insert statement not working](http://stackoverflow.com/questions/15316672/insert-statement-not-working-using-executearray-of-pdo-extension) — paulsm4, Nov 09 '15 at 21:43
Voting to close until more information on debugging efforts have been provided. Without that it is impossbile to know which of several different problems - bad DB connection setting, bad references to DB schema object, etc. - might be causing the problems. Generally, though you need to NOT assume that your code will always follow a happy path. You need error handling conditions around PDO object instantiation, prepared statement initiation, query execution, etc. with the ability to log errors that may have caused code execution to not proceed as expected. — Mike Brant, Nov 09 '15 at 21:46
Out of curiosity, have you tried `array(":selectedMain" => "test", ":selectedSide" => "test2")` in your `execute()` instead of `array("selectedMain" => "test", "selectedSide" => "test2")`? — Rasclatt, Nov 09 '15 at 22:38
Edited. I do have error enabling, which is why I'm in a pinch; to me, it seems PHP thinks there is no problem. Re: Rasclatt, I just tried it, no luck — Sbfussy, Nov 09 '15 at 23:27
What exactly is the problem? *"the table is null"* is not enough to go on; what does that even mean? — Phil, Nov 09 '15 at 23:31
Would it be worth setting up am [SQLFiddle](http://sqlfiddle) with some suitable test data? And the simplified code that recreates the error. We can test it ourselves then? — Ryan Vincent, Nov 10 '15 at 03:36

score 0 · Answer 1 · answered Nov 09 '15 at 22:19

0

First you need to properly set connection to MySQL database. You can write this code to sql.php:

<?php
  $ServerName = "******";
  $Username = "******";
  $Password = "******";
  $DataBase = "******";

  try {
    $CONN = new PDO("mysql:host=$ServerName; dbname=$DataBase", $Username, $Password);
    $CONN->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    $CONN->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  } catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
  }
?>

Now, when you properly set connection, you need to execute sql, but before this you need to include sql.php:

try {
    $SQL = 'INSERT INTO MyDB10 (SelectedMain, SelectedSide) VALUES(:SelectedMain, :SelectedSide)'; // Write SQL Query to variable
    $SQL = $CONN->prepare($SQL); // Prepare SQL Query
    $SQL->execute(array('SelectedMain' => 'Test', 'SelectedSide' => 'Test2')); // Execute data to Insert in MySQL Databse
  } catch(PDOException $e) {
     echo "Error: " . $e->getMessage();
  }

When you finish all queries you must close connection with:

$CONN = null;

answered Nov 09 '15 at 22:19

Miljan Ilic

235
3
13

Thanks for writing this up, no luck in getting it to work though. I was previously able to connect to the server and create tables, that was never an issue for me, the problem is that I can't seem to write data to those tables – Sbfussy Nov 09 '15 at 23:28
Watch those spaces in the DSN, they'll cause the connection to fail. Also, throwing away valuable information in the exception is no way to find bugs. Avoid catching exceptions only to display a tiny amount of information; just let the exception go uncaught – Phil Nov 09 '15 at 23:31
@Phil I'm new to PHP, what part of my code is only displaying a tiny amount of the error information? I would indeed like to see more info there if it is available. – Sbfussy Nov 09 '15 at 23:36
The part where you only echo out the exception message. Exceptions hold **much more** information than that. Also, catching the exception allows the program to continue when it should probably stop. – Phil Nov 09 '15 at 23:38
If PDO ran into an exception, it would echo out a message though, correct? Because currently, no error messages are being returned back to me. – Sbfussy Nov 09 '15 at 23:47
Code that I wrote works 100%, I tested it. If not there is problem with your server. – Miljan Ilic Nov 10 '15 at 09:00

Simple PDO write not working

1 Answers1