Converting previous website code to YII framework code for images data record

Thanks guys for this useful resources. I have this code for inserting the image information in a database from my previous website and am converting it to to use Yii. as part of uploading the files and getting the data to insert in the database the code works fine only my loop does not give the right values for one column name which is main_image which should be set to 1 or 0 based on the condition from in the if loop. Here is the previous code.




<?php 

//see if listing already has main image set if not set image as main

$sql = "SELECT COUNT(*) FROM listimages WHERE listingid = " . $listingid . " AND  mainimage =1";

$res = sqlQuery($sql); if(sqlErrorReturn())   sqlDebug(__FILE__,__LINE__,sqlErrorReturn());

$num = sqlFetchRow($res);


    if ($num['0'] > 0) {

    if (!empty($save_path)) {

        $sql = "INSERT INTO listimages (imagepath,imagethumbpath,mainimage,listingid,owner,approved) VALUES ('" . $save_path . "','" . $save_thumb_path . "',0, " . $listingid . ", '" . $owner . "', " . $approved . ")";

        $res = sqlQuery($sql); if(sqlErrorReturn()) sqlDebug(__FILE__,__LINE__,sqlErrorReturn());

        return 99;

    }

} else {

    if (!empty($save_path)) {

        $sql = "INSERT INTO listimages (imagepath,imagethumbpath,mainimage,listingid,owner,approved) VALUES ('" . $save_path . "','" . $save_thumb_path . "',1, " . $listingid . ", '" . $owner . "', " . $approved . ")";

        $res = sqlQuery($sql); if(sqlErrorReturn()) sqlDebug(__FILE__,__LINE__,sqlErrorReturn());

        return 99;

    }

}

?>



And here is my Yii code am using from the above code





<?php 

/See if listing already has main_image set if not set image as main

             $sql = "SELECT COUNT(*) FROM {{auto_listing_images}} WHERE listing_id = :listingId AND main_image = :mainImage";

             $command = Yii::app()->db->createCommand($sql);

             $command->bindValue(":listingId", $listing_id, PDO::PARAM_INT);

             $command->bindValue(":mainImage", 1, PDO::PARAM_INT);

             $result = $command->queryAll();

              foreach($result as $num){

             //write image file on the database with respect to number

             if(isset($num['0']) > 0) {

               if(!empty($save_path)){

             $sql = "INSERT INTO {{auto_listing_images}} (image_path, image_thumb_path, main_image, listing_id, create_time, author_id)

                                 VALUES(:imagePath, :imageThumbPath, :mainImage, :listingId, :createTime, :authorId)";

             $command = Yii::app()->db->createCommand($sql);

             $command->bindValue(":imagePath", $save_path, PDO::PARAM_STR);

             $command->bindValue(":imageThumbPath", $thumbDestName, PDO::PARAM_STR);

             $command->bindValue(":mainImage", 0, PDO::PARAM_INT);

             $command->bindValue(":listingId", $listing_id, PDO::PARAM_INT);

             $command->bindValue(":createTime", time(), PDO::PARAM_INT);

             $command->bindValue(":authorId", Yii::app()->user->id, PDO::PARAM_INT);

             return $command->execute();

                 }

             } else {

                if(!empty($save_path))

                {

             //write image file on the database

             $sql = "INSERT INTO {{auto_listing_images}} (image_path, image_thumb_path, main_image, listing_id, create_time, author_id)

                                 VALUES(:imagePath, :imageThumbPath, :mainImage, :listingId, :createTime, :authorId)";

             $command = Yii::app()->db->createCommand($sql);

             $command->bindValue(":imagePath", $save_path, PDO::PARAM_STR);

             $command->bindValue(":imageThumbPath", $thumbDestName, PDO::PARAM_STR);

             $command->bindValue(":mainImage", 1, PDO::PARAM_INT);

             $command->bindValue(":listingId", $listing_id, PDO::PARAM_INT);

             $command->bindValue(":createTime", time(), PDO::PARAM_INT);

             $command->bindValue(":authorId", Yii::app()->user->id, PDO::PARAM_INT);

             $command->execute();

                }

           

             }

            }

?>



Am just confused as with the previous query assign query to $res variable then fetch the row as this is called return record/result set in mysql. in yii i thought it will be queryAll then just use foreach to go through each row. Please help as the way my code is and I try to run it always insert 1 in the main_image whatever how many times I run it. Again is only the main_image section not being inserted correctly with the condition.

Ok I have changed




<?php

//See if listing already has main_image set if not set image as main

         $sql = "SELECT COUNT(*) AS mainImage FROM {{auto_listing_images}} WHERE listing_id = :listingId AND main_image = :mainImage";

         $command = Yii::app()->db->createCommand($sql);

         $command->bindValue(":listingId", $listing_id, PDO::PARAM_INT);

         $command->bindValue(":mainImage", 1, PDO::PARAM_INT);

         $result = $command->queryAll();

         $num = (int)$result[0]["mainImage"];




And elimited the foreach loop




<?php

//write image file on the database with respect to number

         if($num > 0) {

           if(!empty($save_path)){

         $sql = "INSERT INTO {{auto_listing_images}} (image_path, image_thumb_path, main_image, listing_id, create_time, author_id)

                             VALUES(:imagePath, :imageThumbPath, :mainImage, :listingId, :createTime, :authorId)";

         $command = Yii::app()->db->createCommand($sql);

         $command->bindValue(":imagePath", $save_path, PDO::PARAM_STR);

         $command->bindValue(":imageThumbPath", $thumbDestName, PDO::PARAM_STR);

         $command->bindValue(":mainImage", 0, PDO::PARAM_INT);

         $command->bindValue(":listingId", $listing_id, PDO::PARAM_INT);

         $command->bindValue(":createTime", time(), PDO::PARAM_INT);

         $command->bindValue(":authorId", Yii::app()->user->id, PDO::PARAM_INT);

         return $command->execute();

             }

         } else {

            if(!empty($save_path))

            {

         //write image file on the database

         $sql = "INSERT INTO {{auto_listing_images}} (image_path, image_thumb_path, main_image, listing_id, create_time, author_id)

                             VALUES(:imagePath, :imageThumbPath, :mainImage, :listingId, :createTime, :authorId)";

         $command = Yii::app()->db->createCommand($sql);

         $command->bindValue(":imagePath", $save_path, PDO::PARAM_STR);

         $command->bindValue(":imageThumbPath", $thumbDestName, PDO::PARAM_STR);

         $command->bindValue(":mainImage", 1, PDO::PARAM_INT);

         $command->bindValue(":listingId", $listing_id, PDO::PARAM_INT);

         $command->bindValue(":createTime", time(), PDO::PARAM_INT);

         $command->bindValue(":authorId", Yii::app()->user->id, PDO::PARAM_INT);

         $command->execute();




But the problem is when I select 2 images only 1 image record is inserted with one image uploaded. Same if I select 4 two are uploaded and two records inserted