PHP MySQL prepared statement in a prepared statement

Question

In modelSlikeVrijednost I have a reference to the model-s primary key. ModelSlikeVrijednost can contain a lot of images (depends on the user). I need to delete the folder based on the modelID.

Example of path: /home/mainSite/public_html/site/img/1/1/.

Is it possible to do this ?

Code:

if ($stmt = $mysqli->prepare("SELECT modelID FROM model WHERE proizvodacID='$id'")) {    
    $stmt->execute();

    $stmt->bind_result($modelID);

    while ($stmt->fetch()) {
        $path="/home/mainSite/public_html/site/img/".$id."/".$modelID."/";

        if ($stmt1 = $mysqli->prepare("SELECT modelSlikeVrijednost FROM modelSlike WHERE modelID='$modelID'")) {    
            $stmt1->execute();

            $stmt1->bind_result($slike);

            while ($stmt1->fetch()) {
                if(is_null($slike)){
                    rmdir($path);
                }
                else{
                    $slikePath="/home/mainSite/public_html/site/".$slike;
                    if($slikePath!=$path){
                        unlink($slikePath);
                    }
                   rmdir($path);
                }
             }

            $stmt1->close();

        }
        else {
            printf("Prepared Statement Error: %s\n", $mysqli->error);
        }
    }

    $stmt->close();

}

I get this error :Prepared Statement Error: Commands out of sync; you can't run this command now Prepared Statement Error: Commands out of sync; you can't run this command now

sql results usually come back as array objects, not strings. try echoing $modelID and see what shows up before trying to use it in another statement. — Jeff Hawthorne, Feb 20 '13 at 14:13
@JeffHawthorne: Not when one is using `mysqli::bind_result` which binds a column value directly to a PHP variable. — prodigitalson, Feb 20 '13 at 14:39
@prodigitalson so if you had a query that returned multiple rows, would it throw an error? — Jeff Hawthorne, Feb 20 '13 at 14:42
@JeffHawthorne: No, it binds the variable to the column value from the current row everytime `mysqil_stmt::fetch` is called. — prodigitalson, Feb 20 '13 at 14:44

prodigitalson · Accepted Answer · 2013-02-20T14:31:24.637

3

No you cant.... you need to loop through all the results, close the cursor, or use a separate connection.

However what you are trying to do is better accomplished with a join anyway...

SELECT ms.modelSlikeVrijednost, m.modelID FROM model m, modelSlike ms
WHERE ms.modelID= m.modelID
AND m.proizvodacID ='$id'

This will give you all the information you need in each row.

However you ar also using prepared statements incorrectly. You shouldnt be passing in php variables directly you should be binding them as parameters to the query:

$sql = 'SELECT ms.modelSlikeVrijednost, m.modelID FROM model m, modelSlike ms'
       .' WHERE ms.modelID= m.modelID'
       .' AND m.proizvodacID = ?';

if($stmt = $mysqli->prepare($sql)) {

   // bind the $id to the parameter as an integer
   $stmt->bind_param('i', $id);

   $stmt->execute();

   // bind the fields of the result to the same variables you had before
   $stmt->bind_result($slike, $modelID);

   // less prone to error if we only type this manually once :-)
   $basePath = "/home/mainSite/public_html/site";

   while($stmt->fetch()) {

        $path= $basePath . "/img/".$id."/".$modelID."/";
        $slikePath = $basePath . "/" . $slike;

        if(is_null($slike)){
          rmdir($path);
        } else {
           if($slikePath!=$path) {
              unlink($slikePath);
           }

           rmdir($path);
        }
   }
}

edited Feb 20 '13 at 14:31

answered Feb 20 '13 at 14:12

prodigitalson

60,050
10
100
114

This worked. Thanks. Just one more question, how can I achieve the same thing but only with delete ? Same first query but instead of the second , I use `DELETE FROM modelLinkovi WHERE modelID='$modelID'`. It only deletes the last record. – Vucko Feb 20 '13 at 14:50
Im not 100% sure what you are asking but i think you want to `DELETE` form both `model` and `modelLinkovi`... If thats that case your query would look like: `DELETE FROM model m, modelLinkovi ml WHERE ml.modelID = m.modelID AND m.modelID = ?` – prodigitalson Feb 20 '13 at 14:56
Of course since they both have the `modelID` column the `ml.modelID = m.modelID` is a bit redundant but its more consistent with what you use to pull data. – prodigitalson Feb 20 '13 at 14:58
I want to `DELETE` from `modelSlike` based on the `modelID ` in `model`. Example, the `modelID` in `model` is `1` and in `modelSlike` I have 3 rows that reference to that `1`. The query for that would be `DELETE FROM modelSlike WHERE modelID='1'`. Now I want to use the same query but to loop through the `modelID`. Is that possible ? – Vucko Feb 20 '13 at 15:01
Can you create another question with the schema for `modelSlike` and `model` along with an explanation of the relationship and what you want deleted? – prodigitalson Feb 20 '13 at 15:22
I found the answere [here](http://stackoverflow.com/questions/652770/mysql-delete-w-join). Anyway, thanks for the answer for the original question. – Vucko Feb 20 '13 at 15:33

Your Common Sense · Answer 2 · 2013-02-20T14:32:40.307

Do not use bare mysqli API.
Get yourself a helper class, like safemysql
Then your code would be

$models = $db->getCol("SELECT modelID FROM model WHERE proizvodacID=?i",$id);
foreach($models as $modelID) {
    $path  = "/home/mainSite/public_html/site/img/$id/$modelID/";
    $sql   = "SELECT modelSlikeVrijednost FROM modelSlike WHERE modelID=?i";
    $sarr  = $db->getCol($sql, $modelID));

    foreach($sarr as $silke) {
        if(!$slike)) {
           rmdir($path);
        } else {
            $slikePath="/home/mainSite/public_html/site/".$slike;
            if($slikePath!=$path){
                unlink($slikePath);
            }
                rmdir($path);
            }
        }
    }
}

But yes, it's better to do it in one query, like prodigitalson said:

$sql = "SELECT ms.modelSlikeVrijednost, m.modelID FROM model m, modelSlike ms
        WHERE ms.modelID= m.modelID AND m.proizvodacID=?i";
$sarr = $db->getCol($sql, $id);
foreach($sarr as $silke) {
    if(!$slike)) {
       rmdir($path);
    } else {
        $slikePath="/home/mainSite/public_html/site/".$slike;
        if($slikePath!=$path){
            unlink($slikePath);
        }
            rmdir($path);
        }
    }
}

The main idea is to get your data already from the query and then use it.

+1 for recommending a helper class. The Mysqli API cant be laborious to use (which is one of the reasons i prefer PDO) so a helper that abstracts some of the craziness away is good. — prodigitalson, Feb 20 '13 at 14:38
Good to know that there is a helper class for this issue. Will definitely use it next time. — Vucko, Feb 20 '13 at 14:51

PHP MySQL prepared statement in a prepared statement

2 Answers2