How to delete a column from a row inside a table? (mysql)

How to delete a column from a row inside a table? (mysql) - Printable Version

+- SA-MP Forums Archive (https://sampforum.blast.hk)
+-- Forum: SA-MP Scripting and Plugins (https://sampforum.blast.hk/forumdisplay.php?fid=8)
+--- Forum: Scripting Help (https://sampforum.blast.hk/forumdisplay.php?fid=12)
+--- Thread: How to delete a column from a row inside a table? (mysql) (/showthread.php?tid=545529)

How to delete a column from a row inside a table? (mysql) - Capua - 09.11.2014

So yeah again, how to delete a column from a row inside a table INSIDE my gamemode script? I've tried something but I get this error "(error #1109) Unknown table 'pass' in MULTI DELETE" - What I tried is this:

pawn Код:

new query[300];
    format(query, sizeof (query), "DELETE `pass`, `salt` FROM `users` WHERE `id` = '%i'",
    gPlayerInfo[playerid][pPass], gPlayerInfo[playerid][pSalt], gPlayerInfo[playerid][pID]);
    mysql_function_query(gHandle, query, false, "", "", "");

Basically I want to remove pass and salt column from an user inside users table upon choosing a new password with /changepass. And after the old pass and salt has bee deleted, new pass and salt will be assigned for the player who changed his pass.

This is how it looks like inside the users table:

Thanks for help! Rep++ for helpers!

Re: How to delete a column from a row inside a table? (mysql) - Eth - 09.11.2014

Nvm.

Re: How to delete a column from a row inside a table? (mysql) - Eth - 09.11.2014

here you are:

pawn Код:

new query[300];
    format(query, sizeof (query), "DELETE `pass`, `salt` FROM `users` WHERE `id` = '%i'",
    gPlayerInfo[playerid][pID]);
    mysql_function_query(gHandle, query, false, "", "", "");

Re: How to delete a column from a row inside a table? (mysql) - Capua - 09.11.2014

[ERROR] CMySQLQuery::Execute[()] - (error #1064) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELETE `pass`, `salt` FROM `users` WHERE `id` = '1'' at line 1

It's like this now:

pawn Код:

format(query, sizeof (query), "UPDATE `users` DELETE `pass`, `salt` FROM `users` WHERE `id` = '%i'", gPlayerInfo[playerid][pID]);
    mysql_function_query(gHandle, query, false, "", "");

Re: How to delete a column from a row inside a table? (mysql) - Eth - 09.11.2014

Quote:

Originally Posted by ******

I'll tell you this: Neither of you are even close! As I said, go learn SQL.

If you know the answer then why don't you help him instead of doing those comments? lol.
and this one you posted up here:

pawn Код:

format(query, sizeof (query), "UPDATE `users` DELETE `pass`, `salt` FROM `users` WHERE `id` = '%i'", gPlayerInfo[playerid][pID]);
    mysql_function_query(gHandle, query, false, "", "");

is totally wrong.
try to delete one after an other And make sure that your code is the same as this:

pawn Код:

new query[300];
    format(query, sizeof (query), "DELETE `pass` FROM `users` WHERE `id` = '%i'",
    gPlayerInfo[playerid][pID]);
    mysql_function_query(gHandle, query, false, "", "", "");

then:

pawn Код:

new query2[300];
    format(query2, sizeof (query2), "DELETE `salt` FROM `users` WHERE `id` = '%i'",
    gPlayerInfo[playerid][pID]);
    mysql_function_query(gHandle, query2, false, "", "", "");

full code:

pawn Код:

new query[300];
    format(query, sizeof (query), "DELETE `pass` FROM `users` WHERE `id` = '%i'",
    gPlayerInfo[playerid][pID]);
    mysql_function_query(gHandle, query, false, "", "", "");
    new query2[300];
    format(query2, sizeof (query2), "DELETE `salt` FROM `users` WHERE `id` = '%i'",
    gPlayerInfo[playerid][pID]);
    mysql_function_query(gHandle, query2, false, "", "", "");

Re: How to delete a column from a row inside a table? (mysql) - Mauzen - 09.11.2014

The DELETE statement does not accept single fields. It is meant to delete whole rows only, as it already says in the first paragraph of the documentation: http://dev.mysql.com/doc/refman/5.0/en/delete.html

If you want to reset/remove the value of a single field you should just use UPDATE to set it to NULL

Re: How to delete a column from a row inside a table? (mysql) - Capua - 09.11.2014

Alright here we go.. pass updates successfully, error comes up when trying to update salt. Claims I have a syntax error. I've tried various of edits but the error stays same;

Код:

[04:38:10] [DEBUG] mysql_escape_string - source: "haistavittu", connection: 1, max_len: 512
[04:38:10] [DEBUG] mysql_tquery - connection: 1, query: "UPDATE `users` SET `pass`='9261DF172F8C1821CB028D83E92E97FB22FFA", callback: "query", format: "(null)"
[04:38:10] [DEBUG] CMySQLQuery::CMySQLQuery() - constructor called
[04:38:10] [DEBUG] mysql_tquery - scheduling query "UPDATE `users` SET `pass`='9261DF172F8C1821CB028D83E92E97FB22FFAC69BC8DCFCCB41AC8B18C672A47BDED211170D8737E3569DD609C28E71B0FE3C884E33D30BC975E739632E9A2BE', `salt`='lkGpJ801745495045A0E6Jazb9m0269261DF172F8C1821CB028D83E92E97FB22FFAC69BC8"..
[04:38:10] [DEBUG] CMySQLQuery::Execute[query()] - starting query execution
[04:38:10] [ERROR] CMySQLQuery::Execute[query()] - (error #1064) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''lkGpJ801745495045A0E6Jazb9m0269261DF172F8C1821CB028D83E92E97FB22FFAC69BC8' at line 1

The code at the moment is the following;

pawn Код:

CMD:changepass(playerid, params[])
{
    if (gPlayerInfo[playerid][pLogged] == 0)
        return SendClientMessage( playerid, -1, "You need to be logged in to use this. " );
    new newpass[129];
    if(sscanf(params, "s[128]", newpass))
        return SendClientMessage( playerid, -1, "USAGE: /changepass [New password]. " );
    if (strlen(params) > 90)
        return SendClientMessage(playerid, -1, "Password has to be between 1 and 90 characters.");
    new Salt[30];
    randomString(Salt, SALT_LENGTH);
    format(newpass, sizeof(newpass), "%s%s", Salt, escape(newpass));
    WP_Hash(newpass, 129, newpass);
    new query[240];
    format(query, sizeof(query), "UPDATE `users` SET `pass`='%s', `salt`='%s' WHERE `id`='%i'", newpass, Salt, gPlayerInfo[playerid][pID]);
    mysql_function_query(gHandle, query, false, "query", "", "");
    SendClientMessage(playerid, -1, "You have successfully changed your password.");
    new string[70];
    format(string,sizeof(string),"Your new password is %s", newpass);
    SendClientMessage(playerid, -1, string);
    return 1;
}

What am I doing wrong now?

Re: How to delete a column from a row inside a table? (mysql) - LaPiMoNsTeR - 09.11.2014

pawn Код:

format(newpass, sizeof(newpass), "%s%s", Salt, escape(newpass));

Why you put 'escape(newpass)' ? I think you can just put 'newpass'.

Re: How to delete a column from a row inside a table? (mysql) - Eth - 09.11.2014

Quote:

Originally Posted by ******

That depends on your definition of "help". I know the answer because I have studied SQL as a language and read the documentation. I have done this because I recognise that it is a whole language in- and of-itself, and not just a minor add-on to PAWN you can master in five minutes.

So yes, I could use the knowledge I have gained from taking the time to learn this language to give a one-line answer on how to do exactly this one thing, but that won't help anyone. They will know how to do this one exact thing, but won't understand why the magic string of words I posted did it, or how to modify those words for the next slightly different situation.

Do you think I know how to do this because I have encountered exactly the same situation before, asked someone, and been told the exact answer to parrot back to you now? Because if so I can tell you for a fact that that isn't the case. Once, many years ago, I was where you are - a n00b asking how to do every little thing, and someone did to me what I am doing to you now (I still know exactly who it was, and considered them a good friend for years after, but have sadly lost touch now). They went by the mantra "teach a man to fish" - instead of just spoon-feeding me the answer, they pointed me at the documentation that was available and gave me a good kick to go read it. I think many people will agree that it clearly worked, and as a graduate of this "help them help themselves" method I wouldn't have it any other way.

So yes, I can help, and will help, by telling you to read the documentation carefully.

I do understand that you want to help the guy to study and know SQL I already know SQL and I don't need to study it anymore..But I have never ever use DELETE that's why I didn't give him a proper answer, but your comment should have been better than that like you would tell him: "the problem is in Format so try to figure it out"
or something like that would be better than your comment above, I can't say that I am perfect in scripting but I can say that I am very very very very very good in scripting..Also how did you know that he is totally noob in SQL? I mean maybe he knows everything about SQL except the DELETE part,when you said: "I'll tell you this: Neither of you are even close! As I said, go learn SQL." you made me sad as I didn't expect that from you. You didn't even said what I did wrong in that part I tried my best and it didn't work so what? Idk what more to say , Anyways

You need to hash newpassword then format it and after that you query it

also right here:

pawn Код:

format(newpass, sizeof(newpass), "%s%s", Salt, escape(newpass));
    WP_Hash(newpass, 129, newpass);

Why do you need to format the new pass with salt and newpass? I think it should be only like this:

pawn Код:

WP_Hash(newpass, 129, newpass);
    format(newpass, sizeof(newpass), "%s", escape(newpass));

Re: How to delete a column from a row inside a table? (mysql) - Eth - 09.11.2014

Quote:

Originally Posted by ******

If they knew all of SQL except for DELETE they would already have the answer since the answer is not in DELETE - hence why you were all way off. I would also expect someone adept at a language to already know of the documentation, having used it extensively, and know to go there to plug holes in their knowledge. If I forget the difference between LEFT INNER JOIN and RIGHT INNER JOIN, I don't post a topic here I just check the documentation.

This forum is not here to do things for people (despite popular opinion), it is here to point them in the right direction. In this case, the right direction is SQL documentation. Just to make that even more explicit:

http://dev.mysql.com/doc/ <- Very comprehensive documentation.
http://use-the-index-luke.com/ <- Guides to optimisation.
https://www.******.co.uk/search?num=...53.IQAXELkZWAI <- SQL tutorials. I'd say look at any except the W3Schools one.

This forum is to help people if they have any problem in their script . as you can see it's called "Scripting Help" it means help people who needs help in scripting ,Also he is not asking us to do a full code for him, all he asks for that we help him with that little thing also if you told him the answer he will learn from it after that.