MySQL: mysqli_fetch_array()

Question

How to use mysqli_fetch_array() for these 3 queries in the same table? or how to make this 3 query in to 1 query?

SELECT clinic,AVG(rating) FROM review GROUP BY clinic ORDER BY clinic
SELECT clinic,COUNT(rating) AS die FROM review WHERE rating = 1 GROUP BY clinic
SELECT clinic,COUNT(rating) AS normal FROM review WHERE rating = 7 GROUP BY clinic

score 1 · Answer 1 · answered Dec 22 '17 at 23:31

1

You could use case expressions to move the condition in the last two queries to expressions that are counted. count skips nulls, so just use an expression that only returns something for the condition you want to count:

SELECT   clinic, 
         AVG(rating),
         COUNT(CASE rating WHEN 1 THEN 1 END) AS die,
         COUNT(CASE rating WHEN 7 THEN 1 END) AS normal
FROM     review
GROUP BY clinic 
ORDER BY clinic

answered Dec 22 '17 at 23:31

Mureinik

277,661
50
283
320

Wow Thank a lot ^.^ I searching in google and youtube for 5 hour but cannot find how. – Zizero Kub Dec 22 '17 at 23:43

score 0 · Answer 2 · answered Dec 22 '17 at 23:33

You can use mysqli::multi_query. As an example,

$query  = "SELECT CURRENT_USER();"; # Example Query
$query .= "SELECT Name FROM City ORDER BY ID LIMIT 20, 5"; # Second Example Query

/* execute multi query */
if ($mysqli->multi_query($query)) {
    do {
        /* store first result set */
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_row()) {
                printf("%s\n", $row[0]);
            }
            $result->free();
        }
        /* print divider */
        if ($mysqli->more_results()) {
            printf("-----------------\n");
        }
    } while ($mysqli->next_result());
}

/* close connection */
$mysqli->close();

Since StackOverflow is not a code writing service, I am not going to write how this is going to happen in your case. Try to reflect it to your code, when you face problems, post a question.

Note: Example is from the link provided of the whole documentation of mysqli::multi_query.

Good Luck!

IMHO, multi-query is almost never the right solution. There's little gained from it over just calling `mysqli_query()` separately for each query, and it just makes fetching the results more complicated. — Barmar, Dec 23 '17 at 00:44

MySQL: mysqli_fetch_array()

2 Answers2