[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 218
  • Last Modified:

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
2 Solutions
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 ....
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.

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now