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

    Join Date
    Aug 2016
    Posts
    79
    Rep Power
    2

    Adding Query Result to a variable


    My title indicates what I ‘think ’ I should be trying.

    However I am not sure if its the correct solution.

    Basically I have a query that returns 1 row. With two columns.

    Here is the query:
    Code:
    SELECT
    (SELECT 
    	COUNT(vacation_picks._date1)
    FROM schedule
    LEFT OUTER
    	JOIN vacation_picks ON schedule._date = vacation_picks._date1
    LEFT OUTER
    	JOIN employee ON employee.employee_id = vacation_picks.employee_id
    WHERE vacation_picks._date1 = '2017-10-06' )
     AS Total,
     (SELECT 
    	COUNT(vacation_picks._date1) as 'Officers'
    FROM schedule
    LEFT OUTER
    	JOIN vacation_picks ON schedule._date = vacation_picks._date1
    LEFT OUTER
    	JOIN employee ON employee.employee_id = vacation_picks.employee_id
    WHERE vacation_picks._date1 = '2017-10-06' AND employee.rank_id > 6)
      AS Officers
    What is the best way for me to use the result for ‘Officers’ and ‘Total’ in an if else statement.

    Something along the lines of :

    Code:
    If $officer is >= 3 then...
    Else if $total is >= 5 then....
    Else....
    Or am I going about it all wrong?

    Thanks for looking
  2. #2
  3. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,498
    Rep Power
    111
    Well, there is nothing wrong with the thought of your PHP, as long as $officer is to take precedence over $total. However, your SQL isn't right at all.

    Generally speaking, you are simply just running 2 queries. 1 asking for an "Officers" count, and the other asking for a "Total Employees" count.

    Your line for the "Total Employees" as well as the other, is making useless JOINs.
    Code:
    SELECT COUNT(vacation_picks._date1) FROM schedule LEFT OUTER JOIN vacation_picks ON schedule._date = vacation_picks._date1 LEFT OUTER JOIN employee ON employee.employee_id = vacation_picks.employee_id WHERE vacation_picks._date1 = '2017-10-06';
    The simplicity of what this line is doing is:
    Code:
    SELECT COUNT(_date1) FROM vacation_picks WHERE _date1 = '2017-10-06';
    If you do need to JOIN, an example format would be:
    Code:
    SELECT tableA.col1, tableA.col2, tableB.col1 FROM tableName AS tableA INNER JOIN anotherTable AS tableB ON tableA.col = tableB.col WHERE table?.column = value;
    EDIT: As a possibility, you can select all, then make the counts in your PHP. 1 will simply do the full count of your array for your total. The other will count via the set WHERE value, similar to the added 'AND employee.rank_id > 6' in your SQL. I suggest the 2 SQL calls, to help avoid waiting for a counting loop or such.

    EDIT2: If this helps...
    For your $total value:
    Code:
    SELECT COUNT(_date1)
    FROM vacation_picks
    WHERE _date1 = '2017-10-06';
    For your $officers value:
    Code:
    SELECT COUNT(a._date1)
    FROM vacation_picks AS a
      INNER JOIN employee AS b
      ON a.employee_id = b.employee_id
    WHERE a._date1 = '2017-10-06'
    AND b.rank_id > 6;
    Last edited by Triple_Nothing; October 4th, 2017 at 08:06 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. #3
  5. Wiser? Not exactly.
    Devshed God 2nd Plane (6000 - 6499 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    6,123
    Rep Power
    4103
    You can get both counts in a single query without using two sub-queries like you are currently.
    Code:
    SELECT 
        COUNT(vacation_picks._date1) as total
        , SUM(CASE WHEN employee.rank_id > 6 THEN 1 ELSE 0 END) as officers
    FROM schedule
    INNER OUTER JOIN vacation_picks ON schedule._date = vacation_picks._date1
    LEFT OUTER JOIN employee ON employee.employee_id = vacation_picks.employee_id
    WHERE 
        vacation_picks._date1 = '2017-10-06'
    Your first join should definitely be an inner join since your WHERE condition depends on the join being successful. Unless you want to pick up rows where there is no matching employee (if that's even possible) your second join should also be an INNER join. INNER join's usually allow the query to be processed more efficiently, so you should use them over other join types unless you have a reason not to.

    As far as the PHP code goes, once you run the query you just fetch the result row into an array and use that in your if statement.
    Code:
    $row = $stmt->fetch();
    if ($row['officers'] >= 3){...}
    else if ($row['total'] >= 5){ ... }
    else { ... }
    Recycle your old CD's



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  6. #4
  7. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,498
    Rep Power
    111
    @kicken: Nice mention of the CASE WHEN. I tried to think of putting it into a single query, but that didn't cross my mind.

    @SGC3: As far as your if/then/else goes, we should probably ask your intent. Such an item will only execute 1 section, the first true section. In your example, as long as your returned query returns 3 or more 'officers', it will never execute beyond that, so you won't ever get a 'totals' sum or such. Within these, what actions are you aiming on taking? Just setting variables?

    If setting variables, based on your intended outcome, a shorthand if/else may be of interest.
    Code:
    $officers = $row['officers'] >= 3 ? true : false; // Or 1 : 0
    $total = $row['total'] >= 5 ? 'Yes it is' : 'No it's not';

    Comments on this post

    • SGC3 agrees
    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
    79
    Rep Power
    2
    Thank you both for your detailed replies. Everything I know (not much) is due to the generous help of users on this forum. And lots of googling. To explain my terrible query, I basically had two queries that I thought would be more efficient as one. So I googled was to put them together, I am not suprised that its poorly done.

    @triple_nothing The purpose of my if else is to change the background colour of a calendar date based on who is off that day. When 5 of any staff is off its ‘closed’ and plan on turning the background red. When there are 3 ‘officers’ off no more ‘officers’ can be off that day. So I want to change the background yellow. Else = green.

    I have to repeat 94 dates for this query, so efficiency is what I am hoping to achieve. I will start playing with the code you guys have provided today, and see what I can understand/make of it.

    Thanks so much.

    #kicken

    Code:
    INNER OUTER JOIN vacation_picks ON schedule._date = vacation_picks._date1
    I assume that was meant to be ?
    Code:
    INNER JOIN vacation_picks ON schedule._date = vacation_picks._date1
    Thanks guys
    Last edited by SGC3; October 5th, 2017 at 02:46 PM.
  10. #6
  11. Wiser? Not exactly.
    Devshed God 2nd Plane (6000 - 6499 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    6,123
    Rep Power
    4103
    Originally Posted by SGC3
    #kicken

    I assume that was meant to be ?
    Yes.

    Running a query inside a loop is terrible for efficiency so it's best avoided if possible. So instead of running your count query for each day in a month for example, you should instead find a way to get all days and their counts in a single query. You can use GROUP BY to do that.

    Code:
    SELECT
        vacation_picks._date1 as vacationDay
        , COUNT(vacation_picks._date1) as total
        , SUM(CASE WHEN employee.rank_id > 6 THEN 1 ELSE 0 END) as officers
    FROM schedule
    INNER JOIN vacation_picks ON schedule._date = vacation_picks._date1
    LEFT JOIN employee ON employee.employee_id = vacation_picks.employee_id
    WHERE 
        vacation_picks._date1 BETWEEN '2017-10-01' AND '2017-10-31'
    GROUP BY   
        vacation_picks._date1

    That would give you a list of each day in the given range and the counts for that day. It'd only return a result for a day that exists in the database so if say nobody has requested 2017-10-05 then no row would be generated for that day.

    In your PHP code you can turn the results into an array that is indexed by day and use that to look-up the counts when displaying your table. If the index does not exist then treat it as a day with 0 for the counts.

    Comments on this post

    • SGC3 agrees
    Recycle your old CD's



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  12. #7
  13. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,498
    Rep Power
    111
    In relation to your if/then/else...

    You won't be able to check as you started. The first will simply check if $officer is greater than 3, and set the color to yellow. Now, even if the total is larger than 5, the color will remain yellow as long as $officer is equal to or greater than 3, since once your if() statement is true, it will continue no farther. In many cases, checking from largest to smallest may work, which I think may be an option in your case.

    Example:
    Code:
    if ($total >= 5) {
      $color = 'red';
    } elseif ($officer >= 3) {
      $color = 'yellow';
    } else {
      $color = 'green';
    }
    Last edited by Triple_Nothing; October 5th, 2017 at 04:45 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.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    79
    Rep Power
    2
    Originally Posted by kicken
    Yes.

    Code:
    SELECT
        vacation_picks._date1 as vacationDay
        , COUNT(vacation_picks._date1) as total
        , SUM(CASE WHEN employee.rank_id > 6 THEN 1 ELSE 0 END) as officers
    FROM schedule
    INNER JOIN vacation_picks ON schedule._date = vacation_picks._date1
    LEFT JOIN employee ON employee.employee_id = vacation_picks.employee_id
    WHERE 
        vacation_picks._date1 BETWEEN '2017-10-01' AND '2017-10-31'
    GROUP BY   
        vacation_picks._date1
    This is the greatest. Its going to save me HOURS and HOURS of time. If you dont mind another question?

    Would it work to be able to see all the dates, even if the result was null for the counts? If I understand correctly that would be a FULL join?

    @Triple_Nothing
    Thank you, I wondered after you explained in your previous reply if that would be the solution. Unfortunately I havent gotten to that part of it yet. Still working on getting the variables correct with my query results etc.

    You guys are awesome. When I did this for my work last year I created several individual queries changing dates on each, which ended up being 3 files of about 90 different queries, times four different shifts.....
  16. #9
  17. Wiser? Not exactly.
    Devshed God 2nd Plane (6000 - 6499 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    6,123
    Rep Power
    4103
    Originally Posted by SGC3
    Would it work to be able to see all the dates, even if the result was null for the counts? If I understand correctly that would be a FULL join?
    A FULL join is something different. A FULL join is like doing both a left and a right join together, in my experience there's usually not much of a use for full joins.

    You could create a result set including dates without any counts, but it complicates the query and is not worth while in my opinion. How exactly you do it depends on what database engine you're using as well. It's easier to just handle that case in your PHP code. For example:
    Code:
    function getDayCount($dayList, $date){
        $zeroCount = ['total' => 0, 'officers' => 0];
    
        return isset($dayList[$date])?$dayList[$date]:$zeroCount;
    }
    Call that function passing in your array of counts indexed by date and the date your interested in. If it exists you get the data, if not you get zeros.
    Recycle your old CD's



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  18. #10
  19. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,498
    Rep Power
    111
    When you say "null for the counts", you can still SELECT the DATES. The thing with the count is the fact that the count has really nothing to do with the actual data on the returned row. It is simply counting how many of such have been returned. So, if you did a SELECT date, COUNT(date) type query, the first item would hold the actual value from the date column, and the second value would simply be SQL's count of how many of such existed. In reference to the "even if the result was null for the counts" part, if the date existed for you to see in the first place, how would such a count not exist?
    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. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    79
    Rep Power
    2
    So if I am understanding correctly. Once I run the query I can put each row into an array. The array is numbered 1-90 (the max number of results). I can then call each row to display the count of people off on that day?

    I am getting in deeper than my understanding of all this. So I apologize if I am frustrating.

    I cant seem to find the syntax for getting say $result row[5] for the 5th day of work for example?
  22. #12
  23. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,498
    Rep Power
    111
    Well, the thing is we are offering an answer to the basic of a question without the true fullness in knowledge of the intent. Your first question may ask how to count, and then use an if/then, but as you define more and more how you wish to run this, our view, understanding, explanations, and suggestions may change.

    In relation to "rows", your fetch() command is most likely in a while() loop, and each iteration of this can act as you wish.
    I'm not sure how exactly you process your queries, but if like this, the while() loop will store your selected values in an array.
    Code:
    $stmt = $db->prepare('SELECT col1, col2 FROM table WHERE col = ?;');
    $stmt->bind_param('s', $var);
    $stmt->execute();
    $stmt->bind_result($col1, $col2);
    while($stmt->fetch()) {
      $rows[] = array($col1, $col2);
    }
    $stmt->close();
    bind_param() will define the question marks with the variables holding their values.
    bind_result() defines variables to use for each column or such being returned/SELECTed.
    The fetch() within the while() loop takes a step through the retrieved list, one row at a time, storing each rows values in the multi-dimensional array.

    You would then fetch the values of the 5th returned row like so:
    $rows[4][0] holding the first retrieved column/value
    and $rows[4][1] holding the 2nd column/value

    Keep in mind, the 5th will land as the 4th because, by default, many things do start at 0, not 1.
    Last edited by Triple_Nothing; October 5th, 2017 at 09:01 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. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    79
    Rep Power
    2
    I have been trying so many different things today trying to get an array to 'echo' the first success I have had was with this

    Code:
    $result = mysql_query("SELECT
    		 vacation_picks._date1 as vacationDay
        , COUNT(vacation_picks._date1) as total
        , SUM(CASE WHEN employee.rank_id > 6 THEN 1 ELSE 0 END) as officers
    FROM schedule
    INNER JOIN vacation_picks ON schedule._date = vacation_picks._date1
    LEFT JOIN employee ON employee.employee_id = vacation_picks.employee_id
    WHERE 
        vacation_picks._date1 BETWEEN '2017-01-01' AND '2017-12-31' AND employee.shift_id = '1'
    GROUP BY   
        vacation_picks._date1
    ");
    
    while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
        printf("Date: %s  Total: %s Officers: %s <br/><br/>", $row["vacationDay"], $row["total"], $row["officers"] );  
    }
    
    mysql_free_result($result);
    I decided to use MYSQL_ASSOC instead of _NUM because I like to see the column title instead of 0,1,2. It just seems easier for me to read.

    Now as far as being able to pull a specific row for use in my calendar.... I have tried $row[4]["column_title] and $row["column_title"][4] just as an experiment to get the specific row or date I will need. But neither will work. Obviously I am missing something. Or likely many things?

    I am very appreciative of all your help!
  26. #14
  27. Wiser? Not exactly.
    Devshed God 2nd Plane (6000 - 6499 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    6,123
    Rep Power
    4103
    Originally Posted by SGC3
    So if I am understanding correctly. Once I run the query I can put each row into an array. The array is numbered 1-90 (the max number of results). I can then call each row to display the count of people off on that day?
    My suggestion was to index your array by the date value in your query. Then your array becomes a lookup table where you check if the date exists and if so get the counts, otherwise get zeros. You could index it some other way if that works better. How you index your array just depends on how you want to use it.

    Code:
    <?php
    
    
    function getDateCounts(\PDO $db, \DateTime $start, \DateTime $end){
        $sql = '
        SELECT
            vacation_picks._date1 as vacationDay
            , COUNT(vacation_picks._date1) as total
            , SUM(CASE WHEN employee.rank_id > 6 THEN 1 ELSE 0 END) as officers
        FROM schedule
        INNER JOIN vacation_picks ON schedule._date = vacation_picks._date1
        LEFT JOIN employee ON employee.employee_id = vacation_picks.employee_id
        WHERE 
            vacation_picks._date1 BETWEEN :start AND :end
        GROUP BY   
            vacation_picks._date1
        ';
    
    
        $stmt = $db->prepare($sql);
        $stmt->bindValue(':start', $start->format('Y-m-d'));
        $stmt->bindValue(':end', $end->format('Y-m-d'));
        $stmt->execute();
    
    
        $result = [];
        foreach ($stmt as $row){
            $result[$row['vacationDay']] = $row;
        }
    
    
        return $result;
    }
    
    
    function getDayCount($dayList, $key){
        $zeroCount = ['total' => 0, 'officers' => 0];
    
    
        return isset($dayList[$key])?$dayList[$key]:$zeroCount;
    }
    
    
    
    
    $db = new \PDO('...', '...', '...');
    $start = new \DateTime('2017-10-01');
    $end = new \DateTime('2017-12-31');
    $interval = new \DateInterval('P1D');
    $lookup = getDateCounts($db, $start, $end);
    
    
    for (; $start < $end; $start->add($interval)){
        $date = $start->format('Y-m-d');
        $count = getDayCount($lookup, $date);
    
    
        echo 'On date '.$date.'; total='.$count['total'].'; officers='.$count['officers'].PHP_EOL;
    }
    On another note, you need to stop using the mysql_* functions and upgrade to something like PDO or MySQLi. I'd recommend PDO, it's simpler to use.

    The mysql_* functions are outdated, unsupported and have been removed from PHP entirely at this point.
    Last edited by kicken; October 6th, 2017 at 11:15 AM.
    Recycle your old CD's



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  28. #15
  29. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    79
    Rep Power
    2
    Again thanks!

    I am working through all of that to try to understand it the best I can.

    I have been trying to use mysqli but sometimes when I get working and trying many things (google) I end up with some outdated solutions. While working on the code you provided I obviously had to look into PDO, and so far I do like it better.

    What I did find is that when I run the code I get a list of dates in the range I set with $start and $end.

    But the count ‘total’ and count ‘officer’ are all returning as zero.
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo