...

View Full Version : adding multiple checkbox values from recordset to two different tables



MsKazza1
04-08-2013, 12:00 PM
I have a page for adding products into a database, its fine until i try to add the colour options to the database.

My table structure is :

Products - (various product fields, name, description, price, etc.)

Colours - colour_id, colour_name, colour_image, supplier_code

colour_variation - colour_variation_id, product_code, colour_id

on the Products page i have checkboxes that are taken from the colours table (via recordset) and displayed for the user to choose the colour options for each product.


<table>
<tr>
<?php
$colours_endRow = 0;
$colours_columns = 4; // number of columns
$colours_hloopRow1 = 0; // first row flag
do {
if($colours_endRow == 0 && $colours_hloopRow1++ != 0) echo "<tr>";
?>
<td><input type="checkbox" name="colour_option" id="<?php echo $row_colours['colour_name']; ?>" />
<?php echo $row_colours['colour_name']; ?></td>
<td>- <i><?php echo $row_colours['supplier_code']; ?></i></td>
<?php $colours_endRow++;
if($colours_endRow >= $colours_columns) {
?>
</tr>

<?php
$colours_endRow = 0;
}
} while ($row_colours = mysql_fetch_assoc($colours));
if($colours_endRow != 0) {
while ($colours_endRow < $colours_columns) {
echo("<td>&nbsp;</td>");
$colours_endRow++;
}
echo("</tr>");
}?>
</table>


Now i need for these checkboxes to be added to the colour_variation table, i don't know how to insert into multiple tables with information taken from a recordset.

I have looked at other questions (such as: Inserting multiple checkbox values into a single SQL database column (http://stackoverflow.com/questions/14448942/inserting-multiple-checkbox-values-into-a-single-sql-database-column) ) but from what i can see these are hardcoded form options not recordset options.

I'm guessing i'll have to use some kind of loop, but i've no idea how to get the loop to only show the checkboxes that have been selected and then add to a different table than the main insert statement.

Main insert statement :


if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
$insertSQL = sprintf("INSERT INTO products (product_code, supplier_product_code, supplier, category_id, sub_category_id, product_name, product_description, product_additional_description, product_price, product_thumbnail, product_image_big) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
GetSQLValueString($_POST['product_code'], "text"),
GetSQLValueString($_POST['supplier_product_code'], "text"),
GetSQLValueString($_POST['supplier'], "text"),
GetSQLValueString($_POST['category_id'], "int"),
GetSQLValueString($_POST['sub_category_id'], "int"),
GetSQLValueString($_POST['product_name'], "text"),
GetSQLValueString($_POST['product_description'], "text"),
GetSQLValueString($_POST['product_additional_description'], "text"),
GetSQLValueString($_POST['product_price'], "int"),
GetSQLValueString($_POST['product_thumbnail'], "text"),
GetSQLValueString($_POST['product_big_image'], "text"));

$insertSQL = sprintf("INSERT INTO colour_variation (product_code, supplier_code) VALUES (%s, %s)",
GetSQLValueString($_POST['product_code'], "text"),
GetSQLValueString($_POST['supplier_code'], "text"));

I'm fairly certain that the second insert statement is totally wrong so any help would be very much appreciated.

Thanks v much,

Karen

Fou-Lu
04-08-2013, 07:02 PM
Don't pay attention to that link you have. Stuffing a collection of data into a single field is a huge normalization issue; you have the right idea already.
This will be a part of your issue:


<td><input type="checkbox" name="colour_option" id="<?php echo $row_colours['colour_name']; ?>" />

Checkboxes are successful only when checked, but this one doesn't have a method in which to collect multiple or tell which one is which.
If I understand what you are trying to do here, you'll want to create an array for your colour_option and give it the values for each of the items.


<td><input type="checkbox" name="colour_option[]" id="<?php echo $row_colours['colour_name']; ?>" value="<?php echo $row_colours['colour_id'];?>" />

I assumed that colour_id is the value going to be inserted. When its posted, you should be able to retrieve that with an array:


if (isset($_POST['colour_option']) && is_array($_POST['colour_option']))
{

}


I'd suggest using mysqli and binding on a prepared statement. It's a lot more efficient than using many queries in a loop. Or, if there is only very few checked options, you can combine them.


if (isset($_POST['colour_option']) && is_array($_POST['colour_option']))
{
// I don't know how you are associating, so I'll just use $_POST['product_code'].
$sQry = "INSERT INTO colour_variation (product_code, colour_id) VALUES ";
$i = 0;
foreach ($_POST['colour_option'] AS $colour)
{
if ($i++ > )
{
$sQry .= ", ";
}
$sQry .= sprintf("(%s, %s)", GetSQLValueString($_POST['product_code'], "text"), GetSQLValueString($colour, "text"));
}
}

I'd suggest a bind myself though.
I'm not sure how you are associating a product_code to a colour, so that's something you'll need to look into as well.

MsKazza1
04-09-2013, 12:57 PM
thanks for your reply. i'm getting the following error:Parse error: syntax error, unexpected ')' in C:\Website\xampp\htdocs\adlantic\admin\addproducts.php on line 139

that is the line : if ($i++ > )

i'm not sure what the problem is.

Fou-Lu
04-09-2013, 01:51 PM
I'm missing a 0 in the comparison here: if ($i++ > ) .

MsKazza1
04-10-2013, 11:22 AM
thanks for your reply, it is inserting to the products table but still not adding the colours to the colour_variation table. any help advise you can give much appreciated i've spent the whole night up looking through this and can't see the problem, below is the whole file. thanks


<?php require_once('../Connections/adlantic.php'); ?>

<script type="text/javascript" language="javascript">
function toggleField(field) {
var myTarget = document.getElementById(field);
if(myTarget.style.display == 'none'){
myTarget.style.display = 'block';
} else {
myTarget.style.display = 'none';
myTarget.value = '';
}
}
</script>

<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}

