Jump to content

PHP insert in MySQL database table in random order?


Metasearching

Recommended Posts

Hi,

I'm a researcher (and complete coding noob), and am planning a longitudinal study that requires e-mail follow-up with subjects taking an initial survey. For purposes of ethics/anonymity due to sensitive survey data, I'd like the acquired e-mails to be saved uncoupled from the survey responses; this is simple to deal with, and I use a basic PHP e-mail form, which injects the email address in a table in MySQL in a different server than the one used for the survey.

 

The issue is that the e-mails are saved in the MySQL database in order of injection, thus it is still theoretically possible for me to link the e-mails back to the survey responses (which have a time stamp that I cannot remove).  Ideally I would like not to be able (at all) to link the e-mails to the survey responses, and one way to do that (since I don't save the e-mail injection timestamps in MySQL) might be to have the e-mails saved in MySQL in a random order. Not sure if this is possible, and not even sure if this would be via PHP or MySQL side of things. The server is on godaddy and uses Starfield interface for MySQL but I cannot find an option for random insert/saving of table items (emails). They are saved in order of injection.

Any solution for this?

Thanks,

Link to comment
Share on other sites

I edited the title of the topic to better reflect what you want.  Usually, when referring to a database, 'inject' relates to SQL injection attacks.

 

Do you have an integer id column in your table?  If so, instead of setting it to auto-increment upon data insertion, have your PHP script generate a random number for the id (look at mt_rand), and insert that with the rest of the data.  When you print out a report, simply ORDER BY id ASC.  The emails will be effectively shuffled.

Link to comment
Share on other sites

Hi KevinM1,

Thanks for the clarification, re: inject versus insert.

As for the tip you mentioned; if I did that, would it be impossible for me to order them in temporal order (e.g., prior to making the report)? Or would what you suggested useful only for shuffling report? I guess what I'm looking for is the ability to log in to the MySQL database and have no idea or technical ability to see the e-mails in order of insertion no matter what? I'm guessing with your suggestion, the shuffling would done only on the report export and not on the insertion itself, thus technically, I could still have access to the ordered e-mails?? Am  I even making any sense?  ;D

Thanks!

Link to comment
Share on other sites

In addition, if you will to randomize it in mysql-raw-data, you could query "ALTER TABLE emailtable ORDER BY columnid" every new email inserted to table (the columnid contains random id as suggested by KevinM1)

But, it may impact performance if it is a big table :)

Link to comment
Share on other sites

Update: I managed to get it to work! Raw data is now randomly ordered: each email is given a random ID (using mt_rand), and after each insert the table is ordered by columnid (using ALTER TABLE/ ORDER BY) so there is no way I can determine the original order!

thanks a bunch guys!

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.