Member Avatar for bLuEmEzzy

Good day, Please help guys, I have records in which if I updated table1, I have to update table2.
This is my code for update table1(which is proja)

$sqlu = mysql_query("UPDATE proja SET 
        pname= '$par',
        amunt = '$amt',
        janua = '$jan',
        febru = '$feb',
        march = '$mar',
        april = '$apr',
        prmay = '$may',
        pjune = '$jun',
        pjuly = '$jul',
        augus = '$aug',
        septe = '$sep',
        octob = '$oct',
        novem = '$nov',
        decem = '$dec',
        balan = '$bal'
    WHERE proaid = $proaid
    ");

After I update proja, I will select tables(in which I will select the updated record of proja and other record based on projid)

if ($sqlu == true) 
{   //UPDATE connected tables(total, pstotal or mototal or cototal)

    $sqlb = mysql_query("SELECT * 
            FROM projb
            INNER JOIN proja ON projb.projid = proja.projid
            INNER JOIN parti ON proja.parid = parti.parid
            INNER JOIN pstotal ON proja.projid = pstotal.projid
            WHERE proja.projid = $projid
            AND parti.parid =$parid");

        if (!$sqlb){
            echo 'Could not run query:' .mysql_error();
            exit;
        }

        $row = mysql_fetch_row($sqlb);
        echo " <br> pstid <br>";
        $pstid = $row[0];

    //Add Subtotal for PS   

     if ($parid==1) //Personal Services
        {
            while($row = mysql_fetch_array($sqlb))
            {
                $psamt=$row['amunt'];
                $psjan=$row['janua'];
                $psfeb=$row['febru'];
                $psmar=$row['march'];
                $psapr=$row['april'];
                $psmay=$row['prmay'];
                $psjun=$row['pjune'];
                $psjul=$row['pjuly'];
                $psaug=$row['augus'];
                $pssep=$row['septe'];
                $psoct=$row['octob'];
                $psnov=$row['novem'];
                $psdec=$row['decem'];
                $psbal=$row['balan'];

            //Sum rows
                $totalpsamt += $psamt;
                $totalpsjan += $psjan;
                $totalpsfeb += $psfeb;
                $totalpsmar += $psmar;
                $totalpsapr += $psapr;
                $totalpsmay += $psmay;
                $totalpsjun += $psjun;
                $totalpsjul += $psjul;
                $totalpsaug += $psaug;
                $totalpssep += $pssep;
                $totalpsoct += $psoct;
                $totalpsnov += $psnov;
                $totalpsdec += $psdec;
                $totalpsbal += $psbal;

If I get the total then I will update pstotal

 //Update PSTOTAL 
                $pssql = mysql_query("UPDATE pstotal SET
                    psamt='$totalpsamt',
                    psjan='$totalpsjan',
                    psfeb='$totalpsfeb',
                    psmar='$totalpsmar',
                    psapr='$totalpsapr',
                    psmay='$totalpsmay',
                    psjun='$totalpsjun',
                    psjul='$totalpsjul',
                    psaug='$totalpsaug',
                    pssep='$totalpssep',
                    psoct='$totalpsoct',
                    psnov='$totalpsnov',
                    psdec='$totalpsdec',
                    psbal='$totalpsbal'
                    WHERE pstid = $pstid
                    ;");

HOWEVER, in my update pstotal, the last record in the for loop were saved, and the records did not sum up! Please help thank you :)

Edited by bLuEmEzzy
  • 5 Contributors
  • 8 Replies
  • 210 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by bLuEmEzzy
Member Avatar for LastMitch

@bLuEmEzzy

HOWEVER, in my update pstotal, the last record in the for loop were saved, and the records did not sum up! Please help thank you :)

What do you mean by last update? You mean this: psbal='$totalpsbal' no updating? There must be a mispell word or you left out a comma or period? The reason why if it runs the data then there must be a mispell word in the db or from your query.

Member Avatar for diafol

HOWEVER, in my update pstotal, the last record in the for loop were saved,

I can't see a for loop.

Member Avatar for bLuEmEzzy

what I mean in while loop... sorry for that :)

Edited by bLuEmEzzy
Member Avatar for diafol

Can't you just to a SUM on the first query?

$sqlb = mysql_query("SELECT SUM(projb.amunt) AS totalpsamt, SUM().... AS .... 
        FROM projb
        INNER JOIN proja ON projb.projid = proja.projid
        INNER JOIN parti ON proja.parid = parti.parid
        INNER JOIN pstotal ON proja.projid = pstotal.projid
        WHERE proja.projid = $projid
        AND parti.parid =$parid");

This way you stop all the looping.

Edited by diafol
Member Avatar for bLuEmEzzy

I did that, still, no improvement, maybe I have a mistake in my query, but when I look at it I can't identify why the records do not update. I look at it again. thank you ;)

Member Avatar for leviathan185

Also to add to diafol's comment, you could add a die() statement after your mysql_query()

$sqlb = mysql_query("SELECT SUM(projb.amunt) AS totalpsamt, SUM().... AS ....
        FROM projb
        INNER JOIN proja ON projb.projid = proja.projid
        INNER JOIN parti ON proja.parid = parti.parid
        INNER JOIN pstotal ON proja.projid = pstotal.projid
        WHERE proja.projid = $projid
        AND parti.parid =$parid") or die(mysql_error());

I usually construct my query into a variable $sql and that way if I get an SQL error I can output the error as well as the query to check it for errors.

$sql = "SELECT SUM(projb.amunt) AS totalpsamt, SUM().... AS ....
        FROM projb
        INNER JOIN proja ON projb.projid = proja.projid
        INNER JOIN parti ON proja.parid = parti.parid
        INNER JOIN pstotal ON proja.projid = pstotal.projid
        WHERE proja.projid = $projid
        AND parti.parid =$parid";

$sqlb = mysql_query($sql) or die(mysql_error()."<br />Query: ".$sql);
Member Avatar for faroukmuhammad

$totalxxx variables have a block scope. Initialize them outside of the while loop to widen there scope.

Member Avatar for bLuEmEzzy

Thank You for the Help, I have an error in my query instead of this

SELECT SUM(amunt), SUM(janua), SUM(febru)

I wrote this one(there's space)

SELECT SUM( amunt ) , SUM( janua ) , SUM( febru )

and when I'm trying to get the result(no space),

$psamt=$row['SUM(amunt)'];

Thank You for the help! God bless!!!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.