Loop into PHP Array, then loop into SQL Insert... or something different... help please

Question

Lets say I have two tables. In table “category_name”, there are fields category_id and category name. In table “category_subscriptions” there are fields userid and category_number.

What I want to be able to do is to insert a row into category_subscriptions for each category_id using the same userid.

Consider “category_name” having the following values: 4, cars, 5, trucks, 6, trains

The ideal output table for “category_subscriptions (for userid=99) would have values: 99,4,99,5,99,6

Rather than use:

INSERT INTO category_subscriptions
VALUES (99,4)

INSERT INTO category_subscriptions
VALUES (99,5)

INSERT INTO category_subscriptions
VALUES (99,6)

I think I should be using a loop, as category.id won’t always be 4,5, &6. I’m just not sure if this is right and am hoping somebody can help. This is part of a plugin for Joomla 1.7/php.

What's the relationship between category_name and category_subscriptions? Is category_number in the latter a reference to category_id in the former? — Dan J
– Dan J, Commented Jan 26, 2012 at 18:55

DerVO · Accepted Answer · 2012-01-26 19:14:46Z

2

Assuming you have an array $Categories including all your category_id's, you can do:

$user_id = 99;
$Categories = array(4, 5, 6);

$sql = "INSERT INTO category_subscriptions VALUES ";
$i = 0;
foreach ($Categories as $categorie) {
    if ($i++) $sql .= ", ";
    $sql .= "({$user_id}, {$categorie})";
}

The output of this code is

INSERT INTO category_subscriptions VALUES (99, 4), (99, 5), (99, 6)

Please note, that this builds a single query. You can insert multiple lines with one INSERT statement using VALUES (<line1>), (<line2>) ...

EDIT

If you really want to link your user to every existing category, you can also do it in plain SQL.

INSERT INTO `category_subscriptions` (`userid`, `category_number`)
    SELECT 99, `category_id` FROM `category_name`

edited Jan 26, 2012 at 19:14

answered Jan 26, 2012 at 19:04

DerVO

3,6791 gold badge27 silver badges27 bronze badges

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

Comments

Zul · Accepted Answer · 2012-01-26 19:15:33Z

0

Maybe something like this :

<?php
$userID = '99';
$catID = array(4,5,6); 

foreach($catID as $cid) 
{
    $value[] = '('.$userID,$cid.')';
}

mysql_query('INSERT INTO category_subscriptions (user_id,category_id) VALUES '.implode(',', $value));
?>

The script above will run mysql query :

INSERT INTO category_subscriptions VALUES (99,4), (99,5), (99,6)

edited Jan 26, 2012 at 19:15

answered Jan 26, 2012 at 19:09

Zul

3,6063 gold badges24 silver badges36 bronze badges

Collectives™ on Stack Overflow

Loop into PHP Array, then loop into SQL Insert... or something different... help please

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related