php update multiple row

Question

how do i update multiple row with one query?

table
id   ref_no   name
-------------------
1              a
2              b
3              c
4              e
5              f
...................

since i just created new ref_no column and its blank. and i expect the column id = ref_no

i tried below but giving me same ids:

$q = $db->query("SELECT id_user FROM user");
while($r = $q->fetch_array(MYSQLI_ASSOC)) :
    $db->query("UPDATE user SET user_no='".$r['id']."'");
endwhile;

jimy · Accepted Answer · 2011-04-18 14:25:13Z

3

You can do it with mysql only

UPDATE table_name SET ref_no = id

answered Apr 18, 2011 at 14:25

jimy

4,9183 gold badges37 silver badges52 bronze badges

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

2 Comments

tonoslfx Over a year ago

is it possible like to append ref in front it? ie: ref1, ref2, etc. $db->query("UPDATE user SET ref_no=AE'".$r['id']."' where id=".$r['id']); not sure it would work?

jimy Over a year ago

you can do that UPDATE table_name SET ref_no = CONCAT('ref',id)

user254875486 · Accepted Answer · 2011-04-18 14:27:05Z

2

Do you want every row to have the same value in the ref_no columan as in the id column? In this case you can use

UPDATE user SET ref_no = id

but I'm not sure if that's what you're looking for...

answered Apr 18, 2011 at 14:27

user254875486

11.3k7 gold badges39 silver badges65 bronze badges

Comments

Álvaro González · Accepted Answer · 2011-04-18 14:31:17Z

1

UPDATE user
SET ref_no = id
WHERE ref_no IS NULL

Be aware that, in SQL context, blank is a special concept that represented by the NULL keyword. You have to use IS NULL or IS NOT NULL to test against NULL.

answered Apr 18, 2011 at 14:31

Álvaro González

147k45 gold badges282 silver badges378 bronze badges

Comments

Nic · Accepted Answer · 2011-04-18 14:32:28Z

1

I dont think the other answers is what you want. If you need the id of each row to be equal to the ref:

$q = $db->query("SELECT id_user FROM user");
while($r = $q->fetch_array(MYSQLI_ASSOC)) :
    $db->query("UPDATE user SET ref_no='".$r['id']."' where id=".$r['id']);
endwhile;

answered Apr 18, 2011 at 14:32

Nic

7038 silver badges18 bronze badges

Comments

ryryan · Accepted Answer · 2011-04-18 14:58:06Z

0

Simply:

UPDATE user SET ref_no = id

answered Apr 18, 2011 at 14:58

ryryan

3,91813 gold badges46 silver badges72 bronze badges

Collectives™ on Stack Overflow

php update multiple row

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related