SQL query statements question - Printable Version

SQL query statements question - Printable Version

+- SA-MP Forums Archive (https://sampforum.blast.hk)
+-- Forum: Other (https://sampforum.blast.hk/forumdisplay.php?fid=7)
+--- Forum: Everything and Nothing (https://sampforum.blast.hk/forumdisplay.php?fid=23)
+--- Thread: SQL query statements question (/showthread.php?tid=640964)

SQL query statements question - Meller - 09.09.2017

Since SQL is full of eh.. whatever

This is what I want to do in 1 single query:

insert data to table if row doesn't exist and then select the data
or if row exists, select data

How exactly would I write this in one single threaded query? This is how I would've done it: in plain PAWN:

PHP код:



mysql_tquery(MYSQL_DEFAULT_HANDLE, "SELECT * FROM player_stats WHERE id = 5 LIMIT 1", "OnQueryLoad_PlayerStats", "i," playerid);


forward public OnQueryLoad_PlayerStats(playerid);

public OnQueryLoad_PlayerStats(playerid) {

   if(cache_num_rows() == 0) {

      mysql_tquery(MYSQL_DEFAULT_HANDLE, "INSERT INTO player_stats SET (id, name, etc) VALUES (1, 'Meller', 'etc etc');", "i," playerid);

   }

}


((ignored typos, wrote it in the browser :( ))

If I'm not completely mistaken, how can I shorten this up to one single query?

Re: SQL query statements question - Paulice - 09.09.2017

Look through Vince's posts, how to do it will be shown there lol

And you said scripting/programming was easy in another thread :/

Re: SQL query statements question - Meller - 09.09.2017

Quote:

Originally Posted by Paulice

Look through Vince's posts, how to do it will be shown there lol

And you said scripting/programming was easy in another thread :/

It is, but you do realize what SQL stands for?
Structured Query Language, I've never said I can SQL by 100%. It's a big difference by a language and scripting.

The language describes how the structure should be, you do also realize that this differs so much by each language?
And you do realize scripting is jsut writing the structure?

Should I be able to know every single damn structure within the language because I've used it a few times? No.

Re: SQL query statements question - Xeon™ - 09.09.2017

Quote:

Originally Posted by Meller

Wikipedia people....

actually Mysql is more simpler than any other saving system (as SAVING system)

the base functions are

CREATE TABLE `` (colunm(type(size)), ..)
SELECT * FROM `` WHERE `` = ''
DELETE * FROM `` WHERE `` = ''
INSERT INTO `` () VALUES ()
UPDATE `` SET `` = '', ...

that so simple, you don't have even to think... (that's the functions needed on pawn ^^)
there some others functions such: ALTER, CREATE DATABASE... etc

in your case you don't need to use mysql_tquery, you can format it from the start, by using mysql_format (`%e` for espace) and use mysql_query directly

PHP код:



INSERT INTO player_stats SET (id, name, etc) VALUES (1, 'Meller', 'etc etc')

this won't work, you have an error in your query after playser_stats. you are loading stats, if you have a brain (not insulting nor disrespecting you) you will know that "SET" not needed, and keep on mind that querys aren't a pawn functions, they are like 'commands' so you don't need to put a ";" in end

MySQL is so simple (for me!), there's many query tutorials in the MySQL v8 official website.

Have fun :P

EDIT: you should fetch the values after excuting the query! using cache_get_value_name!

check MySQL R40+ Tutorials, im sure you will understand well!

Re: SQL query statements question - Kaperstone - 09.09.2017

(Untested)

Select or Insert ( https://dev.mysql.com/doc/refman/5.7...functions.html , https://dev.mysql.com/doc/refman/5.7/en/case.html )

Код:

SELECT CASE WHEN (SELECT COUNT(*) FROM player_stats WHERE id = 5 LIMIT 1)>0 
THEN 
    (SELECT * FROM player_stats WHERE id = 5 LIMIT 1) 
ELSE        
    INSERT INTO player_stats (id, name, etc) VALUES (1, 'Meller', 'etc etc')
END

Insert if not exists ( https://dev.mysql.com/doc/refman/5.7...ubqueries.html )

Код:

INSERT INTO player_stats (id, name, etc)
SELECT * FROM (SELECT 'Meller', 'etc etc') AS tmp
WHERE NOT EXISTS (
    SELECT * FROM player_stats WHERE id = 5 LIMIT 1)
) LIMIT 1;

I am not sure whether it will cause mysql errors, but you can search on ****** for some answers or explore the MySQL documentation.

There is also INSERT ... ON DUPLICATE KEY UPDATE

Re: SQL query statements question - Meller - 09.09.2017

Quote:

Originally Posted by XeonMaster

PHP код:



INSERT INTO player_stats SET (id, name, etc) VALUES (1, 'Meller', 'etc etc')

You do realize what I wrote in PAWN there was jsut for you to understand what I'm trying to create in the query of SQL?

Re: SQL query statements question - Vince - 09.09.2017

It seems a bit backwards to me to select data that you just inserted. You can use INSERT IGNORE to suppress duplicate key errors and just pass all the data you inserted to the callback as parameters.