Can I use variables to build variable names in SQL statements using PHP?

Question

I am trying to make a PHP for loop to insert into a table. I would like to build my variable names dynamically using variables and concatenation inside the SQL statement.

    $item1Name = 'blue';
    $item2Name = 'red';
    $item3Name = 'green';
    $item4Name = 'orange';
    $item5Name = 'yellow';

    for ($x=1;$x<5;$x++){   
    $insertItem = "insert into tblInvoiceItems (invoiceItemID,invoiceID,item".$x."Name) values";
    $insertItem .= "('','','".$item.$x."Name')";
    runQuery($insertItem,$page);
    }

The first line seems to work, but the second one is not.

I am trying to dynamically build these queries:

    insert into tbl (invoiceItemID,invoiceID,item1Name) values('','','blue')
    insert into tbl (invoiceItemID,invoiceID,item2Name) values('','','red')
    insert into tbl (invoiceItemID,invoiceID,item3Name) values('','','green')
    insert into tbl (invoiceItemID,invoiceID,item4Name) values('','','orange')
    insert into tbl (invoiceItemID,invoiceID,item5Name) values('','','yellow')

Thanks

[Variable variables](http://php.net/manual/en/language.variables.variable.php) — FirstOne, Jun 30 '16 at 17:09
what that mean? Show us current variable value and expected result. Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. — Juan Carlos Oropeza, Jun 30 '16 at 17:09
if you build also like that what the use? what value those variables have? — Alive to die - Anant, Jun 30 '16 at 17:11
Thanks Juan, I read both of those pages before I posted. I don't think that is what I am looking for. — Reed, Jun 30 '16 at 17:14
eval ("$item".$x."Name") will return the value of actual variable "$item1Name" — Tin Tran, Jun 30 '16 at 17:24
the thing is if he have `$item1Name` available then why not directly to use that? — Alive to die - Anant, Jun 30 '16 at 17:25
it doesn't look like he has that..he's building it from the loop variables — Tin Tran, Jun 30 '16 at 17:26
looks like array can be used here but i am just telling him of how to evaluate variable names if he's manually trying to build it — Tin Tran, Jun 30 '16 at 17:28
When comment use `@` like `@Juan` so user get a notification message. That page wont solve your question the idea is you learn how make a better question so you get more answers. — Juan Carlos Oropeza, Jun 30 '16 at 19:16

Maksym Semenykhin · Answer 1 · 2016-07-01T05:06:16.767

0

Best way to do this is to use array as values storage. Keen in mind that keys ia array begin from 0 not 1.

is that what you wanted DEMO LINK ?

php code:

<?php

$values = [
    'blue',
    'red',
    'green',
    'orange',
];

$count = count($values);
for ($x=0;$x<$count;$x++){

    $insertItem = "insert into tbl (invoiceItemID,invoiceID,item".$x."Name) values";
    $insertItem .= "('','','".$values[$x] ."')";
    echo $insertItem ."\n"; 

}

result:

insert into tbl (invoiceItemID,invoiceID,item0Name) values('','','blue')
insert into tbl (invoiceItemID,invoiceID,item1Name) values('','','red')
insert into tbl (invoiceItemID,invoiceID,item2Name) values('','','green')
insert into tbl (invoiceItemID,invoiceID,item3Name) values('','','orange')

edited Jul 01 '16 at 05:06

answered Jun 30 '16 at 17:57

Maksym Semenykhin

1,924
15
26

This answer was the most helpful. I have revised my question to better illustrate what I am trying to accomplish. Thanks Семенихин Максим – Reed Jun 30 '16 at 20:46
ok, i got what you mean. Look at code and result again – Maksym Semenykhin Jul 01 '16 at 05:07

score 0 · Answer 2 · answered Jul 01 '16 at 05:20

If I understand correct you need to use this ,

$item1Name = 'blue';
$item2Name = 'red';
$item3Name = 'green';
$item4Name = 'orange';
$item5Name = 'yellow';

for ($x=1;$x<5;$x++){   
$insertItem = "insert into tblInvoiceItems (invoiceItemID,invoiceID,item".$x."Name) values";
$insertItem .= "('','','${'item'.$x.'Name'}')";
runQuery($insertItem,$page);
}

Waldir J. Pereira Junior · Answer 3 · 2016-07-01T09:54:10.393

-1

You need to use variable variable in PHP. Try this code:

$item1Name = 'blue';
$item2Name = 'red';
$item3Name = 'green';
$item4Name = 'orange';
$item5Name = 'yellow';

for ($x=1;$x<5;$x++){   
    $insertItem = "insert into tblInvoiceItems (invoiceItemID,invoiceID,item".$x."Name) values";
    $varName = "item".$x."Name";
    $insertItem .= "('','','".$$varName."')";
    runQuery($insertItem,$page);
}

edited Jul 01 '16 at 09:54

answered Jun 30 '16 at 17:41

Waldir J. Pereira Junior

826
1
10
17

you were error in your code – Maksym Semenykhin Jul 01 '16 at 05:10
Edited. I wrote on my phone. Sorry. But the intention was give you the idea. – Waldir J. Pereira Junior Jul 01 '16 at 05:48
still has errors=) – Maksym Semenykhin Jul 01 '16 at 05:55
ok.. I edited with the entire code. Now it's ok. – Waldir J. Pereira Junior Jul 01 '16 at 09:55

Can I use variables to build variable names in SQL statements using PHP?

3 Answers3