php (array)-> mysql (multiple rows with a foreign key) Update specific row in table where id and row number varies

Question

$schoolinfo = mysqli_prepare($con, "UPDATE table SET firstname=?, lastname=? from school where   foreignkey='$id'");

mysqli_stmt_bind_param($schoolinfo,'ss', $firstname, $lastname)


for ($i=0;$i<count($_POST['row']);$i++){     
$firstname = mysqli_real_escape_string($con, $_POST['firstname'][$i]);
$lastname = mysqli_real_escape_string($con, $_POST['lastname'][$i]);
mysqli_stmt_execute($schoolinfo);
}

This updates all rows with the same firstname and lastname.

I want to update rows from selection where foreignkey = '$id' and rownumber ='i'

Any queries or subqueries out there?

You should not use mysqli_real_escape_string when using bind_param. You'll end up escaping twice. — Barmar
– Barmar, Commented Jan 1, 2014 at 0:51
You're repeatedly updating the same row, there's no rownumber in your query. — Barmar
– Barmar, Commented Jan 1, 2014 at 0:56
Foreign keys only, no Ids. Table has multiple rows with same foreign key. I want to post the info from array into the correct rows. — user3150424
– user3150424, Commented Jan 1, 2014 at 1:09

echo_Me · Accepted Answer · 2014-01-01 01:10:13Z

1

just remove this from school from your update query and give a name to your table .

like that

 $schoolinfo = mysqli_prepare($con, "UPDATE school SET firstname=?, lastname=? where   foreignkey='$id'");

you mixed between SELECT and UPDATE.

i dont know if im wrong or , you have binded firstname and lastname before the loop .

try this

$schoolinfo = mysqli_prepare($con, "UPDATE school SET firstname=?, lastname=? WHERE foreignkey='$id'");

for ($i=0;$i<count($_POST['row']);$i++){     
$firstname = $_POST['firstname'][$i];
$lastname = $_POST['lastname'][$i];
mysqli_stmt_bind_param($schoolinfo,'ss', $firstname, $lastname)
mysqli_stmt_execute($schoolinfo);
}

edited Jan 1, 2014 at 1:10

answered Jan 1, 2014 at 0:46

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

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

1 Comment

Barmar Over a year ago

You don't need to bind in the loop. bind_param uses references.

Barmar · Accepted Answer · 2014-01-01 01:04:23Z

0

Try this:

$schoolinfo = mysqli_prepare($con, "UPDATE school SET firstname=?, lastname=?
                                    WHERE foreignkey=? and rownumber = ?");

mysqli_stmt_bind_param($schoolinfo,'sssi', $firstname, $lastname, $id, $i);

for ($i=0;$i<count($_POST['row']);$i++){     
    $firstname = $_POST['firstname'][$i];
    $lastname = $_POST['lastname'][$i];
    mysqli_stmt_execute($schoolinfo);
}

You didn't have rownumber in the query. And it's best to use bind_param for all variables that you're substituting.

edited Jan 1, 2014 at 1:04

answered Jan 1, 2014 at 0:59

Barmar

789k57 gold badges554 silver badges669 bronze badges

4 Comments

user3150424 Over a year ago

I need to create the rownumber it doesn't exist in the table

user3150424 Over a year ago

I am pretty sure I have to use a SELECT subquery here to create the row_number()

Barmar Over a year ago

If you search SO for [mysql] rownumber you will find examples of that. But you need an ordering criteria for your table, otherwise the row number assignments will be random.

Barmar Over a year ago

I'm a little confused, though. Isn't the id field a unique identifier? How can there be multiple rows with the same id?

Collectives™ on Stack Overflow

php (array)-> mysql (multiple rows with a foreign key) Update specific row in table where id and row number varies

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related