Page 1 of 1
editing large array / database table - guidance needed
Posted: Mon Nov 24, 2008 2:46 pm
by sjk1000
Hi all
To the best of my current abilities, I've produced the attached form within a table that displays database values on my site. I'm trying to have the 4 numerical values - Quantity, Price, Shipping, SD Price - editable in the input fields on the right. When I have only minor changes, i.e. to one row, I want to click on the Send button for that row. When I have a large number of changes to make I want to click the Send All which will submit the whole table to the database.
At present the form handler doesn't exist because I'm not confident in where I'm heading next. I believe I need each row's Send button associated with the product ID and the values - see highlights in blue, then in the handler I'll distinguish between the Send All and Send Row buttons, remove the non-null elements and submit these to the database.
First of all, is this the right thing to do? If not, can someone please point me in the right direction eg a tutorials, threads, similar scripts
Any help really is very much appreciated.
Many thanks, Steve

- Admintable.jpg (89.34 KiB) Viewed 1062 times
function display_db_table($tablename)
{
$query_string = "SELECT products.products_id, products.products_model, products_name, stock_quantity, seller_stock.products_price, shipping_price, seller_sameday_price
FROM products_description, seller_stock, products
WHERE products_description.products_id = products.products_id
AND products.products_model = seller_stock.products_model
AND seller_id = 2
ORDER BY products_name";
$result_id = mysql_query($query_string);
$column_count = mysql_num_fields($result_id);
print("<form action='".$_SERVER['PHP_SELF']."' method='post'>");
print("<TABLE ALIGN=center VALIGN=TOP width='875' BORDER=1>\n");
print("<TR align=center><TH>Prod ID</TH><TH>Supplier Ref</TH><TH>Title</TH><TH>Qty</TH><TH>Price</TH><TH>Shipping</TH><TH>SD Price</TH><TH>Qty</TH><TH>Price</TH><TH>Shipping</TH><TH>SD Price</TH><TH><input SIZE='5' type='submit' name='SubmitForm' value='Send All'></TH></TR>");
while ($row = mysql_fetch_row($result_id))
{
print("<TR ALIGN=LEFT VALIGN=TOP>");
for ($column_num = 0;
$column_num < $column_count;
$column_num++)
print("<TD>  $row[$column_num]   </TD>\n");
print "<TD><input SIZE='5' type='text' name= ".$quantity."id='quantity' VALUE = ''></TD>";
print "<TD><input SIZE='5' type='text' name= ".$price."id='price' VALUE = ''></TD>";
print "<TD><input SIZE='5' type='text' name= ".$shipping."id='shipping' VALUE = ''></TD>";
print "<TD><input SIZE='5' type='text' name= ".$sdprice."id='sdprice' VALUE = ''></TD>";
print "<TD align = center><input SIZE='5' type='submit' name='SubmitForm' value='Send Row'></TD></TR>";
}
print("</TABLE>\n");}
print("</form>");
?>
Re: editing large array / database table - guidance needed
Posted: Mon Nov 24, 2008 3:33 pm
by dude81
Uh? Shopping cart, even I'm stuck with some kind of problem with it too. But there are few suggestions that I can tell you. I dont know they are right or wrong, but defnitely its my way of doing this, If I were in your place
Code: Select all
function display_db_table($tablename)
{
$query_string = "SELECT products.products_id, products.products_model, products_name, stock_quantity, seller_stock.products_price, shipping_price, seller_sameday_price
FROM products_description, seller_stock, products
WHERE products_description.products_id = products.products_id
AND products.products_model = seller_stock.products_model
AND seller_id = 2
ORDER BY products_name";
$result_id = mysql_query($query_string);
$column_count = mysql_num_fields($result_id);
print("<form action='".$_SERVER['PHP_SELF']."' method='post'>");
print("<TABLE ALIGN=center VALIGN=TOP width='875' BORDER=1>\n");
print("<TR align=center><TH>Prod ID</TH><TH>Supplier Ref</TH><TH>Title</TH><TH>Qty</TH><TH>Price</TH><TH>Shipping</TH><TH>SD Price</TH><TH>Qty</TH><TH>Price</TH><TH>Shipping</TH><TH>SD Price</TH><TH><input SIZE='5' type='submit' name='SubmitForm' value='Send All'></TH></TR>");
while ($row = mysql_fetch_row($result_id))
{
print("<TR ALIGN=LEFT VALIGN=TOP>");
for ($column_num = 0;
$column_num < $column_count;
$column_num++)
print("<TD>  $row[$column_num]   </TD>\n");
print "<TD><input SIZE='5' type='text' name= ".$quantity."id='quantity' VALUE = ''></TD>";
print "<TD><input SIZE='5' type='text' name= ".$price."id='price' VALUE = ''></TD>";
print "<TD><input SIZE='5' type='text' name= ".$shipping."id='shipping' VALUE = ''></TD>";
print "<TD><input SIZE='5' type='text' name= ".$sdprice."id='sdprice' VALUE = ''></TD>";
print "<TD align = center><input SIZE='5' type='submit' name='SubmitForm' value='Send Row'></TD></TR>";
}
print("</TABLE>\n");}
print("</form>");
?>
Comments on the form: I doubt name=".$quantity." would work? even though if it works, but definitely must be out of naming conventions. it can be of the form name='".$quantity."id' id='".quantity."id' and so the other form variables
There are two solutions I can see how you can achieve submitting each row or all rows
One way is do the following making each row as a form or get URL with an anchor tag around the button
Code: Select all
<a href="send_row.php?name='".$quantity."&price='".$price."'&ship='".$shipping."'&sdprice='".$sdprice."'><input type='button' name='SubmitForm' value='Send Row'> </a>
The other way is
Code: Select all
<input type="button" name="SubmitForm" value="Send Row" onclick="Javascript:Ajaxfunction">
Re: editing large array / database table - guidance needed
Posted: Tue Nov 25, 2008 5:32 am
by sjk1000
Thanks for the quick reply Dude.
I tried to highlight in blue the variable names that I expected not to work but it didn't take the color for some reason.
As I haven't got my head into Javascript yet I'll go for the first option. I can deal with the Send Row, but how do you see the Send All working - I may have 2-300 rows?
Thanks, Steve
Re: editing large array / database table - guidance needed
Posted: Tue Nov 25, 2008 5:48 am
by aceconcepts
Stick with PHP for this.
What you want to do is pass each field as an array.
Code: Select all
print "<TD><input SIZE='5' type='text' name='qty[]' id='quantity' VALUE ='".$qty."'></TD>";
Adding the square brackets after the elemt's name will pass this field as an array.
Also, create a hidden field to contain the item's ID from the database that uniquley identifies this item e.g.
Code: Select all
echo'<input type="hidden" name="itemId[]" value="'.$row['itemId'].'" />';
The reason you pass the fields as an array is because you can retrieve each individual field using a for loop without having to use different names for each field. As the for loop counter increments, the counter is used to identify each index in the elements array - it's easier to demonstrate.
Let me know if you kind of understand this logic and i'll help you code it

