Mysql PHP looping

Question

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));

if you're learning PHP from a course, you need to tell your course instructor that the `mysql_xx()` functions are deprecated and should not be used -- and certainly not taught to new developers! You should be using either the `mysqli_xx()` functions or the PDO library instead. See [Why shouldn't I use mysql_* functions in PHP?](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) for more info. — SDC, Dec 19 '12 at 13:06
I'm not on a course, I have been using online tutorials and Dreamweaver CS5 which is outdated to create my projects. Thanks for the tip I will certainly look into updating my code. — monsterboy, Dec 19 '12 at 13:15

score 1 · Accepted Answer · answered Dec 19 '12 at 13:16

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?

Nice, thanks you have steered me in the right direction. I think I had been trying too long to figure it out and got myself all muddled up. — monsterboy, Dec 19 '12 at 13:36

Mysql PHP looping

1 Answers1