Get a unique hash value for any given input using PHP

Question

I need to regularly check many (100.000's) rows and check if their current state is the same as the latest stored version in another database. Is there a way to get some sort of unique value for a row to match them, or would I have to manually check the rows column by column?

The source database is a SQL Server 2005 database and the table doesn't have a timestamp mechanism for create, update and/or delete action. I've looked around to check if there is row information available but the only thing available is a pseudo column %%lockres%% and the row information, but that doesn't provide date and or time information.

I'm limited in my tools, but I have a webserver running Apache and PHP and direct access to the source and destination databases. I only have read permissions on the source database.

What would be the most efficient way to compare the data and maintain performance on the source database.

It is not quite clear what you want. How can you compare some row from one db to the next if you don't know which ones to compare? — Erwin Moller
– Erwin Moller, Commented Sep 28, 2015 at 13:36
Should this table be updated as a whole, or on the row-by-row basis? Can you add a trigger on that database? — raina77ow
– raina77ow, Commented Sep 28, 2015 at 13:41
Because of limitation in tools and funding I'm (maybe) going to create my own ETL tool. The table shouldn't be updated as a whole, but only the changed rows (which would lead to a new row in their dimension table in the destination database). @ErwinMoller, comparison is possible because of identifying keys. I only want to (effeciently) compare the rows because of the mass of data) — Ben
– Ben, Commented Sep 28, 2015 at 13:44
@Ben In that case I would add a simple column to both, that calculates a simple hash based on all fields that can be updated (maybe that is all the fields). Then it is up to you if you implement triggers to update the hash, or run it once by hand before comparing. HashBytes('MD5', col1+col2+col3) when you must make sure to cast all cols to the right type. — Erwin Moller
– Erwin Moller, Commented Sep 28, 2015 at 13:49

Tejaswa Nama · Accepted Answer · 2021-04-05 11:14:50Z

1

It's simple. Just create a column in that table name it anything in my case i took token name. Now if you want this code is generated automatic when user register then als you can use this by :

$token = bin2hex(random_bytes(20));
$sql_query = "INSERT INTO `table_name` (`token`) VALUES ('$token')";

Here bin2hex() funtion means binary to hexadecimal. random_bytes() shows generate random bytes inside that write the length of the random character yopu want to choose.

Or You Can simply run this query in your table

$token = bin2hex(random_bytes(20));
"UPDATE `table_name` SET `token`='$token'";

If till now also your query is not resolved. You may concern to me about that again. Then, I will tell you another method to solve this problem.

answered Apr 5, 2021 at 11:14

Tejaswa Nama

1110 bronze badges

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

Comments

Smar · Accepted Answer · 2015-09-28 14:04:17Z

Since you don’t have access to database, I’d suggest to use ”alternative” database for storing the information. I can think of few different approches, each with different pros and cons.

Approach 1

For using hashes outside of the table for the modification checks would require always querying all the data again, making it highly slow operation, I’d use separate table to store the hashes, where you could always first check if the value matches and just then update it.

Basically when inserting data, you can calculate the local hash from that data, then compare it to the helper database and if they don’t match, you know that the data is out of sync, and can update the data to the real database and save the new hash to the helper database.

Pros:

Only necessary updates to the real database

Cons:

Slower than using hash value in database

Approach 2

Update the record in real database always. This is simplest solution, and unless you need to update thousands of records at the same time and the remote database can handle the extra load, performance impact shouldn’t be that much. It’s just simple update operations.

Pros:

Simple and easy to do

Cons:

Extra load to real database

Approach 3

Just get the permission to modify the remote database. If you are going to maintain that thing for long time, this may just be the best thing in the future.

Pros:

It will work fastest

Cons:

You need to get permission to modify the table.

While I say database, at simplest it could just be a plain text file, SQLite database or anything, that would allow you handle the local operations.

Collectives™ on Stack Overflow

Get a unique hash value for any given input using 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