up vote 0 down vote favorite

The below script works fine but only for the first record in the array.

$codes = array(1,2,3,4,5,6,7,8,9,10); // demo for this question, i actually have 1000+ 
$con = mysql_connect("localhost","user","pass");
if (!$con)
 {
 die('Could not connect1: ' . mysql_error());
 }
$con2 = mysql_select_db("db", $con);
 if (!$con2)
 {
 die('Could not connect2: ' . mysql_error());
 }

 $productsid = "select `products_id` from `coupons_products` where    `coupons_id`=58386264";
 $productsquery = mysql_query($productsid);

 foreach ($codes as $code) {
 while ($productid = mysql_fetch_assoc($productsquery)){
 $sql = "insert into discount_coupons_to_products values (
            '$code', 
            '{$productid['products_id']}')";
                    $con4 = mysql_query($sql);
        if (!$con4)
 {
 die('Could not connect4: ' . mysql_error());
 }
        }


 } // end foreach

I have an array of codes from the database that need apply only to specific products(same as 58386264). The codes works but only for the first coupon in the array ($codes).

share|improve this question
95% accept rate
Please, use a table for test, don't run it directly. You can mess everything up. – Hamikzo Nov 16 '11 at 11:30
1  
I backed-up the database but thank you for the advice. – Stephen Adrian Rathbone Nov 16 '11 at 11:33
feedback

1 Answer

up vote 1 down vote accepted

If I understand what it means, you will need to run mysql_query command every step inside foreach, not just run mysql_fetch_assoc like you're actually doing.

share|improve this answer
Fixed, thank you. Can you explain why? Surely that query is the same wether or not in the loop? – Stephen Adrian Rathbone Nov 16 '11 at 11:32
It works like a cursor, everytime you call fetch_assoc, you go to the next line. So, for each code you will run some lines, right? For each code new you get from the main array, you will need every lines again. That's because you need use mysql_query again, for each code you read from cursor, you'll need all resultset again. (Sorry for my weak english, anyway =P, I hope you understand what I tried to explain) – Hamikzo Nov 16 '11 at 11:37
1  
Ok, I think I understand. So when the code goes back to the start of the foreach loop the fetch_assoc cursor is already at the end. – Stephen Adrian Rathbone Nov 16 '11 at 11:42
Exactly. Instead use mysql_query again, inside foreach loop, try to use "mysql_data_seek(0)". This way you will not load database again, but memory, it's a fast way, because your cursor will go back to first result. Read more here: php.net/manual/pt_BR/function.mysql-data-seek.php – Hamikzo Nov 16 '11 at 11:49
feedback

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.