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? for more info. — SDC
– SDC, Commented Dec 19, 2012 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
– monsterboy, Commented Dec 19, 2012 at 13:15

Lucas Moeskops · Accepted Answer · 2012-12-19 13:16:43Z

1

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?

answered Dec 19, 2012 at 13:16

Lucas Moeskops

5,4333 gold badges31 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

monsterboy Over a year ago

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.

Collectives™ on Stack Overflow

Mysql PHP looping

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related