Combined query in MySQL and more

[Disco]

Hey folks!

I'd like to do this:

* PC enters module, and I check if he is assigned a database ID (stored on an item called Journal)

* If not I make a new record, collect the ID and store it on the PC's Journal and save his character.

* If I find the ID I update the record with some data

I am used to work with MySQL from PHP and in that case I'd simply fetch the last insert ID with another query (well, there's a dedicated funstion, but nvm that), but I am not sure how trustworthy this is in NWN(X). I'd also prefer to keep the number of database queries to the absolute minimum.

Thanks!
My question: what's the most reliable & efficent way of implementing this?

[Papillon]

If you do this is in a transaction, I'd say the result can be trusted. If not... well, other servers could insert data in the meantime. The probability is low, but not zero.

Do you really need the ID ? Maybe you could get rid of it entirely. If not, you could fetch the newly made entry using Playername + Pcname, which should be unique in that table. You could use that to safely identify the record that was just inserted.
_________________
Papillon

[Disco]

I was thinking of using the loginname + timestamp, but a simple ID is quite elegant. It's all about efficiency, and transferring large amounts of INT(6) s is a bit cheaper than strings of 30/40 characters. I need to retrieve the ID only once, and I need to use it very often afterwards for INSERTS and UPDATES.

[Papillon]

The difference is negligible.
_________________
Papillon

[Grinning Fool]

[Disco]

Testing it now.

I tried the timestamp + login way, which worked rather well, but the fact that some people use account of 60+ characters gives me the creeps. It's simply not esthetical!

So, stubborn as I am I rewrote the entire script again and used the "INSERT record first and SELECT it on the next transition for the right ID" method. It's also a good way to check if a record has been made anway, as people manage to login before the database scripts are initialised.

No I have another multiple statement question:

I have a record ID and two tables that I want to search two table with this ID. I just want to know if both have an entry. I need to search both tables on more values that just the ID, and I want the result set to be in the form of column1 = value if record exists, Column2 = value if record exists.

I tried using joins, but didn't get that far with those. I could try to use INSERT... UPDATE ON DUPLICATE KEY, but I want to update records that need to have unique combinations of values, not unique primary keys.

For example: IP | Account | CharID | Count | Updated

IP | Account | CharID should be unique together

Count | Updated should be updated.

I have no clue how I can make sure such a record exists without SELECTing it first, and I don't want to force errors by blindly INSERTing it. I think I need to SELECT, and if I must I'd like to select another table in the same statement, and that's why I ask the question above.

Hope my rather vague explanation tells you what I want.

[Papillon]

It's difficult to give detailed advice without knowing the table and data structure, but IF you really can not do it with a join, subselects (or subqueries in MySQL) might help you out:

http://dev.mysql.com/doc/refman/5.0/en/subqueries.html
_________________
Papillon

[pdwalker]

mysql has a non standard insert statement that allows you to update a record if the record already exists.

http://dev.mysql.com/doc/refman/4.1/en/insert.html

Perhaps you could use this to solve your problem?

[Disco]

What I am looking for is an UPDATE if exists INSERT statement. It may seem to be equivalent to INSERT if exists UPDATE but it isn't as you select on a full WHERE clause isntead of the primary key.

Say I have a table A that has the fields a1, a2, a3, a4 and a table B with b1, b2, b3

I also have a variable CharID, a variable Week, and a variable Account

I want to update a3, a4 if a1 = CharID and a2 = Week and b3, b4 if b1 = CharID and b2 = Account. If either record doesn't exist I want to create a new one which I can update with the next call. In pseudocode:

[Disco]

Nevernermind this, I just learned about combined unique indexes!