Mysql - How to query a mysqli_multi_query() to return the entire row

Question

My select:

$query  = "SELECT * FROM user where userID='1';";

within my mysqli_multi_query() is not returning the entire row, it only returns a single column.

This is my result:

1-----------------book1 book3 book4

But this is what I want:

1 John Jan-26 4-----------------book1 book3 book4

Here are my two tables:

Table1: user

|    userID   |     name        |   date       |   booksRead
---------------------------------------------------------------------
|    1        |     John        |   Jan-26     |   4
|    2        |     Andy        |   Jan-27     |   7
|    3        |     Mark        |   Jan-28     |   8

Table 2: booksCheckedOut

row    |   userID   |      book    |  date
-------------------------------------------------
1      |    1       |      book1   |  Jan-26
2      |    2       |      book2   |  Jan-27
3      |    1       |      book3   |  Jan-28
4      |    1       |      book4   |  Jan-29

Here is my php code:

<?php
$mysqli = new mysqli("localhost","root","password","database_name");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query  = "SELECT * FROM user where userID='1';";
$query .= "SELECT book FROM booksCheckedOut where userID='1';";


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

        if ($mysqli->more_results()) {    /* print divider */
            printf("-----------------\n");
        }
    } while ($mysqli->next_result());
}

$mysqli->close();
?>

Does anyone know what I'm doing wrong?

Thanks in advance!

Answer 1

Based on the output specified by you, joins is the way to proceed.

See if this helps
$query = select u.*, b.book from users u LEFT JOIN booksCheckedOut b ON u.userID = b.userID;

Answer 2

I hope this will work i think

select U.userID,U.name,U.date,U.booksRead,BCO.book from user U,booksCheckedOut BCO where U.userID=BCO.userID  

Answer 3

try with this simple query :

SELECT u.*, b.book FROM users AS u INNER JOIN booksCheckedOut  AS b ON u.userID = b.userID;

Answer 4

Query1: "SELECT * FROM user where userID='1';" returns 1 row with 4 columns.

Query2: "SELECT book FROM booksCheckedOut where userID='1';" returns 3 rows with 1 column

If you wanted to output all of the columns from Query1, then you should have used:

printf("%s %s %s %s", $row[0]);

All three rows (containing just one column) from Query2 are appropriated iterated and outputted using:

printf("%s", $row[0]);

So printf() was interrupting your output, but honestly you should be using LEFT JOIN because you are referencing two tables using the same id. If you are not familiar with JOINs, become familiar because your situation calls for it.