editing large array / database table - guidance needed

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
sjk1000
Forum Commoner
Posts: 26
Joined: Tue Nov 11, 2008 8:50 am

editing large array / database table - guidance needed

Post 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
Admintable.jpg (89.34 KiB) Viewed 1063 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>&nbsp $row[$column_num] &nbsp </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>");
?>
User avatar
dude81
Forum Regular
Posts: 509
Joined: Mon Aug 29, 2005 6:26 am
Location: Pearls City

Re: editing large array / database table - guidance needed

Post 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>&nbsp $row[$column_num] &nbsp </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&#058;Ajaxfunction">
sjk1000
Forum Commoner
Posts: 26
Joined: Tue Nov 11, 2008 8:50 am

Re: editing large array / database table - guidance needed

Post 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
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: editing large array / database table - guidance needed

Post 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 :wink:
sjk1000
Forum Commoner
Posts: 26
Joined: Tue Nov 11, 2008 8:50 am

Re: editing large array / database table - guidance needed

Post by sjk1000 »

Thanks very much for your help here.

I see where we're headed (for the first time in days :wink: )

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>&nbsp $row[$column_num] &nbsp </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!
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: editing large array / database table - guidance needed

Post 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>&nbsp $row[$column_num] &nbsp </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?
sjk1000
Forum Commoner
Posts: 26
Joined: Tue Nov 11, 2008 8:50 am

Re: editing large array / database table - guidance needed

Post by sjk1000 »

not quite. :D
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>&nbsp $row[$column_num] &nbsp </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>");
?>
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: editing large array / database table - guidance needed

Post 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>&nbsp $row[$column_num] &nbsp </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.
sjk1000
Forum Commoner
Posts: 26
Joined: Tue Nov 11, 2008 8:50 am

Re: editing large array / database table - guidance needed

Post by sjk1000 »

That did it!
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: editing large array / database table - guidance needed

Post 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"; ?>
sjk1000
Forum Commoner
Posts: 26
Joined: Tue Nov 11, 2008 8:50 am

Re: editing large array / database table - guidance needed

Post by sjk1000 »

Consider it done.....
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: editing large array / database table - guidance needed

Post 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.
sjk1000
Forum Commoner
Posts: 26
Joined: Tue Nov 11, 2008 8:50 am

Re: editing large array / database table - guidance needed

Post 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
:D
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: editing large array / database table - guidance needed

Post by aceconcepts »

No probs. Let me know if you have any problems :D
Post Reply