Writing multiple options to mysql

[cyronuts]

New question...

I am working on a project to allow users to select one or more 'options' to go with a particular item, ie., item is T-Shirts and options are small, medium, large etc. The site owner can create the options available to the site visitor.

I have figured out how to write a single option to the database, and bring back multiple items in a select box, but how do I then send back options that are MULTIPLY selected for each item....

I have the following code to get any options related to an item...would I reverse the code...putting the items into an array, and then writing the array to the datbase? As you can tell, I am confused:

<td><select name="opt_display" multiple size="8" width="15">
<?
// list of possible options comes from database

      $cat_array=get_options();
      foreach ($cat_array as $thiscat)
      {
           echo "<option value=\"";
           echo $thiscat["opt_display"];
           echo "\"";
           // if existing book, put in current catgory
           if ($edit && $thiscat["opt_display"] == $book["opt_display"])
               echo " selected";
           echo ">"; 
           echo $thiscat["opt_display"];
           echo "\n"; 
      }
      ?>
      </select>
    </td>

[bastien]

that is exactly what i did for option in a site that i am working on. just pop them in an array and write the array to the DB. works great....(ps I hate options (white XL is a we have dammit!))

hth

[cyronuts]

Hey,

Thanks for the idea, I am slowly formulating a plan of action. I am pretty new to this - any snippets you could provide that I can take a peek at? Thanks!

[bastien]

you'll have to wait till I get home...give you the page

[cyronuts]

Thanks! I'll be looking out!

[bastien]

comments

//count the options choosen to see if you need to implode the
//array to be able to pass it to the DB. I found not imploding the
//array did not work as a valid DB entry.

$numOpts = count($options);
//if the number of options choosen is more than 1, impode

if ($numOpts>1){
   $myOpts=implode(",",$options);

//if the number of options = 1, then everything okay as it is not
//an array

}elseif ($numOpts==1){
$myOpts=$options;
//assign an empty string value to the options variable for DB entry
}else{
$myOpts="";
}
//cart functionality
//security - if not referred by product or distrib then pass to default
if (!$distrib){
$distrib = 0;
}
//this are all includes that provide DB connections and
//headers,footers and forms
require("commonmn.php");
require("dbconng.php");
HTMLHeader();

 //insert info in to cart sales table

$InsertItem = "Insert into gulf_orders(session_id, prod_id, baseColor, bodyColor, options_ordered, quantity_ordered, distrib_id,ip)
values ('$PHPSESSID',$PNum,'$baseColor','$bodyColor','$myOpts',$quant,$distrib,'$ip');";
//echo $InsertItem;
$insertCart = mysql_query($InsertItem, $conn) or die ("<center><table bgcolor = \"#C0C0C0\"><tr><td>
Couldn't update cart</td></tr></table></center>");

hope that make things a little clearer...

[cyronuts]

Thanks Bastien,

Keeping in mind I am a little new to PHP, I your comments on what that snippet is doing - can you go into a little more detail? Thanks!

[cyronuts]

Thanks Bastien,

Keeping in mind I am a little new to PHP, I your comments on what that snippet is doing - can you go into a little more detail?

The basic functionality I've outlined is this:

In the Site Administrator (for example).

1) Site owner enters production options one by one (I've got this working)

2) Site owner enters products. In the product form, there is a (field) box of all possible options entered in the dbase so far. Owner can then select one or multiple options to relate to the product (for the site visitor).

3) Upon entering the product, the options selected are written back to the database to relate to the product.

Is this logic even right?

Thanks!

[bastien]

so I would say that you are on the right track, from my point of view. there are many ways to accomplish the same result so whatever gets that result is doing it right....

I made some more comments to my post with the code in it...hope it makes things a little clearer...

good luck

[cyronuts]

Thank you so much! I guess where I am REALLY confused (there are so many opportunities for confusion!) is where the code goes in my insert_product.php file, which follows:

<?

require_once("sc_fns.php");

