php array with checkboxes

need to set a loop to insert into db

[Birdman]

Hello again. I'm in a little over my head, since I just started learning php and sql a couple weeks ago.

What I'm working on is a page for the client to enter item variables into a mysql db. I've got the first part, which updates the `prod` table, working properly.

Now I need to update the `prod_to_cat` table. I would like to have checkbox for each category so items can be in multiple categories.

So far what I have is:

<input type='checkbox' name='cats[]' value='1' />category 1
<input type='checkbox' name='cats[]' value='2' />category 2
<input type='checkbox' name='cats[]' value='3' />category 3

I can't figure out how to construct the loop that will build the query string. I'd appreciate it if anyone could explain to me how to go about this.

[ukgimp]

Birdman

You are going through all the same problems I encounterd a while back

Some help here
[webmasterworld.com...]

And if I have you right the following should help

$sql = "your sql";
$pResult=mysql_query( $sql , $db_connection );
$number = mysql_num_rows($pResult);
//Loop through records and print new row for each
$i = 0;
if ($number < 1) {
print "There are No Records";
}
else 
{
//This bit does the loop 
while ($number > $i) {
$ID = mysql_result($pResult,$i,"YourCat");
$NA= mysql_result($pResult,$i,"YourName");
//print out checkboxes and escape quotes
print "<input type=\"checkbox\" name=\"NA[]\" value=\"$ID\">
";
$i++;
}
}

[andreasfriedrich]

Have a look at php to add the value of ANY ticked box to a message [webmasterworld.com].

Andreas

[Birdman]

Thanks for the replies and the links.

I'm not actually building the checkbox list from the db(but I will soon). What I'm trying to do is build a sql query from the checkbox values in the form results.

So I use the brackets in the name of the checkboxes so it will be an array. Now I need to make an INSERT line for each of the checked boxes.

I think a foreach loop is what I need. Basically, I need the finished product to be this:
$query="
INSERT INTO prod_to_cat(relcat_id,relprod_id) VALUES('value 1','$item_no')
INSERT INTO prod_to_cat(relcat_id,relprod_id) VALUES('value 2','$item_no')
INSERT INTO prod_to_cat(relcat_id,relprod_id) VALUES('value 3','$item_no')";

Thank you for your guidance!

[andreasfriedrich]

Something like this might work:

foreach($form as $key => $value) { 
 if ($value > 0) $sql .= 
 sprintf("INSERT INTO prod_to_cat(relcat_id,relprod_id) VALUES('%s','%s');", 
 $value, $item_no); 
}

Iīm not really sure if it is a good idea to have multiple SQL queries in one string, though.

Andreas

[Birdman]

Sorry for disappearing. andreas, that worked for me but if multiple boxes are checked it doesn't update.

not really sure if it is a good idea to have multiple SQL queries in one string

What would be the proper way to add a new row for each box checked?

Once again, thanks.

[jatar_k]

>>if multiple boxes are checked it doesn't update

it should, there is nothing wrong with it. It should loop through and for each checked box build a query for it. You would have to execute said query but andreas' code is sound, as always.

>>not really sure if it is a good idea to have multiple SQL queries in one string

It is a bad idea, I don't even thik it would necessarily work every time, if at all. The method that andreas put in willl build them seperately and then you can execute them within that loop once built.

[Birdman]

I wasn't doubting andreas' code. I can tell she is very good from her posts.

and then you can execute them within that loop once built.

Like this?

foreach($cats as $key => $value) {
if ($value > 0) $sql .=
sprintf("INSERT INTO prod_cat(relcat_id,relprod_id) VALUES('%s','%s');",
$value, $old_no);
mysql_query($sql);
}

[jatar_k]

I wasn't referring to the fact that you doubted, no offense meant, I was more complimenting andreas.

I would add it into the if statement though and reinitialize the $sql var.

foreach($cats as $key => $value) {
$sql = "";
if ($value > 0) {
$sql .= sprintf("INSERT INTO prod_cat(relcat_id,relprod_id) VALUES('%s','%s');",
$value, $old_no);
mysql_query($sql);
}
}

I thought andreas was a he. :)

[Birdman]

No offense taken. My compliments to her as well. I'll try that right now. I wish I just knew how you guys come up with this stuff.

[Birdman]

Thanks for the code jatar! For some reason the fourth checkbox value wasn't added. I'll try to figure it out. I don't want to keep harping on the issue, since I can still enter the info by hand. What I need to do is go read some more!

[edit]It worked perfectly[/edit]

[andreasfriedrich]

I thought andreas was a he.

I also. :)

The method that andreas put in will build them seperately

Actually my code in msg#5 [webmasterworld.com] was meant to build one large $sql string, hence the .= assignment operator. This was what birdman asked for in msg#4 [webmasterworld.com].

Iīm not really sure if it is a good idea to have multiple SQL queries in one string, though.

This was my polite way of saying I wouldīt do it that way. The better way would be to execute each sql statement within the loop as suggested be Adam. If you do it that way you donīt need the ".=" assignment operator anymore, you can use simply "=". Then you wouldnīt have to reinitialize the $sql var either. The code would look like this:

foreach($cats as $key => $value) {  
 // $sql = ""; // donīt need that anymore 
 if ($value > 0) {  
 $sql = sprintf("INSERT INTO prod_cat(relcat_id,relprod_id) VALUES('%s','%s');",  
 $value, $old_no);  
 mysql_query($sql);  
 }  
}

Andreas

[Birdman]

Thanks andreas:) Sorry about the she thing. I know better than to guess someones gender by thier name. I hope one day I can interpret php/sql as well as you and the others here. That one little project ate up a good portion of my day yesterday. Like I said before, time to do some serious reading.

Thanks everyone!