Pdo query inside a function problem

After reading the post about migrating my MySql deing depreciated I decided to change my code over to use pdo. After a bit of a fight I managed to get the first page sorted although it seems a bit of a waste that every query is connecting to the database when the previous query has already opened it.

But my second page is more of a problem as I am using a user function with the database query inside it.
I have tried putting the database connection details with the function like this:


Function gallery( $gallery_name, $host, $user, $pass, $dbname ){
$DBH = new PDO("mysql:host=$host; dbname=$dbname", $user, $pass);
$STH = $DBH->query("SELECT * FROM plans.........");
// etc.
 }
gallery( $gallery_name, ‘localhost’, ‘username’, ‘password’, ‘database’ );

I have also tried:


$DBH = new PDO("mysql:host=$host; dbname=$dbname", $user, $pass);
Function gallery( $gallery_name, $DBH){
$STH = $DBH->query("SELECT * FROM plans.........");
//etc.
 }
gallery( $gallery_name, $DBH);

Neither of these has worked; what is the best way to do this?

Is the code that you’re using it with procedural or OOP?

procedural or OOP

It is not OOP so it must be procedural?

I am just using basic php with a user function - the only OOP part is the pdo bit.

has any error mensage?

i suggest you to create a config file with a connection e after called in the other pages.

cofig.php


function connect($driver, $host, $database, $user, $pass){
return new PDO("$driver:host=$host; dbname=$database", $user, $pass);
}

$driver = 'mysql';
$host = 'localhost';
$database = 'test';
$user = 'root';
$pass = 'pass';

$db = connect($driver, $host, $database, $user, $pass);


other_page.php


include 'config.php';

function getGalery($db){
   $sql = 'SELECT * FROM table....';

   $stmt = $db->prepare($sql);
   $stmt->execute();
   
   return $stmt->fetchAll(PDO::FETCH_ASSOC);

}

//this variable $db is from config.php
$galeries = getGalery($db);

foreach($galeries as $item){
   echo $item['key'];
}



use preparedStatement is save against sql injection.

those articles are about others features of PDO

Thanks @perdeu ; your example works but it is doing what I want to avoid and that is I need to format and display the data returned from the function. I have three of these function calls and I would then need to write a lot more code as currently the data is formatted and displayed within the function.

With my tests no errors are returned; I just get a blank page. I will have to write and upload a better example as there is no point in uploading the page as is with all the extra code complicating things.

Is the general idea to reuse the functions in a number of places, or are they functions that are tied to a particular query?

I do not know why I did not think of posting the original code as the pdo code I have been using is a mess.

This selects the different prices and displays them in a table; I need to replace the MySQL code in the function with the pdo code.
This is the only page this function is used on and it is called three times with a different region - prices are different due to postage costs etc.


<?php
// Function to display the models, prices and check boxes.
function display_prices( $region ){

echo"<table width='95%' border='0' bgcolor=\\"#18a8d0\\" cellspacing=\\"5\\">\
";

// Get the data from the table
$result = MYSQL_QUERY(" SELECT * FROM prices WHERE region='$region' ORDER BY price DESC") or die ( mysql_error() );

// Display the data
while ( $row = mysql_fetch_array( $result )) {

echo "<tr><td align=\\"left\\"><p class=\\"content\\">".$row['model']."</p></td>\

<td align=\\"right\\">&pound;".$row['price']."</td>\

<td><input type=\\"text\\" name=\\"plans[".$row['ref_number']."]\\" size=\\"1\\"  /></td>\

</tr>\
";

}
echo "</table>\
";
}

// Database connection
include "connect.php";

display_prices( 'UK' );
echo "</td><td>";
display_prices( 'Europe' );
echo "</td><td>";
display_prices( 'ROW' );

MYSQL_CLOSE();

?>

The problem I am having is when I put the pdo code into the function I am not getting any output.
A secondary problem is I have tried multiple things and I can not remember what did what!

Isolate all of your new code and develop incrementally.

In a separate folder create a fresh connect.php

just echo a line and then include it in say, index.php

Make sure the echo appears.

Put a connection to your db in that and make sure it does not throw an error


echo 'No, I am NOT losing my mind.' . PHP_EOL ;

$user = 'rubble';
$pass = ''; 

try {
    $PDO = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}

So now you know you are at least connecting to your db.

in index.php, leave the function aside for a moment.

Just check you can get something from your db.



include './connect.php' ;

    foreach($PDO->query('SELECT * FROM prices ORDER BY price DESC') as $row) {
        print_r($row);
    }


Bear in mind that probably the main benefit of using PDO is prepared statements which used correctly will protect your from sql injection attacks.

This means you are using 2 classes in tandem, PDO and PDOStatement. From my own experience of first using PDO not establishing which is which leads to much confusion and frustration.

That should give you a start, if all went well you have made a connection to PDO and used its native ->query() method to send in a query.

The next thing is to do what @perdeu; says and to pass that connection to a function.


include './connect.php' ;

function display_prices($PDO){

    foreach($PDO->query('SELECT * FROM prices') as $row) {
        print_r($row);
    }

}

display_prices($PDO);

Then reintroduce your variable for the WHERE clause as a second argument and just stick it into the naked PDO connection for now.


display_prices($PDO, $region);

Then it would be a good idea to introduce the PDOStatement so that you properly escape the data



function display_prices($PDO, $region) {

$stmt = $PDO->prepare("SELECT * FROM prices where region = ?");

// now you've switched to using PDOStatement class ...

// this is just one way of doing it, taken from the manual
if ($stmt->execute(array($region))) {
  while ($row = $stmt->fetch()) {
    print_r($row);
  }
}

}

Then you can fiddle with that and you should try using bindparam() method etc.

Just to say I haven’t tested any of this code btw - so there might be syntax errors etc but should get you started. See how you get on.

Thank you for the help Cups - I should have started simple in the first place rather than trying to build it into the current page.

That all works and I will have to read up on the prepared statements and bindparam()

I will have to look back but from memory the other page on a different website I changed I had to use $row->price and in others places $row[‘price’] which was causing some of the problems.

you can change the type of return the associative array to object,

[code language=“php”]
$stmt->fetchAll(PDO::FETCH_OBJ)



in the link has all type of returns, search for PDO::FETCH_

http://br1.php.net/manual/en/pdo.constants.php

So @perdeu ; on the other page I was using PDO::FETCH_OBJ which means I needed the $row1->photo and if I was using PDO::FETCH_ASSOC ( or I assume in this case the default as nothing is specified ) I would use $row1[‘photo’]

Hi, glad it helped.

The term to remember is Object notation ( where a std class is returned with properties that you access using ->) and Array notation.

Having a name to hang things on helps.

Using PDO::FETCH_OBJ means you access the results using Object notation.

You choose to match the return type to what you already have.