Using PDO inside PHP function

Hi there everyone,

I won’t post all the attempts that I’ve made but it involves an hour of trying examples found in search results, creating function for the PDO connection, etc. Everything I’ve tried has resulted in error and I’m out of brain cells to resolve it.

I would like to use PDO inside multiple functions. I can not figure out how. Here’s an example of what fails.

My connection:

$pdo = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpasswd);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

My function:

/* Bad Apple value is determined by failed logins, registrations and the like */
function badapple(PDO $pdo, $user_sid, $ba_action){
	$stmt = $pdo->prepare("SELECT user_sid, ba_action, count FROM badapple WHERE sid = :user_id AND action = :ba_action LIMIT 1");
	$ba = $stmt->bindParam(':user_sid', $user_sid, ':ba_action', $ba_action, PDO::PARAM_STR);
	if ($ba->rowCount() > 0) {
		$ba['count'] = $baValue;	
	}else{
		/* No record for this user/action combo. */
		$baValue = '0';
	}

	return $baValue;
}

Creating a function for the PDO connection results in endless errors, up to and including that it doesn’t know what to do with localhost. Using my existing PDO connection in a function (both with and without passing it in the variables) results in errors of it’s own.

Fatal error: Uncaught TypeError: badapple(): Argument #1 ($pdo) must be of type PDO, string given, called in /var/www/clients/client1/web22/web/includities/pages/page-registration.php on line 10 and defined in /var/www/clients/client1/web22/web/includities/functions.php:9 Stack trace: #0 /var/www/clients/client1/web22/web/includities/pages/page-registration.php(10): badapple() #1 /var/www/clients/client1/web22/web/index.php(164): include(‘…’) #2 {main} thrown in /var/www/clients/client1/web22/web/includities/functions.php on line 9

Could someone share with me how to utilize my connection inside functions?

1 Like

One problem is you are not executing the query.

How exactly are you calling the function?

You are going to end up with a lot of redundant code going down this path. You might want to take a look at my “Clean-PDO” repo for an example.

1 thing to also note, your database connection should return an object. If it’s not then it’s returning an error. I would suggest wrapping it around a try-catch block first and then return your connection. In the catch statement just for testing purposes, I’d use a die and print out the actual error so I know what’s going on. For production, I’d remove that from the catch statement and exit out with a generic message such as “Unable to connect to database” or something like that.

The current error, about the argument being a string, is due to what value you are actually supplying when you call the function. You would need to post the code leading up to and including the function call for anyone here to be able to help with this error.

As to trial and error attempts to program. Programming is a writing (and reading) activity. To effectively write code that does what you want, you must learn the meaning of the words and syntax you are trying to use, so that you will know which words and syntax you should even be using for any particular task. This is where the dictionary (documentation) for the programming language(s) you are using comes in handy. Just trying a bunch of things, without learning why each one doesn’t work, doesn’t allow you to find solutions that do work.

Your database connection code should also -

  1. set the character set to match your database tables, so that no character conversion occurs when sending data to/from the database server. this is also required when using emulated prepared queries, in order to allow php to properly escape string data to prevent any sql special characters in a value from being able to break the sql query syntax.
  2. set the emulated prepared query setting to false, so you use true prepared queries.
  3. set the default fetch mode to assoc, so that you don’t need to specify it in each fetch statement.

In your your function code -

  1. the value returned by the ->bindParam() call is either a true or false value. see the documentation.
  2. you don’t need to explicitly bind input data, you can simply supply an array of input values to the ->execute([…]) call (which you don’t have in your code at all), and eliminate any bindParam()/bindValue() calls.
  3. if you do use bindParam()/bindValue() calls, that’s not the correct parameter usage. see the documenation.
  4. the rowCount() method is a PDOStatement method (you would call it using $stmt->rowCount()) and is not guaranteed to work with a select query. you should instead simply fetch (which you don’t have in your code at all) and test if there was any fetched data.
  5. assignment statements assign the right-side value to the left-side variable. $ba[‘count’] = $baValue; is both backwards and since $ba is either the true or false value from the ->bindParam() call, not the fetched data from the query, would be producing an error about trying to access a nonexistent offset on a boolean value.

Hi there,

I’m trying to utilize your example in the function but I’m having the same issue that I’m struggling to understand how to pass the db connection to the function.

My connection:

<?php

/* File: /includities/configlio.php */

session_start();


if(!defined('parentInclude')) {
	header("Location: /");
	exit;
}

set_exception_handler('my_exception_handler');
require_once './includities/clean-pdo/Connection.php';
require_once './includities/clean-pdo/DB.php';

// Handle Exceptions however you want
function my_exception_handler($e) {
    echo 'Error: ';
	print_r($e->getMessage());
    error_log($e->getMessage());
}

$db = new DB((new Connection())->connect(require_once './includities/clean-pdo/dbConfig.php'));
...

My code using my function:

/* If user has more than 5 badapple value in sessions(usually because of failed form submissions), don't allow access to this page. */
if(badapple($usr['sid'], 'registration') >= 5){
	$alert['danger'] = "You seem to be having a problem entering a valid email address. We're going to give you some time to figure out what yours is before allowing you another chance to try to register.";
	include("./includities/pages/page-default.php");
	exit;
}

My function:

/* Bad Apple value is determined by failed logins, registrations and the like */
function badapple($user_sid, $ba_action){
	$ba = $db->run('SELECT count FROM badapple WHERE user_sid = ? AND action = ?', [$ba_action])->fetch();
	if ($ba->rowCount() > 0) {
		$baValue = $ba['count'];	
	}else{
		/* No record for this user/action combo. */
		$baValue = '0';
	}

	return $baValue;
}

