Jump to content

OsirisElKeleni
Go to solution Solved by Barand,

Recommended Posts

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!

Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

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 by OsirisElKeleni
Link to comment
Share on other sites

  • Solution

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.