1

Figuring out prepared statements

I'm trying to implement prepared statement in this code:

$result=mysqli_query($connect,"SELECT subcategories.subcat_name, subsubcategories.subsubcat_name, subcategories.subcat_ID FROM subcategories INNER JOIN subsubcategories ON subcategories.subcat_ID=subsubcategories.subcat_ID WHERE subcategories.cat_ID = $cat_ID OR subcategories.extra_cat_ID = $cat_ID ORDER BY subcategories.subcat_name, subsubcategories.subsubcat_name ASC");
$lastcat = 0;
while ($row = mysqli_fetch_array($result)){
    if($lastcat != $row['subcat_ID']){
        $lastcat = $row['subcat_ID'];
        echo "<br>"; 
        echo $row['subcat_name'];
        echo "<br>";
        echo "<br>";
        }
    echo $row['subsubcat_name'];
    echo "<br>";
    }

This is how I did it so far:

$stmt = mysqli_prepare($connect, "SELECT subcategories.subcat_name, subsubcategories.subsubcat_name, subcategories.subcat_ID FROM subcategories INNER JOIN subsubcategories ON subcategories.subcat_ID=subsubcategories.subcat_ID WHERE subcategories.cat_ID = ? OR subcategories.extra_cat_ID = ? ORDER BY subcategories.subcat_name, subsubcategories.subsubcat_name ASC");
mysqli_stmt_bind_param($stmt, "ii", $cat_ID, $cat_ID);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $cat_ID, $cat_ID);
$lastcat = 0;
while ($row = mysqli_stmt_fetch($stmt)){
    if($lastcat != $row['subcat_ID']){
        $lastcat = $row['subcat_ID'];
        echo "<br>"; 
        echo $row['subcat_name'];
        echo "<br>";
        echo "<br>";
        }
    echo $row['subsubcat_name'];
    echo "<br>";
    }

But I get this error:

Warning: mysqli_stmt_bind_result(): Number of bind variables doesn't match number of fields in prepared statement in "mysqli_stmt_bind_result($stmt, $cat_ID, $cat_ID);"

Any idea what I'm doing wrong?

Thoaren
  • 194
  • 9
  • Seems like a weird design to me. You're limited to 2 levels of categories. What if you want 3 in the future? You might want to look into closure tables, or at least adjacency lists to allow arbitrary category depth. http://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database might prove useful. – GordonM Jun 10 '14 at 08:19
  • @user0000000 How can my question be reversed to an active one? Do I need to do something else? – Thoaren Jun 10 '14 at 10:53
  • No, just wait. The question already has 4 reopen votes, it only needs one more. – Madara's Ghost Jun 10 '14 at 11:01

1 Answers1

1

After some research and testing I found out how to do it:

if ($stmt = mysqli_prepare($connect, "SELECT subcategories.subcat_name, subsubcategories.subsubcat_name, subcategories.subcat_ID FROM subcategories INNER JOIN subsubcategories ON subcategories.subcat_ID=subsubcategories.subcat_ID WHERE subcategories.cat_ID = ? OR subcategories.extra_cat_ID = ? ORDER BY subcategories.subcat_name, subsubcategories.subsubcat_name ASC")){
mysqli_stmt_bind_param($stmt, "ii", $cat_ID, $cat_ID);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $subcat_name, $subsubcat_name, $subcat_ID);
$lastcat = 0;
while (mysqli_stmt_fetch($stmt)){
    if($lastcat != $subcat_ID){
        $lastcat = $subcat_ID;
        echo "<br>"; 
        echo $subcat_name;
        echo "<br>";
        echo "<br>";
        }

    echo $subsubcat_name;
    echo "<br>";
    }
}

I know html is basic. It's just for giving an idea how I did it. Thanks for help.

Thoaren
  • 194
  • 9