Page 1 of 3 123 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    69
    Rep Power
    2

    Trying to get a variable from PDO


    First I am unsure if Variable is the correct term.

    I am just learning about PDO and trying to update my queries to use it.

    My goal is to be able to adjust the query with a different date multiple times in the same page. Return the result into a variable? to call on another page.

    Here is as far as I have gotten:

    PHP Code:
     date_default_timezone_set('America/Toronto');
    $db = new \PDO('mysql:host=localhost;dbname=mydbname', 'user', 'pass'); 
    ?>

    <?php
        
    function getDateCounts(\PDO $db, \DateTime $start){
    (
    "SELECT (employee.name)Name
    FROM employee
    LEFT OUTER JOIN
    vacation_picks on
    employee.employee_id = vacation_picks.employee_id
    WHERE vacation_picks._date1 = :start"
    );

    $stmt $db->prepare($sql);
    $stmt->bindValue(':start'$start->format('Y-m-d'));
    $stmt->execute();
    }

    return 
    $result;
    I am unclear on how to go past here to get what I am looking for. I am not ever sure what term to search. Or is what I have already done poorly done?

    Thanks in advance
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jul 2003
    Posts
    4,342
    Rep Power
    630
    After the execute you need to do some kind of fetch depending on the objective.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2016
    Location
    Lakewood, WA
    Posts
    207
    Rep Power
    17
  6. #4
  7. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,494
    Rep Power
    111
    Hope this helps a bit...
    PHP Code:
    <?php
      
    function getDateCounts(\PDO $db, \DateTime $start) {
        (
    "SELECT ..."); // Missing binding variable?
        
    $stmt $db->prepare($sql); // Assuming SELECT was to be bound to $sql
        
    $stmt->bindValue(':start'$start->format('Y-m-d'));
        
    $stmt->execute();
        
    // $stmt->fetch() needed here in a way to bind results to $result
        
    $stmt NULL// If you wish to clear your call to avoid hanging your connection
      
    }
    return 
    $result// Belongs within your function's curly bracket
    ?>
    Last edited by Triple_Nothing; October 14th, 2017 at 08:46 AM.
    He who knows not that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    100
    Rep Power
    50
    My goal is to be able to adjust the query with a different date multiple times in the same page.
    Your goal should be to run the fewest number of queries on any particular page. If you are getting data to populate a calendar, for example, you should run a single query that gets all the data needed to display the date range of that calendar. For simple queries, which is what you are showing, the time needed for the communications between php and the database server, for both the sql query statement (which is always a string) and any bound input values (which are usually strings), is several times longer than the actual time it takes to either prepare/plan or execute the query.

    For properly indexed tables/queries, after making sure you are using a single database connection on the page (a connection is the most time consuming database operation and too many people try to make a new connection every time they run a query), reducing the number of communications between php and the database server is the one thing that you can do in your code that will have a measurable impact.

    Your PDO connection should set the character encoding being used, set the error mode to exceptions, disable emulated prepared queries, and to save typing, set the default fetch mode to assoc, so that you don't have to specify it in every fetch statement.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    69
    Rep Power
    2
    @DSmabismad

    Thank you, yes that is what I am trying. But this is new to me so I am trying to do it in 'pieces' I guess? Starting with getting something to work, then expanding it from there.

    Here is where I am at now, thanks to all your suggestions and reading.

    PHP Code:
    <!-- Creates PDO Connection and $db variable -->
    <?php date_default_timezone_set('America/Toronto');
    $host 'localhost';
    $db   'dbname';
    $user 'user';
    $pass 'pass';
    $charset 'utf8';

    $dsn "mysql:host=$host;dbname=$db;charset=$charset";
    $opt = [
        
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        
    PDO::ATTR_EMULATE_PREPARES   => false,
    ];
    $db = new PDO($dsn$user$pass$opt);

    $sth $db->prepare("
        SELECT (employee.name)Name
        , vacation_picks._date1 as Date
    FROM employee
    LEFT OUTER JOIN
    vacation_picks on
    employee.employee_id = vacation_picks.employee_id
    WHERE vacation_picks._date1 = :day
    "
    );

    $myVariable = ('2017-10-1');

    // This executes the SQL query, binding the parameter as we go.
    $sth->execute(array(
        
    ':day' => $myVariable
    ));

    // find out the number of rows.
     
    $sth->rowCount();

    // Return name results
    while($row $sth->fetch(\PDO::FETCH_ASSOC)) {
        echo (
    $row['Name']);
     
    }
    ?>
    So I managed to get the (on the example date) 5 names that are off to echo. But adding it to a variable that I can use. Say like $d1names = $row['Name'] only returns the last row when I echo $d1names.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jul 2003
    Posts
    4,342
    Rep Power
    630
    row is an array. It is already a variable you can use. If you want all the rows in an array use fetchall instead of using a loop on each row.

    Comments on this post

    • SGC3 agrees : Thanks!
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    69
    Rep Power
    2
    Originally Posted by gw1500se
    row is an array. It is already a variable you can use. If you want all the rows in an array use fetchall instead of using a loop on each row.
    Wow, thanks. I was under the idea that I had to rename each variable or it would fail with multiples of the same name. I just tested and was able to get another day without changing the variable name like you said.

    Cheers
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    69
    Rep Power
    2
    Another question if you have a minute?

    As I have it now, I am pasting in this code:
    PHP Code:
    <?php
    $myVariable 
    = ('2017-10-2');

    // This executes the SQL query, binding the parameter as we go.
    $sth->execute(array(
        
    ':day' => $myVariable
    ));

    // find out the number of rows.
     
    $sth->rowCount();

    // Return name results
    while($row $sth->fetch(\PDO::FETCH_ASSOC)) {
        echo (
    $row['Name']. "<br>");
    }
    ?>
    It will clutter up the calendar.php page pretty good. The DB connection and select statement are called from another page include.php. Is there a way that I can keep all of that on the include page and just call it with a function or class? or something else?
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jul 2003
    Posts
    4,342
    Rep Power
    630
    Yes. Move that code to a function in the include or a method if you are using OOP in that class.

    P.S. You might want to use require_once or 'require' rather than 'include'.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    69
    Rep Power
    2
    I am having no luck getting this into a function. OOP is "Object Oriented....?"

    I am using required_once as you suggested also.

    But its turning the above code into a function that is really tweaking me. I have no idea where to go with it. Just been randomly googling examples and trying different things. But not getting anywhere close. Any suggestions would really be appreciated.
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jul 2003
    Posts
    4,342
    Rep Power
    630
    Post what you have tried and we can go from there.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  24. #13
  25. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,494
    Rep Power
    111
    Only a guess, but I assuming/hoping you have this code in 3 parts. If not, feel free to post everything you got to give us a clear idea of your current usage/understanding.

    1.) Most people will have a dedicated file for the database connection that they can simply include()/require() into anything they wish. This should hold the creation of your database handler.
    2.) As you defined at first, it appears your goal is to make a function to use. That is usually in a section among other defined functions.
    3.) The lines within the script making the call to the function with defined values and use of the returned values.
    He who knows not that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    69
    Rep Power
    2
    Here is what I have so far. Its a mashup of examples, my limited knowledge and another working file I have.

    PHP Code:
    <?php date_default_timezone_set('America/Toronto');
    $db = new \PDO('mysql:host=localhost;dbname=dbname''user''pass'); 
    ?>
    <?php
    function getName(\PDO $db, \DateTime $day){
        
    $sql 'employee.name as Name
        , vacation_picks._date1 as Date
    FROM employee
    LEFT OUTER JOIN
    vacation_picks on
    employee.employee_id = vacation_picks.employee_id
    WHERE vacation_picks._date1 = :day'
    ;

        
    $stmt $db->prepare($sql);
        
    $stmt->bindValue(':day'$start->format('Y-m-d'));
        
    $stmt->execute();

        
    $result = [];
        foreach (
    $stmt as $row){
            
    $result[$row['Name']] = $row;
        }

        return 
    $result;
    }
    ?>
    <?php 
        
    $day 
    = new \DateTime('2018-1-3');


        
    $date $day->format('2017-10-5');
        
    $d1names getName$date);

    PHP_EOL?>
    <?php 
    echo $d1names['Name'];?>
    I am testing all on the same page for now. But the final product I planned on having two sections. The connection, and the functions. And the call of the functions on the end users page.

    Thanks guys
  28. #15
  29. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,494
    Rep Power
    111
    Where are you using $day in your function?
    Where is $start defined?
    $date = $day->format('2017-10-5'); is invalid...
    And if it is to process as I am thinking it may, there is no reason, in your example, to format the same way twice...
    You pass $day to the function via namespace and can omit the $date = $day->format('2017-10-5'); line.
    Your call you the function is passing the variable $date, which isn't needed, so can be removed as well.

    Within your foreach(), you are defining: $result[$row['Name']] = $row;
    Then your echo is calling: $d1names['Name']
    Now, in order for this to work, the value of $row['Name'] has to actually hold the value of 'Name'
    Last edited by Triple_Nothing; October 14th, 2017 at 09:19 AM.
    He who knows not that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.
Page 1 of 3 123 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo