CODEWITHSUNDEEP
phpmysqlloops
monsterboy
monsterboy

Reputation: 153

Mysql PHP looping

hope you can help me out with this.

I use Dreamweaver to help me code as I'm still learning PHP and I'm stuck on trying to create a loop.

I have a Course Provider's Table and an Invoices Table.

What I'm trying to do:

Check the invoices table for a course providers ID - if exists - check for last months date - if exists - don't insert anything.

If there are no records with the course provider's ID and last months date, insert a record.

Here's my code which works first time I refresh the page but, if I change a date to something other than last months date and refresh again it will just keep adding even though the record already exists.

Please excuse my messy code:

mysql_select_db($database_dbconnect, $dbconnect);
$query_rs_get_cps = "SELECT tl_course_providers.cp_id FROM tl_course_providers ";
$rs_get_cps = mysql_query($query_rs_get_cps, $dbconnect) or die(mysql_error());
$row_rs_get_cps = mysql_fetch_assoc($rs_get_cps);
$totalRows_rs_get_cps = mysql_num_rows($rs_get_cps);

$invoiceDate = date("Y-m-d",strtotime("-1 months"));
$dueDate = date("Y-m-d",strtotime("+1 months"));

do{
    $cpid = $row_rs_get_cps['cp_id'];

    $cpid_rs_get_invoices = "-1";
    if (isset($cpid)) {
      $cpid_rs_get_invoices = $cpid;
    }
    mysql_select_db($database_dbconnect, $dbconnect);
    $query_rs_get_invoices = sprintf("SELECT * FROM tl_invoices WHERE tl_invoices.fk_cp_id = %s", GetSQLValueString($cpid_rs_get_invoices, "int"));
    $rs_get_invoices = mysql_query($query_rs_get_invoices, $dbconnect) or die(mysql_error());
    $row_rs_get_invoices = mysql_fetch_assoc($rs_get_invoices);
    $totalRows_rs_get_invoices = mysql_num_rows($rs_get_invoices);

    if($row_rs_get_invoices['invoice_date'] != $invoiceDate)
    {
    // for testing
    echo "CP Table: ".$cpid."<br />";
    echo "Invoice Table: ".$row_rs_get_invoices['fk_cp_id']."<br />";

    $insertSQL = sprintf("INSERT INTO tl_invoices (fk_cp_id, invoice_date, due_date, total, invoice_status) VALUES (%s, %s, %s, %s, %s)",
                           GetSQLValueString($row_rs_get_cps['cp_id'], "int"),
                           GetSQLValueString($invoiceDate, "date"),
                           GetSQLValueString($dueDate, "date"),
                           GetSQLValueString('0', "text"),
                           GetSQLValueString('Due', "text")
                           );

    mysql_select_db($database_dbconnect, $dbconnect);
    $Result1 = mysql_query($insertSQL, $dbconnect) or die(mysql_error());
    }else{
    break;
    }

}while($row_rs_get_cps = mysql_fetch_assoc($rs_get_cps));

Upvotes: 0

Views: 115

Answers (1)

Lucas Moeskops
Lucas Moeskops

Reputation: 5443

So you want to insert a record only when no record is available for last month for a course provider id.

You obtain all invoices for the course provider and then proceed to check only 1 of the found (invoice) records. It seems quite random whether or not this would match last month's date.

I would modify your query on tl_invoices and only obtain rows that match the date you are looking for (by matching the record date to your invoice date.

Another way would be to also loop through all found invoice records and only proceed inserting a new record if none of them matches last month's date.

Would that solve your problem?

Upvotes: 1

Related Questions