Can I query from a previous query in prepare statement

Can I query from a previous query in prepare statement
0

#1

Hey guys!

I have part of the following code but I can’t seem to get $row[‘name’]to display. Is it possible to query a previous query with prepare statement or must I query it again? I have also tried to move the echo statement around various {}but doesn’t seem to work:


<?php

$id = $_GET['id'];

if ($id) {
  $sql = "SELECT * FROM forum_sub_cats WHERE id = ?;";

  if(!mysqli_stmt_prepare($stmt, $sql)) {
           echo "SQL error";
        } else {
          mysqli_stmt_bind_param($stmt, "i", $id);
          mysqli_stmt_execute($stmt);
          $result = mysqli_stmt_get_result($stmt);
          $resultCheck = mysqli_num_rows($result);
          $admin_user_level = $admin_user_level + '1';
          if ($resultCheck < 1) {
          	 echo "The forum you are trying to create a topic on, does not exist\n";
          } else {
          	   $row1 = mysqli_fetch_assoc($result);
          	   if ($row1['admin'] == 0) {
                  echo "You are not an administrator, therefore you cannot post on this forum!\n";
          	   } else {
          	   	if (!$_POST['submit']) {
          	   echo "<table border=\"0\" cellspacing=\"3\" cellpadding=\"3\">\n";
          	   echo "<form method=\"POST\" action=\"./forum_main.php?act=create&id\">\n";
          	   echo "<tr><td>Form Sub Category</td><td><select name=\"cat\">\n";

          	   $sql2 = "SELECT * FROM forum_cats WHERE admin < ?;";
               
              
          	   if(!mysqli_stmt_prepare($stmt, $sql2)) {
		           echo "SQL error";
		        } else {
		          mysqli_stmt_bind_param($stmt, "i", $admin_user_level);
		          mysqli_stmt_execute($stmt);
		          $result2 = mysqli_stmt_get_result($stmt);
		          while ($row = mysqli_fetch_assoc($result2)) {
                   $row = $row['id'];
                  
		          	$sql3 = "SELECT * FROM forum_sub_cats WHERE cid = ?;";
	                  	
	                 if(!mysqli_stmt_prepare($stmt, $sql3)) {
		                echo "SQL error";
		            } else {
		                mysqli_stmt_bind_param($stmt, "i", $row);
		                mysqli_stmt_execute($stmt);
		                $result3 = mysqli_stmt_get_result($stmt);
		                
		                  echo "<option value=\"0\">".$row['name']."</option>\n";
		               	   while ($row2 = mysqli_fetch_assoc($result3)) {
		               	   	  $selected = ($row2['id'] == $id) ? "SELECTED": "";
		               	   	 
			               	   echo "<option value=\"".$row2['id']."\"".$selected.">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;".$row2['name']."</option>\n";

		               	   }


#2

Just to make sure I understand the question above, do you mean query a previous query result? If so, why would you need to do this if the previous query result is in a variable?

If you did not mean query a previous query result, then can you describe in more detail what you are trying to accomplish?


#3

I am not sure but I think I am trying to use a previous query because I need to use $row from a previous query and I tried it but it didn’t work. It couldn’t access the variable


#4

Unless you reassign a variable, the original variable should still have the query results in it.


#5

but i tried it and it did not work… It doesn’t seem to recognise the variable…


#6

In the code you posted above, can you point out the line where you try to reference a variable containing a previous query result?


#7

In my sublime text, I think it is line 64, $row[‘name’]; doesn’t seem to work. As you can see, from the picture below where the blanks are, it should read the main categories…


#8

Your code indentation is very messy and you should clean it up, but I believe I found the problem.

while ($row = mysqli_fetch_assoc($result2))
    {
    $row = $row['id'];
    $sql3 = "SELECT * FROM forum_sub_cats WHERE cid = ?;";
    if (!mysqli_stmt_prepare($stmt, $sql3))
        {
        echo "SQL error";
        }
      else
        {
        mysqli_stmt_bind_param($stmt, "i", $row);
        mysqli_stmt_execute($stmt);
        $result3 = mysqli_stmt_get_result($stmt);
        echo "<option value=\"0\">" . $row['name'] . "</option>\n";

If the last line is where you are getting the error message, then the 2nd line of the while loop (below), reassigns a new value to $row. I do not know what $row[‘id’] contains, but I assume it does not have a ‘name’ index you attempt to display in your echo statement.

    $row = $row['id'];

#9

thanks but I think the problem could be in this line of code: Have you seen this query before?

 $sql2 = "SELECT * FROM forum_cats WHERE admin < ".$admin_user_level+'1'.";";

I am not sure if my concatenation is correct here but am trying to get this to work with prepare statement…


#10

What data type is the admin field? What range of values does the admin field have?

I ask because you are comparing admin to a string. Maybe you meant to write the following?

$sql2 = "SELECT * FROM forum_cats WHERE admin < ".($admin_user_level+1).";";

#11

I think the person who did the tutorial did not explain well with regard to the variable $admin_user_level but it is the same as my $row[‘admin’], which shows the priveledge but I am not sure why he has the + 1 in that query?


#12

If you are not sure the data type of admin, you should look at the database field and find out and also look at what values are stored in it.

If it is the same as $row[‘admin’], then write the following to see.

echo gettype($row['admin']);