if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}

if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
$editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
$insertSQL = sprintf("INSERT INTO products (product_code, supplier_product_code, supplier, category_id, sub_category_id, product_name, product_description, product_additional_description, product_price, product_thumbnail, product_image_big, product_special, product_special_price, product_display_frontpage, product_publish, product_active) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
GetSQLValueString($_POST['product_code'], "text"),
GetSQLValueString($_POST['supplier_product_code'], "text"),
GetSQLValueString($_POST['supplier'], "text"),
GetSQLValueString($_POST['category_id'], "int"),
GetSQLValueString($_POST['sub_category_id'], "int"),
GetSQLValueString($_POST['product_name'], "text"),
GetSQLValueString($_POST['product_description'], "text"),
GetSQLValueString($_POST['product_additional_description'], "text"),
GetSQLValueString($_POST['product_price'], "double"),
GetSQLValueString($_POST['product_thumbnail'], "text"),
GetSQLValueString($_POST['product_big_image'], "text"),
GetSQLValueString(isset($_POST['product_special']) ? "true" : "", "defined","'Y'","'N'"),
GetSQLValueString($_POST['product_special_price'], "double"),
GetSQLValueString(isset($_POST['product_display_frontpage']) ? "true" : "", "defined","'Y'","'N'"),
GetSQLValueString(isset($_POST['product_publish']) ? "true" : "", "defined","'Y'","'N'"),
GetSQLValueString(isset($_POST['product_active']) ? "true" : "", "defined","'Y'","'N'"));

mysql_select_db($database_adlantic, $adlantic);
$Result1 = mysql_query($insertSQL, $adlantic) or die(mysql_error());
}

