Im trying to populate a table using some other specific tables(there's a set of tables known as table_1,table_2,table_3 etc) in the same database by giving a date interval(to obtain rows which are recorded within a month). below is the code im using
<?php
include("functions.php");
connect();
$table_count = mysql_query("SELECT COUNT(*) AS cnt FROM information_schema.tables WHERE table_schema = 'mydb' AND table_name LIKE 'mytable_%'");
$row = mysql_fetch_assoc($table_count);
$loop_var = ($row["cnt"]);
for ($x=1; $x<=$loop_var; $x++) {
mysql_query("INSERT INTO stats (`useragent_browser`, `useragent_OS`, `timestamp`)
SELECT `useragent_browser`, `useragent_OS`, `timestamp` FROM table_'.$x.'
WHERE `timestamp` > DATE_ADD(NOW(), INTERVAL -1 MONTH)") or die(mysql_error());
}
?>
but at the moment this returns as errror saying
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''.1.' WHERE
timestamp> DATE_ADD(NOW(), INTERVAL -1 MONTH)' at line 2
can anybody help on me soughting this out
Try this:
mysql_query("INSERT INTO stats (`useragent_browser`, `useragent_OS`, `timestamp`)
SELECT `useragent_browser`, `useragent_OS`, `timestamp` FROM table_$x
WHERE `timestamp` > DATE_ADD(NOW(), INTERVAL -1 MONTH)") or die(mysql_error());
}
or
mysql_query("INSERT INTO stats (`useragent_browser`, `useragent_OS`, `timestamp`)
SELECT `useragent_browser`, `useragent_OS`, `timestamp` FROM table_".$x."
WHERE `timestamp` > DATE_ADD(NOW(), INTERVAL -1 MONTH)") or die(mysql_error());
}
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments