Count function

[OsirisElKeleni]

This is a dummy question but i cant get multiple criteria in the count function.

 

This is what i'm trying to do:

SELECT COUNT(Name, Expansion, Foil) AS NameCheck FROM Osiris WHERE Name="$name" Expansion="$expansion" Foil="$foil"

I have Colums named:

`Name` `Color` `Type` `Subtype` `Power` `Toughness` `Manacost` `Rarity` `Expansion` `Foil` `Stock`

I want to check before i enter a new record if its not already there, by checking the Name, Expansion and Foil colum on the same record.

 

I think i have just messed up that code line but i cant seem to find any solution on the web.

 

Thanks in advance!

[Barand]

Are you looking for something like this?

SELECT 
    SUM(IF(Name='$name'),1,0) as namecheck
    , SUM(IF(Expansion='$expansion'),1,0) as expcheck
    , SUM(IF(Foil='$foil'),1,0) as foilcheck
FROM Osiris 
WHERE (Name='$name')
  OR  (Expansion='$expansion')
  OR  (Foil='$foil')

Or are you looking for those where all 3 match the criteria.

 

It's impossible to tell what you want from your post and current query

Edited October 11, 2014 by Barand

[OsirisElKeleni]

I tryd it but the sql didnt work

Are you looking for something like this?

SELECT 
    SUM(IF(Name='$name'),1,0) as namecheck
    , SUM(IF(Expansion='$expansion'),1,0) as expcheck
    , SUM(IF(Foil='$foil'),1,0) as foilcheck
FROM Osiris 
WHERE (Name='$name')
  OR  (Expansion='$expansion')
  OR  (Foil='$foil')

Or are you looking for those where all 3 match the criteria.

 

It's impossible to tell what you want from your post and current query

 

I'm trying to check mutiple criteria in one row if its already in my database the newly sent record should not be added.

 

I'm going to give you an example of what a record looks like:

Name         Color       Type	    Sub-Type   Power   Toughness  CMC  Rarity    Expansion Foil Stock
Kite Shield  Colorless   Artifact   Equipment                      0   Uncommon	    M12	    No	  1

The top row are the names of the colums the bottom row is the data stored.

The thing is another row could be exactly the same as the one above with the expansion changed or the Foil status changed or the name slightly changed. So i'd have to check atleast these 3 criteria in one row to know for sure its not in there already.

 

I hope this explains alot more :/ 

I basicly need to be able to check for 3 different criteria in one row to be sure its not the same.

 

If you look at the second and 3rd row on the webite you'll see that checking the name for differences is not enough because the Foil row is different.

If you type in Loxodon Warhammer in the first input box and press enter you'll see that the Expansion and the Rarity is different.

So i hope now you understand what i'm trying to achieve here.

 

I hope you understand more now.

This is my website: http://osiriselkeleni.be

 

So you can see what setup i have atm.

 

Thanks for helping me out!

Edited October 11, 2014 by OsirisElKeleni

[OsirisElKeleni]

update

I'm trying to check mutiple criteria in one row if its already in my database the newly sent record should not be added but instead the stock count should be added to the stock count already saved.

Edited October 11, 2014 by OsirisElKeleni

[Barand]

Add unique key on Osiris(Name, Expansion, Foil);

Then, when you insert use

INSERT INTO Osiris( <fields> ) VALUE ( <vals> )
ON DUPLICATE KEY UPDATE stock = stock + $qty

Now when inserting and the same combination already exists then the stock will be updated instead of creating a new record

[OsirisElKeleni]

Thanks that worked out great!

I didnt know primary key could be added on multiple columns, now i know!

Thanks alot!