Update array into sql database

Question

I want to update my array into my database with the data from the form. Below is my form:

$query = "SELECT category FROM `$tablename`";
$result2 = mysqli_query($link, $query);


$rowcount = mysqli_num_rows($result2);
if ($rowcount > 0) {
    ?>

    <div class="center_content">  

        <div id="right_wrap">
            <div id="right_content">             


                <ul id="tabsmenu" class="tabsmenu">
                    <li class="active"><a href="#tab1">Update Category</a></li>
                    <li class=""><a href="#tab2">Add Category</a></li>
                    <li class=""><a href="#tab3">View All Category</a></li>

                </ul>
                <div id="tab1" class="tabcontent">
                    <div style="margin:0 auto" align=center>


                    </div>
                    <div class="form">
                        <form action="editCatB.php" method="post"> 


                            <div class="form_row">
                                <label>Outlet Name:</label>
                                <input type="text" class="form_input" name="tablename" value="<?php echo $name; ?>"readonly/>
                            </div>

                            <div class ="form_row">
                                <label>Outlet Category/Stalls :</label>
                            </div>
                            <div class="form_row">

                                <div class="input_fields_wrap">
                                    <?php
                                    mysqli_data_seek($result2, 0);

                                    while ($row2 = mysqli_fetch_array($result2, MYSQLI_ASSOC)) {
                                        ?>            

                                        <div><input class="form_input" type="text" name="mytext[]"value="<?php echo $row2['category']; ?>
                                                    "></div>
                                        <?php
                                        }
                                    }
                                    ?>

And here is my sql. I want to know how to update the respectively row. Because right now it just update all of my category into the first value

$tableName = $_POST['tablename'];
$values = $_POST['mytext'];

$tableCat = $tableName . "categoryList";

$newString = preg_replace('/\s+/', '', $values);

for ($i = 0; $i < count($newString); $i++) {
    $cat = $newString[$i];
    $sql = "UPDATE `$tableCat` SET category = `$cat`";

    $result = mysqli_query($link, $sql) or die(mysqli_error($link));

It also returns me with the error 'Unknown column 'abc' in 'field list'

Are you sure this is full code as i don't see any abc column name in any of your queries. — Pardeep Poria, Apr 27 '16 at 15:22
For the unknown field error, see http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks — Barmar, Apr 27 '16 at 15:26
You need a `WHERE` clause in the `UPDATE` to tell it which rows to update, instead of updating all rows. — Barmar, Apr 27 '16 at 15:27

score 0 · Answer 1 · answered Apr 27 '16 at 15:21

First,

$sql = "UPDATE `$tableCat` SET category = `$cat`";

Should be more like:

$sql = "UPDATE `$tableCat` SET category = '$cat'";

That being said, you're extremely vulnerable to SQL injection. Look into prepared statements.

If you want to update multiple rows with conditionals, you would follow a pattern similar to this:

$sql = "
    UPDATE `$tableCat` SET 
        category = '$cat', 
        foo = '$foo', 
        bar = '$bar' 
    WHERE baz = '$baz'
";

score 0 · Accepted Answer · answered Apr 27 '16 at 15:21

1) Backticks are for table names and column names, not column values. You'll want to use regular quotes, or take advantage of mysqli's bindings, which is recommended to prevent sql injection.

2) You want to use a WHERE clause when updating. I'd suggest using the id value for the row when creating the table

<div><input class="form_input" type="text" name="mytext[<?php echo $row2['id']?>]" value="<?php echo $row2['category']; ?>"></div>

Then when you iterate through the values, you can pull out the id:

foreach($newString as $id=>$cat) {
    $sql = "UPDATE `$tableCat` SET category = '$cat' WHERE id = '$id'";

    $result = mysqli_query($link, $sql) or die(mysqli_error($link));
}

Update array into sql database

2 Answers2