PHP SQL在mysql表中的特定记录中添加单词

Question

I need to add 2 different words to a specific field in a table.

table is:

field1, field2 and field3

So first I need to select the records that I want to alter:

SELECT * FROM MyTable where field3 = 1

Suppose the result is 10 records.

I now have 2 variables var1 and var2

I now need to add var1 to 50% of field2 of the table and var2 to the other 50%

How can I do this?

Example:

table:

  id | field2 | field3
-----------------------------
  1    hello      1
  2    hello2     1

String1 String2

Select * from mytable where field3 = 1

Will return 2 fields...

next...

Insert String1 into 50% of result and String2 into the other 50% of result on field2

Output should be:

  id | field2         |  field3
-----------------------------
  1    String1 hello      1
  2    String2 hello2     1

Answer 1

Get count rows for update.

select count(*) cnt 
from MyTable 
where field3 = 1;

and in php store result to variable $cnt, also make calculation for the middle point between ranges.

$middle = ceil($cnt/2);

then run following query (of course don't forget to escape a values for variables passed in sql):

update MyTable a,
      (SELECT @rownum:=0) b
set field2 = concat(field2, if((@rownum := @rownum+1) > $middle, '$var1', '$var2'))     
where field3 = 1;

Example below:

<?php

$conn = mysqli_connect('localhost', 'user', 'pass', 'dbtest');
$rs = mysqli_query($conn, "select count(*) cnt from MyTable where field3 = 1");
$row = mysqli_fetch_assoc($rs);
$cnt = $row['cnt'];
$middle = ceil($cnt/2);
$var1 = 'xxx';
$var2 = 'yyy';
mysqli_query(
    $conn, 
    "update MyTable a,
           (SELECT @rownum:=0) b
     set field2 = concat(field2, if((@rownum := @rownum+1) > $middle, '$var1', '$var2'))     
     where field3 = 1
    "
);