CODEWITHSUNDEEP
phpsql
user818653
user818653

Reputation: 11

php and mySQL how to get highest value in a varchar column

In a case where I have a column that needs to be unique eg. product:eggs, tomatoes, pepper, pepper1, pepper2 and before i insert another pepper i need to check the last integer, and add 1 to it, so the next pepper would be 'pepper3' How would i do this? Thanks in advance

Upvotes: 1

Views: 543

Answers (2)

luxcem
luxcem

Reputation: 1854

The easy way is to have two columns: the first for the label and the second for the id. It's never good to mix up various information in the same column.

Then you could do something like :

SELECT MAX(product_id) FROM ... WHERE label = "pepper"

and

SELECT CONCAT(label,product_id) FROM ... WHERE id = ...

Returns what you want.

Upvotes: 5

Steven
Steven

Reputation: 928

I would try something like this:

SELECT LEFT(product_name, 6) AS product_name_plain,
    CAST(RIGHT(product_name, 6) AS UNSIGNED) AS product_number
  FROM product_table
  WHERE product_name_plain = "pepper"
  ORDER BY product_number DESC
  LIMIT 1
  • The SELECT breaks apart product names into the plain name ("pepper"), and an unsigned integer version of the product number (3).
  • The WHERE clause identifies the peppers.
  • The ORDER BY will sort them (which should result in the last pepper being the first result)
  • The LIMIT will only fetch the one result.
  • Note that "6" and "pepper" are hard-coded in this query, your code would have to put them in. 6 is the length of "pepper."

Upvotes: 0

Related Questions