How do you check unique existance?

[Jaxel]

Yeah... I know I'm asking a lot of questions today...

I have a function which inserts a new entry into a database... but certain fields in this table are defined as UNIQUE... If I input something that is not unique, I get the following error...

Database error in vBulletin 3.8.1:

Invalid SQL:
INSERT INTO rank_players (pName, pRegion, pTeam, pCount, pTotal, pAverage, pTopTotal, pTopAverage, pExt)
VALUES ('Jaxel', '', '', '0', '0', '0', '0', '0', '');

MySQL Error : Duplicate entry 'Jaxel' for key 2


How would I go about for checking for existance before I attempt to insert into the database? I know I can do an SQL query to check for existance, but that would start adding too many queries. Is it possible to do this check, without doing a query?

[TigerC10]

You can try...


INSERT INTO rank_players (...) ON DUPLICATE KEY UPDATE (...)


OR


REPLACE INTO rank_players (...)


Otherwise you'll just have to do a select query first, and then based on the results of the select query change your insert query.

EDIT:
Just found (http://sqlite.phxsoftware.com/forums/p/1029/4443.aspx) this...

INSERT INTO Timeline (name, ts)

SELECT @name, @ts

WHERE NOT EXISTS (SELECT 1 FROM Timeline WHERE name=@name AND ts = @ts);


It's worth a try. :)

[Jaxel]

Naw... that stuff wont work for me, as I'm trying to return a readable prompt to the user...

I KNOW there is a function to check existance, I've seen it before, I just cant remember what it was.

[TigerC10]

You use the mysql_affected_rows (http://us.php.net/function.mysql-affected-rows) function after the INSERT INTO ... WHERE NOT EXISTS sample code. If 1 row is affected, you inserted properly. If 0 rows are affected, then it already existed and you have to display the prompt to the user.

[Dismounted]

Use INSERT IGNORE INTO and then check affected rows with $db->affected_rows().

[Jaxel]

Thanks Dismounted! That was exactly what I was looking for... I am now using the following code:

$db->query_write("INSERT IGNORE INTO ".TABLE_PREFIX."rank_players (pName, pRegion, pTeam, pCount, pTotal, pAverage, pTopTotal, pTopAverage, pExt)
VALUES ('".$name."', '".$region."', '".$team."', '0', '0', '0', '0', '0', '".$ext."')");

$update = $db->affected_rows();

if ($update)
{
$output = 'The entry for player (<b>'.$name.'</b>) has just been added to the database';
}
else
{
$output = '<font color="red">Unable to add (<b>'.$name.'</b>) - Player name already exists!</font>';
}
return $output;




Is there a function I can use to replace the following code in the same manner?

$db->query_write("UPDATE ".TABLE_PREFIX."rank_players SET pName='".$name."' WHERE playerID='".$player['playerID']."'");
$db->query_write("UPDATE ".TABLE_PREFIX."rank_players SET pRegion='".$region."' WHERE playerID='".$player['playerID']."'");
$db->query_write("UPDATE ".TABLE_PREFIX."rank_players SET pTeam='".$team."' WHERE playerID='".$player['playerID']."'");
$db->query_write("UPDATE ".TABLE_PREFIX."rank_players SET pExt='".$ext."' WHERE playerID='".$player['playerID']."'");

[Dismounted]

You can also use the IGNORE keyword in UPDATE queries. It is just UPDATE IGNORE.

[Jaxel]

Thanks... you're really helping... there is one more query I am trying to figure out...

SELECT rank_players.*, SUM(sValue) as total FROM rank_players
INNER JOIN rank_scores USING(playerID)
GROUP BY playerID
ORDER BY total DESC, pName ASC
LIMIT $limit,$rebcount

This is a routine I am writing to do a general update of total rankings... it gets the player data, and then the sum of the Values of their scores. The $limit and $rebcount values are the start and end of the query... since its doing a general update, I have it looping on page refreshes and renewing the starts and ends of the select query with each iteration. So far it works great...

However, there is one thing I'm failing at figuring out...

The SUM(sValue) string... I want it to ONLY count the sum of values...


SELECT * FROM rank_scores
INNER JOIN rank_events USING(eventID)
WHERE rank_events.eDate > $cutoff
LIMIT $bestcount

How would I go about doing this? I know I can easily add the INNER JOIN and WHERE statement to the above code, but I cant add the LIMIT statement, because I am already using it. I know there is a way to nest sub-queries... but I cant figure it out...

[Dismounted]

What's $bestcount?

[Jaxel]

$bestcount is 10...

Basically, I only want to SUM the best 10 scores that a player has... normally I would just put the LIMIT in the first statement... but I am already using that limit for the start/end page refresh.

[Marco van Herwaarden]

LIMIT will only limit the number of rows sent back to the client (ie. output), it should not be used to filter data.