phpmysqlpdo

pdo add value to sql result


I use code below to get data from database

if( !empty( $books_ids ) )
{
    $books_ids_in = implode(',', array_fill(0, count($books_ids), '?'));

    $query = "SELECT
        b.id,
        b.`name`,
        b.`year`,
        GROUP_CONCAT(DISTINCT a.`name`) AS author_names,
        GROUP_CONCAT(DISTINCT s.`name`) AS store_names
    FROM
        books AS b
        LEFT JOIN books_authors AS b_a ON b.id = b_a.book_id
        LEFT JOIN authors AS a ON a.id = b_a.author_id
        LEFT JOIN books_stores AS b_s ON b.id = b_s.book_id
        LEFT JOIN stores AS s ON s.id = b_s.store_id
    WHERE
        b.id IN (". $books_ids_in .")
    GROUP BY b.id
    ORDER BY b.id";

    $stmt = $conn->prepare($query); 
    foreach ($books_ids as $k => $id) {
        $stmt->bindValue(($k+1), $id);
    }

    $stmt->execute();
    $results = $stmt->fetchAll();
}

and as I use $book id for this purpose, I would like to add some parameter in result to show that, for example, param = "book" for every row. Is there any way to do that?


Solution

  • Just pass it the string and alias it as a column. Though since you know the value passed in in code and display the values though code...... I'm not sure why you need this... as the value is available to you in the code when being displayed.

    $query = "SELECT
        b.id,
        b.`name`,
        b.`year`,
        GROUP_CONCAT(DISTINCT a.`name`) AS author_names,
        GROUP_CONCAT(DISTINCT s.`name`) AS store_names,
        '". $param."' as forEveryRow
    FROM
        books AS b
        LEFT JOIN books_authors AS b_a ON b.id = b_a.book_id
        LEFT JOIN authors AS a ON a.id = b_a.author_id
        LEFT JOIN books_stores AS b_s ON b.id = b_s.book_id
        LEFT JOIN stores AS s ON s.id = b_s.store_id
    WHERE
        b.id IN (". $books_ids_in .")
    GROUP BY b.id
    ORDER BY b.id";