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
– Jeff Hawthorne, Commented Feb 20, 2013 at 14:13
@JeffHawthorne: Not when one is using mysqli::bind_result which binds a column value directly to a PHP variable. — prodigitalson
– prodigitalson, Commented Feb 20, 2013 at 14:39
@prodigitalson so if you had a query that returned multiple rows, would it throw an error? — Jeff Hawthorne
– Jeff Hawthorne, Commented Feb 20, 2013 at 14:42
@JeffHawthorne: No, it binds the variable to the column value from the current row everytime mysqil_stmt::fetch is called. — prodigitalson
– prodigitalson, Commented Feb 20, 2013 at 14:44

prodigitalson · Accepted Answer · 2013-02-20 14:31:24Z

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, 2013 at 14:31

answered Feb 20, 2013 at 14:12

prodigitalson

60.6k10 gold badges106 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Vucko Over a year ago

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.

prodigitalson Over a year ago

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 Over a year ago

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.

Vucko Over a year ago

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 ?

prodigitalson Over a year ago

Can you create another question with the schema for modelSlike and model along with an explanation of the relationship and what you want deleted?

|

Your Common Sense · Accepted Answer · 2013-02-20 14:32:40Z

2

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.

edited Feb 20, 2013 at 14:32

answered Feb 20, 2013 at 14:23

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

2 Comments

prodigitalson Over a year ago

+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.

Vucko Over a year ago

Good to know that there is a helper class for this issue. Will definitely use it next time.

Collectives™ on Stack Overflow

PHP MySQL prepared statement in a prepared statement

2 Answers 2

6 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related