majqq

SQLite - few questions

Recommended Posts

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.

 

 

 

 

  • Like 1

Share this post


Link to post
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)

  • Like 1

Share this post


Link to post
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?

Share this post


Link to post
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 by IIYAMA
  • Like 1

Share this post


Link to post
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 by IIYAMA
  • Like 1

Share this post


Link to post
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 :D

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.

  • Like 1

Share this post


Link to post
1 hour ago, majqq said:

It worked, thanks again :D

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.

 

  • Like 1

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Recently Browsing   0 members

    No registered users viewing this page.