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?
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 :)