Robert Granlund
asked on
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?
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
ASKER
Thanks, that is what I needed to know.
ASKER
@Cathal, actually that did not work.
What is wrong with the following?
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));
My mistake should be
function mailListCounter($pdo) {
function mailListCounter($pdo) {
Try following the patterns in this article.
https://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
https://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
ASKER
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();
What error are you getting? Do you have error reporting on?
@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.
https://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.
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.
https://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.
ASKER
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.
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!
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!
ASKER
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.
You've already said that, but we still need to see the code, and get some idea of what's 'not working'
Make sure you have this at the top of your page and hopefully it will give a meaningful error
error_reporting(E_ALL);
error_reporting(E_ALL);
ASKER
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
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.
It was there before.
Apart from that the code works fine.
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!
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!
ASKER
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));
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));
Where is your code calling the function?
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
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;
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!
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?
Community: What say you?
ASKER
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.
The function is being called on another page. The function.php file is included in that page before the header.
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?
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?
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.
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.
ASKER
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:
This is the error that I get:
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
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>
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
This is all I have. Nothing more. Not hiding anything.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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'] );
}
ASKER
Why does the argument need to be passed in both pages? mailListCounter($pdo)
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.
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.
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);
//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);
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)
$lists = "SELECT dept, count(email) AS counter
FROM instuctors
GROUP BY dept";
you should use the standard PDO query, not the prepare.
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>
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.
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";
ASKER
@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.
I guess I don't understand "Prepare" and its correct use.
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.
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.
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,
Open in new window