If you could explain to me how to successfully pass your example connection to a function, I’d be greatly appreciative.

Forget the repo example. You are just making new problems.
Get your code working without using a function FIRST, then read up on how functions work before trying to use one. In the link below, pay special attention to the section “PHP Function Arguments”

2 Likes

Thank you very much for your code and help. I think I’ve got it going and will muddle my way forward.

1 Like

1 thing you should really take note on is “scope”. It’s very important when you start to get into more advanced scripting. Scope outside of functions are very different from scope inside functions and it gets even more confusing when you start to add in classes because now there’s scope within classes within functions.

The issue @benanamen is pointing out to you is that you’re trying to treat your code as if it runs and “assumes” scope. Your $db variable doesn’t have any scope within your function yet you’re trying to make your function “assume” it has access to the $db variable when it really doesn’t. Hence why I’m guessing @benanamen gave you the link to PHP functions to look at.

1 Like

Thank you very much and I am reading the link on functions. This code is something I wrote years ago utilizing mysqli and I’m trying to modify it to utilize PDO.

To explain why I’m so bad at this, I’m not a developer and almost always, I’m the only person that uses the code I write. As the time progresses, the languages get more powerful and complex and I get less capable. While I do fully understand that the brute forcing method of my coding is without question the wrong way to do it, it’s what I’m capable of in the small number of minutes a day that I get to sit down and try to work on my project. After a couple weeks of this, I do end up getting better at making things work(not well, mind you) and have to Google or ask the forum less.

I very much appreciate all the help you collectively give, it makes my effort possible.

No I get it. We all started somewhere. What I would suggest is learning the basics first before trying to dive into something as complex as what you’re attempting to do. You’re going to do a lot of damage to your current code base if you don’t know what it’s really doing.

As for your “mysqli going to PDO” comment, I don’t think this is a PDO vs mysqli issue. You could easily do the same thing in mysqli. Not many people on the forums here can switch between mysqli and PDO easily. The issue you’re having is with understanding how the language actually works. I’ll help you out with 2 examples (1 for PDO and 1 for mysqli) here using your original code.

MySQLi version

<?php
try {
	$db = new mysqli('localhost', 'root', '', 'test');
} catch(Exception $ex) {
	die('Unable to connect to the database');
}

/* Bad Apple value is determined by failed logins, registrations and the like */
function badapple(object $db, int $user_sid, string $ba_action): int {
	$sql = 'SELECT count, action FROM badapple WHERE user_sid = ? AND action = ?';
	$prepare = $db->prepare($sql);
	$prepare->bind_param('is', $user_sid, $ba_action);
	$prepare->execute();
	$prepare->store_result();
	if ($prepare->num_rows > 0) {
		$prepare->bind_result($count, $action);
		while ($prepare->fetch()) {
			$baValue = $count;
		}
	} else {
		/* No record for this user/action combo. */
		$baValue = 0;
	}

	return $baValue;
}

print badapple($db, 1, 'ban') . "\r\n";

PDO version

<?php
try {
	$db = new PDO('mysql:host=localhost;dbname=test', 'root', '');
} catch(Exception $ex) {
	die('Unable to connect to the database');
}

/* Bad Apple value is determined by failed logins, registrations and the like */
function badapple(object $db, int $user_sid, string $ba_action): int {
	$sql = 'SELECT count, action FROM badapple WHERE user_sid = :user_sid AND action = :ba_action';
	$prepare = $db->prepare($sql);

	$parameters = [
		':user_sid' => $user_sid,
		':ba_action' => $ba_action,
	];

	$prepare->execute($parameters);
	if ($prepare->rowCount() > 0) {
		while ($row = $prepare->fetch()) {
			$baValue = $row['count'];
		}
	} else {
		/* No record for this user/action combo. */
		$baValue = 0;
	}

	return $baValue;
}

print badapple($db, 1, 'ban') . "\r\n";

Both gives me the same exact outcome. The issue you’re having is understanding how to “pass in” your database connection. If the database connection is returning a string and not an object, that most likely means you’re having a database connection issue whether that’d be a typo in your connection string or the database truly is unreachable. You should really be putting that in a try-catch block so if it truly is a database issue, you don’t want to continue. The reason why you would do this is because if you don’t have access to the database and you’re trying to pull data from the database, there’s nothing for you to pull from because the connection to the database was never made in the first place. You want to halt this action immediately because whatever you do next such as pulling data from the database, inserting data into the database, updating a row from the database, or even deleting a row from the database cannot be performed due to the connection not being there.

So if your $db variable is returning a string or a boolean, you would really want to halt this immediately so that you can debug why this is happening. The reason why these 2 examples “work” is because of what I said earlier about “scope”. We provide the function with what it needs such as database connection and the other 2 parameters. So when we pass in our database connection into the function, now the function’s scope has the database connection.

In simple terms, you can think of a function’s scope as “local” to that function. Nothing outside of that function has any idea on what’s going on inside of that function. You can think of this as something happening on Mars. Yes, we have telescopes (this could be considered a “global” item - you typically don’t want to use this since it’s a no-no in the programming world) that can reach Mars and what not, but we don’t know what’s going on on the surface of Mars. We can only see the red clouds moving, we don’t know what’s really happening inside of those red clouds. This is the same thing with functions. Each function doesn’t have any scope into one another and that’s ok. That’s how it should work.

Hopefully that helps you a little bit.

1 Like