Prepared Statement in a PHP Function?

[doubledee]

So I wrote my first PHP Function early tonight and now I'm getting all excited! 

 

Is there any reason why I could not put a Prepared Statement in a Function so I could pass in a "member_id", run a query, and determine the "# of Posts" the Member has in total and then return that?

 

 

Debbie

 

[trq]

Why not try it and see?

[litebearer]

@Thorpe = always with the common sense - sheesh! What fun is that?

[scootstah]

Aside from impending spaghetti code, no not really.

[doubledee]

Aside from impending spaghetti code, no not really.

 

And so what do you think is a more intelligent approach?!

 

I don't know how isolating something that occurs over and over into one place would make spaghetti code... 

 

 

Debbie

 

[doubledee]

Why not try it and see?

 

Because that wouldn't answer the question "Is it a good way to approach and design things by putting a Prepared Statement inside a Function?"

 

 

Debbie

 

[scootstah]

And so what do you think is a more intelligent approach?!

 

Using an MVC framework. Or at the very least, a basic Model structure in OOP.

 

I don't know how isolating something that occurs over and over into one place would make spaghetti code... 

 

Maybe you should ask one of your hundreds of leading experts.

 

Also, look at Wordpress - that's how.

[awjudd]

Yes, this would be a good use of a function assuming you re-use it elsewhere in your code.

 

~awjudd

[doubledee]

And so what do you think is a more intelligent approach?!

 

Using an MVC framework. Or at the very least, a basic Model structure in OOP.

 

Except I don't know MVC frameworks or OOP yet, so that would be a leap...

 

 

I don't know how isolating something that occurs over and over into one place would make spaghetti code... 

 

Maybe you should ask one of your hundreds of leading experts.

 

I made is pretty clear that I come here for PHP and ask other friends for help with HTML/CSS/Web Design.

 

This isn't an HTML/CSS problem, now is it?  So check the sarcasm....

 

 

Debbie

 

[xyph]

Why would you want to hard code a query in to a function? The idea of a function is to create reusable code, so the more generic you can make it, the better.

 

I'd much rather design a wrapper. Something that takes the repetitive process out of writing queries, and allows me to use one single function regardless the query. I've made the one below quickly, using PDO so it can be used with most database servers.

 

Notice how I can plug in either named, or ? placeholders. The function parses the results for me, or it can return an executed statement in case of a large result. If there's an UPDATE or INSERT statement, it will return the number of affected rows.

 

<?php

try {
# Create our DB object
$db = new PDO('mysql:dbname=db;host=localhost', 'root', '');
# Tell it to throw exceptions on error
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
# Set up our query parameters
$params = array(
	':foo' => array('15', PDO::PARAM_INT),
	':bar' => array('hello world', PDO::PARAM_STR)
);
$query = 'SELECT COUNT(*) as total FROM table WHERE foo = :foo AND bar = :bar';
# Perform the statement
$return = prepared($db, $query, $params);
# This shouldn't be needed, as the function will throw exceptions, but it's there just in case
if( $return === FALSE )
	# Throw an exception in case the class didn't already
	throw new PDOException($db->errorInfo());
# Output what we got
print_r($return);

# Numbered params instead
$params = array(
	array('foo', PDO::PARAM_STR),
	array('bar', PDO::PARAM_STR),
	array('25', PDO::PARAM_INT)
);
$query = 'INSERT INTO data_collection (name, data, event_id) VALUES (?, ?, ?)';
$return = prepared($db, $query, $params);
if( $return === FALSE )
	throw new PDOException($db->errorInfo());
echo '<br>There was '.$return.' row affected';

} catch ( PDOException $e ) {
echo 'There was a DB error!';
echo '<br>'.$e->getMessage();
}

