Silver Silver - 4 months ago 22
SQL Question

PHP Prepared statement (recursive function), can you work around this?

I found this standard category and sub category output recursive function online:

function print_categ_list_product_page($parent = 0) {
global $mysqli;
$qry = "SELECT * FROM ami_categories WHERE ct_parent = '$parent'";
$sqlResult = $mysqli->query($qry);
if ($sqlResult->num_rows > 0) {
while ($row = $sqlResult->fetch_assoc()) {
echo'<ul style="padding-right: 10px;">';
echo'<li class="menu_list_1" ><a href="products/?ct=' . $row['ct_id'] . '">' . $row['ct_name'] . '</a>';
print_categ_list_product_page($row['ct_id']);
echo'</li>';
echo'</ul>';
}
}
}


By no means i am not professional at php. So recently i started using this method called Prepared Statement to call query's because this way i could avoid SQL injections. Everything is fine except i cant figure out any way to make this work recursively.

function print_categories_2_0($parent = 0) {
global $mysqli;

$stmt = $mysqli->prepare("SELECT ct_id, ct_name FROM ami_categories WHERE ct_parent = ?");
$stmt->bind_param('s', $parent);
$stmt->execute();
$stmt->bind_result($id, $name);
while(mysqli_stmt_fetch($stmt)){ //fetch rows
echo'<ul style="padding-right: 10px;">';
echo'<li class="menu_list_1" ><a href="products/?ct=' . $id . '">' . $name . '</a>';
print_categories_2_0($id);
echo'</li>';
echo'</ul>';
}
$stmt->close();
}


And this just simply does not output anything. After some research I found out that its not possible to have recursive Prepared Statement function, simply because connection has to be closed before new is prepared.

So my question is, can someone help me to come up with alternative way to output this category/sub category list with prepared statement, without using recursive function. I was thinking: is it possible to do something like that with INNER JOIN within SQL statement ?

Thanks you!

Answer

First off, please don't use global. Second, the real power of prepared statements is in not having to prepare the statement over and over. So you should pass the mysqli_stmt object from prepare() as an argument. This way you prepare once and execute over and over. You also might want to tell it that you're expecting an integer i and not a string s

$stmt = $mysqli->prepare("SELECT ct_id, ct_name FROM ami_categories WHERE ct_parent = ?");

print_categories_2_0($stmt); // Start the initial call

function print_categories_2_0(mysqli_stmt $stmt, $parent = 0) {
    $stmt->reset();
    $stmt->bind_param('i', $parent);
    $stmt->execute();
    $stmt->bind_result($id, $name);
    $data = array();
    // pull all the records out of the statement
    while($stmt->fetch()) $data[] = array('id' => $id, 'name' => $name);

    foreach($data as $row) { //fetch rows
        echo'<ul style="padding-right: 10px;">';
            echo'<li class="menu_list_1" ><a href="products/?ct=' . $row['id'] . '">' . $data['name'] . '</a>';
            print_categories_2_0($stmt, $row['id']);
            echo'</li>';
        echo'</ul>';
    }
}