prepare different mysqli statement

Question

I get lost when I need to prepare multiple statements. I have problems with prepared statement and connection..

I have a function and I pass to it the connection to the database ($con variable). Inside this function i prepare more statement but i have an error, the server replied with:

mysql_error() expects parameter 1 to be resource, object given in select.php on line 1086

This is the page where i call the function and pass the connection variable:

include('include/connect.php');

echo show_volanti($con);

This is the connection file that I included above:

$con = mysqli_connect($host,$user,$password,$db);

if (!$con) {
    die('Connection Error (' . mysqli_connect_errno() . ') '
        . mysqli_connect_error()); }


if(!mysqli_set_charset($con, "utf8mb4")) {
    printf("Error loading character set utf8: %s\n", mysqli_error($con));
    exit(); }

and this is the function:

function show_volanti($data){

    $con = $data;                                                   // PASSO CONNESSIONE
    $con2 = '';                                                     // SECONDA CONNESSIONE
    $id = 1;                                                        // 1 VOLANTE

    $visibile = 1;                                                  // VARIABILE DI VISIBILITA'

    $rows1 = array();                                               // PREPARO ARRAY 1 PER ID ARTICOLI VOLANTE
    $rows2 = array();                                               // PREPARO ARRAY 2 PER ARTICOLI VOLANTE
    $rows3 = array();                                               // PREPARO ARRAY PER GALLERIA IMMAGINI ARTICOLI

    $stmt1 = '';                                                    // PREPARO GLI STATEMENT
    $stmt2 = '';                                                    // PREPARO GLI STATEMENT
    $stmt3 = '';                                                    // PREPARO GLI STATEMENT

    $g = '';                                                        // RIFERIMENTO ASSOCIAZIONE GALLERY VIEWER

    $id_articoli = array();                                         // IMMAGAZZINO ID ARTICOLI DA RECUPERARE NELLA GALLERIA


    $con2 = mysqli_stmt_init($con);                                 // INIZIALIZZO LA CONNESSIONE

    $stmt1 = mysqli_stmt_prepare($con2,'SELECT
                                        articoli.id
                                        FROM articoli
                                        WHERE articoli.genere1 = ?
                                        AND articoli.visibile = ?')
                                        or die(mysql_error($con2));// QUERY INSERIMENTO DATI

    mysqli_stmt_bind_param($stmt1,'ii',$id,$visibile);                          // LEGO I PARAMETRI

    mysqli_stmt_execute($stmt1);                                                // ESEGUO LA QUERY

    mysqli_stmt_bind_result($stmt1,$rows1['id']);

    while(mysqli_stmt_fetch($stmt1)){
        $id_articoli = $rows1['id'];
    }

    // CREO RIFERIMENTO PER GALLERIA NEL VIEWER

    $g .= "g";
    $g .= $id_articoli;

    $stmt2 = mysqli_stmt_prepare($con2,'SELECT
                                        articoli.id,
                                        articoli.titolo,
                                        articoli.descrizione
                                        FROM articoli
                                        WHERE articoli.genere1 = ?
                                        AND articoli.visibile = ? ')
                                        or die(mysqli_error($con2));                // QUERY INSERIMENTO DATI

    mysqli_stmt_bind_param($stmt2,'ii',$id,$visibile);                          // LEGO I PARAMETRI

    mysqli_stmt_execute($stmt2);                                                // ESEGUO LA QUERY

    mysqli_stmt_bind_result($stmt2,$rows2['id'],$rows2['titolo'],$rows2['descrizione']);                                        


    // PREPARO QUERY PER LA GALLERIA

    $stmt3 = mysqli_stmt_prepare($con2,'SELECT
                                        galleria.id,
                                        galleria.foto
                                        FROM galleria
                                        WHERE galleria.rif_id = ?
                                        AND articoli.visibile = ? ')
                                        or die(mysqli_error($con2));                        // QUERY INSERIMENTO DATI

    mysqli_stmt_bind_param($stmt3,'ii',$id_articoli,$visibile);                         // LEGO I PARAMETRI

    mysqli_stmt_execute($stmt3);                                                        // ESEGUO LA QUERY

    mysqli_stmt_bind_result($stmt3,$rows3['id'],$rows3['foto']);            

    $html = "";
    $html .= "<div class='container'>";
    $html .= "  <div class='row'>";
    $html .= "    <div class='col-sm-12'>";
    $html .= "      <div class='panel panel-default'>";
    $html .= "        <div class='panel-body'>";

    while (mysqli_stmt_fetch($stmt2)){
        $html .= "            <div class='col'>";
        $html .= "              <div class='panel panel-default'>";
        $html .= "                <div class='panel-heading'>$rows2[titolo]</div>";
        $html .= "                  <div class='panel-body'>";
        $html .= "                      <div class='row'>";
        $html .= "                          <div class='class_p'>$rows2[descrizione]</div>";
        $html .= "                      <div> <!-- end first row -->";

        $html .= "                      <div class='class_container clearfix'>";

        while(mysqli_stmt_fetch($stmt3)){
            $html .= "                              <div class='thumbnail col-sm-2'>";
            $html .= "                                  <div class='class_img'>";
            $html .= "                                      <a href='$rows3[foto]' data-toggle='lightbox' data-gallery='$g' >";
            $html .= "                                          <img src='$rows3[foto]' class='img-responsive' class='img-fluid'>";
            $html .= "                                      </a>";
            $html .= "                                  </div> <!-- end class_img -->";
            $html .= "                              </div> <!-- end thumbnail col-sm-2- -->";   
        }

        $html .= "                      </div> <!-- end class_container -->";

        $html .= "                </div> <!-- end panel body -->";
        $html .= "              </div> <!-- end panel panel-default -->";
        $html .= "            </div> <!-- end col -->";
    }

    $html .= "        </div> <!-- end panel-body -->";
    $html .= "      </div> <!-- end panel panel-default -->";
    $html .= "    </div> <!-- end col-sm-12 -->";

    $html .= "  </div> <!-- end row -->";

    mysqli_close($con2);                                                // CHIUDO CONNESSIONE   


    return $html;
}

score 0 · Answer 1 · answered Jun 27 '17 at 21:48

0

This results in the error:

$stmt1 = mysqli_stmt_prepare($con2,'SELECT
articoli.id,
FROM articoli
WHERE articoli.genere1 = ?
AND articoli.visibile = ?')
or die(mysql_error($con2));// QUERY INSERIMENTO DATI

Change mysql_error to mysqli_error and your problem should be fixed.

Note: Using or die() is a bad way to handle an error.

answered Jun 27 '17 at 21:48

Frank M

170
3
14

You can't get exactly the same error when you fixed this error when it is on the same line (because `mysql_error` doesn't exist anymore). Can you explain your problem? – Frank M Jun 27 '17 at 21:56
the error the server reply is: Warning: mysqli_error() expects parameter 1 to be mysqli, object given in /web/htdocs/www.paolucc.it/home/include/select.php on line 1086 and the number of the line is at the beginning of the first query.. I suspect is something about the connection parameter but I am not able to find the exact error.. – P.Davide Jun 27 '17 at 21:59
Try to change `mysqli_error($con2)` to `mysqli_error($con)`. `mysqli_error` expects a link identifier from `mysqli_connect` or `mysqli_init`. – Frank M Jun 27 '17 at 22:03
ok there was a comma that shouldn't be there and now I did a little step forware but still have problem on the following line and it is all about the connection... Warning: mysqli_stmt_bind_param() expects parameter 1 to be mysqli_stmt, boolean given in /web/htdocs/www.paolucc.it/home/include/select.php on line 1088 it is all about how i do the prepared statement.. – P.Davide Jun 27 '17 at 22:09
`mysqli_stmt_prepare` always returns a boolean. The variable for `mysqli_stmt_init` should be the same as the variable of `mysqli_stmt_prepare`. Right now, the `init` function has a variable `$con2` and the `prepare` functions has a variable `$stmt1`. If you add `$stmt1 = $con2;` above `$stmt1 = (...)`, your first query should work. Note that you have to change it for all of the queries. – Frank M Jun 27 '17 at 22:17
sorry i get lost i don't follow you... usually when i do a single prepared statemtent i did this $stmt = mysqli_stmt_init($con); // INIZIALIZZO LA CONNESSIONE mysqli_stmt_prepare($stmt,'SELECT articoli.id, articoli.titolo, articoli.descrizione FROM articoli WHERE articoli.genere1 = ? AND articoli.visibile = ? ') or die(mysqli_error($con)); // QUERY INSERIMENTO DATI and it works.. but i do not understand how i can prepare more statement to be executed later in the function.. – P.Davide Jun 27 '17 at 22:23
1

You can use the same variable. Instead of `$stmt2` and `$stmt3`, use `$stmt2 = $con2;` and `$stmt3 = $con2;`. – Frank M Jun 27 '17 at 22:32
Thanks I got it! sorry for the late reply :) – P.Davide Jul 02 '17 at 10:20
Great, have fun with coding! – Frank M Jul 02 '17 at 21:56

prepare different mysqli statement

1 Answers1