SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,191
    Mentioned
    71 Post(s)
    Tagged
    3 Thread(s)

    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:
    PHP Code:
    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:
    PHP Code:
    $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?

  2. #2
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,904
    Mentioned
    93 Post(s)
    Tagged
    0 Thread(s)
    Is the code that you're using it with procedural or OOP?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  3. #3
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,191
    Mentioned
    71 Post(s)
    Tagged
    3 Thread(s)
    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.

  4. #4
    SitePoint Member
    Join Date
    Jul 2013
    Location
    Brazil
    Posts
    9
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    has any error mensage?

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

    cofig.php
    Code:
    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

    Code:
    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

    http://www.sitepoint.com/migrate-fro...ension-to-pdo/
    http://www.sitepoint.com/avoid-the-o...l-extension-2/

  5. #5
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,191
    Mentioned
    71 Post(s)
    Tagged
    3 Thread(s)
    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.

  6. #6
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Is the general idea to reuse the functions in a number of places, or are they functions that are tied to a particular query?

  7. #7
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,191
    Mentioned
    71 Post(s)
    Tagged
    3 Thread(s)
    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 Code:
    <?php
    // Function to display the models, prices and check boxes.
    function display_prices$region ){

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

    // 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>\n
    <td align=\"right\">&pound;"
    .$row['price']."</td>\n
    <td><input type=\"text\" name=\"plans["
    .$row['ref_number']."]\" size=\"1\"  /></td>\n
    </tr>\n"
    ;

    }
    echo 
    "</table>\n";
    }

    // 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!

  8. #8
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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

    Code PHP:
    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.
    Code PHP:
     
    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.

    Code PHP:
    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.

    Code PHP:
    display_prices($PDO, $region);

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

    Code PHP:
     
    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.
    Last edited by Cups; Oct 9, 2013 at 02:35. Reason: highlight php

  9. #9
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,191
    Mentioned
    71 Post(s)
    Tagged
    3 Thread(s)
    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.

  10. #10
    SitePoint Member
    Join Date
    Jul 2013
    Location
    Brazil
    Posts
    9
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    you can change the type of return the associative array to object,

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

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

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

  11. #11
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,191
    Mentioned
    71 Post(s)
    Tagged
    3 Thread(s)
    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']

  12. #12
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •