Auto increment part of a field in MySql database

ok, here's what i'm trying to do:
I have a field in my database for storing an image name to be pulled out with PHP to display that image on a website.
the syntax for each value will be 'pets_1.jpg' the next will be pets_2.jpg and so on up until 'pets_301.jpg' or so then the suffix will change to 'hf', so the first will be 'hf_1.jpg' all the way up to 'hf_257.jpg' and then the suffix will change again, and so on....Does anybody know a way that I can run a query to automatically generate the filename rather than typing it in line by line?? obviously i would need to run a new query each time the suffix changes and put in the starting and ending value on each query. the primary key is a bog standard auto increment value.
Here's hoping somebody can point me in the right direction
Mick
mickedenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Cornelia YoderArtistCommented:
If you have control over the database structure, why not make it into two fields .... one is the type (value "pets" or "hf" or etc) and the other is the number.  That is much easier to control, although you'll still have to do a query to find the highest value for "pets" and increment it.

To retrieve the image name that you want, you'd just do something like

SELECT concat(Type, "_", Number, ".jpg") as ImageName FROM Table ....
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hujiCommented:
On the other hand, you can do the looping outside the MySQL code. Something like:

$prefixes = array('pets', 'hf', 'blah');

foreach($prefixes as $prefix){
  for($i=1; $i<302; $i++){
    $query = "INSERT INTO tablename(imagename) VALUES('".$prefix."_".$i."')";
  }
}

That assumes that the number of images which each prefix is equal. if it is not, you can change the code to handle it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.