if (isset($_POST['colour_option']) && is_array($_POST['colour_option']))
{
// I don't know how you are associating, so I'll just use $_POST['product_code'].
$sQry = "INSERT INTO colour_variation (product_code, colour_id) VALUES (%s, %s)";
$i = 0;
foreach ($_POST['colour_option'] AS $colour)
{
if ($i++ > 0)
{
$sQry .= ", ";
}
$sQry .= sprintf("(%s, %s)", GetSQLValueString($_POST['product_code'], "text"), GetSQLValueString($colour, "text"));
}
}

mysql_select_db($database_adlantic, $adlantic);
$query_category = "SELECT * FROM categories ORDER BY category_name ASC";
$category = mysql_query($query_category, $adlantic) or die(mysql_error());
$row_category = mysql_fetch_assoc($category);
$totalRows_category = mysql_num_rows($category);

$colname_subcats = "-1";
if (isset($_POST['category_id'])) {
$colname_subcats = $_POST['category_id'];
}
mysql_select_db($database_adlantic, $adlantic);
$query_subcats = sprintf("SELECT * FROM sub_categories WHERE category_id = %s ORDER BY sub_category_name ASC", GetSQLValueString($colname_subcats, "int"));
$subcats = mysql_query($query_subcats, $adlantic) or die(mysql_error());
$row_subcats = mysql_fetch_assoc($subcats);
$totalRows_subcats = mysql_num_rows($subcats);

mysql_select_db($database_adlantic, $adlantic);
$query_colours = "SELECT * FROM colours ORDER BY colour_name ASC";
$colours = mysql_query($query_colours, $adlantic) or die(mysql_error());
$row_colours = mysql_fetch_assoc($colours);
$totalRows_colours = mysql_num_rows($colours);

mysql_select_db($database_adlantic, $adlantic);
$query_suppliers = "SELECT * FROM supplier ORDER BY supplier_name ASC";
$suppliers = mysql_query($query_suppliers, $adlantic) or die(mysql_error());
$row_suppliers = mysql_fetch_assoc($suppliers);
$totalRows_suppliers = mysql_num_rows($suppliers);
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<script type="text/javascript" src="tiny_mce/tiny_mce.js"></script>
<script src="../SpryAssets/SpryTabbedPanels.js" type="text/javascript"></script>

<script type="text/javascript">
tinyMCE.init({
theme : "advanced",
mode : "textareas",
plugins : "fullpage",
theme_advanced_buttons3_add : "fullpage"
});
</script>
<link href="../SpryAssets/SpryTabbedPanels.css" rel="stylesheet" type="text/css" />
</head>

<body>
<!--<?php include('adminmenu.php') ?>--><br />
<p>Add Product</p>
<form action="<?php echo $editFormAction; ?>" method="POST" enctype="multipart/form-data" name="form1" id="form1">
<table width="700" border="0" cellspacing="4" cellpadding="4">
<tr>
<td><table width="100%" border="0" cellspacing="4" cellpadding="4">
<tr>
<td>Product Name</td>
<td colspan="3"><input name="product_name" type="text" id="product_name" size="60" /></td>
</tr>
<tr>
<td>Category</td>
<td><select name="category_id" id="category_id">
<?php
do {
?>
<option value="<?php echo $row_category['category_id']?>"><?php echo $row_category['category_name']?></option>
<?php
} while ($row_category = mysql_fetch_assoc($category));
$rows = mysql_num_rows($category);
if($rows > 0) {
mysql_data_seek($category, 0);
$row_category = mysql_fetch_assoc($category);
}
?>
</select> </td>
<td>Sub Category</td>
<td><select name="sub_category_id" id="sub_category_id">
<?php
do {
?>
<option value="<?php echo $row_subcats['sub_category_id']?>"><?php echo $row_subcats['sub_category_name']?></option>
<?php
} while ($row_subcats = mysql_fetch_assoc($subcats));
$rows = mysql_num_rows($subcats);
if($rows > 0) {
mysql_data_seek($subcats, 0);
$row_subcats = mysql_fetch_assoc($subcats);
}
?>
</select> </td>
</tr>
<tr>
<td>Price </td>
<td><input type="text" name="product_price" id="product_price" /></td>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
<tr>
<td>Supplier</td>
<td><select name="supplier" id="supplier">
<?php
do {
?>
<option value="<?php echo $row_suppliers['supplier_id']?>"><?php echo $row_suppliers['supplier_name']?></option>
<?php
} while ($row_suppliers = mysql_fetch_assoc($suppliers));
$rows = mysql_num_rows($suppliers);
if($rows > 0) {
mysql_data_seek($suppliers, 0);
$row_suppliers = mysql_fetch_assoc($suppliers);
}
?>
</select> </td>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
<tr>
<td>Product Code</td>
<td><input type="text" name="product_code" id="product_code" /></td>
<td>Supplier Code</td>
<td><input type="text" name="supplier_product_code" id="supplier_product_code" /></td>
</tr>
</table></td>
</tr>
<tr>
<td>Description<br />
<div>
<textarea id="product_description" name="product_description" rows="15" cols="80" style="width: 80%">
&lt;p&gt;
This is some example text that you can edit inside the &lt;strong&gt;TinyMCE editor&lt;/strong&gt;.

