Solved

PHP PDO Simplify prepare

Posted on 2014-02-11
34
691 Views
Last Modified: 2014-02-22
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
Comment
Question by:rgranlund
  • 11
  • 9
  • 6
  • +2
34 Comments
 
LVL 58

Expert Comment

by:Gary
ID: 39851496
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
 
LVL 7

Author Comment

by:rgranlund
ID: 39851543
Thanks, that is what I needed to know.
0
 
LVL 7

Author Comment

by:rgranlund
ID: 39851630
@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
 
LVL 58

Expert Comment

by:Gary
ID: 39851642
My mistake should be

function mailListCounter($pdo)      {
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39851665
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
 
LVL 7

Author Comment

by:rgranlund
ID: 39851709
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
 
LVL 58

Expert Comment

by:Gary
ID: 39851790
What error are you getting? Do you have error reporting on?
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39851809
@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
 
LVL 7

Author Comment

by:rgranlund
ID: 39851833
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
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 39851850
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
 
LVL 7

Author Comment

by:rgranlund
ID: 39851887
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
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 39851891
You've already said that, but we still need to see the code, and get some idea of what's 'not working'
0
 
LVL 58

Expert Comment

by:Gary
ID: 39851898
Make sure you have this at the top of your page and hopefully it will give a meaningful error

error_reporting(E_ALL);
0
 
LVL 7

Author Comment

by:rgranlund
ID: 39851908
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
 
LVL 58

Expert Comment

by:Gary
ID: 39851926
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
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 39851928
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
 
LVL 7

Author Comment

by:rgranlund
ID: 39851952
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 58

Expert Comment

by:Gary
ID: 39851961
Where is your code calling the function?
0
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 39851986
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39851992
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39852003
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
 
LVL 7

Author Comment

by:rgranlund
ID: 39852105
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
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 39852128
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39852875
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
 
LVL 7

Author Comment

by:rgranlund
ID: 39854094
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
 
LVL 42

Accepted Solution

by:
Chris Stanyon earned 500 total points
ID: 39854126
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
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 39854144
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
 
LVL 7

Author Comment

by:rgranlund
ID: 39854214
Why does the argument need to be passed in both pages? mailListCounter($pdo)
0
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 39854220
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
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 39854232
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
 
LVL 33

Expert Comment

by:Slick812
ID: 39854242
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39854313
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
 
LVL 7

Author Comment

by:rgranlund
ID: 39854349
@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
 
LVL 33

Expert Comment

by:Slick812
ID: 39854415
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.‚Äč
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Viewers will get an overview of the benefits and risks of using Bitcoin to accept payments. What Bitcoin is: Legality: Risks: Benefits: Which businesses are best suited?: Other things you should know: How to get started:
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now