Yii Framework Forum: dynamic binding not working(possible?) - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

dynamic binding not working(possible?) 1.1.0 Rate Topic: -----

#1 User is offline   bettor 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 751
  • Joined: 02-February 09

Posted 27 January 2011 - 04:44 PM

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
0

#2 User is offline   Maurizio Domba Cerin 

  • Yii - Yesss It Is !!!
  • Yii
  • Group: Yii Dev Team
  • Posts: 4,346
  • Joined: 12-October 09
  • Location:Croatia

Posted 28 January 2011 - 02:50 AM

View Postbettor, on 27 January 2011 - 04:44 PM, said:

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


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
Find more about me.... btw. Do you know your WAN IP?
0

#3 User is offline   bettor 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 751
  • Joined: 02-February 09

Posted 28 January 2011 - 05:34 AM

View Postmdomba, on 28 January 2011 - 02:50 AM, said:

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


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
0

#4 User is offline   bettor 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 751
  • Joined: 02-February 09

Posted 28 January 2011 - 05:39 AM

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, 

0

#5 User is offline   Maurizio Domba Cerin 

  • Yii - Yesss It Is !!!
  • Yii
  • Group: Yii Dev Team
  • Posts: 4,346
  • Joined: 12-October 09
  • Location:Croatia

Posted 28 January 2011 - 05:57 AM

View Postbettor, on 28 January 2011 - 05:34 AM, said:

...
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]);
   }

...


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)

Find more about me.... btw. Do you know your WAN IP?
0

#6 User is offline   Maurizio Domba Cerin 

  • Yii - Yesss It Is !!!
  • Yii
  • Group: Yii Dev Team
  • Posts: 4,346
  • Joined: 12-October 09
  • Location:Croatia

Posted 28 January 2011 - 06:48 AM

Note: Moved to Yii 1.1.x as requested
Find more about me.... btw. Do you know your WAN IP?
0

#7 User is offline   bettor 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 751
  • Joined: 02-February 09

Posted 29 January 2011 - 01:27 AM

View Postmdomba, on 28 January 2011 - 05:57 AM, said:

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)



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
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users