Problem using a database class

[kenoli]

I got some help from this forum previously and am having some more issues.  I created a database class that works and returns a PDO object.  I am having trouble figuring out how to use the object in another class I want to use to access some CRUD functions.  I get to the point where I  start using the pdo object I create in the database class and my code fails.  I am obviously calling the pdo object property incorrectly.  I guess I don't understand the proper syntax.  I've included the code for the database class and the CRUD class.  The problem starts at the point where I try to run a prepared statement.  I have include a comment "Problem stars here" to indicate that point.  There is a lot of debug stuff still in the code.

Thanks,

--Kenoli

<?php
	
	class Db {
	
	public $pdo = '';
	public $message = 'A message from db!<br><br>';

function __construct() {
	
	$servername = "localhost";
	$username = "root";
	$password = "";
	$dbname = "tio-local";
	
	$db_options = array(
	PDO::ATTR_EMULATE_PREPARES => false, 
	PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, 
	PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
	);
	
	try {
		
	  $this->pdo = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password, $db_options);
	  // set the PDO error mode to exception
	  $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

	}  // End Try
	
	catch(PDOException $e) {
		  echo "Error: " . $e->getMessage();
		}
		
	} // End __construct
	
} // End class definition DB.php

$db = new Db;

$pdo = $db->pdo;

print_r ($pdo);

?>

<?php // Db_functions.php

include_once ('Db.php'); // $db instantiated in class file

$pdo = $db->pdo;

class Db_functions {
	
	public $pdo = '';
	
	function __construct($pdo) { 
		
		$this->pdo = $pdo;
		
	} // Close __construct
  
