Moving data between 2 tables

[adammc]

Hi folks,

I was hoping someone could help me with this, Im a bit stuck :(

How would I go about doing the following:

table name - jobalert
Username field- uname
Original data - jobcategory

Copy contents of jobcategory into table below (jobcategory row)

table name - job_seeker_info
Username field - uname
Row where I need the data from "jobalert" table copied into - jobcategory

[jatar_k]

you could look at mysqldump or you could use a small script to pull a row and insert it into the other table in a loop.

[adammc]

jatar_k,
thanks for your reply :)

Can you point me in the write direction on how to begin writing the code using a loop?

[jatar_k]

the steps are fairly straight forward

1. connect to db
2. select db
3. select first row from table1
4. insert data into table2
5. repeat 3 and 4 until 3 returns nothing

things to keep in mind

you need a way to track what has been done already, if the script dies you don't really want to start over.

also you need error handling. If an insert doesn't work then you need to get the info about which row(s) it was. You also need to decide whether you want it to die on error or log it and continue, leaving you to deal with a log of errors when it is done.

[adammc]

Thanks for the tips :)

This is waht I came up with, but it didnt work, no errors happenned & nothing was changed in the database?

I had to do it like this because there are some muliple entries for usernames in the jobalert table:

[php]
# connect to database
$cid = mysql_connect($host,$usr,$pwd);
if (!$cid) { echo("ERROR: " . mysql_error() . "\n"); }

mysql_select_db($db);

// Show errors, if any
ini_set ('display_errors', 1);
error_reporting (E_ALL & ~E_NOTICE);

# start script

$names = array();

$results = mysql_query("SELECT uname FROM jobalert");

while($record = mysql_fetch_assoc($results)){

array_push($names,$record['uname']);

}

foreach($names as $nameNum=>$name){

mysql_query('INSERT INTO job_seeker_info (jobcategory)

(SELECT jobcategory

FROM jobalert

WHERE uname="' . $name . '" LIMIT 1)

ON DUPLICATE KEY UPDATE job_seeker_info SET jobcategory=

(SELECT jobcategory

FROM jobalert

WHERE uname="' . $name . '"

LIMIT 1)');

}

?>
[/php]

[Elixr]

I would use something like this:

 $get_names = "select distinct(uname) FROM jobalert";
 $get_names_res = mysql_query($get_names) or die(mysql_error());
 while($returnArray = mysql_fetch_array($get_names_res)){
 $uname = $returnArray['uname'];
  $insert_name = "insert into job_seeker_info values ('$uname');
 mysql_query($insert_name) or die(mysql_error());
 }

using the distinct function in mysql will return only 1 of each name if there are duplicates of the same name. Also using the "or die(mysql_error())" is helpful because php will display any errors that mysql returned when executing your query.

[adammc]

Hi Elixer,

Thanks for the reply.

I got the following error when using your suggested code :

Column count doesn't match value count at row 1

Any ideas?

[Elixr]

You got this error because there are more fields in your table than just uname;

$insert_name = "insert into job_seeker_info values ('$uname'); 

when you insert a row into your database you need to enter a value for each of the fields in the table.

Im not sure what your other fields are but you will need to include values for them too (or just '', if the field can except null values) seperated by commas

IE:

$insert_name = "insert into job_seeker_info values ('$uname','field2','field3');

From mysql you can do 'describe job_seeker_info;' which will give you the definitions for each column. If you paste that info in hear I might be able to give more specific help.