CODEWITHSUNDEEP
phpmysqlsqlhash
Shafizadeh
Shafizadeh

Reputation: 10380

how to create a new column contained the hash of id column

I have a table contained 1 column id. now I want to create a new column for my table, So I want the data of new column be hashed of id. something like this:

// my table
+------+
|  id  |
+------+
|  1   |
|  2   |
|  3   |
+------+

// I want this table
+------+------------+
|  id  |   hashed   |
+------+------------+
|  1   |  00320032  |
|  2   |  00330033  |
|  3   |  00340034  |
+------+------------+

It should be noted, the hashed column is based:

hash('adler32', '1'); // output: 00320032

hash('adler32', '2'); // output: 00330033

hash('adler32', '3'); // output: 00340034

Now, Is it possible to I do that ?

Upvotes: 4

Views: 955

Answers (1)

someOne
someOne

Reputation: 1675

Among other possible approaches, you may first fetch all the ids, compute the hashed values for each one of them, and re-insert the data back into the table (and avoid duplicates :)

The following is of interest (no error checking is done :)

<?php
// Adding the column named 'hashed'
$mysqli->query('ALTER TABLE your_table ADD COLUMN (`hashed` INT);');

// Retrieving all the 'id's
$result = $mysqli->query('SELECT id FROM your_table;');
$IDs = $result->fetch_all(MYSQLI_ASSOC);
$result->close();

// Assembling the values for a bulk INSERT statement
$values = array();
foreach($IDs as $row) {
    $ID = $row['id'];
    $hashed = hash('adler32', $ID);
    $values[] = "($ID, $hashed)";
}
$values = implode(',', $values);

// Delete to avoid duplicates
$mysqli->query("DELETE FROM your_table;");

// Insert the 'id's and their respective hashed values
$mysqli->query("INSERT INTO your_table (id, hashed) VALUES $values;");

Upvotes: 2

Related Questions