dynamic binding not working(possible?)

Yii 1.1 General Discussion
1

Hi community,

I am trying to manage a dynamic binding but cannot get it to work. Here is the situation:

I take the metadata of a table and load it into a variable


$meta = Yii::app()->db->schema->getTable($table)->columnNames;

$lastID = count($meta)-1;

Next I dynamically create an SQL query:


foreach($meta as $n=>$field)

                     {

                         if($n == '0'){

                            $values .= $field.', ';

                            $placeholder .= ':'.$field.', ';

                         }elseif($n == $lastID){

                            $values .= $field.'';

                            $placeholder .= ':'.$field;

                        }else{

                            $values .= $field.', ';

                            $placeholder .= ':'.$field.', ';

                        }

                     }

                $sql = "INSERT INTO ".$table." (".$values.") VALUES(".$placeholder.")";

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

So far works like a charm but here is where it fails. I have csv lines that I would like to load into DB so I iterate each line with an explode and attempt to bind each value to the query above, however unsuccessfully:


for($x=0; $x<=count($lineDump)-1; $x++)

                    {  

                        $command->bindValue('":'.$meta[$x].'"', $lineDump[$x]);


                    }

The above code iterates 13 times but not the way I would expect it to.

$meta[] - is the array with the table’s metadata; $lineDump[] - array containing part of a csv line that needs to go into DB. This last part does not work and throws an error that I have declared incorrect number of parameters. When I go with hardcoding the params like this works fine:


$command->bindValue(':id', $lineDump[0]);

$command->bindValue(':field1', $lineDump[1]);

$command->bindValue(':field2', $lineDump[2]);

However, this is not an option as I would like to automate this. Is there a correct way to automate the value binding process?

Cheers,

bettor

2

Can you explain better this part… what is the error you get?

Have you checked that in $lineDump you have all required parameter that are in $meta

3

Hi and thanks for the response. Here is more data:

Sample data from the import.csv file:


1;1;46;910;38;27;5;6;103;32;71;86;0

2;2;46;910;38;21;7;10;67;41;26;70;0

3;3;46;910;38;27;4;7;86;28;58;85;0

$meta[] explained below:

$meta = Yii::app()->db->schema->getTable($table)->columnNames;

//$meta dump = Array ( [0] => id [1] => t_id [2] => l_id [3] => season [4] => pl [5] => ws [6] => ds [7] => ls [8] => gf [9] => ga [10] => diff [11] => pts [12] => deductpts ;

This is how I manage to get each value out of the csv file:


$handle = fopen('import.csv', "r");


while (($data = fgetcsv($handle, 1000, "\n")) !== FALSE)

{

   $lineDump = explode(';', $data[0]);


   //below I manage the bindValue for the query

   for($x=0; $x<=count($lineDump)-1; $x++)

   {

      $command->bindValue('":'.$meta[$x].'"', $lineDump[$x]);

   }

}

Now the below part doesn’t work


for($x=0; $x<=count($lineDump)-1; $x++)

   {

      $command->bindValue('":'.$meta[$x].'"', $lineDump[$x]);

   }

but the below works


for($x=0; $x<=count($lineDump)-1; $x++)

   {

                    $command->bindValue(':id', $lineDump[0]);

                    $command->bindValue(':t_id', $lineDump[1]);

                    $command->bindValue(':l_id', $lineDump[2]);

                    $command->bindValue(':season', $lineDump[3]);

                    $command->bindValue(':pl', $lineDump[4]);

                    $command->bindValue(':ws', $lineDump[5]);

                    $command->bindValue(':ds', $lineDump[6]);

                    $command->bindValue(':ls', $lineDump[7]);

                    $command->bindValue(':gf', $lineDump[8]);

                    $command->bindValue(':ga', $lineDump[9]);

                    $command->bindValue(':diff', $lineDump[10]);

                    $command->bindValue(':pts', $lineDump[11]);

                    $command->bindValue(':deductpts', $lineDump[12]);

   }

which makes me think that the dynamic binding I am trying to achieve either does not work or I am doing anything incorrectly.

Hope this additional info helps. BTW sorry for raising this in an inapropriate section it is meant to be raised in the Yii1.1.x section if you could move it it would be great. If more info is needed I will provide.

Cheers,

bettor

4

more on the error I’m getting:


CDbCommand failed to execute the SQL statement: SQLSTATE[HY093]: Invalid parameter number: parameter was not definedINSERT INTO grades_rank (id, t_id, l_id, season, pl, ws, ds, ls, gf, ga, diff, pts, deductpts) VALUES(:id, :t_id, :l_id, :season, :pl, :ws, :ds, :ls, :gf, :ga, :diff, :pts, :deductpts)

Source File


/home/domain/framework/db/CDbCommand.php(227)


00215:             $n=$this->_statement->rowCount();

00216: 

00217:             if($this->_connection->enableProfiling)

00218:                 Yii::endProfile('system.db.CDbCommand.execute('.$this->getText().')','system.db.CDbCommand.execute');

00219: 

00220:             return $n;

00221:         }

00222:         catch(Exception $e)

00223:         {

00224:             if($this->_connection->enableProfiling)

00225:                 Yii::endProfile('system.db.CDbCommand.execute('.$this->getText().')','system.db.CDbCommand.execute');

00226:             Yii::log('Error in executing SQL: '.$this->getText().$par,CLogger::LEVEL_ERROR,'system.db.CDbCommand');

00227: throw new CDbException(Yii::t('yii','CDbCommand failed to execute the SQL statement: {error}'.$this->text,
5

Note that this two code samples are not the same…

You need a for loop for the rows… and inside that another for loop for the fields…

Edit:

Why you use


$x<=count($lineDump)-1

Isn’t it more readable to use


$x<count($lineDump)
6

Note: Moved to Yii 1.1.x as requested

7

Hi mdomba,

I have the two loops. The above code is just a zoom of the inner loop but I have provided the outer loop in one of the previous comments. Anyway, it didn’t work either way but I found a work around…well it’s officially not work around but just another options. Instead of binding Values I just built an array within the loop and then pass it along with execute(). That did a perfect job. mdomba, I would like to thank you for your efforts to assist me but the time constraint on the project does not really allow me to fight this anymore. Thanks again. I still love Yii :P

Cheers,

bettor