phpmysqlmysqlimysqli-multi-query

Cannot figure out how to run a mysqli_multi_query and use the results from the last query


I've never used mysqli_multi_query before and it's boggling my brain, any examples I find on the net aren't helping me to figure out exactly what it is I want to do.

Here is my code:

<?php

    $link = mysqli_connect("server", "user", "pass", "db");

    if (mysqli_connect_errno()) {
        printf("Connect failed: %s\n", mysqli_connect_error());
        exit();
    }

    $agentsquery = "CREATE TEMPORARY TABLE LeaderBoard (
        `agent_name` varchar(20) NOT NULL,
        `job_number` int(5) NOT NULL,
        `job_value` decimal(3,1) NOT NULL,
        `points_value` decimal(8,2) NOT NULL
    );";
    $agentsquery .= "INSERT INTO LeaderBoard (`agent_name`, `job_number`, `job_value`, `points_value`) SELECT agent_name, job_number, job_value, points_value FROM jobs WHERE YEAR(booked_date) = $current_year && WEEKOFYEAR(booked_date) = $weeknum;";
    $agentsquery .= "INSERT INTO LeaderBoard (`agent_name`) SELECT DISTINCT agent_name FROM apps WHERE YEAR(booked_date) = $current_year && WEEKOFYEAR(booked_date) = $weeknum;";
    $agentsquery .= "SELECT agent_name, SUM(job_value), SUM(points_value) FROM leaderboard GROUP BY agent_name ORDER BY SUM(points_value) DESC";

    $i = 0;
    $agentsresult = mysqli_multi_query($link, $agentsquery);

    while ($row = mysqli_fetch_array($agentsresult)){
        $number_of_apps = getAgentAppsWeek($row['agent_name'],$weeknum,$current_year);
        $i++;
?>

            <tr class="tr<?php echo ($i & 1) ?>">
                <td style="font-weight: bold;"><?php echo $row['agent_name'] ?></td>
                <td><?php echo $row['SUM(job_value)'] ?></td>
                <td><?php echo $row['SUM(points_value)'] ?></td>
                <td><?php echo $number_of_apps; ?></td>
            </tr>

<?php

    }
?>

All I'm trying to do is run a multiple query and then use the final results from those 4 queries and put them into my tables.

the code above really doesn't work at all, I just get the following error:

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\hydroboard\hydro_reporting_2010.php on line 391

any help?


Solution

  • Okay after some fiddling around, trial and error and taking reference from another post that I came across in a Google search I've managed to solve my problem!

    Here's the new code:

    <?php
    
        $link = mysqli_connect("server", "user", "pass", "db");
    
        if (mysqli_connect_errno()) {
            printf("Connect failed: %s\n", mysqli_connect_error());
            exit();
        }
    
        $agentsquery = "CREATE TEMPORARY TABLE LeaderBoard (
            `agent_name` varchar(20) NOT NULL,
            `job_number` int(5) NOT NULL,
            `job_value` decimal(3,1) NOT NULL,
            `points_value` decimal(8,2) NOT NULL
        );";
        $agentsquery .= "INSERT INTO LeaderBoard (`agent_name`, `job_number`, `job_value`, `points_value`) SELECT agent_name, job_number, job_value, points_value FROM jobs WHERE YEAR(booked_date) = $current_year && WEEKOFYEAR(booked_date) = $weeknum;";
        $agentsquery .= "INSERT INTO LeaderBoard (`agent_name`) SELECT DISTINCT agent_name FROM apps WHERE YEAR(booked_date) = $current_year && WEEKOFYEAR(booked_date) = $weeknum;";
        $agentsquery .= "SELECT agent_name, SUM(job_value), SUM(points_value) FROM leaderboard GROUP BY agent_name ORDER BY SUM(points_value) DESC";
    
        mysqli_multi_query($link, $agentsquery) or die("MySQL Error: " . mysqli_error($link) . "<hr>\nQuery: $agentsquery");
        mysqli_next_result($link);
        mysqli_next_result($link);
        mysqli_next_result($link);
    
        if ($result = mysqli_store_result($link)) {
            $i = 0;
            while ($row = mysqli_fetch_array($result)){
                $number_of_apps = getAgentAppsWeek($row['agent_name'],$weeknum,$current_year);
                $i++;
    ?>
    
                <tr class="tr<?php echo ($i & 1) ?>">
                    <td style="font-weight: bold;"><?php echo $row['agent_name'] ?></td>
                    <td><?php echo $row['SUM(job_value)'] ?></td>
                    <td><?php echo $row['SUM(points_value)'] ?></td>
                    <td><?php echo $number_of_apps; ?></td>
                </tr>
    
    <?php
    
            }
        }
    ?>
    

    after sticking mysqli_next_result in there multiple times for each query it magically worked! yay! I understand why it works, because i'm telling it to skip to the next result 3 times, so it skips to the result for query #4 which is the one i want to use.

    Seems a bit clunky to me though, there should just be a command for something like mysqli_last_result($link) or something if you ask me...

    Thanks for the help rik and f00, I got there eventually :)