How can I get the value of $id to the table creation SQL?
Posted: Sun Nov 09, 2003 4:24 pm
Hi all,
I have a database called RATINGS that is empty. I have another database called NEWS that has a table called ARCHIVES and is full of info, thousands of entries.
What I want to do is create a new TABLE in the RATINGS database for every entry in the ARCHIVES table.
ie:
1) parse news.archives in a loop, retrieving the newsID from every news story
2) in the same loop, place some create table code and use that newsID to name the tables, to ensure the news.id and the table name are identicle.
OK, good in theory, let's look at the code then I'll tell you what happens:
OK, the lil line echo "$id - "; is just a test to output the ID's as i parse thru the archives table. It works and all the ids get echo'd to screen. All good.
So I stick in the table creation code as you see it, and it creates ONE table, and rather than name it the contents of the variable $id it actually names it $id (as in, not the contents, but the actual name $id).
Now the question is this:
How can I get the value of $id to the table creation SQL?
I'm tearing hair out on this one...
Thanks so much for reading, I hope you have some brainwave or laugh at me
Rob
I have a database called RATINGS that is empty. I have another database called NEWS that has a table called ARCHIVES and is full of info, thousands of entries.
What I want to do is create a new TABLE in the RATINGS database for every entry in the ARCHIVES table.
ie:
1) parse news.archives in a loop, retrieving the newsID from every news story
2) in the same loop, place some create table code and use that newsID to name the tables, to ensure the news.id and the table name are identicle.
OK, good in theory, let's look at the code then I'll tell you what happens:
Code: Select all
$boxtoptext = "Creating RATINGS Database Tables...";
include("../skinned_box_bar.php");
echo "<br>connecting to ARCHIVES database...<br><br>";
$connection = mysql_connect($dbhost, $dbusername, $dbpassword) or die( mysql_error ());
mysql_select_db($dbname, $connection);
mysql_select_db($dbname);
$sql = "SELECT * FROM archives ORDER BY id ASC";
$content = mysql_query($sql);
$Xcontent = mysql_fetch_array($content);
$cShowMax = mysql_num_rows($content);
// process all entries
for ($y=1; $y<="$cShowMax"; $y++)
{
$id = $Xcontent["id"];
//connect to the ratings database and create the tables
mysql_select_db($dbnameratings, $connection);
//create the actual tables
//create the actual tables
$sqlcreate ='CREATE TABLE $id (
`id` INT NOT NULL AUTO_INCREMENT,
`userid` INT,
`rating` INT NOT NULL,
PRIMARY KEY ( `id` )
)' ;
mysql_query ($sqlcreate ,$connection );
//end of table creation code
//end of table creation code
echo "$id - ";
$Xcontent = mysql_fetch_array($content);
}
mysql_free_result($content);So I stick in the table creation code as you see it, and it creates ONE table, and rather than name it the contents of the variable $id it actually names it $id (as in, not the contents, but the actual name $id).
Now the question is this:
How can I get the value of $id to the table creation SQL?
I'm tearing hair out on this one...
Thanks so much for reading, I hope you have some brainwave or laugh at me
Rob