Prepared statements for MySQL from PHP (with mysqli)

Question

I have these tables in a MySQL database:

CREATE TABLE `product` (
`idProduct` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`category` varchar(255) NOT NULL,
PRIMARY KEY (`idProduct`)
);

CREATE TABLE `sale` (
`idSale` int(10) unsigned NOT NULL AUTO_INCREMENT,
`highDate` date NOT NULL,
`idProduct` int(10) unsigned NOT NULL,
`idUser` varchar(45) NOT NULL,
PRIMARY KEY (`idSale`)
);

I would like to get a table with the users in rows and products in columns, like this question: MySQL Pivot row into dynamic number of columns

In SQL Fiddle (Demo) works fine but I need help to query it from PHP with mysqli.

Is this correct?

$this->dbh->query("SET @sql = NULL");
$stmt = $this->dbh->query("SELECT GROUP_CONCAT(DISTINCT
                          CONCAT(
                          'count(case when category = ''',
                          category,
                          ''' then 1 end) AS ',
                          replace(category, ' ', '')
                       )
                    ) INTO @sql
                FROM product;");

$stmt = $this->dbh->prepare("SET @sql = CONCAT('SELECT s.idUser, ', @sql, ' FROM sale s
                            LEFT JOIN product p ON p.idProduct = s.idProduct
                            GROUP BY s.idUser');");

$stmt->execute();

How can I retrieve the data from the query? Execute() method returns TRUE or FALSE.

score 0 · Answer 1 · answered Oct 03 '12 at 08:03

0

use this for fetching result

while ($stmt->fetch()) {
    //code
}

http://php.net/manual/en/mysqli-stmt.fetch.php

answered Oct 03 '12 at 08:03

Yogesh Suthar

30,424
18
72
100

Ok, thanks! The problem is: How can I know the number of columns? To use `fetch()` I need to do before `bind_result($column1, $column2,...)` but I don't know how many columns retrieves the query. – Stokres Oct 03 '12 at 08:11

mtk · Answer 2 · 2012-10-09T12:53:05.057

0

You can use $stmt->fetch or $stmt->get_result() to retreive results from the queried statement as follows:

fetch()

while ($stmt->fetch()) {
    printf ("%s (%s)\n", $name, $code);
}

get_result()

$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_array(MYSQLI_NUM)) {
    foreach ($row as $r) {
        print "$r ";
    }
    print "\n";
}

edited Oct 09 '12 at 12:53

answered Oct 03 '12 at 08:10

mtk

13,221
16
72
112

score 0 · Accepted Answer · answered Oct 03 '12 at 13:05

In the end I did the following:

$query = $this->dbh->query("SELECT GROUP_CONCAT(DISTINCT CONC...");
$result = array();
$row = $query->fetch_row();

$query = $this->dbh->query("SELECT " . $row[0] . " FROM sale s ...");
return $query->fetch_array(MYSQLI_ASSOC);

Prepared statements for MySQL from PHP (with mysqli)

3 Answers3