function prepared( pdo $pdo, $query, $params = FALSE, $return_values = TRUE ) {
# Prepare the statement
$stmt = $pdo->prepare($query);
# Since we don't know or care if the PDO class will throw an exception on error,
# we'll check for errors and return FALSE when they happen
if( !$stmt )
	return FALSE;
# See if there are any params to bind
if( is_array($params) ) {
	# Loop through params
	foreach( $params as $param => $data ) {
		# If the keys are integers, then it's probably a ? placeholder
		if( is_int($param) )
			# Since arrays are 0-index, and ? placeholders are 1-index, increase
			# array key by one to match
			$param++;
		# If it is a value like ':param', we don't want to change it
		if( !$stmt->bindValue($param, $data[0], $data[1]) )
				return FALSE;
	}
}
# Check if the execute fails
if( !$stmt->execute() )
	return FALSE;
# Check if asked to not return values, but instead the raw, executed statement
if( !$return_values )
	return $stmt;
# If columnCount = 0, there is no result set, so we'll return affected rows
if( $stmt->columnCount() == 0 )
	return $stmt->rowCount();
# Otherwise, we'll return an associative array of the results.
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

?>

 

This is a basic example, and is a rough introduction to more complex, reusable design patterns.

 

So check the sarcasm....

 

There's no need to respond... If you don't like the comment, ignore it. Dictating acceptable content is the job of the mods/admins.

[scootstah]

Except I don't know MVC frameworks or OOP yet, so that would be a leap...

 

That's like a mechanic saying they don't know how to use a wrench yet, so they'll just beat on it with a hammer instead.

 

Take a break from whatever you are doing and spend a week or two to learn the fundamentals of OOP. PHP Objects Patterns and Practice is a very very good book that covers a wide range of OOP topics in PHP, as well as some other things like unit testing and deployment. If you don't have this book, you need to get this book.

 

CodeIgniter is an "introductory" framework of sorts. As far as frameworks go, it is incredibly simple to learn. The documentation is excellent with easy-to-follow code examples. It also has a huge supportive community. You only need to know the uttermost basics of OOP to use CodeIgniter if you don't plan on extending it in any way. If you can create a simple class and use $this then you're good to go.

[doubledee]

Why would you want to hard code a query in to a function? The idea of a function is to create reusable code, so the more generic you can make it, the better.

 

I couldn't very easily follow your OOP/PDO example since I don't know either, but I think what I did on my own is similar...

 

/**
 * Returns the # of Posts made by a Member
 *
 * Takes the Member's ID and determines how many (approved) Comments
 * the Member has made since he/she registered.
 *
 * @param		integer $memberID
 * @return	integer
 */
function getNumberOfPosts($dbc, $memberID){

	// Cast argument to Integer type.
	$memberID = (int)$memberID;

	// Build query.
	$q1 = 'SELECT COUNT(member_id)
					FROM comment
					WHERE member_id=?
					AND approved_on IS NOT NULL';

	// Prepare statement.
	$stmt1 = mysqli_prepare($dbc, $q1);

	// Bind variable to query.
	mysqli_stmt_bind_param($stmt1, 'i', $memberID);

	// Execute query.
	mysqli_stmt_execute($stmt1);

	// Store results.
	mysqli_stmt_store_result($stmt1);

	// Check # of Records Returned.
	if (mysqli_stmt_num_rows($stmt1)==1){
		// Query Succeeded.

		// Bind result-set to variables.
		mysqli_stmt_bind_result($stmt1, $numberOfPosts);

		// Fetch record.
		mysqli_stmt_fetch($stmt1);

		// Close prepared statement.
		mysqli_stmt_close($stmt1);

	}else{
		// Query Failed.
		$_SESSION['resultsCode'] = 'FUNCTION_QUERY_FAILED_xxxx';

		// Set Error Source.
		$_SESSION['errorPage'] = $_SERVER['SCRIPT_NAME'];

		// Redirect to Outcome Page.
		header("Location: " . BASE_URL . "/account/results.php");

		// End script.
		exit();
	}

	return $numberOfPosts;
}//End of getNumberOfPosts

 

 

Isn't that close to what you had?

 

 

Debbie

 

 

 

[xyph]

Your terrible analogy just killed... *rolls d20* 8 puppies.

 

The following is just an opinion:

For someone who just designed their first procedural function, I don't think there's a huge rush to get in to OOP. It's important to understand what objects are (collection of properties and methods) and how to use them, but design patterns are well beyond the scope of this thread.

 

It's a good time to learn the basics of good design, though. Minimizing dependencies and creating robust, reusable functions should be the goal here

 

I'd also second the book's recommendation, though I think Debbie sees PHP as a means to accomplish a goal, rather than PHP being the goal itself.

 

Again, opinion.

[doubledee]

Except I don't know MVC frameworks or OOP yet, so that would be a leap...

 

That's like a mechanic saying they don't know how to use a wrench yet, so they'll just beat on it with a hammer instead.

 

Your analogy is blatantly wrong.

 

Maybe it's an age thing, but people have been programming without OOP for the last 50+ years and even today much of the world of computers does NOT use OOP. 

 

There is life beyond OOP...

 

 

Take a break from whatever you are doing and spend a week or two to learn the fundamentals of OOP. PHP Objects Patterns and Practice is a very very good book that covers a wide range of OOP topics in PHP, as well as some other things like unit testing and deployment. If you don't have this book, you need to get this book.

 

Learning OOP beyond academic examples takes A LOT longer than a few weeks. 

 

I am trying to avoid the train-wreck of code that exists out there by people who *think* they understand OOP but really have no clue.

 

So my slowness to switch from POP to OOP isn't that I'm a wimp, but rather that I respect OOP and want to do it the *right* way...

 

It will be easier to refactor well-written procedural code from a *working* website than to take stab at OOP now.

 

 

CodeIgniter is an "introductory" framework of sorts. As far as frameworks go, it is incredibly simple to learn. The documentation is excellent with easy-to-follow code examples. It also has a huge supportive community. You only need to know the uttermost basics of OOP to use CodeIgniter if you don't plan on extending it in any way. If you can create a simple class and use $this then you're good to go.

 

I have heard lots of people say similar things, and after I get this release done, I'm open to such things.

 

 

Debbie

 

[scootstah]

For someone who just designed their first procedural function, I don't think there's a huge rush to get in to OOP. It's important to understand what objects are (collection of properties and methods) and how to use them, but design patterns are well beyond the scope of this thread.

 

It's a good time to learn the basics of good design, though. Minimizing dependencies and creating robust, reusable functions should be the goal here

 

I understand that it takes time to learn things properly, and I don't expect someone to grasp everything overnight.

 

However I think that dedicating a lot of time designing something that is fundamentally wrong is time you could have spent learning the proper way.

[xyph]

Yeah, but a mechanic isn't going to start teaching an apprentice how to adjust fuel tables under boost just after he's rebuilt his first carb.

 

It's a process, some go through it faster than others.

[KevinM1]

Maybe it's an age thing, but people have been programming without OOP for the last 50+ years and even today much of the world of computers does NOT use OOP. 

 

There is life beyond OOP...

 

OOP's theoretical underpinnings can be traced to the 1950s.  Smalltalk (it's a language) was released in 1972.  In terms of widespread use, one can assume that the adoption of C++ over C by many developers in the 80s/90s played a major role.  In any case, implying that OOP is somehow new is incorrect.

 

There's nothing bad/wrong with procedural programming.  Like xyph said, the idea of creating abstractions that can be used in a variety of situations is a key component of programming in a general sense.  OOP and procedural programming are merely different ways of creating those abstractions.

 

OOP is the dominant paradigm for application programming because it allows us to model things in an intuitive way.  It's not a good fit in all situations because of the overhead it requires.  For a somewhat relevant example, you won't see objects in the linux kernel for that very reason.