MySQL Copying

[ackerley1]

I want to know how I would go about copying data from one ID to another. I have a hotel booking program with packages. Now the packages are all the same for each hotel room (1 or 2 bedroom) with a slightly different price. But to do this through the admin would take me hours. I would like to know if there is a way to do this through MySQL directly.

Load All Packages for PID 1 through Admin area.

Copy all Packages from PID 1 to PID 2, 3 and 4.

Then I can go in either through MySQL or the Admin area and adjust the price and conditions.

Any help would be appreciated.

Thanks

[Jermooski]

It sounds like there is time involved whether you enter through the admin area, or going through the SQL mngmnt angle. Of course you "copy" the data to existing PID's. You will have
to know the SQL lang... which is a very useful tool if you're managing any kind of dynamic website.

Steps involved:
You can use the mysql command line interface or a GUI tool. I prefer the Ems Hi Tech sqlmanager dot net or SQLyog is pretty cool. You will have to be very familiar with
the logic involved.. there may be many tables to update at once. For now... just learn about the UPDATE and WHERE statement... do this using/creating temp tables.

Get a good book on SQL... SQL in 24 hours? Not really sure.

Good Luck,
Jer.

[niggles]

Hi,

Roughly you could do it like this (I haven't checked the code, so it might need tweaking + ALWAYS backup the entire database before doing anything like this to it).

$thisDescription = "The text you want to insert";

$numberOfItemsToInsert = 10;

for($i=0;$i<$numberOfItemsToInsert;$i++){

$query = mysql_query("INSERT INTO mytable (`pid`, `description`) VALUES ('', '$thisDescription')");

}

But that is the basic idea - grab the description you want, put it into $thisDescription, then loop through the number of times you want to insert it into the database.

You need to know a bit of PHP to do the above i.e connecting to the database, but that's the basic premise of doing it without needing to do it by hand.

Cheers,
Niggles

[Clay Martin]

One solution that does not involve programming and assumes this is one table is as follows. Go to the admin area of your SQL system (like phpMySql) and export your table. Use the options of structure and data and drop table if it exists. Download the export to your pc (its a text file with a sql extension). Then copy all the insert statements with pid=1, paste below the copied block then use a text editor to change all the values in the pid column from 1 to 2. Repeat for 3, 4, etc. NOTE, the last insert (in the original) will not end in a comma, so you will have to correct this in all but your last copied block.

Then go back to your admin and execute a sql command (or import). Just paste your modified exported contents into the sql text area and run the query.

Note you will want to save a copy of the original export so that if you make a mistake you can restore it.

HTH,
Clay