[SQL Server]Incorrect syntax near table

Question

I want to execute one and the same sql statement for a series of tables e.g. 37 tables.

For the table object name of each queried table I want to use a php variable named '$table'. The object names of the tables are provided in an included php file 'tables.php'.

The variable '$table' is generated repetitively from a concatenation of the string '$table' and an array '$numbers' for each table number, and put into the statement.

SQL reads the generated variable e.g. '$table1'. But I get an error from SQL Server for the FROM clause:

[SQL Server]Incorrect syntax near '$table1'.

I put the variable '$table' in brackets and quotation marks but it did not help.

Please help!

//php script one, 'tables.php':

$table1 = 'myTable1';
$table2 = 'myTable2';
...
$table37 = 'myTable37';

//php script two:

include_once('tables.php');

$numbers = range(1,37);

foreach($numbers as $number) {

    $table = '$table' . $number;

    $stmt = $db_conn->prepare("SELECT * FROM $table;");
    $stmt->execute();
}

This is the solution provided by Hasan. The magic is to put the concat for variable '$table' in curly brackets led by $:

include_once('tables.php'); //provides table object names for variables $table1, $table2, etc., e.g. $table1 = 'mytable1_in_database';

//first number of a closed range of variables for tables to be queried
$i = 1; 

//last number of closed range of variables for tables to be queried
$j = 37;

for($i=1; $i<=$j; $i++) {

    $table = ${'table' . $i};

    $stmt = $db_conn->prepare("SELECT * FROM $table;");
    $stmt->execute();
}

Simpler to make an array of table names and foreach over that — RiggsFolly, Oct 02 '18 at 12:38
Are you want to use variable variables - something like that: $variable = 'table'.$number; $table = $$variable; ? — Zhorov, Oct 02 '18 at 12:46
Don't know if I understand correctly: No, it is not used. I can 'echo' the variable '$table1' at any step of the script and it returns 'myTable1'. — Mich, Oct 02 '18 at 12:48
@RiggsFolly That is what I want to avoid: Make a second list of tables at another place of my script. I have already a list of tables in file 'tables.php'. — Mich, Oct 02 '18 at 12:52
You may try to replace $table = '$table' . $number; with $variable = 'table'.$number; $table = $$variable; — Zhorov, Oct 02 '18 at 12:56

Teoman Tıngır · Accepted Answer · 2018-10-02T13:18:06.367

1

You're getting error because you're using single quotes and variables can't interpreted, you may use double quotes or choose my below approach

$i = 1;
foreach($numbers as $number) {
    $table = 'myTable' . $i;
    //$table = "${'table' . $i}"; use it if you have already defined variables

    $stmt = $db_conn->prepare("SELECT * FROM $table;");
    $stmt->execute();
$i++;
if($i == 38) break;
}

As the question is now on Hold, hope you dont mind me adding a suggestion in your answer its to long for a comment.

This shoudl get you where you want to be, I hope :)

foreach($numbers as $number) {

    $t = '$table' . $number;
    $table = $t;
    $stmt = $db_conn->prepare("SELECT * FROM $table");
    $stmt->execute();
}

edited Oct 02 '18 at 13:18

answered Oct 02 '18 at 12:25

Teoman Tıngır

2,766
2
21
41

In the for loop that wont do what you think! As it will reuse the previous value of `$table` and get in a mess – RiggsFolly Oct 02 '18 at 12:37
@RiggsFolly yeah you're right, I updated my question – Teoman Tıngır Oct 02 '18 at 12:51
1

Now I expect the OP wont like you using the table name hardcoded in the string, as I would guess they are not named quite so simply in his real code. Think he want to be able to use the already defined `$table1` and `$table2` etc etc varibales – RiggsFolly Oct 02 '18 at 12:55
@RiggsFolly Yes, I want to use the already prepared long list of variables in my included file, and do not want to create a copy of that list. – Mich Oct 02 '18 at 13:00
@RiggsFolly it's hard to give certain answer without seeing his real code, I just explained his mistakes and show new approach. If he have defined variable, he may use variable variables then.. – Teoman Tıngır Oct 02 '18 at 13:01
@ Hasan Tingir Thank a lot for your idea, but that won't do the job, as RiggsFoley said: I want to keep my already prepared variables '$table1', '$table2', etc. Strange to me is, usually I can use the variable '$table1' in a sql statement, but in this case I get an error: When I put in '$table, which is '$table1', which is 'myTable1'. – Mich Oct 02 '18 at 13:04
@Mich hey mich, I added new line to my answer. Use it if you're already defined your tables as variables – Teoman Tıngır Oct 02 '18 at 13:04
Forgive me for messing with your answer, I hope it does not upset you – RiggsFolly Oct 02 '18 at 13:06
@RiggsFolly if it helps op, of course it doesn't bother me :) But it's similar with commented line in my anwer I think – Teoman Tıngır Oct 02 '18 at 13:08
@Hasan Tingir Thank you so much, Hasan! The statement succeded. Your first corrected answer was right and worked. The second returned the known error: ][SQL Server]Incorrect syntax near '$table1'. – Mich Oct 02 '18 at 13:14
@Riggs Foley Thank you for your time and brain. – Mich Oct 02 '18 at 13:15
@Zhorov Thank you for your time and brain, too. – Mich Oct 02 '18 at 13:15
@Mich ops it should be inside of double quotes but you can directly use it inside of your sql – Teoman Tıngır Oct 02 '18 at 13:18
well actually it returns string already, it shouldn't throw an error.. weird.. – Teoman Tıngır Oct 02 '18 at 13:28
@Hasan Tingir No, it worked fine this way: '$table = "${'table' . $i}";' and I put the variable '$table' in the statement (without quotes). Again, thank you very much. – Mich Oct 02 '18 at 13:36

[SQL Server]Incorrect syntax near table

1 Answers1