Pdo query inside a function problem

[Rubble]

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?

[SpacePhoenix]

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

[Rubble]

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.

[perdeu]

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

PHP Master | Migrate from the MySQL Extension to PDO

PHP Master | Avoid the Original MySQL Extension, Part 2

[Rubble]

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.

[Cups]

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

[Rubble]

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!

[Cups]

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.

[Rubble]

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.

[perdeu]

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

[Rubble]

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’]

[Cups]

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.