Tavish Tavish - 1 year ago 81
MySQL Question

mysql REPLACE INTO table with hundreds of columns

I have a table with hundreds of columns. The table structure is out of my control (controlled by a third party). The table also has horrendous field names with spaces, single quotes, etc. and so do the table values. The table is updated once per hour via cron. The cron job truncates and rebuilds the table each time. I also keep an archive table of that table, that I use a REPLACE INTO statement to update or insert as required.

My challenge - I prefer not to have to explicitly define all 350 field names and values, and do so again in my REPLACE INTO statement as this will take a very long time and will require maintenance if the table changes. I would much rather use arrays. Here is what is not working but hopefully gives an idea of the goal (I realize this is deprecated MySQL but it is what it is for a variety of reasons):

$listings = mysql_query("SELECT * FROM current.table");

while ($listing = mysql_fetch_assoc($listings)){

//prepare variables

$fields = array_keys($listing);
$fields = implode('`, `', $fields);
$fields = "`$fields`";

$values = array_values($listing);
$values = implode("`, `", $values);
$values = "`$values`";

mysql_query('REPLACE INTO archive.table ($fields) VALUES ($values)');


Answer Source

Posting as a community wiki, no rep should come from this since it did solve the OP's question (as per suggested in comments).

"Aha! Erroneous single quotes on the mysql_query statement was the culprit. I also did mysql_real_escape_string on $values and used single quotes instead of ticks. Worked like a charm. Thank you! Final answer: – Tavish"

Use mysql_error() on the queries. What you posted seems legit code, however values needs to be quoted ' and escaped for possible injection, not ticked. While using double quotes " for the second query's encapsulation.

mysql_query("REPLACE INTO archive.table ($fields) VALUES ($values)");

As well as other suggestions given.

OP's final code (taken from comments):

while ($listing = mysql_fetch_assoc($listings)){ 
    $fields = array_keys($listing); 
    $fields = implode(', ', $fields); 
    $fields = "$fields"; 
    $values = array_values($listing); 
    $values = implode(", ", $values); 
    $values = mysql_real_escape_string($values); 
    $values = str_replace("`","'",$values); 
    $values = "'$values'"; 
    mysql_query("REPLACE INTO archive.table ($fields) VALUES ($values)"); 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download