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
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.
|
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
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!
|
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.