Databases
StackOverflow2013
Postsdbo
POchecking uniqueness of items before inserting in the database
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POchecking uniqueness of items before inserting in the database
    primarykey
    data
    text
    <p>I'm currently working on a project in which access to an API is restricted to registered users. The API itself is already finished and works as expected. Limiting access to the API has turned out fairly straightforward as well. However, my problem (or question, rather) is how to go about ensuring the efficiency of the database interactions for the registration, verification, and/or lost and found process.</p> <p>Here's an example of what currently happens:</p> <ol> <li>User requests an API key by entering their email address</li> <li>User is sent a verification email</li> <li>User clicks link in email and php checks hash against database</li> <li>Once hash is verified, API key is generated, stored, and emailed</li> <li>If user forgets/loses API key, it can be emailed again</li> <li>If verification email wasn't received, it can be emailed again</li> </ol> <p>Here's an example of the database structure: <a href="http://s13.postimage.org/h8ao5oo2v/dbstructure.png" rel="nofollow">http://s13.postimage.org/h8ao5oo2v/dbstructure.png</a></p> <p>As you can probably imagine, there is a LOT of database interaction going on behind the scenes for each of these particular steps in the process. One step that I'm wondering about the efficiency of is that of checking uniqueness of certain items. Obviously, we don't want any duplicate API keys floating around, nor do we want any duplicate email verification hashes. </p> <p>So, I wrote a dead simple function that checks the database for these things before inserting them into the database. However, this project is on the order of hundreds of times larger than any I've undertaken before. I've built and maintained projects that serviced 500 - 1,000 users before... but this project is estimated to be servicing a minimum of around 50,000 users daily. I'm extremely happy that I've finally landed a large project, but becoming increasingly daunted at the scale of it.</p> <p>At any rate, here's the function I wrote to interact with the database to check uniqueness of items before storing them.</p> <pre><code>function isUnique($table, $col, $data) { mysql_connect("localhost", "root", "") or die(mysql_error()); mysql_select_db("api") or die(mysql_error()); $check = mysql_query("SELECT ".$col." FROM ".$table." WHERE ".$col."='".$data."'"); $match = mysql_num_rows($check); if($match &lt; 1) { return true; } return false; mysql_close('localhost'); } </code></pre> <p>This function is used in conjunction with another function which just generates a random 40 digit string of 0-9, a-z, and A-Z for the email verification hash as well as the API key itself. (function below)</p> <pre><code>function makeRandom($length = 40) { $characters = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'; $randomString = ''; for($i = 0; $i &lt; $length; $i++) { $randomString .= $characters[mt_rand(0, strlen($characters) - 1)]; } return $randomString; } </code></pre> <p>And then the combination of those 2 functions is used in 3 different pages related to the API key issuance: Page one for registration/request, Page two for verification of email, Page 3 for lost keys or unreceived email. Now here it is in practice:</p> <pre><code>$hash = makeRandom(); $unique = isUnique('users', 'hash', $hash); if($unique == false) { while($unique == false) { $hash = makeRandom(); $unique = isUnique('users', 'hash', $hash); } } else { $searchactive = mysql_query("SELECT email, active FROM users WHERE email='".$email."' AND active='1'") or die(mysql_error()); $matchactive = mysql_num_rows($searchactive); $searchinactive = mysql_query("SELECT email, active FROM users WHERE email='".$email."' AND active='0'") or die(mysql_error()); $matchinactive = mysql_num_rows($searchinactive); if($matchactive &gt; 0) { $hash = mysql_query("SELECT hash FROM users WHERE email='".$email."' AND active='1'") or die(mysql_error()); $hash = mysql_fetch_assoc($hash); $hash = $hash['hash']; $msg = 'The email address you entered is already associated with an active API key. &lt;a href="lost.php?email='.$email.'&amp;amp;hash='.$hash.'&amp;active=1"&gt;[Recover Lost API Key]&lt;/a&gt;'; } elseif($matchinactive &gt; 0) { $hash = mysql_query("SELECT hash FROM users WHERE email='".$email."' AND active='0'") or die(mysql_error()); $hash = mysql_fetch_assoc($hash); $hash = $hash['hash']; $msg = 'The email address you entered is already pending verification. &lt;a href="lost.php?email='.$email.'&amp;amp;hash='.$hash.'&amp;active=0"&gt;[Resend Verification Email]&lt;/a&gt;'; } } </code></pre> <p>My primary question is this: With this much query'g going on just for such a (seemingly) simple function, is this going to create more problems than it solves? I really need to make sure that there aren't any duplicate verification hashes or API keys for obvious reasons. However, with an estimated 50k people using this feature, is this going to bog down the server due to the amount of SQL queries? Primary concern is due to the while() loop used to check the uniqueness of the generated content before inserting it.</p> <p>I know this isn't a complete picture of what's going on behind the scenes, but it does give a clue as to how the rest of the pages work. If more information about the process as a whole is needed, I'll be happy to post it.</p> <p>Thanks for any insight you can offer!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. UScode.feind
    1. UScode.feind
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POchecking uniqueness of items before inserting in the database
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POchecking uniqueness of items before inserting in the database
      1. This table or related slice is empty.
      1. VTUpMod
    1. COFor the hash, you can use the email address + time() + rand() in some order to keep from having dups.
      singulars
      1. POchecking uniqueness of items before inserting in the database
      1. UShonyovk
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload