using mysqli_multi_query to extract some data and re-enter it in a different table

Question

I am trying to rewrite a piece of code that used several mysql_query calls to use one mysqli_... but cannot get it to work. If I simply replace my mysql_query statements with mysqli_query statements in the same place I get "Commands out of sync; you can't run this command now"

If I instead try to pull all the queries into a mysqli_multi_query I get a "Notice: Undefined variable: activeUser in /home/almostes/public_html/addrUpdate-proc-multi.php on line 14" error.

In a nutshell this is what I had

// get list of all active users

SELECT entity_id FROM customer_entity_int WHERE attribute_id = 153 and value = 1 ORDER BY entity_id

// cycle through this list of entity_ids checking is there an address stored against this user

SELECT * FROM customer_address_entity WHERE parent_id = $entity_id;

// if no address stored against this user get the info stored in customer_entity_varchar

SELECT * FROM customer_entity_varchar WHERE entity_id = $entity_id;

// and then enter this into the appropriate address tables

INSERT INTO customer_address_entity VALUES (blah, blah, blah);

I think I need to replace this with something like

$query = "SELECT entity_id FROM customer_entity_int WHERE attribute_id = 153 and value = 1 ORDER BY entity_id;";    # get list of all active users#
$query .= "SELECT * FROM customer_address_entity WHERE parent_id = $entity_id;";    # is there an address stored against this name
$query .= "SELECT * FROM customer_entity_varchar WHERE entity_id = $entity_id;";    # get the info stored in customer_entity_varchar
$query .= "INSERT INTO customer_address_entity VALUES (blah, blah, blah);"

$result = mysqli_multi_query($mysqli, $query);

/* execute multi query */
if ($result) {
    do {
        /* store first result set */
        if ($result = mysqli_store_result($mysqli)) {
            while ($row = mysqli_fetch_assoc($result)) {
                $entity_id = $row['entity_id'];
        echo "<br />active user = $entity_id<br />";

        }
        mysqli_free_result($result);
    }
    /* print divider */
    if (mysqli_more_results($mysqli)) {
        printf("-----------------\n");
    }
      if (($result = mysqli_num_rows($mysqli)) < 1) // if no address stored against this user retrieve the registration data and enter into address data fields
      {
            echo '<br />yes<br />';
      }
    } while(mysqli_more_results($mysqli) && mysqli_next_result($mysqli));
}

But a) this gives a "Notice: Undefined variable: activeUser in /home/almostes/public_html/addrUpdate-proc-multi.php on line 14" error where line 14 is "$query .= "SELECT * FROM customer_address_entity WHERE parent_id = $entity_id;";"

and then gives the echoed output

"active user = 5

active user = 6

active user = 78"

and b) I cannot see the syntax to move through the different sql queries to get the users without address info and the retrieve the desired fields

Any help would be much appreciated. Many thanks

I would probably take the time to combine the three select statements. Perhaps query1 LEFT JOIN ( (query2) UNION (query3) LIMIT1) WHERE address IS NOT NULL ...or something like that. That would be one query with potentially many rows returned and all would have addresses. Then run just one query which would INSERT the whole batch of users and their addresses. This would be a light weight approach. — mickmackusa, Nov 15 '16 at 14:37

score 0 · Answer 1 · answered Apr 02 '14 at 04:50

0

Obviously, you can't use mysqli-multi-query this way. And, honestly, you don't need it too.

So, just run your queries one by one, usual way. And say the Saint Occam's prayer: "entia non sunt multiplicanda praeter necessitatem" which is for "entities must not be multiplied beyond necessity".

Next time, when you face an error in your code, ask a question regarding this very error, instead of erecting more complex and error-prone construction.

answered Apr 02 '14 at 04:50

Your Common Sense

156,878
40
214
345

really sorry about that, thought I was on the right track, I have reposted my question here [link](http://stackoverflow.com/questions/22804069/replace-mysql-query-with-mysqli-query-and-get-commands-out-of-sync-error) – Lewis Kite Apr 02 '14 at 07:15

using mysqli_multi_query to extract some data and re-enter it in a different table

1 Answers1