Insert Duplication

[foucquet]

As part of an image gallery I am trying to INSERT data into a table to make accessible the relevant info of individual images - the problem I have encountered is that each query seems to be being inserted twice, and I can't figure out why. The code =

 

<?php

/*==============================================
Connect to MySQL and select the correct database
==============================================*/
mysql_connect("localhost", "uname", "pwd") or die(mysql_error());
echo "Connected to MySQL<br />";
mysql_select_db("gallery_db") or die(mysql_error());
echo "Connected to Database gallery_db.<br />";

/*=============================================
Include the files needed to undertake this task
=============================================*/
include("functions.php");

/*=========================
Set up arrays and variables
=========================*/
$store = array();
$exif = array();

$folder = "./images/";
$insrt = 'INSERT INTO images VALUES (img_id, ';
$file_name = '';
$file_location ='';
$copyright ='';
$caption = '';
$file_date = '';
$file_time = '';
$camera = '';
$speed = '';
$fNo = '';
$ISO = '';

/*=================================
Open folder and read relevant files
=================================*/
if ($handle = opendir('./images')) {
    while (false !== ($file = readdir($handle))) {
        if ($file != "." && $file != "..") {

          $file_name = $file;  //>>> File Name <<<
          $file_location = $folder . $file;     //>>> File Location <<<

          $exif = exif_read_data($file_location, 'EXIF');   //>>> Read exif data <<<

          /*==============================================================
          Check if the 'Copyright' info existe in the exif data, and if it
          doesn't set the $copyright variable to the relevant value.
          ==============================================================*/
          if (array_key_exists('Copyright', $exif)) {
          $copyright = $exif['Copyright'];
      }
        else {
          $copyright = "copy name";
        }

        $caption = $exif['COMMENT'][0];     //>>> Get the Caption <<<

        //>>> Re-format DateTime into separate date and time var's $file_date & $file_time <<<
        $temp = substr($exif['DateTime'], 0, 10);
        $file_date = substr($temp, 8, 2) . substr($temp, 4, 4) . substr($temp, 0, 4);
        $file_time = substr($exif['DateTime'], 11, ;
        //>>> Set the rest of the variables <<<
        $camera = $exif['Model'];   //>>> Get the camera make and model <<<
        $speed = $exif['ExposureTime'];     //>>> Get the shutter speed <<<
        $fNo = $exif['COMPUTED']['ApertureFNumber'];    //>>> Get the f No. <<<
        $ISO = $exif['ISOSpeedRatings'];        //>>> Get the ISO rating <<<

        //>>> Fill the $store[] array with the relevant bits for each seperate query <<<
        $store[] = $insrt . "'" .
        $file_name . "', " . "'" .
        $file_location . "', " . "'" .
        $copyright . "', " . "'" .
        $caption . "', " . "'" .
        $file_date . "', " . "'" .
        $file_time . "', " . "'" .
        $camera . "', " . "'" .
        $speed . "', " . "'" .
        $fNo . "', " . "'" .
        $ISO . "')";

        }
    }
    closedir($handle);
}

/*=============================================================================
Get individual queries from $store[] and INSERT INTO images the relevant VALUES
=============================================================================*/
$num_queries = count($store);       //>>> Check the number of queries in the array $store
$loop = 0;      //>>> Starting point for query No. bearing in mind that array keys are numbered from zero

while ($loop <= $num_queries - 1) {
  $query = $store[$loop];

  if (!mysql_query($query)){
    die('Error: ' . mysql_error());
}

mysql_query($query);

  $loop++;
}

?>

 

I know it's not pretty, but that can be attended to once working properly. I am quite sure that it will be a very simple solution, probably so simple that I haven't even thought of it.

[Psycho]

I know it's not pretty, but that can be attended to once working properly.

 

Um no, you should always write clean, organized code. Otherwise trying to fix problems can be much more work than it needs to be and you end up spending much more time "cleaning up" your code than it would have taken to be more methodical in your original coding. Or, even worse, you will end up with "hidden" bugs that will come back to haunt you later.

 

Anyway, your problem is pretty obvious

while ($loop <= $num_queries - 1) {
  $query = $store[$loop]; <== Query is defined in the loop

  if (!mysql_query($query)){ // <== Each Query is run here within the loop
    die('Error: ' . mysql_error());
}

mysql_query($query); // <== LAST query from the loop above is run here again

 

Besides you should NEVER run queries in loops. Gather all the data for your INSERTS and run one INSERT query.

[ManiacDan]

I second everything mjdamato said.

[foucquet]

I knew it was something simple - I had just not seen that second insertion...

 

I did say that it was no pretty, but cmon guys we all have to learn somewhere along the line...

 

Thanks for pointing out the bleedin' obvious Btw.

[Psycho]

I did say that it was no pretty, but cmon guys we all have to learn somewhere along the line...

 

No one ever said you should be perfect when learning. But, that was not what you were saying. You made a statement that inferred that it was OK to write sloppy code to get it working and THEN try and make it "pretty". Too many people come to this forum expressly due to following that kind of process - they create simple errors or have "logic" issues because they don't plan what they are doing before they do it. I was only telling you not to use it as an excuse (doesn't matter if you are new or experienced). Good habits don't magically happen, you have to practice them.

 

I'm glad you got your problem resolved.

[foucquet]

Thanks again for the help mjdamato. I agree with you to a point, which is why I tend to 'echo' everything along the way to ensure that what I want to happen is happening, but I know from long experience as an artist that even the best plans are sometimes impossible to implement, and that one has to work around one's limtations to get a framework which one can then work on to improve. Given that I am not writing all this for any other reason than keeping a very old brain active maybe a few 'hidden bugs' would be good, as it would make this very old brain of mine work harder to sort them out. I don't advocate anyhting other than working to best principles in all things, but sometimes best principles are not quite as obvious as they should be - but that is a whole other debate...

[Psycho]

. . . I know from long experience as an artist . . .

 

That explains it - I bet you are really good at more creative things. I believe that there are a specific set of innate skills needed to be really good at 'creative' functions and a completely different set to be really good at 'analytical' functions. Very rarely do I see a person that has both sets of skills. You can become competent in one area without the innate skills, but it takes more work. Again, this is just my opinion.

 

Good luck to you.

[foucquet]

I think you hit the nail on the head there M, I also have age agaisnt me old dog, new tricks etc.  :-\

[ManiacDan]

If age is working against you, take the proper precautions now to make your code readable.  A young guy might not check to see if there's a towel on the rack before he gets in the shower, because he's confident that he can walk down the hall without slipping and falling.  I bet you check for a towel (or you will now).  Similarly, you should take a little extra care to make sure your braces are explicitly lined up, your variable names all exist in the same format, and your SQL queries are all well formatted.  By spending one minute lining up your braces you save ten minutes of debugging.