copy table to row in mysql using php

[wfcentral]

I have a survey that uses colors. At the moment someone takes the survey I need to capture the values that are in a table called "tbl_colors" and write them to each survey in "tbl_surveys" as a row.

 

tbl_colors

id, hex_color

1 FFF432

2 CC3399

3 DD3399

and so on to the number 12

 

tbl_survey

id, timestamp, name, hex01, hex02, hex03, hex04, etc to hex12

 

I have written code that "works" but I know it is extremely redundant and overloaded and someone could probably do the same thing in less than 10 lines of code. So, I post it here so I can learn from my mistakes.

 

// Make a MySQL Connection
$query = "SELECT * FROM tbl_colors WHERE id = 1"; 
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result);
$color1 = $row['hex_color'];
$query = "SELECT * FROM tbl_colors WHERE id = 2"; 
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result);
$color2 = $row['hex_color'];
$query = "SELECT * FROM tbl_colors WHERE id = 3"; 
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result);
$color3 = $row['hex_color'];
$query = "SELECT * FROM tbl_colors WHERE id = 4"; 
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result);
$color4 = $row['hex_color'];
$query = "SELECT * FROM tbl_colors WHERE id = 5"; 
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result);
$color5 = $row['hex_color'];
$query = "SELECT * FROM tbl_colors WHERE id = 6"; 
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result);
$color6 = $row['hex_color'];
$query = "SELECT * FROM tbl_colors WHERE id = 7"; 
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result);
$color7 = $row['hex_color'];
$query = "SELECT * FROM tbl_colors WHERE id = 8"; 
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result);
$color8 = $row['hex_color'];
$query = "SELECT * FROM tbl_colors WHERE id = 9"; 
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result);
$color9 = $row['hex_color'];
$query = "SELECT * FROM tbl_colors WHERE id = 10"; 
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result);
$color10 = $row['hex_color'];
$query = "SELECT * FROM tbl_colors WHERE id = 11"; 
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result);
$color11 = $row['hex_color'];
$query = "SELECT * FROM tbl_colors WHERE id = 12"; 
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result);
$color12 = $row['hex_color'];

//echo $color1.$color2.$color3.$color4.$color5.$color6.$color7.$color8.$color9.$color10.$color11.$color12;


mysql_query("INSERT INTO tbl_surveys (hex01, hex02, hex03, hex04, hex05, hex06, hex07, hex08, hex09, hex10, hex11, hex12)
VALUES ('$color1', '$color2', '$color2', '$color3', '$color4', '$color5', '$color6', '$color7', '$color8', '$color9', '$color10', '$color11', '$color12')");

 

[marcelobm]

I think this should do  the work

 

$query = "SELECT * FROM tbl_colors ORDER BY id ASC"; 
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_assoc($result)){
       $fields[] = "hex".($row['id']<10 ? '0' : '').$row['id'];
       $values[] = "'".$row['hex_color']."'";

}
mysql_query("INSERT INTO tbl_surveys (".implode(',',$fields).") VALUES (".implode(',', $values).")");

 

 

[wfcentral]

THANKS

 

works perfectly - and in only 7 lines... definitely knew I was doing it the hard way!

 

 

[wfcentral]

how would I modify the INSERT to become an UPDATE line? After looking at another part of the project I see that I will need to update a record that was just created rather than inserting a new record.

[BlueSkyIS]

http://www.google.com/search?client=safari&rls=en&q=php+update+record&ie=UTF-8&oe=UTF-8

[wfcentral]

marcelobm,

 

thanks for you quick response and example. I no longer need the UPDATE version of your code - I modified my survey to include your code in the original INSERT statement my survey does. Thanks for taking the time to help me with this. I know a lot of other people simply skim over a post and point people to Google when that's not really helpful at all (since they have already spent a few hours there and are looking for a very specific solution that would take 20 hours of Googling).

 

I already know how to do UPDATE statements and was looking specifically for how to modify code posted by you (implode statements) for use as an UPDATE statement...

 

Again, thanks for sharing your talent with those of us still learning... (and Googling).

[marcelobm]

You are welcome.  I'm glad i could help