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)

the gcCode count but i need to be able to add a new record at the same time — StealthRT
– StealthRT, Commented Sep 4, 2010 at 23:47

Daniel Vassallo · Accepted Answer · 2010-09-04 23:47:24Z

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.

answered Sep 4, 2010 at 23:47

Daniel Vassallo

346k72 gold badges513 silver badges447 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

OMG Ponies · Accepted Answer · 2010-09-04 23:48:25Z

0

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

answered Sep 4, 2010 at 23:48

community wiki

OMG Ponies

Collectives™ on Stack Overflow

MySQL - COUNT before INSERT in one query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related