</textarea>
</div></td>
</tr>
<tr>
<td></td>
</tr>
<tr>
<td></td>
</tr>

<tr>
<td></td>
</tr>
<tr>
<td><div id="Options" class="TabbedPanels">
<ul class="TabbedPanelsTabGroup">
<li class="TabbedPanelsTab" tabindex="0">Images</li>
<li class="TabbedPanelsTab" tabindex="0">Additional Description</li>
<li class="TabbedPanelsTab" tabindex="0">Colour Options</li>
<li class="TabbedPanelsTab" tabindex="0">Size Options</li>
<li class="TabbedPanelsTab" tabindex="0">Display Options</li>
</ul>
<div class="TabbedPanelsContentGroup">
<div class="TabbedPanelsContent">Please select images for this product:<br />
<br />
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td>Thumbnail:</td>
<td><input type="file" name="product_thumbnail" id="product_thumbnail" /></td>
</tr>
<tr>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
<tr>
<td>Big Image</td>
<td><input type="file" name="product_big_image" id="product_big_image" /></td>
</tr>
</table>
</div>
<div class="TabbedPanelsContent">Enter any additional description here:<br /><br />
<div>
<textarea id="product_additional_description" name="product_additional_description" rows="25" cols="80" style="width: 80%">
&lt;p&gt;
This is some example text that you can edit inside the &lt;strong&gt;TinyMCE editor&lt;/strong&gt;.

</textarea>
</div></div>
<div class="TabbedPanelsContent">Please choose the available colours:<br />
<table>
<tr>
<?php
$colours_endRow = 0;
$colours_columns = 2; // number of columns
$colours_hloopRow1 = 0; // first row flag
do {
if($colours_endRow == 0 && $colours_hloopRow1++ != 0) echo "<tr>";
?>
<td><input type="checkbox" name="colour_option" onClick="javascript:toggleField('variation_price');" id="<?php echo $row_colours['colour_name']; ?>" value="<?php echo $row_colours['colour_id'];?>" />
<?php echo $row_colours['colour_name']; ?></td>
<td>- <i><?php echo $row_colours['supplier_code']; ?></i></td>
<td id="myrow" style="visibility:hidden"></td>
<?php $colours_endRow++;
if($colours_endRow >= $colours_columns) {
?>
<td><input name="variation_price" id="variation_price" type="text" style="display:none;" /></td>
</tr>

<?php
$colours_endRow = 0;
}
} while ($row_colours = mysql_fetch_assoc($colours));
if($colours_endRow != 0) {
while ($colours_endRow < $colours_columns) {
echo("<td>&nbsp;</td>");
$colours_endRow++;
}
echo("</tr>");
}?>
</table>
<br />
</div>
<div class="TabbedPanelsContent">Select the size options available for this product:</div>
<div class="TabbedPanelsContent">Choose your display options here:<br /><br />
<table width="90%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="22%">Special Offer</td>
<td width="12%"><input type="checkbox" name="product_special" id="product_special" /></td>
<td width="26%">Special Offer Price</td>
<td width="40%"><input type="text" name="product_special_price" id="product_special_price" /></td>
</tr>
<tr>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
<tr>
<td>Publish</td>
<td><input type="checkbox" name="product_publish" id="product_publish" /></td>
<td>Display Frontpage</td>
<td><input type="checkbox" name="product_display_frontpage" id="product_display_frontpage" /></td>
</tr>
<tr>
<td>Active</td>
<td><input type="checkbox" name="product_active" id="product_active" /></td>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
</table>
</div>
</div>
</div></td>
</tr>
<tr>
<td><input type="submit" name="submit" id="submit" value="Submit" /></td>
</tr>
</table>
<input type="hidden" name="MM_insert" value="form1" />
</form>
<p>&nbsp; </p>
<script type="text/javascript">
<!--
var TabbedPanels1 = new Spry.Widget.TabbedPanels("Options");
//-->
</script>
</body>
</html>
<?php
mysql_free_result($category);

