Page 1 of 1

How can I get the value of $id to the table creation SQL?

Posted: Sun Nov 09, 2003 4:24 pm
by robster
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:

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);
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

Posted: Sun Nov 09, 2003 4:56 pm
by volka
mysql_insert_id() might solve this problem.
But if I may ask: What do you need thounsands of tables for. Esp. if they all look alike? Wouldn't it be more reasonable to have table and use an id to bind records to a specific entry in the ARCHIVES table?

Re: How can I get the value of $id to the table creation SQL

Posted: Mon Nov 10, 2003 4:35 am
by Weirdan
robster wrote:

Code: Select all

$sqlcreate ='CREATE TABLE $id (
`id` INT NOT NULL AUTO_INCREMENT,
`userid` INT,
`rating` INT NOT NULL,
PRIMARY KEY ( `id` )
)' ;
   

mysql_query ($sqlcreate ,$connection );
Now the question is this: :)
How can I get the value of $id to the table creation SQL?
Enclose SQL query in double quotes:

Code: Select all

$sqlcreate ="CREATE TABLE $id (
`id` INT NOT NULL AUTO_INCREMENT,
`userid` INT,
`rating` INT NOT NULL,
PRIMARY KEY ( `id` )
)" ;

Posted: Wed Nov 12, 2003 2:33 pm
by robster
Thanks so much for the feedback there.

Just to answer a few things.

q) Why not use the BIND feature.
a) I really don't know how to use it nor find it. I'm quite confused at this php stuff. If you'd like to see what I've done so far you can visit my site at http://10secondclub.net It's a site for animators.

q) Why Use all the tables?
a) Because my MySQL and PHP skills aren't fabulous (yet ;)) and I have a lot of spare databases from my great host so I see this as a quick method that will still get the job done.

Basically I tried double quotes and that made the query not work at all.
I also tried to learn about mysql_insert_id() but it was WAY beyond my current skill level.

Is there anyone here who can help me with this?
I just need the $id of my table to be passed to the table create command (see my first questions in this thread).

There MUST be a way, and if the only way is with mysql_insert_id() then can somebody perhaps explain this a little to me? I really appreciate it :)

Rob