Mysql Mutiple Select Nested

[branding4you]

Ok so I saw the few posts here, none do what I need mine to do, and for some reason it does not work.

I upload CSV files to webserver (bank statement)
5 fields - it inserts into a MYSQL DB.
Numbers dates are all formated correct etc etc. This all is working

The aim is to check the payments table to see if the data just uploaded from the CSV file has been captured already, We doanload our statement and sometimes the transactions overlap the dates used. (dont ask lol)
Both table have the same first lot of fields but the payments table has two or three fields more used for other purposes.

FIELDS:
Date, Description, invref, amount

I thought of doing a While ... the reason is to loop thourg each record and check another table for a match
The loop finds the first record, it checks the second table for that exact entry, if it finds a match it marks the one in the previous table as a possible duplicate, if it does not carry on with next record and then back to second record in first table.

Is this a stupid way of doing it ... lol

My code:

<?php
include_once "db_conf.php";

$statement="SELECT * FROM statement"; 
$result=mysql_query($statement); 

while ($row = mysql_fetch_assoc($result)) 
{

    $id=$row['id']; 
    $date = $row['date'];
    $description = $row['description'];
    $invref = $row['invref'];
    $amount = $row['amount'];
    $duplicate = $row['duplicate'];

    //CHECK PAYMENTS TABLE FOR ABOVE DATA

        $payments="SELECT * FROM payments WHERE date = '$date' AND description = '$description' AND invref = '$invref' AND amount = '$amount'"; 
        $paymentresult=mysql_query($payments); 

        while ($paymentrow = mysql_fetch_array($paymentresult)) 
        {

            $idpayments=$paymentrow["id"]; 
            $datepayments = $paymentrow["date"];
            $descriptionpayments = $paymentrow["description"];
            $invrefpayments = $paymentrow["invref"];
            $amountpayments = $paymentrow["amount"];

            //JUST SHOW SOMETJHING ON PAGE FOR ME TO SEE
            echo $invrefpayments;
            echo "<br>";

            if($invrefpayments==$invref)
            {

            //update statements table duplictae field to yes
            $order = "UPDATE statement SET duplicate = 'y' WHERE date = '$datepayments' AND description = '$descriptionpayments' AND invref = '$invrefpayments' AND amount = '$amountpayments'";
            $result = mysql_query($order);
            echo "Duplicate Found";
            echo "<br>";

            }else{

            echo "No Duplicate";
            echo "<br>";

            }

        }

} 

?>

If there is any onther way please could someone show me an example, I am stuck on this error for days now, cnat seem to get it away, teh tables update but when the error apears it stops and goes no further.

INV10001
Duplicate Found

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\findreplace\checkdup.php on line 7

I checked that line and there seems to be no problem

Thank you

[broj1]

The solution for your requirement seems to be OK. But in some circumstances the result in line 5 returns FALSE which indicates an error when querying. You should change the code in line 5 to:

$result=mysql_query($statement) or die(mysql_error());

This way if there is an error when querying the error will be displayed and the script stopped. Now you can examine the reason for the error.

BTW: in future you might want to consider dropping the old and deprecated mysql extension and switching to newer mysqli extension or PDO.

[paulkd]

At line 6 (just before the error) add

if(!$result) {
    echo mysql_error();
    die;
}

to see what error is being reported

[branding4you]

Thank you guys, I realy strugled with it ALLOT! days and my head ache is worse now than yesterday ...

I did this in the last two hours and tested it and it works

<?php

error_reporting(0);
include_once "db_conf.php"; 

//  
$StatementQuery = "SELECT * FROM statement";
$StatementResult = mysql_query($StatementQuery);

//  Scan through all Statement Records
while ($StatementRow = mysql_fetch_assoc($StatementResult)) {

    $StatementDate = $StatementRow['date'];
    $StatementDescription = $StatementRow['description'];
    $StatementInvref = $StatementRow['invref'];
    $StatementAmount = $StatementRow['amount'];


    //  Get all Payments with same details
    $PaymentsQuery = "SELECT * FROM payments WHERE date = '$StatementDate' AND description = '$StatementDescription' AND invref = '$StatementInvref' AND amount = '$StatementAmount'";
    $PaymentResult = mysql_query($PaymentsQuery);

    //  Scan through all Payments
    while ($PaymentsRow = mysql_fetch_assoc($PaymentResult)) {

        echo "This is a duplicate entry: ";
        echo $PaymentsRow['invref'];
        echo "<br>"; 

        $order = "UPDATE statement SET duplicate = 'y' WHERE date = '$StatementDate' AND description = '$StatementDescription' AND invref = '$StatementInvref' AND amount = '$StatementAmount'";
        $result = mysql_query($order);
        echo "Marked as possible duplicate";
        echo "<br>";

    }

}

?>

I will use these comments you gave Broj and Paulkd - thank you!!