mysql_free_result($subcats);

mysql_free_result($colours);

mysql_free_result($suppliers);
?>

Fou-Lu
04-10-2013, 08:41 PM
colour_option isn't an array. Add the [] to the checkbox name of it to select the multiple.

MsKazza1
04-15-2013, 12:00 PM
Ok first off thanks for all your help :)

I've added the [] which i missed, however it still adds nothing to the colour_variation table, adds to the products though.

this is the full file, all names etc are correct. there are no error messages it just simple doesn't add to the colour_variation table.


<?php require_once('../Connections/adlantic.php'); ?>
<?php require_once('../ScriptLibrary/incPureUpload.php'); ?>
<?php
// Pure PHP Upload 2.1.12
$ppu = new pureFileUpload();
$ppu->path = "../img/products/thumbs";
$ppu->extensions = "GIF,JPG,JPEG,BMP,PNG";
$ppu->formName = "form1";
$ppu->storeType = "file";
$ppu->sizeLimit = "";
$ppu->nameConflict = "over";
$ppu->nameToLower = false;
$ppu->requireUpload = false;
$ppu->minWidth = "";
$ppu->minHeight = "";
$ppu->maxWidth = "";
$ppu->maxHeight = "";
$ppu->saveWidth = "";
$ppu->saveHeight = "";
$ppu->timeout = "600";
$ppu->progressBar = "";
$ppu->progressWidth = "300";
$ppu->progressHeight = "100";
$ppu->redirectURL = "";
$ppu->checkVersion("2.1.12");
$ppu->doUpload();
?>

<script type="text/javascript" language="javascript">
function toggleField(field) {
var myTarget = document.getElementById(field);
if(myTarget.style.display == 'none'){
myTarget.style.display = 'block';
} else {
myTarget.style.display = 'none';
myTarget.value = '';
}
}
</script>

<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}

if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}

