• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 798
  • Last Modified:

PHP PDO Simplify prepare

What am I doing wrong here?  I want to make the PDO Prepare simpler.
See the function (Connect) in the second function.  How come this won't work?
function connect()	{
	error_reporting(E_ALL);
	ini_set('display_errors', '1');
	$pdo = new PDO("mysql:host=localhost;dbname=BLAH", "BLAH BLAH", "BLAH BLAH BLAH");
	$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);	
}

function mailListCounter()	{
	connect();

	$lists = 	"SELECT count(email) AS counter, season
				FROM email
				GROUP BY season";

	$pdos = $pdo->prepare($lists, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
		
		try {
    	$pdos->execute();
    	$results = $pdos->fetchAll();
			if($results) {
				foreach ($results as $row) {
					$season = $row['season'];
					$emails = $row['counter'];
					if($season == '')	{
						echo 'List 1:: '.$emails;
					}
				}
			}
		}  //  END TRY			          	
		catch(PDOException $e) {
   		echo 'ERROR: ' . $e->getMessage();
		}
}  //  END mailListFunction

Open in new window

0
rgranlund
Asked:
rgranlund
  • 11
  • 9
  • 6
  • +2
1 Solution
 
GaryCommented:
Having a db connection in a function makes no sense apart from the fact that it is local to that function only (unless you made it global)
Are you calling your mailListCounter multiple times? That would result in multiple connections being setup.
If you need a db connection then just set it up and pass it to your functions. e,g,


error_reporting(E_ALL);
ini_set('display_errors', '1');
$pdo = new PDO("mysql:host=localhost;dbname=BLAH", "BLAH BLAH", "BLAH BLAH BLAH");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);	

mailListCounter($pdo);

function mailListCounter($pdos)	{

	$lists = 	"SELECT count(email) AS counter, season
				FROM email
				GROUP BY season";
...
...
}

Open in new window

0
 
rgranlundAuthor Commented:
Thanks, that is what I needed to know.
0
 
rgranlundAuthor Commented:
@Cathal, actually that did not work.

What is wrong with the following?

error_reporting(E_ALL);
ini_set('display_errors', '1');
$pdo = new PDO("mysql:host=localhost;dbname=BLAH", "BLAH BLAH", "BLAH BLAH BLAH");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);		

mailListCounter($pdo);

