Multiple prepared statements

[skyer2000]

I can't figure out how to make another database call in the middle of this code to fetch more results:

 

<?
    if (mysqli_stmt_prepare($stmt, $sql)) {
        mysqli_stmt_bind_param($stmt, 'i', $topid);
        mysqli_stmt_execute($stmt);
        mysqli_stmt_bind_result($stmt, $fieldid, $fieldtype, $parentid, $fieldvalue);
        while(mysqli_stmt_fetch($stmt)) {
        echo '<p>';    
        echo '<label for="'.$fieldid.'">'.$fieldvalue.'</label><br />';
            if($fieldtype == '1') {
                echo '<input type="text" />';
            } elseif($fieldtype == '2') {
                echo '<select>';
                                 //there needs to be another database call here to pull in the option values for the select
                               echo '</select>';
            } elseif($fieldtype == '3') {
                echo '<textarea></textarea>';
            }
        echo '</p>';    
        }
    }
?>

 

With standard MySQL, I could just start another MySQL statement there. But now, what do I need to do to get multiple prepared statements running at the same time? Or are there different, better ways?

[teng84]

call your object and assign it to another var

 

something like

 

$var1 = new db(db1);

$var2 = new db(db2);

 

[skyer2000]

How about procedural?

[teng84]

i believe you can use the database name so you dont need to select db not sure bout this but it should work

 

something like

 

select * from  dbname.table

[skyer2000]

Tried but still not working. After doing some searches online I'm surprised not many people have asked this question!

[PFMaBiSmAd]

Umm. The first parameter ($stmt in your posted code) holds the statement identifier. You use that in all the related prepared functions to reference which prepared statement they are to operate on. To use more than one prepared statement, use a different variable name to hold the statement identifier.

[skyer2000]

Putting in the new statement identifier brings up the following errors:

 

$sql2 = 'NEW SQL STATEMENT';

$stmt2 = mysqli_stmt_init($connect);

 

mysqli_stmt_prepare($stmt2, $sql2);

mysqli_stmt_bind_param($stmt2, 'i', $papid);

mysqli_stmt_execute($stmt2);

mysqli_stmt_bind_result($stmt2, $stuff);

while (mysqli_stmt_fetch($stmt2)) {

$echo stuff;

}

mysqli_stmt_close($stmt2);

 

Warning: mysqli_stmt_bind_param() [function.mysqli-stmt-bind-param]: invalid object or resource mysqli_stmt in C:\location\functions_conf.php on line 177

 

Warning: mysqli_stmt_execute() [function.mysqli-stmt-execute]: invalid object or resource mysqli_stmt in C:\location\functions_conf.php on line 178

 

Warning: mysqli_stmt_bind_result() [function.mysqli-stmt-bind-result]: invalid object or resource mysqli_stmt in C:\location\functions_conf.php on line 179

 

Warning: mysqli_stmt_fetch() [function.mysqli-stmt-fetch]: invalid object or resource mysqli_stmt in C:\location\functions_conf.php on line 180

 

Warning: mysqli_stmt_close() [function.mysqli-stmt-close]: invalid object or resource mysqli_stmt in C:\location\functions_conf.php on line 183

[PFMaBiSmAd]

The mysqli_stmt_prepare() probably failed due to a sql syntax error. You MUST check in your code if a statement works or not before blindly continuing execution.

[skyer2000]

The SQL works, the entire block of code works if it is not nested within another prepared statement.

[PFMaBiSmAd]

Cannot really help you without seeing all the relevant code responsible for the symptoms.

[skyer2000]

<?
    if (mysqli_stmt_prepare($stmt, $sql)) {
        mysqli_stmt_bind_param($stmt, 'i', $topid);
        mysqli_stmt_execute($stmt);
        mysqli_stmt_bind_result($stmt, $fieldid, $fieldtype, $parentid, $fieldvalue);
        while(mysqli_stmt_fetch($stmt)) {
        echo '<p>';   
        echo '<label for="'.$fieldid.'">'.$fieldvalue.'</label><br />';
            if($fieldtype == '1') {
                echo '<input type="text" />';
            } elseif($fieldtype == '2') {
                echo '<select>';
                                      $sql2 = 'WORKING SQL STATEMENT';
                                      $stmt2 = mysqli_stmt_init($connect);

                                      mysqli_stmt_prepare($stmt2, $sql2);
                                      mysqli_stmt_bind_param($stmt2, 'i', $papid);
                                      mysqli_stmt_execute($stmt2);
                                      mysqli_stmt_bind_result($stmt2, $stuff);
                                      while (mysqli_stmt_fetch($stmt2)) {
                                          $echo stuff;
                                      }
                                      mysqli_stmt_close($stmt2);
                               echo '</select>';
            } elseif($fieldtype == '3') {
                echo '<textarea></textarea>';
            }
        echo '</p>';   
        }
    }
?>

[skyer2000]

I tried putting a regular MySQL statement in there:

 

$query = 'WORKING SQL STATEMENT';
$result = mysqli_query($connect, $query);
$row = mysqli_fetch_array($result, MYSQLI_BOTH);
echo $row['foo'];

 

This works anywhere outside of    while(mysqli_stmt_fetch($stmt)) { }

 

However, once placed in there it spits out the error:

 

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in C:\path\functions_conf.php on line 175

 

 

At the beginning I even tried a $connect2 = $connect, then tried using $connect2 instead. That didn't work either.

[skyer2000]

I might of figured out the direction I need to go to solve my issue with multiple prepared statements. I've been doing some reading and ran into Subqueries in MySQL.

 

 

Here is the first MySQL statement, that just pulls up the information:

 

SELECT papers.papid AS papid,papers.title AS title,papers.authors AS authors, papers.status AS status_num 
FROM papers WHERE papers.topid = ?

 

The second query takes the papid, checks a different table, and finds all values that have the same papid

 

SELECT keywords.keyid AS keyid,keywords.keyword AS keyword 
FROM keywords WHERE keywords.papid = ?

 

 

How would I then combine those two into one query, then somehow pull only the paper info once, but the keywords as many times as I need? Or is that even possible?

[fenway]

You need to join those tables...

[skyer2000]

Ok so lets say I do this:

 

SELECT papers.papid AS papid,papers.title AS title, papers.authors AS authors, papers.status AS status_num, keywords.keyid AS keyid, keywords.keyword AS keyword
FROM papers,keywords
WHERE papers.topid = ?
AND keywords.topid = papers.topid

 

I then can echo all these values, however, keywords only displays the first keyword it found. How do I make it display multiple keywords (that it should have found with this query)

[skyer2000]

This is actually now more suited for the PHP forum, thanks for the direction.

[fenway]

Why would it only display the first one?