Updating a database column (vertically) with an array

Question

I have a database table with the following structure. id column is added later. So all id cells are empty.

id  | song  | album | artist
-----------------------------
    | song1 | alb1  | art1
    | song2 | alb2  | art2
    | song3 | alb3  | art3
    | song4 | alb4  | art4
    | song5 | alb5  | art5

I have an array which holds the id values for the table. I'll be updating the table (the id column) with the values in the array. For example:

$array = [
    "C45Rm3fLGn",
    "ocIik81up2",
    "IcuSn9T77y",
    "tJv7AbF53r",
    "a9eZ6xYM5Y",
];

These items are unique random strings.

How should I proceed? I'm thinking about iterating the array and using UPDATE on each item.

$array = [
    "C45Rm3fLGn",
    "ocIik81up2",
    "IcuSn9T77y",
    "tJv7AbF53r",
    "a9eZ6xYM5Y",
];

$rows = $mysqli->query("SELECT * FROM songs")->fetch_all(MYSQLI_ASSOC);

for ($i = 0; $i < count($array); $i++) {
    $row = $rows[$i];
    $id  = $array[$i];
    $mysqli->query("UPDATE songs SET id = '$id' WHERE song = '{$row["song"]}' AND artist = '{$row["artist"]}'");
}

Is there a more preferable way?

UPDATE: I do not use auto increment and the id column didn't exist at the time of table was created. Now, I've added an id column. There are 300+ records. IDs of the records are unique random strings. Before I add another record to the database, every record needs to have a unique random string for its id, so that when I insert a new record, I can create a random string and check if it's unique or not by comparing it to the ids in the table.

At this stage, I just need to update the id column using an array. Array items are irrelevant.

Although MYSQL_ASSOC has actually the same value as MYSQLI_ASSOC, you should use "MYSQLI_ASSOC" when using mysqli. The mysql functions are marked deprecated and will be removed in newer versions: http://php.net/manual/en/function.mysql-fetch-array.php — Marco, Jul 11 '16 at 22:29
@ Down voter, What is possibly wrong with this question? If there's anything, help me improve it. Down voting doesn't really help. This is becoming a thing now. Getting random downvotes. It's starting to get to me. — akinuri, Jul 11 '16 at 22:35
You seem very sure that your database fetch will always be in the same order as your array. I can imagine many circumstances where this would not be the case. Why can't you build your array so that it references the database item-name? Also, editing so that your column names match up in your schema and your code would be helpful. — miken32, Jul 11 '16 at 22:56
@miken32 The order doesn't really matter in this case. I'm modifying an already built table. There will be more rows. Every new row will have unique random string. I just need to fill the id column before any new insertion. Updated the question. — akinuri, Jul 11 '16 at 23:09

score 1 · Accepted Answer · edited May 23 '17 at 11:51

1

Prepared statements are designed to be prepared once and executed many times, with a minimum of overhead. Using them is also an absolute necessity if the data you're inserting is user-generated.

It's been a long time since I've done anything with MySQLi (I strongly recommend looking at PDO for much simpler code) but this should work:

$array = [
    "C45Rm3fLGn",
    "ocIik81up2",
    "IcuSn9T77y",
    "tJv7AbF53r",
    "a9eZ6xYM5Y",
];

$rows = $mysqli->query("SELECT * FROM songs")->fetch_all(MYSQLI_ASSOC);
$stmt = $mysqli->prepare("UPDATE songs SET id=? WHERE song=? AND artist=?");

foreach ($array as $i=>$id) {
    $row = $rows[$i];
    $stmt->bind_param("sss", $id, $row["song"], $row["artist"]);
    $stmt->execute();
}

I would also recommend building your array such that it references the columns you're renaming. You're relying on both the database and the array being in the same order, which may not always be the case.

edited May 23 '17 at 11:51

Community

1
1

answered Jul 11 '16 at 23:06

miken32

42,008
16
111
154

I've recently started using prepared statements and should have thought about this. This seems like a better approach. – akinuri Jul 11 '16 at 23:23
I think there is a small problem with this. I suppose you wanted to type `foreach ($array as $i=>$id) {` not `$rows`, because ids are in `$array`. Length of the both arrays (`$array` and `$row`) are equal, so it's safe to use `$array` in foreach. – akinuri Jul 11 '16 at 23:57
Yes, fixed. Thanks. – miken32 Jul 12 '16 at 00:04

score 0 · Answer 2 · answered Jul 11 '16 at 22:50

0

Please try this query:

UPDATE `myTable` SET `id`= CONCAT(item-name, "-id");

Or you can set a counter like this:

UPDATE `myTable`, (SELECT @i := 0) c SET `id`= CONCAT(item-name, "-id-", @i:=@i+1)

I hope this will help you.

answered Jul 11 '16 at 22:50

Ismail RBOUH

10,292
2
24
36

Updating a database column (vertically) with an array

2 Answers2