CODEWITHSUNDEEP
phpmysqlipdo
medk
medk

Reputation: 9529

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()

Upvotes: 0

Views: 42

Answers (1)

deceze
deceze

Reputation: 522085

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->fetch_object()) {
    $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.

Upvotes: 1

Related Questions