Pull number of rows from a SQL query and put it in PHP as a variable?

Question

This is 4 queries put into one. This is really old code and once I can make this work we can update it later to PDO for security. What I am trying to do is count rows from

select count(*) from  dialogue_employees d_e, 
    dialogue_leaders d_l where
    d_l.leader_group_id = d_e.leader_group_id

and use it in a formula where I also count how many rows from dialogue.status = 1.

The formula is on the bottom to create a percentage total from the results. This is PHP and MySQL and I wasn't sure the best way to count the rows and put them as a variable in php to be used in the formula on the bottom?

function calculate_site_score($start_date, $end_date, $status){
    while($rows=mysql_fetch_array($sqls)){
        $query = "
        SELECT 
            dialogue.cycle_id, 
            $completecount = sum(dialogue.status) AS calculation, 
            $total_employees = count(dialogue_employees AND dialogue_leaders), dialogue_list.*, 
        FROM dialogue,
            (SELECT * FROM dialogue_list WHERE status =1) AS status, 
            dialogue_employees d_e, 
            u.fname, u.lname, d_e.*
            user u,
            dialogue_list,
            dialogue_leaders d_l 
                LEFT JOIN dialogue_list d_list 
                ON d_e.employee_id = d_list.employee_id,
        WHERE 
            d_l.leader_group_id = d_e.leader_group_id 
            AND d_l.cycle_id = dialogue.cycle_id
            AND u.userID = d_e.employee_id 
            AND dialogue_list.employee_id
            AND site_id='$_SESSION[siteID]' 
            AND  start_date >= '$start_date' 
            AND start_date <= '$end_date'";
        $sqls=mysql_query($query) or die(mysql_error());
    }
    $sitescore=($completecount/$total_employees)*100;
    return round($sitescore,2);
}

do you have an example of what you are trying to do? or is that your example? im not sure if you want to add to this code for another col `status` or if you want to change your math formula to incorporate a row count for e.g. can you point if your `$sitescore` line is an example, or the part your keeping. — blamb, Apr 26 '16 at 22:38
Just combining 4 tables together and counting the rows of 2 results. The $completecount and $total_employees are the 2 things I am trying to get results for. The query works, but I don't have the syntax right for how to count it and make it the 2 variables. — , Apr 26 '16 at 22:40
Ok, i see, the query is trying to set them to var, ok what you need to do, is run the query, to get all the data you need first, set that to a var, then after you have your data, assuming you joined it, you will have all your data, then you parse it. so you dont set the vars inside the query like that. I can give you an example once i understand more of what you need. — blamb, Apr 26 '16 at 22:45
I guess the best way to ask this is how do you count rows in an SQL query and then turn that result into a PHP variable(Integer)? — , Apr 26 '16 at 22:47
I know how to pull a column, and a result such as $cycle_id = $rows[cycle_id]; But to do this and add a sum of rows is where I am getting stuck. — , Apr 26 '16 at 22:49
you can run a count on your query http://stackoverflow.com/questions/10492164/how-do-i-count-columns-of-a-table . However, if your iterating, you can run a counter in that loop as well. So in the while loop, you can use the index counter as your counter. But you only want one query, if it has to be a join, that's find. Then you run that one query, with those results, they are an object, and you parse that object, i think that should be enough,if you needed to though, in your case, inside that parsing(iterating over) that object you can fire another query from within if needed. — blamb, Apr 26 '16 at 22:53
your putting too much into one function. put all each db queries into each their own function, separate this from your code that does the calculation function. inside that function, call your db functions, which return results from each db query. Then you can make better sense of whats going on there. and use `mysqli` instead of `mysql`. — blamb, Apr 26 '16 at 23:02
http://stackoverflow.com/questions/12789396/how-to-get-multiple-counts-with-one-sql-query This I think was part of my problem. Being able to use count twice and where I should probably use subqueries for both of them. — , Apr 26 '16 at 23:04

score 0 · Answer 1 · edited May 23 '17 at 12:33

If you separate out your queries you will gain more control over your data. You have to be careful what your counting. It's pretty crowded in there.

If you just wanted to clean up your function you can stack your queries like this so they make more sense, that function is very crowded.

function calculate_site_score($start_date, $end_date, $status){

    $query="select * from dialogue;";

    if ($result = $mysqli->query($query))) {

        //iterate your result
        $neededElem = $result['elem'];
        $query="select * from dialogue_list where status =1 and otherElem = " . $neededElem . ";";

        //give it a name other than $sqls, something that makes sense. 
        $list = $mysqli->query($query);

        //iterate list, and parse results for what you need
        foreach($list as $k => $v){
            //go a level deeper, or calculate, rinse and repeat
        }
    }

Then do your counts separately.

So it would help if you separate queries each on their own.

Here is a count example How do I count columns of a table

Pull number of rows from a SQL query and put it in PHP as a variable?

1 Answers1