vb.org Archive
Page 1 of 2 1 2
Show 40 post(s) from this thread on one page

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)
-   -   How do you check unique existance? (https://vborg.vbsupport.ru/showthread.php?t=205302)

Jaxel 02-14-2009 02:26 AM
How do you check unique existance?
 
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...

Code:
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 02-14-2009 02:33 AM
You can try...

Code:
INSERT INTO rank_players (...) ON DUPLICATE KEY UPDATE (...)
OR

Code:
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 this...
Code:
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 02-14-2009 02:56 AM
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 02-14-2009 03:38 AM
You use the 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 02-14-2009 05:01 AM
Use INSERT IGNORE INTO and then check affected rows with $db->affected_rows().

Jaxel 02-14-2009 11:16 AM
Thanks Dismounted! That was exactly what I was looking for... I am now using the following code:

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?

Code:
        $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 02-14-2009 11:47 AM
You can also use the IGNORE keyword in UPDATE queries. It is just UPDATE IGNORE.

Jaxel 02-14-2009 01:27 PM
Thanks... you're really helping... there is one more query I am trying to figure out...

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

Code:
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 02-15-2009 03:09 AM
What's $bestcount?

Jaxel 02-15-2009 01:15 PM
$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.


All times are GMT. The time now is 12:27 PM.
Page 1 of 2 1 2
Show 40 post(s) from this thread on one page

Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2025, vBulletin Solutions Inc.

X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.01662 seconds
  • Memory Usage 1,739KB
  • Queries Executed 10 (?)
More Information
Template Usage:
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (8)bbcode_code_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)pagenav
  • (1)pagenav_curpage
  • (1)pagenav_pagelink
  • (1)post_thanks_navbar_search
  • (1)printthread
  • (10)printthreadbit
  • (1)spacer_close
  • (1)spacer_open 
Phrase Groups Available:
  • global
  • postbit
  • showthread
Included Files:
  • ./printthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_hook.php
  • ./includes/modsystem_functions.php
  • ./includes/class_bbcode_alt.php
  • ./includes/class_bbcode.php
  • ./includes/functions_bigthree.php 
Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • printthread_start
  • pagenav_page
  • pagenav_complete
  • bbcode_fetch_tags
  • bbcode_create
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • printthread_post
  • printthread_complete