if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
$editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if (isset($editFormAction)) {
if (isset($_SERVER['/QUERY_STRING/'])) {
if (!preg_match("GP_upload=true", $_SERVER['QUERY_STRING'])) {
$editFormAction .= "&GP_upload=true";
}
} else {
$editFormAction .= "?GP_upload=true";
}
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
$insertSQL = sprintf("INSERT INTO products (product_code, supplier_product_code, supplier, category_id, sub_category_id, product_name, product_description, product_additional_description, product_price, product_thumbnail, product_image_big, product_special, product_special_price, product_display_frontpage, product_publish, product_active) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
GetSQLValueString($_POST['product_code'], "text"),
GetSQLValueString($_POST['supplier_product_code'], "text"),
GetSQLValueString($_POST['supplier'], "text"),
GetSQLValueString($_POST['category_id'], "int"),
GetSQLValueString($_POST['sub_category_id'], "int"),
GetSQLValueString($_POST['product_name'], "text"),
GetSQLValueString($_POST['product_description'], "text"),
GetSQLValueString($_POST['product_additional_description'], "text"),
GetSQLValueString($_POST['product_price'], "double"),
GetSQLValueString($_POST['product_thumbnail'], "text"),
GetSQLValueString($_POST['product_big_image'], "text"),
GetSQLValueString(isset($_POST['product_special']) ? "true" : "", "defined","'Y'","'N'"),
GetSQLValueString($_POST['product_special_price'], "double"),
GetSQLValueString(isset($_POST['product_display_frontpage']) ? "true" : "", "defined","'Y'","'N'"),
GetSQLValueString(isset($_POST['product_publish']) ? "true" : "", "defined","'Y'","'N'"),
GetSQLValueString(isset($_POST['product_active']) ? "true" : "", "defined","'Y'","'N'"));

mysql_select_db($database_adlantic, $adlantic);
$Result1 = mysql_query($insertSQL, $adlantic) or die(mysql_error());
}

if (isset($_POST['colour_option']) && is_array($_POST['colour_option']))
{
// I don't know how you are associating, so I'll just use $_POST['product_code'].
$sQry = "INSERT INTO colour_variation (product_code, colour_id) VALUES (%s, %s)";
$i = 0;
foreach ($_POST['colour_option'] AS $colour)
{
if ($i++ > 0)
{
$sQry .= ", ";
}
$sQry .= sprintf("(%s, %s)", GetSQLValueString($_POST['product_code'], "text"), GetSQLValueString($colour, "text"));
}
}

mysql_select_db($database_adlantic, $adlantic);
$query_colours = "SELECT * FROM colours ORDER BY colour_name ASC";
$colours = mysql_query($query_colours, $adlantic) or die(mysql_error());
$row_colours = mysql_fetch_assoc($colours);
$totalRows_colours = mysql_num_rows($colours);

mysql_select_db($database_adlantic, $adlantic);
$query_suppliers = "SELECT * FROM supplier ORDER BY supplier_name ASC";
$suppliers = mysql_query($query_suppliers, $adlantic) or die(mysql_error());
$row_suppliers = mysql_fetch_assoc($suppliers);
$totalRows_suppliers = mysql_num_rows($suppliers);
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<script type="text/javascript" src="tiny_mce/tiny_mce.js"></script>
<script src="../SpryAssets/SpryTabbedPanels.js" type="text/javascript"></script>

<script type="text/javascript">
tinyMCE.init({
theme : "advanced",
mode : "textareas",
plugins : "fullpage",
theme_advanced_buttons3_add : "fullpage"
});
</script>

<?php
$db = new mysqli('localhost','root','','adlantic');//set your database handler
$query = "SELECT id,cat,cat_display_order FROM categories ORDER BY cat_display_order ASC";
$result = $db->query($query);

while($row = $result->fetch_assoc()){
$categories[] = array("id" => $row['id'], "val" => $row['cat']);
}

$query = "SELECT id, catid, subcat FROM sub_categories";
$result = $db->query($query);

while($row = $result->fetch_assoc()){
$subcats[$row['catid']][] = array("id" => $row['id'], "val" => $row['subcat']);
}

$jsonCats = json_encode($categories);
$jsonSubCats = json_encode($subcats);
?>

<script type='text/javascript'>
<?php
echo "var categories = $jsonCats; \n";
echo "var subcats = $jsonSubCats; \n";
?>
function loadCategories(){
var select = document.getElementById("categoriesSelect");
select.onchange = updateSubCats;
for(var i = 0; i < categories.length; i++){
select.options[i] = new Option(categories[i].val,categories[i].id);
}
}
function updateSubCats(){
var catSelect = this;
var catid = this.value;
var subcatSelect = document.getElementById("subcatsSelect");
subcatSelect.options.length = 0; //delete all options if any present
for(var i = 0; i < subcats[catid].length; i++){
subcatSelect.options[i] = new Option(subcats[catid][i].val,subcats[catid][i].id);
}
}
</script>


