Truncate table in foreach breaking with table name 'order'

Question

I am trying to work with a script written by a previous developer that empties a number of different tables. The problem now is that I have added a table called 'order' and breaks the loop.

set_time_limit (0); 
$conn = mysql_connect('localhost', 'root', '') or die ('Error connecting to mysql');
mysql_select_db('database-name');

$tables = array(
    'address',
    'manufacturer',
    'order',
    'voucher_history',
    'voucher_theme',
);

foreach ($tables as $table) {
    $sql = sprintf('TRUNCATE TABLE %s', $table);
    printf('%s %s ', $sql, str_repeat('.', 73 - strlen($sql)));
    $result = mysql_query($sql);
    if (!$result) {
        die('Invalid query: ' . mysql_error());
    }else {
        echo "Done!<br />";
    }
}

Clodoaldo Neto · Accepted Answer · 2011-07-07T18:07:03.440

1

order is a reserved keyword so it be must enclosed in back quotes:

'`order`',

edited Jul 07 '11 at 18:07

answered Jul 07 '11 at 17:45

Clodoaldo Neto

108,856
25
211
247

can I add this into the sprintf()? – John Magnolia Jul 07 '11 at 17:55
1

@John You mean `sprintf('TRUNCATE TABLE \`%s\`', $table)`? Why not? – Clodoaldo Neto Jul 07 '11 at 18:04

score 0 · Answer 2 · answered Jul 07 '11 at 17:53

0

backquote your table names. Order is a reserved keyword and will cause your query to break

answered Jul 07 '11 at 17:53

Zak

24,446
11
37
67

Better yet, don't name your tables using keywords. – Michael Mior Jul 07 '11 at 17:55

Truncate table in foreach breaking with table name 'order'

2 Answers2