Copy values from one table into another one
I am trying to make a select with a sum in it and group by account and copy the results in another table.
As the site has the 4.0.25 MySQL version i guess i cant make subconsults. I thought of storying the results in vectors, but up to now it didnt work, i´v done this:
<?php
$FECHAINICIO="";
$FECHAFINAL="";
$TOTALDIAS="";
$DATODEBE[]="";
$DATOTIPO[]="";
$DATOCRUB[]="";
$DATONOMR[]="";
$DATOCCUE[]="";
$DATONOMC[]="";
$DATOIMPO[]="";
$i="";
include("../public_html/phpform/global.inc.php");
$errors=0;
$error="The following errors occured while processing your form input.<ul>";
pt_register('POST','CConsorcio');
pt_register('POST','FCierre');
pt_register('POST','FCierreI');
pt_register('POST','FVencimiento');
if($CConsorcio=="" ){
$errors=1;
}
if($FCierre=="" ){
$errors=1;
}
if($FVencimiento=="" ){
$errors=1;
}
$error.="<li>You did not enter one or more of the required fields. Please go back and try again.";
if($errors==1) echo $error;
else{
$where_form_is="http".($HTTP_SERVER_VARS["HTTPS"]=="on"?"s":"")."://".$SERVER_NAME.strrev(strstr(strrev($PHP_SELF),"/"));
$link = mysql_connect("localhost","XXXXXXXXXXXX","XXXXXXXXXXXX");
mysql_select_db("XXXXXXXXXXXXXX",$link);
//I find the incial date
$query0="SELECT MAX(FCierre) as dias FROM CIERRES WHERE CConsorcio='".$CConsorcio."' GROUP BY CConsorcio ";
mysql_query($query0);
$result0 = mysql_query($query0) or die('Query failed: ' . mysql_error());;
while ($row = mysql_fetch_array($result0, MYSQL_ASSOC)) {
$FECHAINICIO= "{$row['dias']}";
}
// i copy the initial date, and the other dates in a table CIERRES
$query2="insert into CIERRES (`CConsorcio`,`FCierre`,`FCierreI`,`FVencimiento`) values ('".$CConsorcio."','".$FCierre."','".$FCierreI."','".$FVencimiento."')";
mysql_query($query2);
$query3="SELECT CConsorcio, MAX(FCierre)as FECHAINICIAL, FCierreI FROM CIERRES WHERE CConsorcio='".$CConsorcio."' GROUP BY CConsorcio ";
mysql_query($query3);
$result3 = mysql_query($query3) or die('Query failed: ' . mysql_error());
$FECHAFINAL='".$FCierre."';
// i try to make a selection of data and store it in Vectors.
$queryTR=" SELECT DebeHaber,TipoR,CRubro,NombreR,CCuenta,NombreC,sum(ImporteT)as submont FROM TRANSACCIONES WHERE CConsorcio='".$CConsorcio."' AND FechaI>'".$FECHAINICIO."' GROUP BY TipoR, CRubro, CCuenta, DebeHaber ";
mysql_query($queryTR);
$resultTR = mysql_query($queryTR) or die('Query failed: ' . mysql_error());
while ($row = mysql_fetch_array($resultTR, MYSQL_BOTH)) {
for ($i= 0 ; $i < 2 ; $i ++) {
$DATODEBE[$i]= "{$row['DebeHaber']}";
$DATOTIPO[$i]= "{$row['TipoR']}";
$DATOCRUB[$i]= "{$row['CRubro']}";
$DATONOMR[$i]= "{$row['NombreR']}";
$DATOCCUE[$i]= "{$row['CCuenta']}";
$DATONOMC[$i]= "{$row['NombreC']}";
$DATOIMPO[$i]= "{$row['submont']}";
}
}
//I try to copy the vectors into the table
for ($i= 0 ; $i < 2 ; $i ++) {
$queryTS="insert into SALDOS (`DebeHaber`,`CConsorcio`,`TipoR`,`CRubro`,`NombreR`,`CCuenta`,`NombreC`,`ImporteS`,`FechaS`) values ('$DATODEBE[$i]','".$CConsorcio."',$DATOTIPO[$i],$DATOCRUB[$i],$DATONOMR[$i],$DATOCCUE[$i],$DATONOMC[$i],$DATOIMPO[$i],'".$FCierre."')";
mysql_query($queryTS);
}
// Free resultset
mysql_free_result($result0);
mysql_free_result($result3);
}
?>
</body>
</html>