[RESOLVED] UPDATE using array?

PHPsites

Hello.

People provide me with their 5 favorite colors. This is stored in array
$favorite = "red", "blue", "green", "orange", "black", for example.

I have a db with primary key id & columns Num1 Num2 Num3 Num4 Num5.

How can I use 1 UPDATE command to update all columns?

Obviously, I can do

UPDATE table SET Num1 = '.$favorite[0].', Num2 = '.favorite[1].', ...... WHERE id = 'id';

But really I have well over 5 items so I would like a loop if possible to say
$query = "UPDATE table SET ";

for ($i=1; $i<=1000; $i++) {
$query .= "Num".$i." = ' ".$favorite[$i]."' ";
if ($i < 1000) {
$query .= ",";
}
}
$query .= "WHERE id = 'id'";

I guess what I am asking is, is this possible? Thanks for the help.

Scriptmaker

There shouldn't be any problem using a loop to build a string for you. Looks like you already took into account not adding a comma to the last loop too.

Since everything will be in array, using a foreach loop would work too.

While it is sometimes just unavoidable, if you have so many columns that you have to do a loop, you should probably spend a few minutes analyzing your db's structure.

PHPsites

Thanks Jorge.

Scriptmaker wrote:
Since everything will be in array, using a foreach loop would work too.

1) What would you suggest for speed - foreach or for($i=1,...) loop?

Scriptmaker wrote:
While it is sometimes just unavoidable, if you have so many columns that you have to do a loop, you should probably spend a few minutes analyzing your db's structure.

2) Can you recommend anything? Essentially trying to store someone's top 100 favorite colors, teams, games, albums, whatever and then populate using a form. This seemed to be the best structure but I could be wrong. I'm not really a db expert but it appears to be in "normal" form.

Thanks a lot.

mikell

You should read up on database normalization if you're interested in designing databases.

In this case, the normalized/"correct" design would look something like this:

Users

user_id (pk, auto-inc )
user_name
....etc

Colors

color_id (pk, auto-inc )
color_name

Users2Colors

user_id
color_id
rank

for($i = 0; $i < count($favorites); $i++)
{
  //insert the user, color, rank
  $query = "insert into Users2Colors values ($user_id, $favorites[$i], ".($i+1).")";
}

If running 100 insert queries bothers you (efficiency), check out "prepared statements".

bradgrafelman

You could also minimize the overhead by simply using one INSERT query that inserts multiple rows.

PHPsites

bradgrafelman wrote:
You could also minimize the overhead by simply using one INSERT query that inserts multiple rows.

Thanks Brad.

So I would DELETE the initial db entry and then INSERT new? Could you provide an example of the code for one INSERT query w/ multiple rows.

Thanks.

bradgrafelman

PHPsites wrote:
Could you provide an example of the code for one INSERT query w/ multiple rows.

Sure:

INSERT INTO myTable (col1, col2, col3) VALUES ('row1_value1', 'row1_value2', 'row1_value3'), ('row2_value1', 'row2_value2', 'row2_value3'), ...

In other words, if you're following the normalized structure mikell posted above, you could use a for() loop to add on values to the end of the query and then simply execute one single query.

PHPsites

Thanks for the help.

Horizon88

I know you've set it to resolved, but another option would be having one 'colors' field in the users row, like so:

red:blue:green:black:orange

which you could then read back into a string:
$dbColors = $row['colors'];
and explode into an array for individual color management.

$dbColors = $row['colors'];
$colorsArray = explode(':', $dbColors);

Then for inserts, you would just concatenate all the post values into a string before you insert it into their record:

$insertColors = $_POST['first'].':'.$_POST['second'].':'.
$_POST['third'].':'.$_POST['fourth'].':'.$_POST['fifth'];

Just another choice...

PHPsites

Horizon88 wrote:
I know you've set it to resolved, but another option would be having one 'colors' field in the users row, like so:
red:blue:green:black:orange
which you could then read back into a string:
$dbColors = $row['colors'];
and explode into an array for individual color management.

Thanks for another option and I think this one would work best for my intentions.

Technically, is inputting a string of choices considered proper db form? Is this an accepted programming practice?

For my intentions, I believe (guessing) the string option would work best for speed. However, I can also see where an argument is made to keep each choice in a column. That would allow an easy query to determine the most popular choice for color #7, for example.

So which route is considered best? Thanks again!

Horizon88

I think mines a good deal neater than having a whole bunch of columns, but if you want something that's both quick AND efficient you'll want to do relational tables of some sort, instead of columns of colors in the user table, I would think.