PDO data not added to database

[sberry]

Hello, In another topic I mentioned problems with UPDATE and received advice about prepared statements so I went online for examples, then I used the information for adding data (insert) to the database. I don’t get error messages, and the page redirects (location:index.php) fine, but the information in not added to the database.

I also followed information on connecting to the database, which does work but in case I’m missing something I will start with the code:

<?php
$DB_Host = 'localhost';
$DB_User = '##########';
$DB_Pass = '';
$DB_Name = '##########';
$DB_Encoding = 'utf8';
$options = [PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION,
			PDO::ATTR_EMULATE_PREPARES => false,
			PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC];

ini_set('display_errors',1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

try {
    $dbh = new PDO("mysql:host=$DB_Host;dbname=$DB_Name;charset=$DB_Encoding",$DB_User,$DB_Pass,$options);

	$dbh -> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	
    echo 'Connected to Database<br/>'; 
   
    }
catch(PDOException $e)
    {
		echo $e->getMessage();
    }
	
?> 

The following is the form - php:

<?php
if(!empty($_POST["add_record"])) {
	require_once("dbcon/dbc.php");
	
	$data = 
	[
	'pagelinks' => $_POST['pagelinks'], 
	'title' => $title, 
	'sideleft' => $sideleft, 
	'body' => $body, 
	'asideright' => $asideright, 
	'sourceref' => $sourceref, 
	'sourceimg' => $sourceimg
	];
	
	$sql = "INSERT INTO pages ( pagelinks, title, asideleft, body, asideright, sourceref, sourceimg) VALUES (?, ?, ?, ?, ?, ?, ?)";
	
	$pdo_statement = $dbh->prepare($sql);
		
	$pdo_statement->execute($data);
	
	if (!empty($result) ){
	  header('location:index.php');
	}
	
	header('location:read.php');
}
?>

Like I said, the database is connected, and the ‘location’ works. The information is the same as found online so I cann;t see what is wrong.

Any help will be appreciated.

[phdr]

The most immediate problem is that you are mixing up the code used for ? (positional) place-holders and named place-holders. That this doesn’t produce an error is a problem with php and/or the database server.

When using ? place-holders, the array you supply to the execute call ONLY contains the values. (I suspect that since php is expecting a zero referenced numerically indexed array, that it is converting the associative indexes to numbers, but then doesn’t figure out that there’s not a unique set of indexes - probably is just blindly building the index/value pair data it sends to the database server.)

The reason I/others specifically suggested using ? place-holders, is that it results in the simplest syntax for the sql query statement and the simplest array of values that you supply to the execute call.

There are also a bunch of other points for the posted code -

1. You altered the $options setting syntax for the ERRMODE from what I posted in your previous thread, breaking it. The only thing that saved this is you added the line that individually sets the ERRMODE attribute. The original syntax I posted is correct (all three of the settings are key/value pairs in an array.) Put back the original setting and remove the duplicate setting line.
2. The php error related settings should be in the php.ini on your system, so that they exist at a single, accessible point, so that you don’t need to go through all your code every time you need to change them (when you move code from development to a live server, you want to log all errors, not display them.)
3. There’s no good reason for your code to have a try/catch block for errors that a visitor to your site cannot do anything about, which is why I stated in your previous thread to just let php catch and handle most database statement exceptions. Most database statement errors are fatal problems and are only of concern to the site programmer/developer, i.e. you. When learning, developing, and debugging code/queries, you should display these errors. When on a live server, you should log these errors. By letting php catch and handle these exceptions, this switch will happen automatically when you change php’s error related settings (see point #2 in this list.) The exception to this rule is when an error occurs due to visitor submitted data, such as duplicate or out of rage values. In this case, your code would catch the exception, detect if the error number is for something your code can handle, then setup an error message for the visitor telling them exactly what was wrong with the data they submitted. This is the only case where you should have a try/catch block for database exceptions. Remove all others. Also, in your current catch code, by only outputting the error message and letting the code continue to run, you will get follow-on errors, having nothing to do with the actual problem and because you have header() redirects (with php’s output_buffing turned on), you won’t see the error messages anyway.
4. Your form processing code should detect that a post method form was submitted (there are cases where the submit button won’t be set), then trim and validate all input data before using it.
5. There are several variables listed that don’t exist. These should be producing php errors. However, since you are redirecting (with php’s output_buffering turned on), you won’t see any of the php errors that would help you. You should set php’s output_buffing to OFF in the php.ini on your system.
6. One of the variables that doesn’t exist is $result, so the header() redirect to index.php should never be executed. You should always be getting the redirect to read.php.
7. The only redirect you should have in your form processing code is a redirect upon successful completion (no errors), to the exact same page where the form processing code is at, to cause a get request for the page. If you want to navigate elsewhere, output navigation link(s) on the page for the visitor to use.
8. Every header() redirect needs an exit/die statement after it to stop program execution.

[sberry]

Thanks for the help and advice. There is a lot for me to do, but will attempt them and see how far I get before (most likely) asking for help.