Insert Comma seperated values to a mysql database PHP

Question

I asked a similar question yesterday but it's not the same. I have some values that looks like this, and I want to Insert them So that

123,567,287,124,172,846,372,187

And I want it to look like this

id=123 and SortNumber=1
id=567 and SortNumber=2
id=287 and SortNumber=3

And I want to update a table based on this, so something like this

UPDATE char_
SET SortNumber=1
WHERE id=123
Set SortNumber=2
WHERE id=567

Now, I think I can do something like this

$variable=123,567,287,124,172,846,372,187
$anothervariable=$variable = str_replace(",", "\r\n", $variable); 


$i=0;
$i++;

 mysqli_query(UPDATE char_ SET SortNumber=$i WHERE id=$anothervariable)

But, I'm not sure that the code is 100 correct AND, the variable will be the whole contents and I dont know how I can set a different variable to every line?

The syntax of the UPDATE query you want to obtain is incorrect. You need several update statements, or a single update using CASE WHEN — guido
– guido, Commented Nov 30, 2014 at 13:34
Watch out for SQL injection; You should prefer an answer that uses placeholders or similar parameterized SQL — Matt Whipple
– Matt Whipple, Commented Nov 30, 2014 at 13:35

Gordon Linoff · Accepted Answer · 2014-11-30 13:33:26Z

1

Ironically, you can do this directly in MySQL

update char_
    set sortnumber = find_in_set(id, $variable)
    where find_in_set(id, $variable) > 0;

Note: this will not take advantage of an index on id, so only use this if char_ is small (tens or hundreds of rows) or you do not care about performance.

answered Nov 30, 2014 at 13:33

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

MH2K9 · Accepted Answer · 2014-11-30 13:33:41Z

0

Suggest you to use explode() and foreach().

$variable = '123,567,287,124,172,846,372,187';
$columns = array_filter(explode(',', $variable));
foreach($columns as $key=>$val){
    $sql = "UPDATE char_ SET SortNumber = '".($key + 1)."' WHERE id = " . $val;
    $result = mysqli_query($con, $sql);
    //.....
}

answered Nov 30, 2014 at 13:33

MH2K9

12.1k7 gold badges34 silver badges49 bronze badges

Collectives™ on Stack Overflow

Insert Comma seperated values to a mysql database PHP

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related