SQLite - few questions

[IIYAMA]

11 hours ago, majqq said:

iprint result:

 

I would suggest this format:

ID (auto incremented) INT | playerSerial TEXT | itemType TEXT | amount INT

This allows you lot more flexibility.

 

 

1 hour ago, majqq said:

Also, i need to ask about that, it's possible to obtain data from 2 tables in one query? In my case `Players` and `Items`?

(based on your current structure)

SELECT `Player`.*, `Item`.Bandage
FROM `Player` 
LEFT JOIN `Item` ON `Player`.serial = `Item`.serial 

Getting all the info of the player and the bandage quantity.

The keyword JOIN allows you to join another table.

 

LEFT means that:

`Player`.serial = `Item`.serial

1. It will get all the data from the table on the left side. (MAX all the rows of the leftside)
2. Attach data from the table on the right side where possible.

 

 

 

 

[savour]

1 hour ago, majqq said:

obtain data from 2 tables in one query

You can use "INNER JOIN" but you will need to practice it a little bit (columns with the same name will be prefixed with the table name)

[ds1-e]

14 minutes ago, IIYAMA said:

 

I would suggest this format:

ID (auto incremented) INT | playerSerial TEXT | itemType TEXT | amount INT

This allows you lot more flexibility.

 

 

(based on your current structure)

SELECT `Player`.*, `Item`.Bandage
FROM `Player` 
LEFT JOIN `Item` ON `Player`.serial = `Item`.serial 

Getting all the info of the player and the bandage quantity.

The keyword JOIN allows you to join another table.

 

LEFT means that:

`Player`.serial = `Item`.serial

1. It will get all the data from the table on the left side. (MAX all the rows of the leftside)
2. Attach data from the table on the right side where possible.

 

 

 

 

Thanks, but what if i would need to use WHERE to check if serial is matching with a player?

[IIYAMA]

30 minutes ago, majqq said:

Thanks, but what if i would need to use WHERE to check if serial is matching with a player?

As far as I can remember here:

FROM `Player` WHERE  ... LEFT JOIN

Edited September 12, 2019 by IIYAMA

[IIYAMA]

31 minutes ago, majqq said:

Thanks, but what if i would need to use WHERE to check if serial is matching with a player?

Correction. It should be after LEFT JOIN.

Because you need first the data and then you set the condition/filter.

 

LEFT JOIN `Item` ON `Player`.serial = `Item`.serial WHERE true

 

Edited September 12, 2019 by IIYAMA

[ds1-e]

On 12/09/2019 at 13:31, IIYAMA said:

Correction. It should be after LEFT JOIN.

Because you need first the data and then you set the condition/filter.

 

LEFT JOIN `Item` ON `Player`.serial = `Item`.serial WHERE true

 

It worked, thanks again

I need to ask about moment when server prepare string with function dbPrepareString, this is the moment when table is created/re-created. I was thinking it's possible that due of timing this will be executed at same time, so data for another player wouldn't save (script re-creates tables). Not sure if it's only my imagination.

[IIYAMA]

1 hour ago, majqq said:

It worked, thanks again

I need to ask about moment when server prepare string with function dbPrepareString, this is the moment when table is created/re-created. I was thinking it's possible that due of timing this will be executed at same time, so data for another player wouldn't save (script re-creates tables). Not sure if it's only my imagination.

 

dbPrepareString does not apply changes to the database.

The most logic reason I can think of for the dbPrepareString databaseConnection argument, are the string escape rules. But that is also where my knowledge ends.

 

A database has a query queue, so if your timing is correct on Lua, the timing on the database is also correct.