MySQL quicky.

[Pantho]

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?

[FPtje]

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]

[Pantho]

[QUOTE=FPtje;40664116]

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][/QUOTE]

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?

[FPtje]

How can you get this confused?

An UPDATE query UPDATES the value of the [u]given[/u] columns of the [u]selected[/u] 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 [b]very[/b] 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 [u]specifically[/u] at the same part:
[lua]SET ".. QueryUpdate .."[/lua]
There is no '=' character here, therefore your query is syntactically [highlight]wrong[/highlight]

I'm going to [i]guess[/i] that it has to be something like this:
[lua]SET SOME_COLUMN = ".. QueryUpdate .."[/lua]

[Pantho]

[QUOTE=FPtje;40667213]How can you get this confused?

An UPDATE query UPDATES the value of the [U]given[/U] columns of the [U]selected[/U] 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 [B]very[/B] 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:

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

Now let's look [U]specifically[/U] at the same part:

SET ".. QueryUpdate .."

There is no '=' character here, therefore your query is syntactically [highlight]wrong[/highlight]

I'm going to [I]guess[/I] that it has to be something like this:

SET SOME_COLUMN = ".. QueryUpdate .."
[/QUOTE]

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 [B]random[/B] words to [U]emphasise[/U] 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.

[find me]

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

[Drakehawke]

[QUOTE=FPtje;40667213]How can you get this confused?

An UPDATE query UPDATES the value of the [u]given[/u] columns of the [u]selected[/u] 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 [b]very[/b] 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 [u]specifically[/u] at the same part:
[lua]SET ".. QueryUpdate .."[/lua]
There is no '=' character here, therefore your query is syntactically [highlight]wrong[/highlight]

I'm going to [i]guess[/i] that it has to be something like this:
[lua]SET SOME_COLUMN = ".. QueryUpdate .."[/lua][/QUOTE]

The QueryUpdate variable probably includes the "="

[editline]17th May 2013[/editline]

[QUOTE=Pantho;40667431]
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.[/QUOTE]

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

[Pantho]

[QUOTE=Drakehawke;40669941]The QueryUpdate variable probably includes the "="

[/QUOTE]
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.

[QUOTE=Drakehawke;40669941]

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

I'll look into that.

[FPtje]

[QUOTE=Drakehawke;40669941]The QueryUpdate variable probably includes the "="[/QUOTE]
So he didn't give half the query. This is ridiculous.

[>>oubliet]

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 )".

[Bo98]

He fixed it oubliette.

[Pantho]

[QUOTE=FPtje;40674168]So he didn't give half the query. This is ridiculous.[/QUOTE]

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

[FPtje]

[QUOTE=Pantho;40675806]Not all of us like our gamemode to spam extremely inefficient queries...[/QUOTE]
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.