Returning an array from a MySQL query with a JOIN operator

Question

I have two tables;

Books ,book_id ,mbook_id (Relation with mbook_id in the master_books table) ,org_id ,book_rating

master_books ,mbook_id ,book_name ,book_authors ,book_summary ,book_pub_date ,book_ISBN10 ,book_ISBN13 ,book_image

Now the idea is that there are a collection of books in the master_books table, but say there were 3 libraries who all had the same book. Instead of having 3 entries of the book the books table just references the master_books table id for the book information.

To start with i've set up a simple query to get all books, like so;

$result = mysqli_query($link, "SELECT books.book_id, master_books.book_name, master_books.book_image FROM books JOIN master_books ON master_books.mbook_id WHERE org_id='" . $org_id . "'");

I'm using the following to output the data;

$books = mysqli_fetch_array($result);
foreach($books as $x => $x_value) {
    echo "Key=" . $x . ", Value=" . $x_value;
    echo "<br>";
}

The result is follows;

Key=0, Value=1
Key=book_id, Value=1
Key=1, Value=Picturepedia: 15 Plants (Picturepedia)
Key=book_name, Value=Picturepedia: 15 Plants (Picturepedia)
Key=2, Value=9780751350852.jpg
Key=book_image, Value=9780751350852.jpg

I'm trying to figure out why it duplicates with Key=0 and then Key=book_id and so on. The lines beginning with Key=0, Key=1, Key=2 shouldn't be there

Sorry, just changed the last sentence, trying to figure out why i'm getting the key=0, key=1, key=2 lines — Nathan
– Nathan, Commented May 20, 2015 at 18:43
You're using a fetch function which returns a dual-keyed array. THere's nothing unusual about this. It's exactly how mysql/mysqli "fetch" functions have always worked, unless you use one of the single-type return variants. In other words, RTFM: php.net/manual/en/mysqli-result.fetch-array.php — Marc B
– Marc B, Commented May 20, 2015 at 18:46
Thats becuase the mysqli_fetch_array default behaviour is to return both types of data so you get the elements by number and by string key — Lupin
– Lupin, Commented May 20, 2015 at 18:47

Lupin · Accepted Answer · 2015-05-20 18:48:44Z

1

if you just want to get the array with the key names and not the numeric elements use this instead:

$books = mysqli_fetch_array($result, MYSQLI_ASSOC);

answered May 20, 2015 at 18:48

Lupin

1,26411 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dinei · Accepted Answer · 2015-05-20 18:48:52Z

0

It's because mysqli_fetch_array receives 2 parameters and you are ignoring the second: resulttype

As of the documentation, you have 3 options:

MYSQLI_ASSOC - you receive the field names on the array key
MYSQLI_NUM - you receive a counter on the array key
MYSQLI_BOTH - you receive both the options, all duplicated. This is the default.

As you said that

The lines beginning with Key=0, Key=1, Key=2 shouldn't be there

I think you want to call it like this: $books = mysqli_fetch_array($result, MYSQLI_ASSOC);

answered May 20, 2015 at 18:48

Dinei

5,7834 gold badges43 silver badges64 bronze badges

Comments

TimoStaudinger · Accepted Answer · 2015-05-20 18:49:33Z

The response is perfectly valid.

The returned array from mysqli_fetch_array() that you store in $books represents only one line from your result table.

You can access the columns of your result table either by using the column name, e.g. book_id, or by using the position in your SELECT statement: book_id is the first field you requested, thus index 0, book_name is the second, thus index 1 and so forth.

If you don't want or need the entries with indices, use mysqli_fetch_assoc() instead.

In order to access the next lines, you have to use a loop:

while($books = mysqli_fetch_array($result)) {

    // do stuff

}

This will fetch another row as long as another row is available. Once you fetched all of them, mysqli_fetch_array() will return NULL, which evaluates to false and the loop will end.

Collectives™ on Stack Overflow

Returning an array from a MySQL query with a JOIN operator

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related