MySQL quicky.

First, wasn’t there a thread for quick questions to ask for LUA based stuff as I cannot find it.

Anyway, this is more MySQL but still. I have this query:

        DB.Query("UPDATE byb_darkrp_stats SET ".. QueryUpdate .." WHERE uid = ".. ply:UniqueID().." AND session='"..SessID.."' AND server='"..Serv.."';")

Now SessID is the os.time() of the server start, and while it’s extremely unlikely a server will both start on the same second I figured I should be safe. However, it’s not working.
If there are 2 entries in the table with the same session then it will use the first it finds, basically it won’t do the “AND server=” part of the query and I’m not sure why.

Anyone feel like helping?


UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

you’re just doing SET col_name1
What are you setting it to, knob?

[lua]DB.Query(“UPDATE byb_darkrp_stats SET “… QueryUpdate …” = WhatHere? WHERE uid = “… ply:UniqueID()…” AND session=’”…SessID…"’ AND server=’"…Serv…"’;")[/lua]

So so confused

QueryUpdate could be editing 1 column or all 27 columns of this table. I made the query modular as it runs inside a repeated function. Why force it to update all 27 columns if only 1 has changed?

How can you get this confused?

An UPDATE query UPDATES the value of the given columns of the selected rows to a value.

THIS is the UPDATE syntax:


UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

Look very specifically at


SET col_name1={expr1|DEFAULT}

Note that there is an ‘=’ character. It is obligatory. It has to be there. If you don’t have it, the query has the wrong syntax.

Now here’s your query:
[lua]DB.Query(“UPDATE byb_darkrp_stats SET “… QueryUpdate …” WHERE uid = “… ply:UniqueID()…” AND session=’”…SessID…"’ AND server=’"…Serv…"’;")[/lua]

Now let’s look specifically at the same part:
[lua]SET “… QueryUpdate …”[/lua]
There is no ‘=’ character here, therefore your query is syntactically


wrong

I’m going to guess that it has to be something like this:
[lua]SET SOME_COLUMN = “… QueryUpdate …”[/lua]

Wrong? So completely wrong, unless I’m misunderstanding you. What you are going on about has nothing to do with what I asked, I even said the query works just the WHERE statement is conflicting, it’s as if I’m using OR instead.

The query is running without errors and IS updating the row I want it to. However, if the sessions ID has duplicates it is updating the wrong session ID.
See, the row is updating just fine. It places it in the correct row. UNLESS the session column has a duplicate, in which case it is not checking if the “AND server=” part of the query.
So the code/query is working, it’s just if you look at the image you will see the last 2 session ID’s are identical, this is causing the issue.

Now sorry to sound rude, but underlining random words to emphasise them only works when you’re right, when you’re wrong it makes you look like a complete tool.

[editline]16th May 2013[/editline]

Ok screw it, let me simplify this:

The query, without the lua variables:

UPDATE byb_darkrp_stats SET pie=pie +1,elephant=elephant +1 WHERE uid=1234232 AND session=123432 AND server=‘RP1’;

Right, the WHERE uid AND session are working fine, it is however NOT checking the 3rd part of: AND server.

[editline]16th May 2013[/editline]

Solved.

Was working in the MySQL console but not via the MySQLoo module, changed it to use sqlstr and it works now, odd though as it should have worked both ways.



"UPDATE table SET column='value_stuff', column='value_stuff', column='value_stuff' WHERE steamid_column='steam_0:1:1231424'"


Nevermind I’m dumb, but I’ll still leave this here.

The QueryUpdate variable probably includes the “=”

[editline]17th May 2013[/editline]

You should use mysqldb:escape( whatever ) instead of sql.SQLStr, MySQL/SQLite have different escape characters.

Yea. I’m not really sure if it makes much difference but I was recording lots of possible data. Figured if only 1 thing had changed I might as well only update that column instead of all of them.

I’ll look into that.

So he didn’t give half the query. This is ridiculous.

You might want to make sure your finished string is exactly what you want it to be, no random escape characaters, duplicate entries ( using static variables for example ), or syntax errors. If that doesn’t work, you could try having brackets, “WHERE ( whatever = whatever AND whatever = whatever )”.

He fixed it oubliette.

Yea, which is why I admit I made it hard to explain. But I did state it was the WHERE AND clauses I was having issues with. Not all of us like our gamemode to spam extremely inefficient queries…

What the fuck are you talking about? You can’t figure out how to escape strings in MySQL and yet you snap at me with a vague accusation that my database management is inefficient?

Judging by the reason why this tread was made, I don’t believe that you have enough knowledge about databases to make that claim. I invite you to either post specifics, which we will then discuss or swallow your pride saying that you don’t really know what you’re talking about.