How do I update consecutive rows in a mysql database using php?

Question

I have a list of values I am trying to enter into a mysql database with a dynamic number of rows. The variables are stored in $box1, $box2, and so on. I am trying to use UPDATE to put in the right value ($box1 in the first row selected, $box2 in the second row selected, and so on) in each consecutive row of the table. I’m not sure what code I have to type in the “while” loop to make this happen.

    <?php


        // Retrieve data from Query String
    $box1 = $_GET['box1'];
    $box2 = $_GET['box2'];
    $box3 = $_GET['box3'];
    $box4 = $_GET['box4'];
    $box5 = $_GET['box5'];
    $session = session_id();


        //build query
    $query = "SELECT * FROM sessionid WHERE sessionid='$session' ";

        //Execute query
    $qry_result = mysql_query($query) or die(mysql_error());

        // Insert the right quantity in each row for each consecutive box 
        $i=1;
        while($row = mysql_fetch_array($qry_result)){
            mysql_query("UPDATE `sessionid` SET `qt`='$box' .$i . '' WHERE `sessionid`='$session' ");
            $i++;
            }


    ?>

I am now inputting the data from the url on the previous page into an array $box[1]. I keep getting an error saying: Parse error: syntax error, unexpected '"', expecting T_STRING or T_VARIABLE or T_NUM_STRING in /home/content/c/a/s/cashme/html/buylooper/change-qt.php on line 13. Line 13 refers to the line that UPDATE query. Any tips?

//Retrieve data from Query String, input into array, and update table
        $count = $_SESSION['id'];
        for ($i=1; $i<$count; $i++){
                $box[$i] = $_GET['box'. $i .''];
            mysql_query("UPDATE `sessionid` SET `qt`='$box["'. $i .'"]' WHERE `sessionid`='$session' AND `id`='$i' ");
            echo $box[$i];
        }

Coul you please provide us your table schema, and give us more information about consecutive rows. Do you mean consecutive id? What if there are blanks in your table? — Guillaume Poussel
– Guillaume Poussel, Commented Mar 6, 2012 at 7:33

Your Common Sense · Accepted Answer · 2012-03-06 07:38:27Z

2

A most basic thing you have to learn by heart:

There are no consecutive rows in the database.

All the rows are completely random.
And you cannot rely on their natural order by any means.

To update a certain row you have to identify it with some unique field. Mysql's standard auto incremented id is the best choice.

Always store the row's id in the form, to be able to know which row current value belongs to.
Make your form like this

<input type="text" name"box[1]" value="20">

where 1 is an actual row's id

Also note that all data manipulations have to be performed using POST method, not GET.

So, in the $_POST['box'] you will have an array of pairs id => value and can easily construct the update query out of them.

answered Mar 6, 2012 at 7:38

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Yanick Rochon Over a year ago

storing the editing row id in the HTML form is a good idea. and +1 for the most basic thing to learn about databases

sharataka Over a year ago

given that I make the changes in the name of the textbox, how do I implement the auto increment id and $_POST?

Your Common Sense Over a year ago

as for the auto increment it is already implemented in your table, I believe. if not - please post the table structure in your question.

sharataka Over a year ago

I tried using your approach to use an array but I get a syntax error when I try to do so. I made an edit to the orignal post that shows what I have and the error I get. Any tips?

Raptor · Accepted Answer · 2012-03-06 07:34:57Z

1

Why don't you store your variables $box1 to $box5` in an array instead of 5 variables?

Then you can use $boxes[$i] to call the related values.

answered Mar 6, 2012 at 7:34

Raptor

54.4k47 gold badges254 silver badges405 bronze badges

Comments

OM The Eternity · Accepted Answer · 2012-03-06 07:37:20Z

0

U need

"UPDATE `sessionid` SET `qt`="'.$box.$i . '" WHERE `sessionid`="'.$session.'"

Also You should Store the values in Array rather than separate $box variables

u shld use

$box[]

not

$box1,$box2, etc...

answered Mar 6, 2012 at 7:37

OM The Eternity

16.2k44 gold badges127 silver badges187 bronze badges

1 Comment

sharataka Over a year ago

You're right. I tried using your approach to use an array but I get a syntax error when I try to do so. I made an edit to the orignal post that shows what I have and the error I get. Any tips?

Collectives™ on Stack Overflow

How do I update consecutive rows in a mysql database using php?

3 Answers 3

There are no consecutive rows in the database.

4 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

There are no consecutive rows in the database.

4 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related