INSERT... ON DUPLICATE KEY and SELECTin same query

Question

I've this two query that i want combine. I already done some proof without success, evidently i go in error ordering the words in the right mode

$qryString="INSERT INTO votation (userID, voto, messageID) VALUES (:user, :voto, :mID) 
ON DUPLICATE KEY UPDATE voto = :voto"; 

$qryString="SELECT AVG(voto) AS average_valutation FROM votation WHERE messageID=:mID";

Hove to unify this two queries in one?

possible duplicate of [INSERT and SELECT in single query MySQL](http://stackoverflow.com/questions/9927097/insert-and-select-in-single-query-mysql) — echo_Me, Jul 24 '13 at 10:27
@Bart Friederichs: why not? I really need it. I need to insert the new vote in table and calculate the average valutation according to the new inserted element. What's strange? — user2590550, Jul 24 '13 at 17:20
@user2590550 just do them one after the other. No need to combine them. — Bart Friederichs, Jul 25 '13 at 06:48
ok, anyway i want to make less queries as possibile in my script. In this case seems that i've to do it separatly — user2590550, Jul 25 '13 at 08:36

echo_Me · Answer 1 · 2013-07-24T10:37:50.897

0

this didnt help ?

$qryString="INSERT INTO votation (userID, voto, messageID) VALUES (:user, :voto, :mID) 
            ON DUPLICATE KEY UPDATE voto = :voto ;"; 
                                                 ^----note this to separate from second query.

$qryString .="SELECT AVG(voto) AS average_valutation FROM votation WHERE messageID=:mID";
           ^---note this point.

edited Jul 24 '13 at 10:37

answered Jul 24 '13 at 10:28

echo_Me

37,078
5
58
78

done, but after queried i'm unable to fetch the result. Why ? – user2590550 Jul 25 '13 at 07:11

score 0 · Answer 2 · answered Jul 24 '13 at 10:34

Depending on the PHP mysql lib you are using, try this:

$results = array();
$qryString="
   INSERT INTO votation (userID, voto, messageID) VALUES (:user, :voto, :mID) 
   ON DUPLICATE KEY UPDATE voto = :voto;
   SELECT AVG(voto) AS average_valutation FROM votation WHERE messageID=:mID;
"; 
$mysqli->multi_query($qryString)
do {
    if ($res = $mysqli->store_result()) {
        $results[] = $res->fetch_all(MYSQLI_ASSOC);
        $res->free();
    }
} while ($mysqli->more_results() && $mysqli->next_result());

http://php.net/manual/de/mysqli.multi-query.php

Multi queries are not implemented in (deprecated) mysql_ functions. Im not sure about PDO.

I know of to fetch results (anyway i'm using PDO) The question is how write the query for do both operations in one, from the moment with variuous attempr failed for me! So the secret is separate the two operation with comma, i will try. thx — user2590550, Jul 24 '13 at 17:21

INSERT... ON DUPLICATE KEY and SELECTin same query

2 Answers2