medk medk - 4 months ago 8
PHP Question

Results not showing after converting PDO query to mySQLi

I have a script to extract data from two tables

matieres
and
sous_matieres
but it's written for PDO, I need it to work for mySQLi:

$stmt = $pdo->query('SELECT
m.id AS m_id, m.url AS m_url, m.title AS m_title,
s.id AS s_id, s.url AS s_url, s.title AS s_title
FROM matieres m
INNER JOIN sous_matieres s ON m.url = s.parent');

while ($row = $stmt->fetchObject()) {
$matieres[$row->m_id]['url'] = $row->m_url;
$matieres[$row->m_id]['title'] = $row->m_title;
$matieres[$row->m_id]['sous_matieres'][$row->s_id] = $row;
}

foreach ($matieres as $m_id => $matiere) {
echo "<h2>$matiere[title]</h2>";
foreach ($matiere['sous_matieres'] as $id => $sm) {
echo "<div>
<a href='{$sm->s_url}'>{$sm->s_title}</a>
</div>";
}
}


My new mySQLi code that shows nothing despite the two tables have data:

$query = 'SELECT
m.id AS m_id, m.url AS m_url, m.title AS m_title,
s.id AS s_id, s.url AS s_url, s.title AS s_title
FROM matieres m
INNER JOIN sous_matieres s ON m.url = s.parent';
$stmt = $mysqli->query($query);
$stmt->execute();
$stmt->store_result();

while ($row = $stmt->fetch()) {
$matieres[$row->m_id]['url'] = $row->m_url;
$matieres[$row->m_id]['title'] = $row->m_title;
$matieres[$row->m_id]['sous_matieres'][$row->s_id] = $row;
}

foreach ($matieres as $m_id => $matiere) {
echo "<h2>$matiere[title]</h2>";
foreach ($matiere['sous_matieres'] as $id => $sm) {
echo "<div>
<a href='{$sm->s_url}'>{$sm->s_title}</a>
</div>";
}
}


I don't know if it's missing
$stmt->bind_result()
and the variable inside. As for normal mySQLi queries (without joins) I have to declare the selected column names as variable inside the
$stmt->bind_result()

Answer

mysqli_stmt::fetch is used when you bind variables to results on a prepared statement, to populate those variables. The alternative method of getting the results is to use the mysqli_result class, which you can use the way you're trying to:

$result = $mysqli->query($query);

while ($row = $result->fetchObject()) {
    $matieres[$row->m_id]['url'] = $row->m_url;
    $matieres[$row->m_id]['title'] = $row->m_title;
    $matieres[$row->m_id]['sous_matieres'][$row->s_id] = $row;
}

See http://php.net/manual/en/mysqli.query.php and http://php.net/manual/en/class.mysqli-result.php.