if ($POST['catid'] == "" or $POST['prod_name'] == "" or $POST['prod_num'] == "" or $POST['prod_price'] == "" or $POST['ship_one'] == "" or $POST['tax_exempt'] == "" or $POST['ship_two'] == "" or $POST['decl_bal'] == "" or $POST['ship_three'] == "" or $POST['curr_inv'] == "" or $POST['ship_weight'] == "" or $POST['special'] == "" or $POST['max_qty_purchase'] == "" or $POST['lg_image'] == "" or $POST['max_qty_purch'] == "" or $POST['sm_image'] == "" or $POST['prod_options'] == "" or $POST['rel_prods'] == "" or $POST['prod_description'] == "" or $POST['add_email_confirm'] == "")

{
admin_header();
echo "<FONT FACE='ARIAL' SIZE='1' COLOR='FF0000'><BR>Error. You did not complete the entire form.<BR>You must enter information for each field to enter your product.<BR>Please try again, thank you.<BR></FONT>";
display_product_form();
do_html_footer();
exit;
}

$POST['catid'] = addslashes($POST['catid']);
$POST['prod_name'] = addslashes($POST['prod_name']);
$POST['prod_num'] = addslashes($POST['prod_num']);
$POST['prod_price'] = addslashes($POST['prod_price']);
$POST['ship_one'] = addslashes($POST['ship_one']);
$POST['tax_exempt'] = addslashes($POST['tax_exempt']);
$POST['ship_two'] = addslashes($POST['ship_two']);
$POST['decl_bal'] = addslashes($POST['decl_bal']);
$POST['ship_three'] = addslashes($POST['ship_three']);
$POST['curr_inv'] = addslashes($POST['curr_inv']);
$POST['ship_weight'] = addslashes($POST['ship_weight']);
$POST['special'] = addslashes($POST['special']);
$POST['max_qty_purchase'] = addslashes($POST['max_qty_purchase']);
$POST['lg_image'] = addslashes($POST['lg_image']);
$POST['max_qty_purch'] = addslashes($POST['max_qty_purch']);
$POST['sm_image'] = addslashes($POST['sm_image']);

$POST['rel_prods'] = addslashes($POST['rel_prods']);
$POST['prod_description'] = addslashes($POST['prod_description']);
$POST['add_email_confirm'] = addslashes($POST['add_email_confirm']);

db_connct();

mysql_select_db("book_sc");
$query = "INSERT INTO products values ('".$POST['catid']."', '".$POST['prod_name']."', '".$POST['prod_num']."', '".$POST['prod_price']."', '".$POST['ship_one']."', '".$POST['tax_exempt']."', '".$POST['ship_two']."', '".$POST['decl_bal']."', '".$POST['ship_three']."', '".$POST['curr_inv']."', '".$POST['ship_weight']."', '".$POST['special']."', '".$POST['max_qty_purchase']."', '".$POST['lg_image']."', '".$POST['max_qty_purch']."', '".$POST['sm_image']."', '".$POST['rel_prods']."', '".$POST['prod_description']."', '".$_POST['add_email_confirm']."')";

$result = mysql_query($query);
if ($result)
admin_header();
echo "<FONT FACE='ARIAL' SIZE='1' COLOR='0000FF'><BR>Thank you. One new product has been entered into the cart database.</FONT>";
display_product_form();
do_html_footer();

?>

</body></html>

---

Should I put it BEFORE my current code?

[bastien]

post some version this :

//count the options choosen to see if you need to implode the
//array to be able to pass it to the DB. I found not imploding the
//array did not work as a valid DB entry.

$numOpts = count($options);
//if the number of options choosen is more than 1, impode

if ($numOpts>1){
$myOpts=implode(",",$options);
//if the number of options = 1, then everything okay as it is not
//an array
}elseif ($numOpts==1){
$myOpts=$options;
//assign an empty string value to the options variable for DB entry
}else{
$myOpts="";
}

before the insert statement - before you open the connection to the DB...

[cyronuts]

Bastien,

Got it all in, but the info in the array (or not) is not being written to the db columm.

I've got the col named 'numOpt', any idea what I am doing wrong? It also won't submit anything at all if I leave that first field checker 'if' statement in - but I've been cutting it out to test the submit. I am sure I've got all my fields named correctly.

<?

require_once("sc_fns.php");

