having trouble with getting rows back from msqli::multi_query

Question

I've got an sql query that contains a number of statements. It:

1. sets a user variable
2. calls a stored procedure
3. calls another stored procedure
4. selects some data

I know that the query is correct, because I've tested it in MySQL Workbench, under the same user. The query is this:

set @current_post = 535; /* 535 for testing, this will be a variable */

call under_user_type(@currrent_post, @user_type, @user_id);
call get_category(@current_post, @category);

select p.post_title, p.post_name,
(
    swell_wp.post_score(p.ID)
) as score,

(
    swell_wp.is_under_user(p.ID, @user_type, @user_id)
) as under_user,

(
    swell_wp.is_under_category(p.ID, @category)
) as under_category

    from wp_posts as p
    where p.post_type = 'post'
    and p.id != @current_post
    and p.post_status = 'publish'
    having (
        under_user = true
        or under_category = true
    )
    order by score desc;

that's just stored in a string: $sql. I then do this with it in PHP:

$query = new MySQLi(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
$query->multi_query($sql);

do {
    $query->next_result();
} while( ! $result = $query->use_result() );

print_r($result);

this prints a result object, but one that is empty. Trying to iterate over it doesn't produce any results either.

What am I doing wrong? Can I even use user variables like this? Or will I need to turn the procedures into stored functions and do three separate queries?

Answer 1

You are apparently not fetching the rows from the result. Please change the code to this and it will print the results.

$query = new MySQLi(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
$query->multi_query($sql);

do {
       /* store first result set */
       if ($result = $query->use_result())
       {
           while ($row = $result->fetch_row())
           {
               print_r($row);
           }
           $result->close();
       }
       /* print divider */
       if ($query->more_results())
       {
           printf("-----------------\n");
       }
   } while ($query->next_result());

Answer 2

Try this:

$query = new MySQLi(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}
if ($query->multi_query($sql)) {
    do {
        if ($result = $query->use_result()) {
            while ($row = $result->fetch_row()) {
                printf("%s\n", $row[0]);
            }
            $result->free();
        }
    } while ($query->next_result());
} else {
   echo 'ERROR FOR QUERY!';
}

This should help you trap any errors. Also, I think your use_result needs to be swapped with the next_result.

UPDATED: One other thing, have you checks to make sure the variables you are passing to the query actually contain data? Print our the query to make sure you can run it in the database and get results manually too.