<link href="../SpryAssets/SpryTabbedPanels.css" rel="stylesheet" type="text/css" />
<script language='JavaScript' src='../ScriptLibrary/incPureUpload.js' type="text/javascript"></script>
<script language='JavaScript' src='../ScriptLibrary/incPureUpload.js' type="text/javascript"></script>
</head>

<body onload='loadCategories()'>
<!--<?php include('adminmenu.php') ?>--><br />
<p>Add Product</p>
<form action="<?php echo $editFormAction; ?>" method="post" enctype="multipart/form-data" name="form1" id="form1" onSubmit="checkFileUpload(this,'GIF,JPG,JPEG,BMP,PNG',false,'','','','','','','');return document.MM_returnValue">
<table width="700" border="0" cellspacing="4" cellpadding="4">
<tr>
<td><table width="100%" border="0" cellspacing="4" cellpadding="4">
<tr>
<td>Product Name</td>
<td colspan="3"><input name="product_name" type="text" id="product_name" size="60" /></td>
</tr>
<tr>
<td>Category</td>
<td><select name="category_id" id="categoriesSelect">
</select> </td>
<td>Sub Category</td>
<td><select name="sub_category_id" id="subcatsSelect">
</select> </td>
</tr>
<tr>
<td>Price </td>
<td><input type="text" name="product_price" id="product_price" /></td>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
<tr>
<td>Supplier</td>
<td><select name="supplier" id="supplier">
<?php
do {
?>
<option value="<?php echo $row_suppliers['supplier_id']?>"><?php echo $row_suppliers['supplier_name']?></option>
<?php
} while ($row_suppliers = mysql_fetch_assoc($suppliers));
$rows = mysql_num_rows($suppliers);
if($rows > 0) {
mysql_data_seek($suppliers, 0);
$row_suppliers = mysql_fetch_assoc($suppliers);
}
?>
</select> </td>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
<tr>
<td>Product Code</td>
<td><input type="text" name="product_code" id="product_code" /></td>
<td>Supplier Code</td>
<td><input type="text" name="supplier_product_code" id="supplier_product_code" /></td>
</tr>
</table></td>
</tr>
<tr>
<td>Description<br />
<div>
<textarea id="product_description" name="product_description" rows="15" cols="80" style="width: 80%">
&lt;p&gt;
This is some example text that you can edit inside the &lt;strong&gt;TinyMCE editor&lt;/strong&gt;.

</textarea>
</div></td>
</tr>
<tr>
<td></td>
</tr>
<tr>
<td></td>
</tr>

<tr>
<td></td>
</tr>
<tr>
<td><div id="Options" class="TabbedPanels">
<ul class="TabbedPanelsTabGroup">
<li class="TabbedPanelsTab" tabindex="0">Images</li>
<li class="TabbedPanelsTab" tabindex="0">Additional Description</li>
<li class="TabbedPanelsTab" tabindex="0">Colour Options</li>
<li class="TabbedPanelsTab" tabindex="0">Size Options</li>
<li class="TabbedPanelsTab" tabindex="0">Display Options</li>
</ul>
<div class="TabbedPanelsContentGroup">
<div class="TabbedPanelsContent">Please select images for this product:<br />
<br />
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td>Thumbnail:</td>
<td><input name="product_thumbnail" type="file" id="product_thumbnail" onChange="checkOneFileUpload(this,'GIF,JPG,JPEG,BMP,PNG',false,'','','','','','','')" /></td>
</tr>
<tr>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
<tr>
<td>Big Image</td>
<td><input name="product_big_image" type="file" id="product_big_image" onChange="checkOneFileUpload(this,'GIF,JPG,JPEG,BMP,PNG',false,'','','','','','','')" /></td>
</tr>
</table>
</div>
<div class="TabbedPanelsContent">Enter any additional description here:<br /><br />
<div>
<textarea id="product_additional_description" name="product_additional_description" rows="25" cols="80" style="width: 80%">
&lt;p&gt;
This is some example text that you can edit inside the &lt;strong&gt;TinyMCE editor&lt;/strong&gt;.