  public function insert($table_name, $white_list, $insert_array) {
		
			if ($white_list == '' && $table_name == 'Sites') {
		
			$white_list = array('gone'=>'','site_name' =>'', 'site_address' =>'', 'grommets' =>'', 'tape' =>'', 'site_image' =>'',  'description' =>'', 'surface' =>'', 'tio_contact' =>'', 'site_contact' =>'','owner' =>'', 'lessee' =>'',  'contact_phone' =>'', 'contact_email' =>'', 'contact_date' =>'', 'comments' =>'');
			} elseif  ($white_list == '' && $table_name == 'Persons') {
				
				$white_list = array('gone'=>'', 'fname'=>'', 'lname'=>'', 'tio'=>'', 'volunteer'=>'', 'general'=>'', 'artist_pic'=>'', 'email'=>'', 'website'=>'', 'telephone'=>'', 'address'=>'', 'city'=>'', 'state'=>'', 'zip'=>'', 'statement'=>'');
			}
			
			echo '<strong>***The following is the PDO object: </strong>';
		print_r ($this->pdo);
			
			echo '<p>The following is the $white_list:<br>';
			
			echo '<pre>';
			print_r ($white_list);
			echo '</pre>';
			
			echo '<strong>***The following is the PDO object: </strong>';
		print_r ($this->pdo);
			
			/** Test and remove any unpermitted columns **/
			
			$insert_array = array_intersect_key($insert_array, $white_list);
			
			echo '<strong>***The following is the PDO object: </strong>';
		print_r ($this->pdo);
		
		/** Generate variables to create prepared statements **/
		
			foreach($insert_array as $key => $value) {  
				$col .= $key . ', ';  
				$val .= ':' .$key . ', ';
			}
			
			echo '$col = ' . $col . '<p>';
			echo '$val = ' . $val . '<p>';
			
			echo '<strong>***The following is the PDO object: </strong>';
		print_r ($this->pdo);
		
		/** Remove ', ' at end of arrays and create prepared statement */
		
			$col = substr_replace($col ,"",-2);
			$val = substr_replace($val ,"",-2);
			$sql = "INSERT INTO Sites ($col) VALUES ($val)";
			
			echo "<p>SQL = $sql<br><br>"; /** Debug **/
		
		echo '<h3>More</h3<br>';	
			
		/** Register prepared statement */
		
		/****** PROBLEM STARTS HERE *****/
		
			$stmt = $this->pdo->prepare($sql);
			
		echo '<h3>More2</h3>';	
		
		/** Create [:field, $value] pairs. */
		
			foreach($insert_array as $key => $value) {
				$param = ':' . $key;
				$stmt->bindParam($param, $$value);
				
			//}
		
		/** Create [field => value] array */
		
			foreach($insert_array as $key => $value) {
				$insert[$key] =  $value;
			}
			
		
		/** Execute statement using $insert array. **/
		
			$stmt->execute($insert);
	
	} // End insert function
	
	
} // Close class definition

$db_functions = new Db_functions($pdo);

$insert_array = array('fname' => 'John', 'lname' => 'Hancock');

$db_functions->insert('Persons', '', $insert_array);

echo '<pre>';
print_r ($db_functions);
echo '</pre>';

?>

 

Edited February 6, 2021 by kenoli

[Barand]

25 minutes ago, kenoli said:

$stmt->bindParam($param, $$value);

Why "$$value" ?

[Strider64]

I have been updating my website to include the Active Response Design pattern using PHP pdo. I have most to the pdo done with a few minor refinements and improvements. If you want to get some ideas or inspiration check out my GitHub repository : https://github.com/Strider64/Miniature01282021

 

I know there's better out there, but it' not to bad for a person who is 56 that has only been programming in PHP the last 6-7 years (though I have previous coding experience in other languages).

[kenoli]

1 hour ago, Barand said:

Why "$$value" ?

That is a typo from when I prepared it to post.  The issue starts in the previous statement:  $stmt = $this->pdo->prepare($sql);.

--Kenoli

 

[kenoli]

16 minutes ago, Strider64 said:

I have been updating my website to include the Active Response Design pattern using PHP pdo. I have most to the pdo done with a few minor refinements and improvements. If you want to get some ideas or inspiration check out my GitHub repository : https://github.com/Strider64/Miniature01282021

 

I know there's better out there, but it' not to bad for a person who is 56 that has only been programming in PHP the last 6-7 years (though I have previous coding experience in other languages).

Thanks.  I'll take a look at it.  I'm 76 and learned php in the early part of the millennium to build a database driven web site back then using mysql_query with no protections.  I think every high school hacker broke into the site at one time or another.  I've used it a bit for various things since like creating a page and gallery for my artist partner.  I'm relearning it now and having the worst time with PDO and prepared statements for some reason.  I'm also getting a bit stumped I think by variable/class scope.

If I create a static method for accessing a pdo connection inside a database class, how do I get access to that connection?  Do I have to access it outside of the class and send a variable containing the connection into a class constructor to get the connection inside of the class, or can I just access it as a static member from within the class.  I tried out some scripts to answer my own question abut that and couldn't get anything to work.

You can see what I did to get the object based connection into the second class by sending it to a constructor and assigning it to a property in that class.  It's not working.

I'd sure like some help with the scripts I posted.  They are driving me crazy.

--Kenoli

[kenoli]

Strider -- You helped me one time before and sent me some of your code.  I recognized it when I went to your github link.  I may be able to solve my problem looking at it, but if you have any pointers, its would be hugely appreciated.  I can see that you were calling a static method  from the class in your database.php class from your DatabaseObject.php file.  When I've tried to do that, it hasn't worked.  I don't know if it is a namespace issue or scope issue or what.  I'm not using any namespaces and am not sure what the use PDO; call in the DatabaseObject file is.  I'm feeling lost in a black hole.--Kenoli

[mac_gyver]

3 hours ago, kenoli said:

The problem starts at the point where I try to run a prepared statement.

what symptom or error did you see that leads you to believe that? 

[kenoli]

1 hour ago, mac_gyver said:

what symptom or error did you see that leads you to believe that? 

The rest of the script doesn't run but when I comment out that line it does run.  The next place where I use $stmt->bindParam($param, $value) the script there and following doesn't run (even with $value rather than $$value).  The whole function with $stmt in if fails to run.

--Kenoli

[mac_gyver]

you are most likely getting an error from the ->prepare() call. do you have php's error_reporting set to E_ALL and display_errors set to ON, preferably in the php.ini on your system, so that php will help you by reporting and displaying all the errors it detects?

[kenoli]

The same script does run when I run it in a separate file in which the pdo connection is made in the script itself

23 minutes ago, mac_gyver said:

you are most likely getting an error from the ->prepare() call. do you have php's error_reporting set to E_ALL and display_errors set to ON, preferably in the php.ini on your system, so that php will help you by reporting and displaying all the errors it detects?

I do have it set to report errors as you can see in the database class, but another problem I am having with that script is that when I put it in a try format, nothing will run.  I had to remove the try brackets to get any of it to run and then it stumbles where I bring in the pdo object.  I have checked to see that it is making a database connection which it is.

Incidentally, the script will run when I run it as a procedural script file and make the database connection in the file.  Since the problem arose when I tried to make the connection in a separate object, I assumed it had to do with the way the connection was getting from the database class to this class.  I spent 4 hours trying to figure out what is going on, checking every step multiple times and retrieve lots of debug info on exactly where it is stumbling.  Nothing.

It doesn't even make it to the error report

I appreciate everyone's efforts.

--Kenoli

Edited February 6, 2021 by kenoli

[mac_gyver]

29 minutes ago, kenoli said:

I do have it set to report errors as you can see in the database class

that's not what I stated/asked about php's error related settings.

you are setting the pdo error mode to exceptions (twice.) what this does is cause an error with a prepare(), query(), exec(), execute(), ... statement to throw an exception. if you don't catch the exception in your code, php will catch it. if php's error related settings are not set up to report and display (or log) all php errors, nothing will happen with this information. your script will just halt at the point of the error.

as to the try/catch block you do have for the connection. a connection error is a fatal problem for a database dependent web page. your code should stop upon such an error so that you don't get follow-on errors by trying to use a connection that doesn't exist. if you just remove that try/catch logic and let php catch the exception, any connection error will get displayed or logged the same as php errors.

the only time you should have a try/catch block for database statement errors is if the visitor to the site can correct an error that he/she caused, such as inserting/updating duplicate or out of range values. in all other cases, you might as well save the typing and just let php catch and handle the database exception.

[kenoli]

8 minutes ago, mac_gyver said:

that's not what I stated/asked about php's error related settings.

you are setting the pdo error mode to exceptions (twice.) what this does is cause an error with a prepare(), query(), exec(), execute(), ... statement to throw an exception. if you don't catch the exception in your code, php will catch it. if php's error related settings are not set up to report and display (or log) all php errors, nothing will happen with this information. your script will just halt at the point of the error.

as to the try/catch block you do have for the connection. a connection error is a fatal problem for a database dependent web page. your code should stop upon such an error so that you don't get follow-on errors by trying to use a connection that doesn't exist. if you just remove that try/catch logic and let php catch the exception, any connection error will get displayed or logged the same as php errors.

the only time you should have a try/catch block for database statement errors is if the visitor to the site can correct an error that he/she caused, such as inserting/updating duplicate or out of range values. in all other cases, you might as well save the typing and just let php catch and handle the database exception.

Thank you so much and apologies for not reading your question about errors more carefully.  I have not fully understood the error process and done some cutting and pasting with error code, thus some of what you describe, particularly the double declaration.  You've given me something to work with.  I am going to take some time to fully understand what you have described and see what I can do.  Good advice and insights that I haven't  heard before.  I'm using Xampp for my php/mysql server and have never checked to see how the errors are set.  There are clearly some things I am going to have to look into.  I'll see what I can do and get back with the results.

I guess there is still an issue as to why the prepare() statement is failing but if I can get the error stuff straightened out maybe I can find out.

--Kenoli

[kenoli]

mac_gyver and others -- I never really figured out what was going here, but I did a work around.  Per your comment, mac_gyver, I looked into the error issue.  I removed the double declaration of the pdo error and the try blocks and checked to make sure php was set for errors, which it was.  More to figure out there as am getting reports on some errors, but less than I am used to.  Not sure what that is all about.  I think the pdo errors are coming through.

One person asked about the $$value in one of my functions.  It was not a mistake.  It is a variable variable where the array is actually naming a variable for me.  That expression does work.

Thanks, to everyone for the help.  I'm going to mark this as solved, even though there are some issues left that I may come back with.

Thanks,

--Kenoli

Edited February 12, 2021 by kenoli

[mac_gyver]

don't use variable-variables, ever. they are not needed, ever, and for the posted code, those bindParam() statements aren't doing anything. you are overriding them by supplying an array to the ->execute(...) call. using bindParam/bindValue and supplying an array of value to the ->execute(...) call are mutually exclusive methods of supplying values to the query. you should simply supply an array of values to the ->execute(...) call.

i was hoping that getting working php/pdo errors would point to a problem i saw in the code. you are hard-coding the table name (Sites) in the $sql query you are building, rather than using the supplied name. since this table name doesn't match the list of columns you are supplying, you should have been getting a pdo/mysql error about unknown columns in the query at the execution of the ->prepare() call.