MySQL - COUNT before INSERT in one query

Question

Hey all, I am looking for a way to query my database table only once in order to add an item and also to check what last item count was so that i can use the next number.

  strSQL = "SELECT * FROM productr"

After that code above, i add a few product values to a record like so:

  ID | Product      | Price  | Description      | Qty | DateSold  | gcCode
  --------------------------------------------------------------------------
  5  | The Name 1   | 5.22   | Description 1    | 2   | 09/15/10  | na
  6  | The Name 2   | 15.55  | Description 2    | 1   | 09/15/10  | 05648755
  7  | The Name 3   | 1.10   | Description 3    | 1   | 09/15/10  | na
  8  | The Name 4   | 0.24   | Description 4    | 21  | 09/15/10  | 658140

i need to count how many times it sees gcCode <> 'na' so that i can add a 1 so it will be unique. Currently i do not know how to do this without opening another database inside this one and doing something like this:

  strSQL2 = "SELECT COUNT(gcCode) as gcCount FROM productr WHERE gcCode <> 'na'

But like i said above, i do not want to have to open another database query just to get a count.

Any help would be great! Thanks! :o)

Answer 1

There's no need to do everything in one query. If you're using InnoDB as a storage engine, you could wrap your COUNT query and your INSERT command in a single transaction to guarantee atomicity.

In addition, you should probably use NULL instead of na for fields with unknown or missing values.

Answer 2

They're two queries; one is a subset of the other which means getting what you want in a single query will be a hack I don't recommend:

 SELECT p.*,
        (SELECT COUNT(*)
           FROM PRODUCTR 
          WHERE gccode != 'na') AS gcCount
   FROM PRODUCTR p

This will return all the rows, as it did previously. But it will include an additional column, repeating the gcCount value for every row returned. It works, but it's redundant data...