inner join or double array?!?

how to take results from two tables and parse them into one table

[Mitch888]

I have the following two tables (cc & paypal). I need to parse them into the third table called 'temp' like demonstrated below. please help... pleeeeese

Table cc
-------------------------
id ¦ date ¦amount¦
-------------------------
1 ¦2003-04-10¦ 4 ¦
-------------------------
2 ¦2003-04-05¦ 2 ¦
-------------------------
3 ¦2003-04-03¦ 6 ¦
-------------------------

Table paypal
-------------------------
id ¦ date ¦amount¦
-------------------------
1 ¦2003-04-09¦ 8 ¦
-------------------------
2 ¦2003-04-05¦ 4 ¦
-------------------------

What is the code to populate the below table?
Table temp
--------------------------------
id ¦ date ¦ cc ¦paypal¦
--------------------------------
1 ¦2003-04-03¦ 6 ¦ 0 ¦
--------------------------------
2 ¦2003-04-05¦ 2 ¦ 4 ¦
--------------------------------
3 ¦2003-04-09¦ 0 ¦ 8 ¦
--------------------------------
4 ¦2003-04-10¦ 4 ¦ 0 ¦
--------------------------------

[jatar_k]

Are you wanting to do this only once and then keep the table together or are you looking to have this be a common event?

If you are doing this once and then maintaining them in a single table (which sounds like a good idea) I would get all of the info from one table and insert it into the new one. Then get the info from the second, check to see if the date exists, if it does update the appropriate col or insert a new row if it doesn't.

otherwise it goes back to th original problem
[webmasterworld.com...]

[Mitch888]

you are an angel if you can tell me how I can how to insert a new row if it does not?

I did exactly what you suggested:
I got all of the data from table “CC” and insert it into the table “temp”. Then got the info from the table “paypal”, I checked to see if the date exists in table “temp”, if it did, I updated the appropriate col; how do I insert a new row if it doesn't exist?!?!?.

I spent three long nights trying to figure it out. Please help.

[jatar_k]

if it is a one time data conversion then it doesn't matter how intensive this process is. This is pretty intensive so you wouldn't want to do it very often.

$sql = "select * from paypal";
$query = mysql_query($sql);
while ($row = mysql_fetch_array($query)) {
$sql2 = "select * from paypal where date='" . $row['date'] . "'";
$query2 = mysql_query($sql2);
if (mysql_num_rows($query2) > 0) {
$sql3 = "update temp set paypal='" . $row['amount'] . "' where date='" . $row['date'] . "'";
} else {
$sql3 = "insert into temp values('','" . $row['date'] . "',0," . $row['amount'] . ")";
}
$query3 = mysql_query($sql3);
}

this assumes, for the insert, that id is an auto_increment primary key and that if row doesn't exist cc=0 and that cc and paypal are int cols. If cc and paypal are not int then use this insert

$sql3 = "insert into temp values('','" . $row['date'] . "','0','" . $row['amount'] . "')";

think taht'a all right. ;)

[Mitch888]

Will try it, if it works, I will pray for you. You saved me lots of sleepless nights. God bless you.

Thank you again :)