How access SQL values in a Group clause with an Mysqli Prepared Statement

Question

I'm trying run a prepared statement using mysqli.

$stmt = mysqli_stmt_init($connection);
mysqli_stmt_prepare($stmt, "SELECT books.book_id, title, publish_date, thumbnail,
GROUP_CONCAT(CONCAT(authors.author_id,' ', first_name,' ', last_name))
FROM books, authors, books_authors
WHERE books.book_id = books_authors.book_id 
AND authors.author_id = books_authors.author_id 
GROUP BY books.book_id, title, publish_date, thumbnail");

mysqli_stmt_execute($stmt);

mysqli_stmt_bind_result($stmt, books.book_id, title, publish_date, thumbnail, $author_id);

All the values are displayed correctly except for the author names. The author names aren't displayed at all. The first_name, and last_name fields are in a GROUP BY clause in the SQL. How do I access those values in the mysqli_stmt_bind_result without getting an error of the number of mysqli_stmt_bind_result fields not matching the mysqli_stmt_prepare?

I'm fetching the results in a while loop with

while (mysqli_stmt_fetch($stmt))

I would suggest you to avoid `FROM a lot of tables` and use `JOIN`s instead. — Vitalii Strimbanu, Jan 12 '17 at 19:31
Also, your usage of `mysqli_stmt_bind_result` function is incorrect. Check here how to correctly use it: http://php.net/manual/en/mysqli-stmt.bind-result.php. — jaro1989, Jan 12 '17 at 20:11

score -1 · Answer 1 · answered Feb 01 '17 at 11:19

Try this may help you

$stmt = mysqli_stmt_init($connection);
mysqli_stmt_prepare($stmt, "SELECT books.book_id, title, publish_date, thumbnail,
GROUP_CONCAT(CONCAT(authors.author_id,' ', first_name,' ', last_name))
FROM books, authors, books_authors
WHERE books.book_id = books_authors.book_id 
AND authors.author_id = books_authors.author_id 
GROUP BY books.book_id, title, publish_date, thumbnail");

mysqli_stmt_execute($stmt);

mysqli_stmt_bind_result($stmt, books.book_id, title, publish_date, thumbnail, $author_id);
while (mysqli_stmt_fetch($stmt)) {
        printf("%s %s\n", $col1, $col2);
    }

How access SQL values in a Group clause with an Mysqli Prepared Statement

1 Answers1