function mailListCounter($pdos)	{
	$lists = 	"SELECT count(email) AS counter, season
				FROM email
				GROUP BY season DESC";

	$pdos = $pdo->prepare($lists, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
GaryCommented:
My mistake should be

function mailListCounter($pdo)      {
0
 
Ray PaseurCommented:
Try following the patterns in this article.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

There may be more sophisticated examples elsewhere, but if you just want to get something running, a procedural script that uses PDO will be a good first step.  The article shows how the familiar, but obsolete, MySQL functions are implemented in MySQLi and PDO, with step-by-step examples.

If you're not familiar with variable scope, read this:
http://www.php.net/manual/en/language.variables.scope.php

In PHP object oriented programming, variable scope is different from procedural programming.  In OOP, it's called visibility.
http://www.php.net/manual/en/language.oop5.visibility.php
0
 
rgranlundAuthor Commented:
Sorry, that did not work either.  Code below:

error_reporting(E_ALL);
ini_set('display_errors', '1');
$pdo = new PDO("mysql:host=localhost;dbname=BLAH", "BLAH BLAH", "BLAH BLAH BLAH");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);		

mailListCounter($pdo);

function mailListCounter($pdo)	{

	$lists = 	"SELECT count(email) AS counter, season
				FROM email
				GROUP BY season DESC";

	$pdos = $pdo->prepare($lists, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
		try {
    	$pdos->execute();
    	$results = $pdos->fetchAll();

Open in new window

0
 
GaryCommented:
What error are you getting? Do you have error reporting on?
0
 
Ray PaseurCommented:
@rgranlund: That last script will not even pass the PHP parser.  Please post at least enough of your SSCCE that we can install it and run it.

If you go to this article, you will see how to use PDO with try and catch.  This will enable your script to tell you what happened when you try to run the query.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

In the instant case I can see no reason to prepare the query.  It has no external variables that need to be bound.
0
 
rgranlundAuthor Commented:
I have a hand full of functions.  Each require a connection to the DB.  I don't want to place the Connection script in each function.  Not sure how to accomplish this.
0
 
Chris StanyonCommented:
Seeing only part of your code and being told 'it doesn't work' is not going to get you answers fast.

When you just say 'it doesn't work', it could be because your computers not turned on!

You need to show us all your code, and tell us exactly what happens - error messages . detail etc.

There's nothing wrong with the code you've posted, but as it stands it does absolutely nothing!
0
 
rgranlundAuthor Commented:
I have a hand full of functions.  Each require a connection to the DB.  I don't want to place the Connection script in each function.  Not sure how to accomplish this.
0
 
Chris StanyonCommented:
You've already said that, but we still need to see the code, and get some idea of what's 'not working'
0
 
GaryCommented:
Make sure you have this at the top of your page and hopefully it will give a meaningful error

error_reporting(E_ALL);
0
 
rgranlundAuthor Commented:
It is all posted at the very top. Here is a revision, this is what is not working.
	error_reporting(E_ALL);
	ini_set('display_errors', '1');
	$pdo = new PDO("mysql:host=localhost;dbname=BLAH", "BLAH BLAH", "BLAH BLAH BLAH");
	$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);	

function mailListCounter($pdo)	{


	$lists = 	"SELECT count(email) AS counter, season
				FROM email
				GROUP BY season";

	$pdos = $pdo->prepare($lists, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
		
		try {
    	$pdos->execute();
    	$results = $pdos->fetchAll();
			if($results) {
				foreach ($results as $row) {
					$season = $row['season'];
					$emails = $row['counter'];
					if($season == '')	{
						echo 'List 1:: '.$emails;
					}
				}
			}
		}  //  END TRY			          	
		catch(PDOException $e) {
   		echo 'ERROR: ' . $e->getMessage();
		}
}  //  END mailListFunction

Open in new window

0
 
GaryCommented:
Not to state the obvious but where is your code that actually calls the mailListCounter function???
It was there before.
Apart from that the code works fine.
0
 
Chris StanyonCommented:
Right. I'll say it one more time, and then I'm out. WHAT's NOT WORKING! We need specifics. When you run your code does it open up IE and browse to Disney.com, or play a tune, or crash and burn, or do nothing, or.......you get the message.

If that's your whole code and nothing but your code, then it will do absolutely nothing - you've defined the function but never called it!

help us to help you!
0
 
rgranlundAuthor Commented:
ERROR:
Notice: Undefined variable: pdo in functions.php on line 13

Fatal error: Call to a member function prepare() on a non-object in /functions.php on line 13

This is LINE 13:
$pdos = $pdo->prepare($lists, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
0
 
GaryCommented:
Where is your code calling the function?
0
 
Chris StanyonCommented:
When you call the function like this:

function mailListCounter($pdo)

Make sure that the call comes after the PDO connnection code at the start of your script.

If your running this stuff over several different files, then make sure you include the code from above, before calling the function.

Also, if you are calling mailListCounter($pdo) from within another function, then you have an issue with scope - $pdo doesn't exist inside the function. If that's the case, drop this in just before the function call and see if it works

global $pdo;

Open in new window

0
 
Ray PaseurCommented:
I have a hand full of functions.  Each require a connection to the DB.  I don't want to place the Connection script in each function.  Not sure how to accomplish this.
That's why object oriented programming is a good idea!
0
 
Ray PaseurCommented:
Experts-Exchange needs a "learning room" to help members of our community grok these broad concepts and get drilled down into the depth of understanding needed to give rise to well-understood programming concepts.

Community: What say you?
0
 
rgranlundAuthor Commented:
I wish I knew how to ask the question better, however, I do appreciate all of the help.

The function is being called on another page.  The function.php file  is included in that page before the header.
0
 
Chris StanyonCommented:
The point I made about scope is important. You are declaring your $pdo variable in the global scope which means it's NOT available inside any other function, so if you're calling mailListCounter($pdo) from within another function you will get an error (Undefined Variable)

Despite several requests, you still haven't shown us the block of code you use to call the function.

We need to see that block of code - all of it. Lines of code don't work in isolation.

We could spend the next 3 days guessing what code you've written, or you could spend 5 minutes actually posting it. If you want an answer to this, I suggest the latter :)

Is there a particular reason you're reluctant to post it?
0
 
Ray PaseurCommented:
What Chris Stanyon said.

When a code set gets large enough that you're considering posting only a fragment of it, it's gotten too large.  This is called a code smell (bloater), and it's a symptom that indicates the possibility of an underlying problem, perhaps a design flaw or a lack of organization.  In these cases, it's better to step away from the complicated problem and deconstruct it into a set of smaller problems that can each be solved on its own.

In the instant case I see a few things that can be treated as standalone problems.

1. When and how would a script prepare a query?
2. What is an appropriate way to run queries, and visualize errors if any?
3. What makes sense for variable encapsulation and visibility of the data base connection?
4. What programming standards should be followed to make the code more readable?

Maybe consider pursuing each of these individually, then using the concepts learned from each point, synthesize the larger question about how to bring this together in a way that implements the standard principles of computer science and object-oriented design.  

What you're asking now is somewhat akin to "how do you bake an apple pie?"  It has a lot of nuance in the many, many parts that have to come together correctly to avoid making a mess.  We don't know, for example, whether you understand the temperature ranges for integrating the fats in pie crust, so if we tell you to "make a crust" we may be wasting your time because you'll turn out a greasy pile of glop instead of a flaky crust, and the pie will be ruined.  Similarly, if we tell you to make a variable global, we don't know whether you understand the risks and benefits.  It's a bit of a balancing act.
0
 
rgranlundAuthor Commented:
This is what I have so far.  In its entity. There are two files.  The functions.php file and the home.php file.  The functions.php file has 1 function in it.  But, in the future there will be more functions.  Each of the functions will require connection to the DB.  I do not want to write the DB connection within each function.  How do I accomplish that?
Functions.php file:
<?php
error_reporting(E_ALL);
ini_set('display_errors', '1');
$pdo = new PDO("mysql:host=...t;dbname=...", "...", "...");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

function mailListCounter($pdo)	{

	$lists = 	"SELECT count(email) AS counter, season
				FROM email
				GROUP BY season DESC";

	$pdos = $pdo->prepare($lists, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
		echo '<table><tr><td width="100">LIST NAME</td><td>TOTAL</td><tr>';
		try {
    	$pdos->execute();
    	$results = $pdos->fetchAll();
			if($results) {
				foreach ($results as $row) {
					$season = $row['season'];
					$emails = $row['counter'];
				}
			}  //  END FOREACH
		}  //  END TRY			          	
		catch(PDOException $e) {
   		echo 'ERROR: ' . $e->getMessage();
		}
		echo '</table>';
}  //  END mailListFunction

Open in new window

The Home.php file:
<?php include('includes/functions.php'); ?>
<!doctype html>
<html lang="en">
<head>
<body>
<!--  START MAILING LIST COUNTER  -->
			<fieldset>
				<?php mailListCounter(); ?>
			</fieldset>
<!--  END MAILING LIST COUNTER  -->

</body>
</html>

Open in new window


This is the error that I get:
Warning: Missing argument 1 for mailListCounter(), called in functions.php 

Notice: Undefined variable: pdo in functions.php 

Fatal error: Call to a member function prepare() on a non-object in functions.php 

Open in new window

This is all I have.  Nothing more. Not hiding anything.
0
 
Chris StanyonCommented:
OK. We're getting somewhere ;)

In your home page you need to pass in the $pdo argument:

<?php mailListCounter($pdo); ?>

Open in new window

0
 
Chris StanyonCommented:
Also, looking at you function file, all it will do is echo out the opening and closing TABLE tags - nothing else. In your foreach loop you are setting the same variable over and over again, but doing nothing with it. I'm guessing you should be echoing out some table row code for each database record:

foreach ($results as $row) {
   printf('<tr><td>%s</td><td>%s</td></tr>', $row['season'], $row['counter'] );
}

Open in new window

0
 
rgranlundAuthor Commented:
Why does the argument need to be passed in both pages? mailListCounter($pdo)
0
 
Chris StanyonCommented:
It doesn't!

In your functions file you are declaring a function and telling it to expect an argument.

In your homepage, you are calling (executing) the function and passing in the argument that your function definition is asking for.
0
 
Chris StanyonCommented:
Look at this:

//create a function that takes an argument
function printMyName($name) {
   print $name;
}

//now we want to execute that function.
//Remember, it expects an argument ...
$myName = "Chris";
printMyName($myName);
0
 
Slick812Commented:
greetings rgranlund, although others have given you some examples here you seem to miss the important points for your code corrections, I hope I can help you on this? ?

 You are a very beginner in the PHP code stuff, so you will naturally have misunderstandings on what, why and when to do correct code, so do not get discouraged, it takes time for everyone at first.

you have missed several suggestions about getting a useful (correct) Reference to your PDO database object as the error you gave -
Notice: Undefined variable: pdo in functions.php on line 13

this is because you have only a "Local" variable inside your function, as has been explained to you already, You need to learn that in PHP, , it CAN NOT REACH OUTSIDE of a function to use other $variables NOT inside that function OR in its arguments, without some special code arrangements. You can PASS INTO a function OTHER $variables, in the Parentheses ( ) as arguments to use in that function, , IMPORTANT! You Need to learn this about passing in variables as function arguments, it is necessary and Useful! !

here is some code I did on my server that gave me correct results (page output)
<?php
ini_set('display_errors', 1);
error_reporting(E_ALL);
?><!DOCTYPE html>
<html><head><title>PDO functions</title></head>
<body><h2>PDO functions</h2>
<p>
<?php

// I do NOT place these in a separate PHP file for this as a TEST
//I can have a connect() function
function connectPDO( ) {
$dbo = new PDO("mysql:host=localhost;dbname=sales;", "name", "passw");
$dbo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
return $dbo;
}

function mailListCounter($fdbo) {
// WARNING My TABLE and columns are different than your
$lists = "SELECT dept, count(email) AS counter
		FROM instuctors
		GROUP BY dept";

try {
  $statem = $fdbo->prepare($lists, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
  $statem->execute();
  $results = $statem->fetchAll();
  if($results) {echo "Rows below<br />";
    foreach ($results as $row) {
echo "There are ". $row['counter'] ." emails in dept - ". $row['dept'] ."<br />";
/* your code for display is NOT GOOD!
      $season = $row['season'];
      $emails = $row['counter'];
      if($season == '')	{
        echo 'List 1:: '.$emails;
        }
*/
      }
    }
  }  //  END TRY			          	
  catch(PDOException $e) {
  echo 'ERROR: ' . $e->getMessage();
  }
}  //  END mailListFunction
	
// // // // // // // // // // // /// BEGINNING OF WORK CODE
$pdo = connectPDO( );// make PDO OBJECT
mailListCounter($pdo); // do a SELECT prepare
                                  
?>
</p>
THE END
</body></html>

Open in new window

you seem to be getting the "VARIABLE NAMES" mixed up as using the $pdo in several places, Please notice, this is a common beginner mistake , you should try and use DIFFERENT variable names in the functions and in the code calls to avoid confusion , see my example above, ALSO, you do NOT need the prepare and execute, if you do NOT have any user input, for this type of select -
$lists = "SELECT dept, count(email) AS counter
            FROM instuctors
            GROUP BY dept";
you should use the standard PDO query, not the prepare.
0
 
Ray PaseurCommented:
There are still a lot of layers of confusion here.  Let's take this one, for now.  Please explain why you think you need to prepare this query. I'd be interested in the documentation you're following or the thought processes that lead you to that opinion.

$lists = 	"SELECT count(email) AS counter, season
				FROM email
				GROUP BY season DESC";

Open in new window

0
 
rgranlundAuthor Commented:
@Ray, I was following your instructions from a previous question.  Do I not need to do that?
I guess I don't understand "Prepare" and its correct use.
0
 
Slick812Commented:
if you just look at the PDo query Example in the PHP Manual -
       http://www.php.net/manual/en/pdo.query.php

they have a "function" that is Almost the same as yours -

function getFruit($conn) {
    $sql = 'SELECT name, color, calories FROM fruit ORDER BY name';
    foreach ($conn->query($sql) as $row) {
        print $row['name'] . "\t";
        print $row['color'] . "\t";
        print $row['calories'] . "\n";
    }
}

changes slightly for you

function mailListCounter($conn) {
    $sql = "SELECT count(email) AS counter, season FROM email GROUP BY season";
    foreach ($conn->query($sql) as $row) {
    echo "There are ". $row['counter'] ." emails in season - ". $row['season'] ."<br />";
    }
}

The mysqli PREPARE is a special operation, that separates the "SQL text" and the Variables from PHP that may be in a SQL, so there is extra protection from SQL injection.

But if there are NO PHP variables in your SQL as -
$sql = "SELECT count(email) AS counter, season FROM email GROUP BY season";

then it WILL work in a prepare(), , BUT it is maybe Overkill to use it as there is NO chance that there can be any SQL injection, and is NO HELP to use it, the $conn->query($sql); will be fine for it.  . For a beginner this is difficult to understand, and easy to miss, even if you have been told about it, but you will pick it up if you look more closely at "Why" you use some code functions (methods) for one thing and other code functions (methods) for different things, as you get more experience.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 11
  • 9
  • 6
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now