if ($POST['catid'] == "" or $POST['prod_name'] == "" or $POST['prod_num'] == "" or $POST['prod_price'] == "" or $POST['ship_one'] == "" or $POST['tax_exempt'] == "" or $POST['ship_two'] == "" or $POST['decl_bal'] == "" or $POST['ship_three'] == "" or $POST['curr_inv'] == "" or $POST['ship_weight'] == "" or $POST['special'] == "" or $POST['max_qty_purchase'] == "" or $POST['lg_image'] == "" or $POST['max_qty_purch'] == "" or $POST['sm_image'] == "" or $POST['prod_options[]'] == "" or $POST['rel_prods'] == "" or $POST['prod_description'] == "" or $POST['add_email_confirm'] == "")

{
admin_header();
echo "<FONT FACE='ARIAL' SIZE='1' COLOR='FF0000'><BR>Error. You did not complete the entire form.<BR>You must enter information for each field to enter your product.<BR>Please try again, thank you.<BR></FONT>";
display_product_form();
do_html_footer();
exit;
}

$POST['catid'] = addslashes($POST['catid']);
$POST['prod_name'] = addslashes($POST['prod_name']);
$POST['prod_num'] = addslashes($POST['prod_num']);
$POST['prod_price'] = addslashes($POST['prod_price']);
$POST['ship_one'] = addslashes($POST['ship_one']);
$POST['tax_exempt'] = addslashes($POST['tax_exempt']);
$POST['ship_two'] = addslashes($POST['ship_two']);
$POST['decl_bal'] = addslashes($POST['decl_bal']);
$POST['ship_three'] = addslashes($POST['ship_three']);
$POST['curr_inv'] = addslashes($POST['curr_inv']);
$POST['ship_weight'] = addslashes($POST['ship_weight']);
$POST['special'] = addslashes($POST['special']);
$POST['max_qty_purchase'] = addslashes($POST['max_qty_purchase']);
$POST['lg_image'] = addslashes($POST['lg_image']);
$POST['max_qty_purch'] = addslashes($POST['max_qty_purch']);
$POST['sm_image'] = addslashes($POST['sm_image']);
$POST['prod_options[]'] = addslashes($POST['prod_options[]']);
$POST['rel_prods'] = addslashes($POST['rel_prods']);
$POST['prod_description'] = addslashes($POST['prod_description']);
$POST['add_email_confirm'] = addslashes($POST['add_email_confirm']);

//count the options choosen to see if you need to implode the
//array to be able to pass it to the DB. I found not imploding the
//array did not work as a valid DB entry.

$numOpts = count($prod_options);
//if the number of options choosen is more than 1, implode

if ($numOpts>1){
$myOpts=implode(",",$prod_options);
//if the number of options = 1, then everything okay as it is not
//an array
}elseif ($numOpts==1){
$myOpts=$prod_options;
//assign an empty string value to the options variable for DB entry
}else{
$myOpts="";
}

db_connct();

mysql_select_db("book_sc");
$query = "INSERT INTO products values ('".$POST['catid']."', '".$POST['prod_name']."', '".$POST['prod_num']."', '".$POST['prod_price']."', '".$POST['ship_one']."', '".$POST['tax_exempt']."', '".$POST['ship_two']."', '".$POST['decl_bal']."', '".$POST['ship_three']."', '".$POST['curr_inv']."', '".$POST['ship_weight']."', '".$POST['special']."', '".$POST['max_qty_purchase']."', '".$POST['lg_image']."', '".$POST['max_qty_purch']."', '".$POST['sm_image']."', '".$POST['numOpts']."', '".$POST['rel_prods']."', '".$POST['prod_description']."', '".$POST['add_email_confirm']."')";

$result = mysql_query($query);
if ($result)
admin_header();
echo "<FONT FACE='ARIAL' SIZE='1' COLOR='0000FF'><BR>Thank you. One new product has been entered into the cart database.</FONT>";
display_product_form();
do_html_footer();

?>

</body></html>

[bastien]

".$_POST['numOpts']."' the $numOpts is a field check to see if the array needs implosion.

you need to insert the $myOpts variable and without the $_POST as you have taken the post and changed it into the $myOpts.

hth

[cyronuts]

AHH YES, SCHAWING BATTER (well, maybe not after today),

but anyway, thanks for the help...

So, as long as the product options do not contain a comma, the code will present back each string?