Mapping Images into MYSQL Database

Hello everyone,

Our website has scouting profiles for the top amateur baseball players heading into the draft. Our events include taking a headshot picture of the players as they come through our events. The pictures are then resized, and uploaded to their player profiles from our CMS. No problem when this is done on a small scale.

The problem comes in when we have an event with 1,000+ players and 1,000+ pictures. It can be a lot of work to put the memory card in the PC, get the pictures off of there, rename the images according to who the player was and match up the sign in sheet with the players name and the order of the pictures, resize the image from the HUGE size that it is, and upload all of those photos to the player profiles individually to their profiles.

i.e. if John Doe and Jane Doe exist in our database as playerID 1234, and 1235, how do we quickly associate those playerID with the proper file name from their picture?

I have a Canon Power Shot, and my idea was to buy software that had remote capture so I could rename the file on the spot on the PC. Or perhaps there is a better way to map the player headshot photo to the existing players (playerID) in the database.

Does anyone have any solutions or ideas that they can help me with to lead me in the right direction so we could:

  1. Take picture and associate with player
  2. Resize photo
  3. Upload

I also have photoshop and I hear that has a macro capability to resize.



Make each player hold up a card with his name (or designated number) on it when the photo is taken.

Then run an OCR programme over the images and get it to copy/rename the file.


… Or farm the job out to Mechanical Turk or similar

Is there any way you could prefix the name of each image with the players name or id? That would be ideal considering you could than rip though the list and map them based on the sub-string. The only way to achieve what you want is to manually assign that mapping of the player => image. Once your able to achieve that though I would just have a single folder on the server place all images in it and hit a page which starts the transfer process. You can set the script to run until it finishes while redirecting to a new page. When the script finishes you can send an email to alert you or on error of status of the script.

What I would do:
Install XAMPP or similar
Setup a table with all the names in a database
Create a form asking for the photo start number and folder name you will be saving the photos into. Input any new names that you will be using ? If you are taking 1,000’s of photos you will soon be back at number 1 again. This data will be saved in the database for use later.
Have a form that generates the photo number with a dropdown of the names and a submit button.
When you start the program the first number comes up; you select the name to go with the photo and when you press submit the data is saved into a database and the next number is displayed.
You can then just upload this new data into the server database.
Resize the photos from the camera however you want and save into the folder specified earlier.

I think you can create input forms for Excel but I do not know if you can create a drop down list. This method means you do not have to convert from an Excel spread sheet.

That’s a perfectly acceptable solution, as long as you don’t reuse filenames throughout the entire set of players. (If you have 10,000 players, what does the camera do when you get to #10000? Does it reset to 0001 if you empty the camera?)

Interesting points. Thanks for taking the time!

Many times, we already have all of the players in our database. For a bit of a “manual” solution, would it make sense to export these players to excel prior to the event with 1 column being their playerID, and the other column being their name. A 3rd column would be image name and left blank until event day.

I could put the camera on sequential numbering, and just ask the player his name, look him up, and then put the number of the sequence we are on in the 3rd column and leave the images named IMG_0001.jpg for example.

Then run the images through Photoshop macro to resize all of them to the size I need and mass upload all of them into a folder on our server. Then export the excel info to the database to map it.

Am I missing something with this solution? Or does this solution give anyone other ideas?

True. At some point there has to be human identification of name (or ID, or timestamp, or something) -> Entry. That’s just unavoidable. I was just trying to use a field that would already be in the table, rather than introduce a synthetic value to the database.

Well I am presuming that there is someone entering the data into a table - all you have to do is reset the time on the camera to match the time on the server, or the computer where the data entry is taking place.

Assuming you have renamed the file to match the player’s name (Cups’ idea is interesting, but how would the computer know which timestamp goes to which player unless you manually entered them?).

foreach($_FILES['yourformname']['name'] AS $key => $picture) {
$picture = substr($picture,0,-4); //knock off .jpg
$sql = "SELECT id FROM players WHERE name LIKE(".str_replace(' ','%',$picture).")";
$res = $db->query($sql);
if($res->num_rows == 1) {
  list($id) = $res->fetch_row();
  $im = createimagetruecolor(<yourwidth>,<yourheight>);
  $src = createimagefromjpeg($_FILES['yourformname']['tmp_name'][$key]);
} else {
  echo "Entry Unknown: ".$_FILES['name'];

I would analyse the exif information coming from your camera and look for opportunities to match that data with when you are updating a text field in your database.

You may also have option to title the photograph using a time stamp or similar, and likewise take a smaller picture in the first place.

You want to identify something that can be used as a unique key which can be matched to time, I would have thought.