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

    Join Date
    Aug 2016
    Posts
    70
    Rep Power
    2
    Thanks for your reply. Obviously I am having trouble understanding the purpose of the code.

    I have made a couple of changes and will comment on what I am thinking it does - still not working as of now.

    First section
    Sets the timezone, and creates a connection to the database called $db
    PHP Code:
     date_default_timezone_set('America/Toronto'); 
    $db = new \PDO('mysql:host=localhost;dbname=dbname''username''pwrd'); 
    Next section
    The function called getName with reference to connection created above, and DateTime parameter called $day.
    Creates $stmt from $db and my select that is name $sql, binds my variable value named :day to the $sql.
    Executes the Select statement with the bound parameter.
    I am pretty lost as to what the $result, and foreach and $row all do. I am basically guessing with different combinations trying to find something that works.
    Last line is creating an array called $result....maybe?

    PHP Code:
    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'$day->format('Y-m-d'));
        
    $stmt->execute();

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

        return 
    $result;

    Last section. This code will be changed based on the date that I want results for. Then those results need to be given a unique name that I can call on another page. ie $d1name, d2name etc...
    obviously I need more than just that one line.

    PHP Code:
    $day = new \DateTime('2018-1-3'); 
    Really unsure of what to do here also
    Last edited by SGC3; October 14th, 2017 at 03:55 PM.
  2. #17
  3. 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 gets you another step...
    PHP Code:
    <?php
      
    function getName(\PDO $db, \DateTime $day) {
        
    $sql 'SELECT 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'$day->format('Y-m-d'));
        
    $stmt->execute();
        return 
    $stmt->fetchAll();
      }
      
    $name getName();
      
    var_dump($name); // This should give you an idea of the returned values
    ?>
    Edit: In relation to the multiple dates... Are they to be totally random dates, or a single range?
    Last edited by Triple_Nothing; October 14th, 2017 at 03:47 PM.
    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.
  4. #18
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    70
    Rep Power
    2
    The dates are pretty random. Are you thinking of making a :start and :end?

    I think just the one date at a time is best for my situation.

    Running the code now I am getting

    Code:
    Catchable fatal error: Argument 1 passed to getName() must be an instance of PDO, none given, called in - on line 27 and defined in - on line 17
  6. #19
  7. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,494
    Rep Power
    111
    Were you running JUST my script? Or were you adding that into yours?

    Is $db still defined in your PDO namespace?

    Edit: With random individual dates, you could append OR's onto your $sql as needed. Or maybe loop an array.

    Edit2: Are you intending on working with namespaces on this? Or is that just the methods your came across as you looked into this?
    Last edited by Triple_Nothing; October 14th, 2017 at 05:34 PM.
    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. #20
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    70
    Rep Power
    2
    Originally Posted by Triple_Nothing
    Were you running JUST my script? Or were you adding that into yours?

    Is $db still defined in your PDO namespace?

    Edit: With random individual dates, you could append OR's onto your $sql as needed. Or maybe loop an array.

    Edit2: Are you intending on working with namespaces on this? Or is that just the methods your came across as you looked into this?
    No I added it to my script, $db is still defined.

    I am not familiar with Namespace. That is likely there because I have come across it as I looked into it.

    I am needing to do this for every day of the year. But I will be displaying it on four different pages with approx 90 dates each.
  10. #21
  11. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,494
    Rep Power
    111
    Well, if aiming for the year, you can define a range, or the YEAR(), and then define a LIMIT to return the 90 that you're aiming to LIMIT or max out at.
    If you want to give it a try, you can remove the namespaces and try the below. Namespaces do help group things, but just don't seem your goal/intent atm.
    You can hand $db in during your function call, or even reference it within via global $db;
    PHP Code:
    <?php
      
    function getName($db$day) {
        
    $sql 'SELECT 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'$day->format('Y-m-d'));
        
    $stmt->execute();
        return 
    $stmt->fetchAll();
      }
      
    $name getName($db$day);
      
    var_dump($name); // This should give you an idea of the returned values
    ?>
    And remove the backslashes from the lines:
    PHP Code:
    $db = new \PDO('... 
    and
    PHP Code:
    $day = new \DateTime('... 
    Edit: This is a shortened version of your overall, and the SELECT is an everything, so just as a start, you can use that, and narrow it down from there, if that helps...
    PHP Code:
    <?php
      $dsn 
    'mysql:dbname=triple_nothing_bills;host=127.0.0.1';
      
    $user 'triple_nothing';
      
    $pass 'MyPw123';
      try {
        
    $dbh = new PDO($dsn$user$pass, array(
            
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC // Original default FETCH_BOTH
        
    ));
      } catch(
    PDOException $e) {
        echo 
    'Connection failed: ' $e->getMessage();
      }
    ?>
    <?php
      
    function getName($dbh) {
        
    $sql 'SELECT * FROM account LIMIT 5';
        
    $sth $dbh->prepare($sql);
        
    $sth->execute();
        return 
    $sth->fetchAll();
      }
      
    $name getName($dbh);
    ?>
    <PRE>
    <?php
      var_dump
    ($name); // This should give you an idea of the returned values
    ?>
    </PRE>
    Last edited by Triple_Nothing; October 15th, 2017 at 12:05 PM.
    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.
  12. #22
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    70
    Rep Power
    2
    That worked using the last code you shared. Thank you

    I managed to get almost what I need....I think.

    adding to what you gave me:

    PHP Code:
      function getName($dbh) { 
        
    $sql '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 = "2017-10-5"
    ORDER by vacation_picks._date1'
    ;
        
    $sth $dbh->prepare($sql);
        
    $sth->execute(); 
        return 
    $sth->fetchAll(); 
      } 
      
    $name getName($dbh);  


    //Calling the result
    foreach($name as $row) {
        echo 
    $row['Name'], "<br>";

    Now I just need to get the bindvalue or parameter figured out. Is that the right approach?
    Last edited by SGC3; October 14th, 2017 at 11:49 PM.
  14. #23
  15. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,494
    Rep Power
    111
    I think the best way to word it may be simply by saying how/when.
    PHP Code:
    $sex 'male';
    $s $dbh->prepare('SELECT name FROM students WHERE sex = :sex');
    $s->bindParam(':sex'$sex); // use bindParam to bind the variable
    $sex 'female';
    $s->execute(); // executed with WHERE sex = 'female' 
    PHP Code:
    $sex 'male';
    $s $dbh->prepare('SELECT name FROM students WHERE sex = :sex');
    $s->bindValue(':sex'$sex); // use bindValue to bind the variable's value
    $sex 'female';
    $s->execute(); // executed with WHERE sex = 'male' 
    When using bindParam, you are binding the variable to that item, and not its value, so if the value changes prior to execute, the final value is what will be used. If using bindValue, you are directly binding the actual value at that moment.

    bindParam is an excellent option when creating loops and running your query multiple times to avoid redefining ->prepare() and ->bindValue's

    Edit: In relation to the foreach(), as long as you've an associative column bound called Name, you can do that with a slight alteration on your echo line...
    PHP Code:
    // Calling the result
    foreach($name as $row) {
      echo 
    $row['Name'] . "<BR />\n";

    The period attaches the 2 parts. The forward slash after BR is not required, but I just open/close my tags that don't have independent closing tags. The backslash n creates a newline in the output code to help keep that clean.
    Last edited by Triple_Nothing; October 15th, 2017 at 09:14 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.
  16. #24
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    70
    Rep Power
    2
    Thanks for the detailed reply. So bindParam is a little bit more flexible? I am probably wanting the one that will allow me to set the variable after the function is executed?

    As far as the foreach I am using fetchAll witch is both ASSOC and NUM by default? Otherwise I would have to do fetchALL(PDO::FETCH_ASSOC) is that needed or just use the default fetchALL.

    Right now I have it working with a static date, fetching the correct results. I can change the foreach by renaming it :
    PHP Code:
    // Calling the result 
    foreach($name as $day1) { 
      echo 
    $day1['Name'] . "<BR />\n"

    Do I just paste that into the page I want to use it on? With the other code added with require_once.

    Thanks again. Hope you are having a solid weekend.
  18. #25
  19. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,494
    Rep Power
    111
    With bindParam you can adjust the date from an array or so, and build a loop which would only hold your ->execute(); and ->fetch() items. bindValue is best to make more individual, one-time calls.

    Well, if you are only requesting 1 thing at a time, a foreach() won't even be needed. In a visual manner, foreach() would be a way of running multiple echo's, 1 for each row returned. If only 1 row is returned in the first place, there is nothing to loop through and echo 1 at a time.

    The fetch will build your array in both an indexed an associative manner. If you wish for just 1, such as an associative manner, you don't have to define it each time. You can simply adjust the default in your connection area like so:
    PHP Code:
    try {
      
    $dbh = new PDO($dsn$user$pass, array(
          
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC // Original default FETCH_BOTH
      
    ));
    } catch(
    PDOException $e) {
      echo 
    'Connection failed: ' $e->getMessage();

    Last edited by Triple_Nothing; October 15th, 2017 at 06:34 PM.
    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.
  20. #26
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    70
    Rep Power
    2
    What I have to do exactly is this.

    For every day in 2018 I need to show who is off on a given day. It could be anywhere from 0 to 5 people. So I will need the loop. I was hoping for a clean way to display it on the end user page calendar.php, with the function on a separate page function.php

    I am bad at explaining how this works. Hopefully it makes sense
  22. #27
  23. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,494
    Rep Power
    111
    Well, the idea actually sounds very simple. You'd basicly define an array to hold the returned individuals for that day, if any. And that foreach() that you've been aiming for would just add each person to your list. The return from your function will return the values from your SQL query as an array assigned to a variable of your choice. Now, if you were to manually build that list if 3 people were returned, how would its HTML look?
    If as an HTML Unordered List, you would open/close your <UL> tags within the HTML, and echo a $list item that you've built in a way similar to below.
    PHP Code:
    <?php
      $list 
    '<LI>Date: ' $_POST['Date'] . "</LI>\n"// Where ever you have the date submitted stored.
      
    foreach($result as $row) {
        
    $list .= '<LI>Name: ' $row['Name'] . "</LI>\n";
      }
    ?>
    <UL>
    <?= $list?>
    </UL>
    Since you are only aiming for 1 already defined date at a time, there is not reason to SELECT the date in this matter, unless your goal is for MySQL to make a count for you on those days, but PHP can do such as well.
    Last edited by Triple_Nothing; October 15th, 2017 at 08:47 PM.
    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.
  24. #28
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    70
    Rep Power
    2
    The data is all being filled into a calendar that displays all 12 months on one page. Clicking a date will create a toast with the names of the people who are off on that day.

    Sorry but I am unclear on what to do with your posted solution?

    The display is very basic, I hadnt planned on much html formating. So a list is fine.
  26. #29
  27. No Profile Picture
    Contributing User
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jul 2003
    Posts
    4,350
    Rep Power
    630
    Explain how you want the names displayed when you click the date. A new page? Perhaps you want something like this.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  28. #30
  29. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    70
    Rep Power
    2
    Just a simple materializecss toast pop up.

    The purpose of this site is for my works vacation picking process. We are only allowed to have 5 people off at a time. So this will be for people to see if a day is available or not. I received help here in setting up an if/else to change the color of a date based on how many are off. But there are circumstances where people will want to see the names of who is off. Not just if its available or not.

    In the code I am posting is just over a calendar week worth of code. And I think its pretty cumbersome already. This is why I am trying to keep as much of the php in other organized pages as possible (although I guess this is common)

    Here is the mess I am looking at right now.

    PHP Code:
    <li><a<?php echo $holiday ;?>1</a>
    <li>
      <a class="
    <?php
    if ($d1total >= ) {
        echo 
    $red;
    }  elseif (
    $d1officer == ) {
        echo 
    $yellow;        
    }  else {
        echo 
    $green;    
    }
    PHP_EOL
    ?>
    " onclick="Materialize.toast('Block 1 : Jan 2 2018<br><?php
    //     Code to populate names in the toast popup
    $day = ('2017-1-2');

    $sth->execute(array(
        
    ':day' => $day
    ));
     
    foreach(
    $sth as $row) { 
         echo 
    $row['Name'], "<br>";
        } 
    ?>',
        'rounded')">2</a>
    </li>
      <li>3</li>
      <li>4</li>
      <li>5</li>
      <li>6</li>
      <li>7</li>
      <li><a class="<?php
    if ($d2total >= ) {
        echo 
    $red;
    }  elseif (
    $d2officer == ) {
        echo 
    $yellow;        
    }  else {
        echo 
    $green;    
    }
    PHP_EOL
    ?>
    " onclick="Materialize.toast('Block 2 : Jan 8 2018<br><?php
    //     Code to populate names in the toast popup
    $day = ('2017-1-8');

    $sth->execute(array(
        
    ':day' => $day
    ));
     
    foreach(
    $sth as $row) { 
         echo 
    $row['Name'], "<br>";
        } 
    ?> ', 'rounded')">8</a>
    </li>
    Its working as I need. (ignore the weird dates - just thrown in for testing) But I was hoping to slim this down to reduce errors on my end.

IMN logo majestic logo threadwatch logo seochat tools logo