Re: editing large array / database table - guidance needed
Posted: Tue Nov 25, 2008 6:15 am
by sjk1000
Thanks very much for your help here.
I see where we're headed (for the first time in days

)
I've amended the script below to include the arrays - correct me if i'm wrong.
Code: Select all
function display_db_table($tablename)
{
$query_string = "SELECT products.products_id, products.products_model, products_name, stock_quantity, seller_stock.products_price, shipping_price, seller_sameday_price
FROM products_description, seller_stock, products
WHERE products_description.products_id = products.products_id
AND products.products_model = seller_stock.products_model
AND seller_id = 2
ORDER BY products_name";
$result_id = mysql_query($query_string);
$column_count = mysql_num_fields($result_id);
print("<form action='".$_SERVER['PHP_SELF']."' method='post'>");
print("<form action='".$_SERVER['PHP_SELF']."' method='post'>");
print("<TABLE ALIGN=center VALIGN=TOP width='875' BORDER=1>\n");
print("<TR align=center><TH>Prod ID</TH><TH>Supplier Ref</TH><TH>Title</TH><TH>Qty</TH><TH>Price</TH><TH>Shipping</TH><TH>SD Price</TH><TH>Qty</TH><TH>Price</TH><TH>Shipping</TH><TH>SD Price</TH><TH><input SIZE='5' type='submit' name='SubmitForm' value='Send All'></TH></TR>");
while ($row = mysql_fetch_row($result_id))
{
print("<TR ALIGN=LEFT VALIGN=TOP>");
for ($column_num = 0;
$column_num < $column_count;
$column_num++)
print("<TD>  $row[$column_num]   </TD>\n");
print "<TD><input SIZE='5' type='text' name='qty[]' id='quantity' VALUE ='".$qty."'></TD>";
print "<TD><input SIZE='5' type='text' name='price[]' id='price' VALUE ='".$price."'></TD>";
print "<TD><input SIZE='5' type='text' name='shipping[]' id='shipping' VALUE ='".$shipping."'></TD>";
print "<TD><input SIZE='5' type='text' name='sdprice[]' id='sdprice' VALUE ='".$sdprice."'></TD>";
print "<TD align = center><input SIZE='5' type='submit' name='SubmitForm' value='Send Row'></TD></TR>";
echo '<input type="hidden" name="itemId[]" value="'.$row['itemId'].'" />';
}
print("</TABLE>\n");}
print("</form>");
?>
The hidden field is inserted and offsets the rows in the displayed table but I'll deal with that one later!
Re: editing large array / database table - guidance needed
Posted: Tue Nov 25, 2008 7:45 am
by aceconcepts
I've slightly revised your code. The hidden field shouldn't cause any problems anymore.
Code: Select all
function display_db_table($tablename)
{
$query_string = "SELECT products.products_id, products.products_model, products_name, stock_quantity, seller_stock.products_price, shipping_price, seller_sameday_price
FROM products_description, seller_stock, products
WHERE products_description.products_id = products.products_id
AND products.products_model = seller_stock.products_model
AND seller_id = 2
ORDER BY products_name";
$result_id = mysql_query($query_string);
$column_count = mysql_num_fields($result_id);
print("<form action='".$_SERVER['PHP_SELF']."' method='post'>");
print("<TABLE ALIGN=center VALIGN=TOP width='875' BORDER=1>\n");
print("<TR align=center><TH>Prod ID</TH><TH>Supplier Ref</TH><TH>Title</TH><TH>Qty</TH><TH>Price</TH><TH>Shipping</TH><TH>SD Price</TH><TH>Qty</TH><TH>Price</TH><TH>Shipping</TH><TH>SD Price</TH><TH><input SIZE='5' type='submit' name='SubmitForm' value='Send All'></TH></TR>");
while ($row = mysql_fetch_row($result_id))
{
print("<TR ALIGN=LEFT VALIGN=TOP>");
for ($column_num = 0;
$column_num < $column_count;
$column_num++)
print("<TD>  $row[$column_num]   </TD>\n");
print "<TD><input SIZE='5' type='text' name='qty[]' id='quantity' VALUE ='".$qty."'></TD>";
print "<TD><input SIZE='5' type='text' name='price[]' id='price' VALUE ='".$price."'></TD>";
print "<TD><input SIZE='5' type='text' name='shipping[]' id='shipping' VALUE ='".$shipping."'></TD>";
print "<TD><input SIZE='5' type='text' name='sdprice[]' id='sdprice' VALUE ='".$sdprice."'></TD>";
print "<TD align = center><input SIZE='5' type='submit' name='SubmitForm' value='Send Row'><input type="hidden" name="itemId[]" value="'.$row['itemId'].'" style="width:0; height:0;" /></TD></TR>";
}
print("</TABLE>\n");}
print("</form>");
?>
Obviously the itemId i used is an example. The field name you should use here is the ID of the item from your products table. Ok?
Re: editing large array / database table - guidance needed
Posted: Tue Nov 25, 2008 8:44 am
by sjk1000
not quite.
I lose the whole page with the latest version
With the Item ID. The
key for the table is the product ID held in the first column of each row. Is the following correct?
Code: Select all
print "<TD align = center><input SIZE='5' type='submit' name='SubmitForm' value='Send Row'><input type="hidden" name="productid[]" value="'.$row[0].'" style="width:0; height:0;" /></TD></TR>";
Code: Select all
<?php
function display_db_table($tablename)
{
$query_string = "SELECT products.products_id, products.products_model, products_name, stock_quantity, seller_stock.products_price, shipping_price, seller_sameday_price
FROM products_description, seller_stock, products
WHERE products_description.products_id = products.products_id
AND products.products_model = seller_stock.products_model
AND seller_id = 2
ORDER BY products_name";
$result_id = mysql_query($query_string);
$column_count = mysql_num_fields($result_id);
print("<form action='".$_SERVER['PHP_SELF']."' method='post'>");
print("<TABLE ALIGN=center VALIGN=TOP width='875' BORDER=1>\n");
print("<TR align=center><TH>Prod ID</TH><TH>Supplier Ref</TH><TH>Title</TH><TH>Qty</TH><TH>Price</TH><TH>Shipping</TH><TH>SD Price</TH><TH>Qty</TH><TH>Price</TH><TH>Shipping</TH><TH>SD Price</TH><TH><input SIZE='5' type='submit' name='SubmitForm' value='Send All'></TH></TR>");
while ($row = mysql_fetch_row($result_id))
{
print("<TR ALIGN=LEFT VALIGN=TOP>");
for ($column_num = 0;
$column_num < $column_count;
$column_num++)
print("<TD>  $row[$column_num]   </TD>\n");
print "<TD><input SIZE='5' type='text' name='qty[]' id='quantity' VALUE ='".$qty."'></TD>";
print "<TD><input SIZE='5' type='text' name='price[]' id='price' VALUE ='".$price."'></TD>";
print "<TD><input SIZE='5' type='text' name='shipping[]' id='shipping' VALUE ='".$shipping."'></TD>";
print "<TD><input SIZE='5' type='text' name='sdprice[]' id='sdprice' VALUE ='".$sdprice."'></TD>";
print "<TD align = center><input SIZE='5' type='submit' name='SubmitForm' value='Send Row'></TD></TR>";
print "<TD align = center><input SIZE='5' type='submit' name='SubmitForm' value='Send Row'><input type="hidden" name="itemId[]" value="'.$row['itemId'].'" style="width:0; height:0;" /></TD></TR>";
}
print("</TABLE>\n");}
print("</form>");
?>
Re: editing large array / database table - guidance needed
Posted: Tue Nov 25, 2008 8:55 am
by aceconcepts
Sorry, I used double quotes when i should have used single quotes in the hidden field.
It should be ok with the below code.
By the way your snippet or code for the hidden field looked good - i've put it in place.
Code: Select all
<?php
function display_db_table($tablename)
{
$query_string = "SELECT products.products_id, products.products_model, products_name, stock_quantity, seller_stock.products_price, shipping_price, seller_sameday_price
FROM products_description, seller_stock, products
WHERE products_description.products_id = products.products_id
AND products.products_model = seller_stock.products_model
AND seller_id = 2
ORDER BY products_name";
$result_id = mysql_query($query_string);
$column_count = mysql_num_fields($result_id);
print("<form action='".$_SERVER['PHP_SELF']."' method='post'>");
print("<TABLE ALIGN=center VALIGN=TOP width='875' BORDER=1>\n");
print("<TR align=center><TH>Prod ID</TH><TH>Supplier Ref</TH><TH>Title</TH><TH>Qty</TH><TH>Price</TH><TH>Shipping</TH><TH>SD Price</TH><TH>Qty</TH><TH>Price</TH><TH>Shipping</TH><TH>SD Price</TH><TH><input SIZE='5' type='submit' name='SubmitForm' value='Send All'></TH></TR>");
while ($row = mysql_fetch_row($result_id))
{
print("<TR ALIGN=LEFT VALIGN=TOP>");
for ($column_num = 0;
$column_num < $column_count;
$column_num++)
print("<TD>  $row[$column_num]   </TD>\n");
print "<TD><input SIZE='5' type='text' name='qty[]' id='quantity' VALUE ='".$qty."'></TD>";
print "<TD><input SIZE='5' type='text' name='price[]' id='price' VALUE ='".$price."'></TD>";
print "<TD><input SIZE='5' type='text' name='shipping[]' id='shipping' VALUE ='".$shipping."'></TD>";
print "<TD><input SIZE='5' type='text' name='sdprice[]' id='sdprice' VALUE ='".$sdprice."'></TD>";
print "<TD align = center><input SIZE='5' type='submit' name='SubmitForm' value='Send Row'></TD></TR>";
print "<TD align = center><input SIZE='5' type='submit' name='SubmitForm' value='Send Row'><input type='hidden' name='productid[]' value='".$row[0]."' style='width:0; height:0;' /></TD></TR>";
}
print("</TABLE>\n");}
print("</form>");
?>
Let me know if this works.
Re: editing large array / database table - guidance needed
Posted: Tue Nov 25, 2008 9:24 am
by sjk1000
That did it!
Re: editing large array / database table - guidance needed
Posted: Tue Nov 25, 2008 9:31 am
by aceconcepts
Excellent Smithers.
So, what you need to do now is validate the POST procedure.
Create a new file and name it something like "product_validation.php".
From the top of you current file write the following at the top (primarily before the form:
Code: Select all
<? include"product_validation.php"; ?>
Re: editing large array / database table - guidance needed
Posted: Tue Nov 25, 2008 9:39 am
by sjk1000
Consider it done.....
Re: editing large array / database table - guidance needed
Posted: Tue Nov 25, 2008 10:00 am
by aceconcepts
What we'll do is write a script for the "Send All" button. First you should rename the "Send All" button to "submitAll".
Next, within product_validation.php write the following:
Code: Select all
if(isset($_POST['submitAll'])) //checks whether the "Send All" button has been clicked
{
//get POSTED fields - each variable below has been passed as an array so we'll need to construct a loop to read their values
$qty=$_POST['qty'];
$price=$_POST['price'];
$shipping=$_POST['shipping'];
$sdprice=$_POST['sdprice'];
$productId=$_POST['productId'];
//create loop to read POSTED array values - a for loop is used when you can determine how many loops will be required i.e. we can count how many values exists in any of the POSTED array
//the count we perform is the amount of time we'll loop
//in english a for loop is like: for(starting point; how many times to loop; increment counter after each loop)
for($x=0; $x<count($_POST['productId']); $x++)
{
//now we are inside the loop we can read all the arrays we defined above - arrays start from 0 so we have set $x equal to 0 as the starting point above
//$qty[$x] is equal to the current field you display in your table
}
}
I'll stop there so you can absorb it so far. Let me know if there is anything thats unclear.
By the way, this isn't finished and has not been tested. If you run this now, nothing will happen.
Re: editing large array / database table - guidance needed
Posted: Tue Nov 25, 2008 10:26 am
by sjk1000
Thanks a load for that lot. I'll have a stab at displaying the posted fields and getting them back to the database then I'll work on picking out the individual rows from the Send Row and see how far I get.
Cheers, Steve

Re: editing large array / database table - guidance needed
Posted: Tue Nov 25, 2008 10:54 am
by aceconcepts
No probs. Let me know if you have any problems