</textarea>
</div></div>
<div class="TabbedPanelsContent">Please choose the available colours:<br />
<table>
<tr>
<?php
$colours_endRow = 0;
$colours_columns = 2; // number of columns
$colours_hloopRow1 = 0; // first row flag
do {
if($colours_endRow == 0 && $colours_hloopRow1++ != 0) echo "<tr>";
?>
<td><input type="checkbox" name="colour_option[]" onClick="javascript:toggleField('variation_price');" id="<?php echo $row_colours['colour_name']; ?>" value="<?php echo $row_colours['colour_id'];?>" />
<?php echo $row_colours['colour_name']; ?></td>
<td>- <i><?php echo $row_colours['supplier_code']; ?></i></td>
<td id="myrow" style="visibility:hidden"></td>
<?php $colours_endRow++;
if($colours_endRow >= $colours_columns) {
?>
<td><input name="variation_price" id="variation_price" type="text" style="display:none;" /></td>
</tr>

<?php
$colours_endRow = 0;
}
} while ($row_colours = mysql_fetch_assoc($colours));
if($colours_endRow != 0) {
while ($colours_endRow < $colours_columns) {
echo("<td>&nbsp;</td>");
$colours_endRow++;
}
echo("</tr>");
}?>
</table>
<br />
</div>
<div class="TabbedPanelsContent">Select the size options available for this product:</div>
<div class="TabbedPanelsContent">Choose your display options here:<br /><br />
<table width="90%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="22%">Special Offer</td>
<td width="12%"><input type="checkbox" name="product_special" id="product_special" /></td>
<td width="26%">Special Offer Price</td>
<td width="40%"><input type="text" name="product_special_price" id="product_special_price" /></td>
</tr>
<tr>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
<tr>
<td>Publish</td>
<td><input type="checkbox" name="product_publish" id="product_publish" /></td>
<td>Display Frontpage</td>
<td><input type="checkbox" name="product_display_frontpage" id="product_display_frontpage" /></td>
</tr>
<tr>
<td>Active</td>
<td><input type="checkbox" name="product_active" id="product_active" /></td>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
</table>
</div>
</div>
</div></td>
</tr>
<tr>
<td><input type="submit" name="submit" id="submit" value="Submit" /></td>
</tr>
</table>
<input type="hidden" name="MM_insert" value="form1" />
</form>
<p>&nbsp; </p>
<script type="text/javascript">
<!--
var TabbedPanels1 = new Spry.Widget.TabbedPanels("Options");
//-->
</script>
</body>
</html>
<?php
mysql_free_result($colours);

mysql_free_result($suppliers);
?>



thanks so much

Fou-Lu
04-15-2013, 01:23 PM
You don't have an execute for $sQry. Add that after the loop.
That's not the same as I've written though:


if (isset($_POST['colour_option']) && is_array($_POST['colour_option']))
{
// I don't know how you are associating, so I'll just use $_POST['product_code'].
$sQry = "INSERT INTO colour_variation (product_code, colour_id) VALUES ";
$i = 0;
foreach ($_POST['colour_option'] AS $colour)
{
if ($i++ > )
{
$sQry .= ", ";
}
$sQry .= sprintf("(%s, %s)", GetSQLValueString($_POST['product_code'], "text"), GetSQLValueString($colour, "text"));
}
mysql_query($sQry);
}

You also have three of the same function declarations. Remove two of them as they are redundant.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum