Indexes - Rolling your own Unique

I am working on a rather large database for work and I came upon a dilemma that I just could figure out. I had 3 tables, one was a user info table whose id was an auto increment and unique. The second table was a list of groups that the users would be put into. The third table was the group mapping where it would pair the user id with the group id.

The thought is that if the user was already present then inserting into the table with the group pairs wouldn’t be a big problem. The auto id would already be made and could easily be inserted into the group pair table. What would happen if the user didn’t exist? How would I then insert it into the group pair table without the id existing for the user. Auto increment only increments after the record is added.

I tried many methods starting with @@IDENTITY but that didn’t work, then I came across SCOPE_IDENTITY() and thought, okay, problem solved. Well, it was short lived. When I inserted the user record with my INSERT query and then immediately ran scope_identity I got the ID I would get the last inserted id. Was real cool.

I thought that scope_identity worked off the last session, but soon found that it didn’t work very well with multiple users on a non persistent data connection…ugh. Alrighty then, even though all the sql db admins I asked said it should work, my testing showed otherwise. It wasn’t consistent enough for me.

After some consideration I decided to roll my own index, so I asked around and got some ideas. Some of the ideas seemed a little bloated others were just so complex that they were hard to understand. I believe that in many cases the simple solution might be the best. This is the code I came up with.

Generate the random ID

$rID = md5(mt_rand(1, 2048));
$id = md5($rID . microtime());

Then do a select to see if it is unique

$result = mysql_query("SELECT UID FROM members WHERE UID = '" . $id . "' LIMIT 1");
$cnt = mysql_num_rows;

With this in mind, I created a class that would check the db for duplicates and then release a new ID if there were no dupes.

<?php

include('dbconnect.php');
$Basic = new r_id;

echo $Basic->Getcnt();

class r_id{
     var $mid;
     var $cnt = 1;

     function Getid(){
          $rID = md5(mt_rand(1, 2048));
          $id = md5($rID . microtime());
          return $id;
     }

     function Getcnt(){
          while ($this->cnt > 0){
               $this->mid = $this->Getid();
               $result = mysql_query("SELECT UID FROM members WHERE UID = '" . $this->mid . "' LIMIT 1");
               $this->cnt = mysql_num_rows($result);
          }
          return $this->mid;
     }
}
?>

The ID will be a huge alphanumeric entry in this fashion - 47e509c01d97f7ee1df29ecf2b0a0e07; the chances of it ever being duplicated are slim. I’m sure someone could quantify its chance of being duplicated; don’t look at me to do it.

Simple PHP Image Upload

I consider myself a fairly descent programmer, not great by any means, but ok; anyway I spent the last three days struggling over a form that would upload a picture to mysql. It shouldn’t be a problem because it’s pretty basic, but I couldn’t get it to work. Why?!!! Gawd, it was frustrating the hell out of me. The form kept passing an empty array…over and over and over I worked with it making the simplest of script and still nothing. Then this morning it dawned on me. I had an epiphany…the harps and trumpets played in tandem at the back of my frustrated mind. *sigh* I was using $_POST and not $_FILES.

Anyhow, here is some simple code for anyone that wants it.

form.php

<?php

     if (!empty($_FILES['imgfile'])){
          echo '<form action="form.php" method="post" enctype="multipart/form-data" >
               <input type="file" name="imgfile">
               <input type="hidden" name="MAX_FILE_SIZE" value="1000000">
          </form>';
     }else{
          $image = $_FILES['imgfile'];
          //make sure this folder is writeable 666
          $path = '/home/user/public_html/images/';
          copy($image['tmp_name'], $path.$image['name']);
     }
?>

This simple script uploads a file, and then copies it from the temporary php upload directory to your permanent directory with its original name.

If you wanted to insert the object into a mysql database, then, to save space one would encode it - basically turn it into a text file - and insert the image into a field with type longtext. The code would look something like this. Again, this is pretty simple code and no error capture. Code takes off from above.

     copy($image['tmp_name'], $path.$image['name']);

     //open file for reading
     $fp = fopen($path.$image['name'], "r");

          //measure the file contents
          $contents = fread($fp, filesize($path.$image['name']));

     //close the file
     fclose($fp);

     //encode the file to save space in the db
     $encoded = chunk_split(base64_encode($contents));

     //my assumption is that you already have a db setup with a longtext field.
     mysql_query("INSERT INTO images (ID, IMAGE) VALUES (" . $id . ",'" . $encoded . "')");

     //since it's inserted into mysql, you can delete the existing file
     unlink($path.$image['name']);

So now, how do we view the file? Good question and I don’t know….

Nah, here you go

image.php

     <?php

     //My assumption is that you already have connected to the database somewhere
     //and the db has an id field
     $result=mysql_query("SELECT * FROM images WHERE ID='".$_GET['img']."'");

     //fetch data from database
     $data=mysql_fetch_array($result);

     $encoded=$data['data'];

     //close connection
     mysql_close($connection);

     //decode and echo the image data
     echo base64_decode($encoded);

?>

This is a simple usage of it.
<img src=”image.php?img=32″ border=”0″>