looping twice as much - writing doubles to MySQL table

Question

In a nutshell, my code seems to be looping twice as much as it should (writing four rows when it should be writing two rows). This should be an easy solution but I'm not having any luck.

Here is my php loop . . . must be one very simple yet invisible thing that no one has yet been able to locate why this baby is not working:

                //query statement before the for loop
                $stmt="INSERT INTO o70vm_invoices_invoices 
                (`id`, `created_by`, `user_id`, `added`, `to_name`, `to_address`, `invoice_num`, `real_invoice_num`, `from_name`, `from_address`, `from_num`, `invoice_date`, `publish`, `notes`, `template_id`, `taxes`, `start_publish`, `end_publish`, `currency_before`, `currency_after`, `status`, `to_email`, `to_company`, `from_phone`, `from_url`, `from_email`, `discount`, `invoice_duedate`, `admin_notes`, `to_city`, `to_state`, `to_country`, `to_vatid`, `to_zipcode`, `rec_year`, `rec_month`, `rec_day`, `rec_nextdate`, `is_recurrent`) VALUES ";

                // loop through number of invoices user selected to create
                for($x = 0; $x < $invoiceCount; $x++) 
                        {

                            // add the user identified days to each invoice
                            $date->modify("+7 days");
                            $invoiceDateNew = $date->format ('Y-m-d 00:00:00');
                            $invoiceDueDateNew = $date->format ('Y-m-d H:m:s');
                            $startPubNew = $date->format ('Y-m-d 00:00:00');

                            // getting the values per row
                            $ValuesAddToQuery[] ="(NULL, '792', '$userID', '$todayDate', '$parentName', 'unknown address', '0000', '0000', '', '', '', '".$invoiceDateNew."', '1', '', '2', '', '".$startPubNew."', '0000-00-00 00:00:00', '$', '', '', '$email', '$childName', '', '', '', '0.00', '".$invoiceDueDateNew."', '', '', '', '', '', '', '0', '0', '0', '0000-00-00', '0')";

                            }

                            $stmt .= implode(',',$ValuesAddToQuery);

                            mysql_query($stmt) or exit(mysql_error());

I store the number of invoices as:

    $invoiceCount

I have echoed out the value of $invoiceCount and the value is always the same value as the user inputs. IE, user selects 2 invoices to create, displays 2 invoices in the variable, yet creates 4 invoices in the MySQL table.

Stranger more: When I check for the rows affected with:

 mysql_affected_rows()

It returns the user-selected number of invoices / rows (not the actual rows I can see are added in the MySQL Table). For example, it will say "2" rows have been affected when four rows have been added.

Even more wild . . . when I echo out the MySQL query:

   echo $stmt;

my query also shows just two rows have been added when the user selected two rows to add but the code wrote 4 actual rows.

Adventurous, I even tried to slice the array to see if I could alter the code that is sent:

                                //implode the values into the statement
                            $stmt .= implode(',',$ValuesAddToQuery);

                            //limit the length of the array
                            array_slice($ValuesAddToQuery,0,2);

                            mysql_query($stmt) or exit(mysql_error());

And, you guessed it, it changes absolutely nothing. I put the array_slice on top of the implode statement. Again, no change in the 4 rows inputted when I only want 2 rows.

The more I look at this, I can't tell in this code why it is doubling.

Any help, much appreciated.

For detailed explanation of some of my input fields and what I'm doing, follow below:

To start, I am letting the user select how many rows to copy and update the invoice date as required. I am getting the values of FREQUENCY (7 days, 14 days, or 30 days) of recurring invoices and the DURATION (number of invoices to create/copy) using these input fields:

                <select name="freqOfInvoices">
                    <option value="7">Weekly</option>
                    <option value="14">Bi-Weekly</option>
                    <option value="30">Monthly</option>
                </select>

    <input type="number" title="numberOfInvoices" name="numberOfInvoices" size="2" id="numberOfInvoices" value="numberOfInvoices" />

I have similar input fields for the three dates I'm looking to ADD x number of days to:

        // assigning variables  
        $freqOfInvoices = htmlentities($_POST['freqOfInvoices'], ENT_QUOTES);
        $numberOfInvoices = htmlentities($_POST['numberOfInvoices'], ENT_QUOTES);
        $invoiceDate = htmlentities($_POST['invoice_date'], ENT_QUOTES);
        $invoiceDueDate = htmlentities($_POST['invoice_duedate'], ENT_QUOTES);
        $startPub = htmlentities($_POST['start_publish'], ENT_QUOTES);


        //assigning number of invoices
        $countInvoices=$numberOfInvoices;

This is [an SQL injection attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1) waiting to happen. See also http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php — miken32, Oct 23 '15 at 18:41
Thanks. Currently I am the only user who can access this page. This helps with my learning. — kentrenholm, Oct 23 '15 at 19:55
Be specific about your `etc.` stuff in your code examples. If you only have two columns you are updating, show exactly what your code looks like. Can you give an exact dump of `$invoiceDateArray`?. — Mike Brant, Oct 23 '15 at 21:14
Thanks Mike, I updated my question to answer your points. Thanks. — kentrenholm, Oct 23 '15 at 21:27

score 1 · Answer 1 · answered Oct 23 '15 at 03:26

1

It seems you may only need 1 loop to construct the values.

//query statement before the foreach loop
$stmt="INSERT INTO o70vm_invoices_invoices (`id`, `.....`, etc) VALUES ";

$ValuesAddToQuery = [];

for($x = 0; $x < $arrayLength; $x++) {
    // add the user identified days to the date
    $date->modify("+7 days");
    $invoiceDateNew = $date->format ('Y-m-d 00:00:00');

    $ValuesAddToQuery[]="(NULL, '....', ".$invoiceDateNew.")";
}

$stmt .= implode(',',$ValuesAddToQuery);

mysql_query($stmt) or exit(mysql_error());

answered Oct 23 '15 at 03:26

JRD

1,957
1
12
17

Thanks. Your code enters 2 invoices when I ask for one. The same as my own code. Must be something so small. – kentrenholm Oct 23 '15 at 11:20
1

It will enter `$arrayLength` number of rows. If you just want to enter 1 row, what is the for loop of `$arrayLength` for? It would be helpful if you could list the exact input data, and the exact result you expect. – JRD Oct 23 '15 at 15:07
Thanks JRD. I have edited my original question to include more detail. Thanks again. I'm wishing for the number of rows to be created by on the user selection. Much appreciated. – kentrenholm Oct 23 '15 at 16:07

score 0 · Answer 2 · answered Oct 25 '15 at 00:11

0

If you echo $stmt does the query string look correct or are your values getting doubled?

answered Oct 25 '15 at 00:11

Christopher Johnston

148
8

my echo of my query shows 2 rows if I select 2 rows. Strange, I know. – kentrenholm Oct 25 '15 at 01:41
Christopher was saying to echo the $stmt not the result of your query. So you can see if the query syntax is correct. Also you do not need to save each value in the array $ValueAddToQuery to concatenate the result of the implode. You can directly concatenate each value in the $stmt on each loop. – Laurent Fauvel Oct 25 '15 at 13:18

score 0 · Accepted Answer · answered Oct 25 '15 at 12:58

I'm answering my own question as I figured out a workaround that "actually" works. I'm keeping this question online for others as if I can save them four days of work, it is my pleasure.

Because I could not figure out why my code was creating twice as many invoices as the user requested, I simply added this code:

                            //////////////////////////
                            // Work around CODE as it keeps on doubling the records in DB
                            // Deleting the same amount of last entries as there are valid entries
                            //////////////////////////

                            $RecordsToDelete=$invoiceCount;
                            $DeleteQuery="DELETE FROM o70vm_invoices_invoices ORDER BY id DESC limit $RecordsToDelete";

                            mysql_query($DeleteQuery) or exit(mysql_error());

immediately after my original implode / execute query code:

                            $stmt .= implode(',',$ValuesAddToQuery);

                            mysql_query($stmt) or exit(mysql_error());

The code works because my "infected" code was writing the full series of invoices (with the user selected dates) once, and then doing the same series again. So, that translated into the first set of invoices (2) to be correct and the last invoices (2) to be duplicates. So, presto, just delete from the last entries the count of your invoices.

I admit, it is a workaround. Hopefully someday I will figure out why my original code produced duplicates. But, happy day regardless. :)

You did not resolve your bug and just clean the mess provoked by it. If someone got to work on your code one day, it will be a nightmare figuring why you did that. IMO fix the bug not its damage. — Laurent Fauvel, Oct 25 '15 at 13:21

looping twice as much - writing doubles to MySQL table

3 Answers3