Insert HTML Table cell values into MySQL

Question

I have a HTML Table with Dynamic Columns which increase or decrease as per the user's selection. I need to Insert this HTML Table in a MySQL Database table after taking inputs from the users in the front end.

The columns from Category to Product Name are constant post that the location column changes depending upon the user requirement.

Once the input is made and clicked on save i need to insert this data into production table in the below format:

The code that i have completed so far kindly guide me with a proper direction:

<?php
    if(isset($_POST['for_post_market'])){ $market = $_POST['for_post_market']; } 
    if(isset($_POST['for_post_prod_date'])){ $date_prod = $_POST['for_post_prod_date']; } 
    if(isset($_POST['for_post_sale_date'])){ $date_sale = $_POST['for_post_sale_date']; } 
    $query = 'SELECT * FROM product WHERE prod_status="Active"';
    $stmt = $DB_con->prepare($query);
    $stmt->execute();
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $result[$row['prod_cat_name']][] = $row['prod_id'];

    }
?>
    <form method="post">
    <table id="invoices" border="1" class="table table-striped table-bordered">
        <thead>
            <col width="65">
            <col width="65">
            <th>Category</th>
            <th>Product ID</th>
            <th>Product Name</th>
            <th hidden="true">Production Date</th>
            <th hidden="true">Sales Date</th>
<?php
    $a=count($market);
    for($i=0;$i<$a;$i++) {
        echo '<th><input type="hidden" value="'. $market[$i] . '">'. $market[$i] .'</th>';
    }
?>
        </thead>
        <tbody>
<?php
    foreach($result as $id => $invoices) {
        echo '<tr>';
        echo '<td rowspan='. count($invoices) . '>' . $id . '</td>';
        $count = 0;
        foreach ($invoices as $invoice) {
            if ($count != 0) {
                echo '<tr>';
            }
            $count++;

            echo '<td>' . $invoice . '</td>';
?>
<?php 
            $psql = $DB_con->prepare("SELECT * FROM product WHERE prod_id='$invoice'");
            $psql->execute();
            $resultpro = $psql->fetchall(PDO::FETCH_ASSOC);
            foreach($resultpro as $line) {
            }
?>
            <td><?php echo $line['prod_name']; ?></td>
<?php
            echo '<td hidden="true">' . $date_prod . '</td>';
            echo '<td hidden="true">' . $date_sale . '</td>';
            $a=count($market);
            for($j=0;$j<$a;$j++) {
                echo '<td><input type="text" name="'. $market[$j] .' "class="form-control" maxlength="6" size="4"></td>';
            }
        }
    }
    echo '</tbody>';
    echo '</table>';
?>
        <button type="submit" class="btn btn-default waves-effect waves-light" name="btn-saveforcast" id="btn-saveforcast">Save</button>
    </form>
<?php
    }
    catch(PDOException $e)
    {
        echo $e->getMessage();
    }

}
else
{
?>

Complete Code Link

Would you like to go through this code and remove all the empty IF and FOR loops, match up all the { and } so we can be sure what it is you think you are doing before we attempt to make any suggestions — RiggsFolly
– RiggsFolly, Commented Dec 16, 2015 at 21:18

RaminS · Accepted Answer · 2015-12-16 21:43:32Z

I tried formatting your code to make it somewhat readable and this is what I got:

<?php 

if(isset($_POST[ 'for_post_market'])){ 
  $market=$ _POST[ 'for_post_market']; 
} 
if(isset($_POST[ 'for_post_prod_date'])){ 
  $date_prod=$ _POST[ 'for_post_prod_date']; 
} 
if(isset($_POST[ 'for_post_sale_date'])){ 
  $date_sale=$ _POST[ 'for_post_sale_date']; 
} 
$query='SELECT * FROM product WHERE prod_status="Active"' ; 
$stmt=$ DB_con->prepare($query); 
$stmt->execute(); 
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { 
  $result[$row['prod_cat_name']][] = $row['prod_id']; 
} 

?>

<form method="post">
    <table id="invoices" border="1" class="table table-striped table-bordered">
        <thead>
            <col width="65">
                <col width="65">
                    <th>Category</th>
                    <th>Product ID</th>
                    <th>Product Name</th>
                    <th hidden="true">Production Date</th>
                    <th hidden="true">Sales Date</th>

                    <?php 
                    $a=count($market); for($i=0;$i<$a;$i++) { echo '<th><input type="hidden" value="'. $market[$i] . '">'. $market[$i] . '</th>'; } 
                    ?>
        </thead>
        <tbody>

            <?php foreach($result as $id=> $invoices) { 
                      echo '<tr>'; 
                      echo '<td rowspan='. count($invoices) . '>' . $id . '</td>'; 
                      $count = 0; 
                      foreach ($invoices as $invoice) { 
                        if ($count != 0) { 
                          echo '<tr>'; 
                        } 
                        $count++; 
                        echo '<td>' . $invoice . '</td>'; 
            ?>
                    <?php 
                        $psql=$ DB_con->prepare("SELECT * FROM product WHERE prod_id='$invoice'"); 
                        $psql->execute(); 
                        $resultpro = $psql->fetchall(PDO::FETCH_ASSOC); 
                        foreach($resultpro as $line) { } 
                    ?>

                    <td>

                        <?php 
                        echo $line[ 'prod_name']; 
                        ?>

                    </td>

                    <?php 
                        echo '<td hidden="true">' . $date_prod . '</td>'; 
                        echo '<td hidden="true">' . $date_sale . '</td>'; 
                        $a=count($market); 
                        for($j=0;$j<$a;$j++) { 
                          echo '<td><input type="text" name="'. $market[$j] . ' "class="form-control" maxlength="6" size="4"></td>'; 
                        } 
                      } 
                    } 
                    echo '</tbody>'; 
                    echo '</table>';
                    ?>

                    <button type="submit" class="btn btn-default waves-effect waves-light" name="btn-saveforcast" id="btn-saveforcast">Save</button>
</form>

<?php 

} catch(PDOException $e) { 
  echo $e->getMessage(); 
} 
} else { 

?>

There are a lot of syntax-errors in your code, but that is not the main issue. The problem is that you have totally misunderstood how HTML and PHP work.

HTML is a language that describes the layout of the page to a web-browser (e.g. Google Chrome). The browser reads the HTML code and then displays the page on your computer screen in the way that it thinks is good. That is it. HTML doesn't do any computations; it is simply a way of describing the layout of the page.

PHP on the other hand, is a back-end scripting language. This means that the PHP script is executed by the server, as opposed to HTML and JavaScript, that your browser takes care of.

I am not sure what you have tried to do here, but I see you have inserted PHP-scripts in table-columns in several places. This does not make any sense at all.

HTML is not a scripting-language so it does not have any order of execution (because it has nothing to execute). It really has nothing to do with PHP at all.

If you want to connect PHP to HTML, meaning that you want data from your server (database) to be displayed on the web page, you need to use JavaScript. JavaScript has the ability to alter HTML, and PHP can echo JavaScript-commands. However, keep in mind: PHP is executed by the server (back-end) and JavaScript is executed by the user (front-end). This means that the JavaScript is really not echoed until the whole PHP-script ends, so you cannot dance back and forth between JavaScript and PHP.

What you should do is something like this:

Obtain the user input via the form and send it to the server:

<form action="foo.php" method="post"> ... </form>

Handle the input with the PHP-script called "foo.php". Your SQL-queries and JavaScript echoes happen here. But again, don't forget, the echoes doesn't reach the user until the PHP script ends.

I am not exactly sure what you are trying to achieve, so I cannot help you much more than this. If you provide more information you can get better help.

I have hosted a demo here, clean.omegakart.com Login ID admin Pass : 123456 What i am trying to do is insert a populated data and input fields to the database table. But my problem is the columns are not constant while taking the input so i am trying to figure out a way
Your website doesn't tell me much. It's pretty empty. Are you trying to take user input and insert in your MySQL, take data from the database and displaying it in a table, or both?
First call the data from the database table products. and then take the inut and then trying to insert the updated the table in new sql table
Oh, so it is a way for the admin to change the data inside the database without having to have any programming knowledge?

Marvinoo_ · Accepted Answer · 2015-12-16 21:07:52Z

1

Put the results inside an array than loop trough the array using a for loop. Inside the for loop put the mysql query to insert into the database.

Don't forget to put the $_POST['name'][$i]; like this inside the for loop so it will insert proper.

Hope this helped.

answered Dec 16, 2015 at 21:07

Marvinoo_

791 gold badge2 silver badges9 bronze badges

5 Comments

Sumeet Pujari Over a year ago

Array was the first thought buy my problem is the input box is variable and it can be two or 4 depending on the market selected before. And i also want to know the input field to which market it is filled for, so i want to also populate the market name first and the respective input for the product ID

Marvinoo_ Over a year ago

So, if i understand you correct. The input can be 2 or 4 depends on users selection on which market one step back in the form. You want to know which market it is filled so a check on the input var (You have different market name tables right?) And the rest is the same on every input right? I thought you want to add them all inside one table.. So what you can do is like this: You have a table named: market1 -> 4 rows market2 -> 2 Rows if ($_POST['postnameofmarket'] == 'market1'){ execute the for loop, looping trough the inputs } Do the same for the other post is what you're looking for?

Sumeet Pujari Over a year ago

Not Excatly if you check the demo link clean.omegakart.com login: admin pass 123456 and go under forecast i am sure you would get a better insight. I have only one table and it has a market column.the header in table which has input box will have the market name so if there is a update then for one product ID if there are 3 market columns it will add three times the product under different market name and the input calue that is captured from front end.

Marvinoo_ Over a year ago

Aaaah i get it. Like this?: You create a forcast --> Selected forcasts are like this: Forecast 1 Forecast 3 Forecast 7 The rest is not selected then inside there you want to insert data Jellies Super Jellies Forecast 1 Jellies Super Jellies Forecast 3 Jellies Small Jellies Forecast 7 And this is what you want to process? Also this is where you want to put an update function for?

Sumeet Pujari Over a year ago

Yup close to this.. if i can get a direction on how i can make an array and loop it in properly considering the columns being dynamic i can complete this code.

Mr Lister · Accepted Answer · 2015-12-21 20:22:50Z

1

Your attribute name have the same value for all <input> fields For example "97th Street" etc. Use market ID instead with some prefix. Check this first, and try to avoid spaces in names.

Each <input> value with identical names will be overwritten.

edited Dec 21, 2015 at 20:22

Mr Lister

46.8k15 gold badges118 silver badges156 bronze badges

answered Dec 16, 2015 at 22:02

JuZer

7832 gold badges7 silver badges14 bronze badges

4 Comments

Sumeet Pujari Over a year ago

The value is an array.. and this is the problem that one product has multiple market and i want to capture everything in a go.

JuZer Over a year ago

As I see from Your code it is not an array! It's just asssigning names in for loop. If You want it to be an array put brackets in the end, like <input type="text" name="'. $market[$j][] . ' And anyhow replace spaces in names with something, orr just remove spaces.

JuZer Over a year ago

Make var_dump($_POST); so we can see what You have there!

JuZer Over a year ago

Once again! If You put empty square brackets in the end of "name" attribute You will gain what You want!!! Array ( [79th_Street] => Array ( [0] => One [1] => Two [2] => Three [3] => Four ) [btn-saveforcast] => )

JuZer · Accepted Answer · 2015-12-17 07:54:22Z

0

Put empty square brackets in the end of "name" attribute like

<input type="text" name="'. $market[$j][] . '

and You will gain what You want!!!

Array
(
    [79th_Street] = Array
        (
            [0] = one
            [1] = two
            [2] = three
            [3] = four
        )

    [btn-saveforcast] = ; 
)

Additionally You can put "Product ID" to this array and then You can get separete arrays for each ProductID

<input type="text" name="'. $market[$j][$product_id][] . '

and result will be:

Array
(
    [79th_Street] = Array
        (
            [2] = Array
                (
                    [0] = one
                )

            [3] = Array
                (
                    [0] = two
                    [1] = three
                    [2] = four
                )

        )

    [btn-saveforcast] = ; 
)

edited Dec 17, 2015 at 7:54

answered Dec 16, 2015 at 23:20

JuZer

7832 gold badges7 silver badges14 bronze badges

2 Comments

Sumeet Pujari Over a year ago

Fatal error: Cannot use [] for reading in C:\Users\Sumeet\Desktop\Omegakart\xampp\htdocs\billingdark\createforcast.php on line 204 This is what i get when i do that.

JuZer Over a year ago

Show how your input field looks like

Collectives™ on Stack Overflow

Insert HTML Table cell values into MySQL

4 Answers 4

4 Comments

5 Comments

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

5 Comments

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related