Creating a new table from an existing one - noobie

[DLR]

HI,

I want to creat a new table using partial dat from an existing table, but I cannot get my code to work.

 

Existing table = company_list

I have created the new table 'newlist' with a autoincrement column

 

I want to end up with a new table that holds a unique list of names (taken from company_list) and have a unique number assisgned to it (by auto increment).

 

$sql = "INSERT INTO newlist.co_name

SELECT company_list.company_name

FROM company_list ";

 

$res = mysql_query($sql) or die;

 

I get no records added to table  newlist

Thanks for the assistance.  :'(

[BMurtagh]

Hello,

 

You're going to want to use PHP and create a while loop after finding out the max # of rows of data in the DB table.

 

$sql = "SELECT * FROM <table>.<database>"     
$res = mysql_query($sql) or die; 

$num = mysql_numrows($res);

/* that should get you the # of rows stored in $res */ 

 

Next would be to create the loop to insert the data into the new table:

 

$i=0;
while ($i < $num) {

$sql = "INSERT INTO <table>.<database>
   SELECT <table>.<database>
   FROM <database>";

$res = mysql_query($sql) or die; 

$i++;
}

 

I have not had the ability to test the code as of right now, but this should at least get you on the right track for accomplishing this. Check this out and reply back if you have other questions

[gassaz]

Try this:

 

CREATE TABLE new_table
SELECT company_list.company_name FROM company_list;

 

When the new table is created you can add the rest of the columna