Inserting PHP variables into MySQL database using foreach loop

Question

I have an array: $product_counts = array_count_values($product_array);

The keys for this array are merchant ID's and the values are integers (product counts). So if I was to write the following code:

foreach($product_counts as $key => $value){
    echo "key: $key";
    echo "value: $value";
        }

I would get the following (which is what I want):

key: 26816928 value: 13
key: 26816931 value: 2 ... 
X the amount of indexes in the array.

However if I was to write the following code:

foreach($product_counts as $key => $value){
    mysql_query("INSERT INTO merchantinfo(ProductCount) VALUES $value WHERE MerchantID = $key"); 
}

The values of the $value variable don't go into the field where MerchantID = $key....instead the tuples just default to null, which is what I've set them to do. I believe this could be a case of needing to type cast the variable as integers .... but I'm in general quite lost with this.

Thanks in advance

Fluffeh · Accepted Answer · 2012-09-27 12:22:09Z

up vote 5 down vote accepted

You cannot use a where clause in an insert statement. I think you want to use update instead.

mysql_query("update merchantinfo set ProductCount= $value WHERE MerchantID = $key");

answered Sep 27 '12 at 12:22

Fluffeh
14.1k42243

	I now feel exceptionally idiotic. however thanks a lot! Complete star. – Tim Platt Sep 27 '12 at 12:32
	@user1703085 We all make silly mistakes every now and again. Part of life :) If anything, I am willing to be you will never again make this one hehe. Pass it on and help the next chap out there! – Fluffeh Sep 27 '12 at 12:34
	I vow to pass on such wisdom! What an excellent start ... I only joined this community 5 minutes ago and I'm already kicking myself for not joining years ago! – Tim Platt Sep 27 '12 at 12:39

Dmytro Zarezenko · Answer 2 · 2012-09-27 12:21:55Z

up vote 2 down vote

Take values in () too, like:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

And in your case you must use UPDATE instead of INSERT

UPDATE merchantinfo SET ProductCount = $value WHERE MerchantID = $key

answered Sep 27 '12 at 12:21

Dmytro Zarezenko
2,0242533

1

This will just insert rows into the database, not update the correct row (as implied with the where clause the OP is trying to use). – Fluffeh Sep 27 '12 at 12:23

Agree, I simple didn't complete my answer yet :) – Dmytro Zarezenko Sep 27 '12 at 12:24

lupatus · Answer 3 · 2012-09-27 12:29:00Z

It looks like you want to update value not insert: That should work for you:

$query = "UPDATE merchantinfo set ProductCount='$value' WHERE MerchantID='$key'";

If you want insert new row then:

$query = "INSERT INTO merchantinfo(ProductCount,MerchantID) VALUES('$value','$key')";

Anyway would be much better to use PDO and prepare statement (at least safer)

$stmt = $pdo->prepare("UPDATE merchantinfo set ProductCount=? WHERE MerchantID=?");
foreach($product_counts as $key => $value){
    $stmt->execute(array($value, $key));
}

asked	6 months ago
viewed	490 times
active	6 months ago

Inserting PHP variables into MySQL database using foreach loop

3 Answers

Your Answer

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

Inserting PHP variables into MySQL database using foreach loop

3 Answers

Your Answer

Sign up or log in

Post as a guest

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

Related