SA-MP Forums

Go Back   SA-MP Forums > SA-MP Scripting and Plugins > Scripting Help > Tutorials

Reply
 
Thread Tools Display Modes
Old 10/06/2017, 11:02 PM   #31
Vince
Spam Machine
 
Vince's Avatar
 
Join Date: Sep 2007
Location: Belgium
Posts: 11,045
Reputation: 2647
Default Re: [MySQL] Table structure and foreign keys

I don't know if SQLite actually enforces relations the way MySQL does but the theory should be all the same.
__________________
Vince is offline   Reply With Quote
Old 10/06/2017, 11:10 PM   #32
Gammix
High-roller
 
Gammix's Avatar
 
Join Date: Jan 2015
Location: Ottawa, Canada
Posts: 1,683
Reputation: 662
Default Re: [MySQL] Table structure and foreign keys

SQLite of samp is horribly outdated.
__________________
Gammix is offline   Reply With Quote
Old 16/07/2017, 12:14 PM   #33
Tass007
Gangsta
 
Tass007's Avatar
 
Join Date: May 2011
Location: Somewhere not near you
Posts: 672
Reputation: 30
Default Re: [MySQL] Table structure and foreign keys

I am quite new to understanding the way mysql works as a whole. Especially Table structure and foreign keys, I've got a system with basically the exact same system as your explanation. However I'm not sure how to add to the table and also how to load from the table. Could someone please explain and show me how to do it? Thank you

PHP Code:
mysql_format(ServerMySQL,string,sizeof(string),"CREATE TABLE IF NOT EXISTS `Vehicles` (\
        `uID` int(11) NOT NULL,\
        `VehID` int(11) unsigned NOT NULL AUTO_INCREMENT,\
        `Type` tinyint(7) NOT NULL DEFAULT '0',\ 
        `Model`  tinyint(7) NOT NULL DEFAULT '0',\
        `Locked` tinyint(7) NOT NULL DEFAULT '0',\
        `Fuel` tinyint(7) NOT NULL DEFAULT '0',\
        `CarX` float NOT NULL,\
        `CarY` float NOT NULL,\
        `CarZ` float NOT NULL,\
        `CarR` float NOT NULL,\
        `VehNum` char(9) NOT NULL,"
);
        
mysql_format(ServerMySQL,string,sizeof(string),"%s\
        `SavedWeaps0` tinyint(7) NOT NULL DEFAULT '0',\
        `SavedAmmos0` tinyint(7) NOT NULL DEFAULT '0',\
        `SavedWeaps1` tinyint(7) NOT NULL DEFAULT '0',\
        `SavedAmmos1` tinyint(7) NOT NULL DEFAULT '0',\
        `SavedWeaps2` tinyint(7) NOT NULL DEFAULT '0',\
        `SavedAmmos2` tinyint(7) NOT NULL DEFAULT '0',"
,string);
        
mysql_format(ServerMySQL,string,sizeof(string),"%s\
        `SavedWeaps3` tinyint(7) NOT NULL DEFAULT '0',\
        `SavedAmmos3` tinyint(7) NOT NULL DEFAULT '0',\
        `SavedWeaps4` tinyint(7) NOT NULL DEFAULT '0',\
        `SavedAmmos4` tinyint(7) NOT NULL DEFAULT '0',\
        PRIMARY KEY (`VehID`)) ENGINE = InnoDB DEFAULT CHARSET=latin1"
,string);
        
mysql_tquery(ServerMySQLstring);
        
mysql_tquery(ServerMySQL"ALTER TABLE `Vehicles` ADD FOREIGN KEY (`uID`) REFERENCES `Users` (`uID`) ON UPDATE CASCADE ON DELETE CASCADE");
        
mysql_format(ServerMySQL,"CREATE TABLE IF NOT EXISTS `Vehicle_Mods` (\
        `VehID` int(11) unsigned NOT NULL,\
        `mod_model` smallint(5) unsigned NOT NULL,\
        `Col1` tinyint(7) unsigned NOT NULL DEFAULT '0',\
        `Col2` tinyint(7) unsigned NOT NULL DEFAULT '0',\
        `CarPaint` smallint(7) unsigned NOT NULL DEFAULT '255',\
        PRIMARY KEY (`VehID`, `mod_model`)) ENGINE = InnoDB DEFAULT CHARSET=latin1"
);
        
mysql_tquery(ServerMySQLstring);
        
mysql_tquery(ServerMySQL"ALTER TABLE `Vehicle_Mods` ADD FOREIGN KEY (`VehID`) REFERENCES `Vehicles` (`VehID`) ON UPDATE CASCADE ON DELETE CASCADE"); 
__________________
Copyright 2016 vR Studios
Currently working with vR Studios
Copying and pasting doesn't allow you to learn the things that reading and understanding will.
Tass007 is offline   Reply With Quote
Old 18/07/2017, 10:49 PM   #34
Tass007
Gangsta
 
Tass007's Avatar
 
Join Date: May 2011
Location: Somewhere not near you
Posts: 672
Reputation: 30
Default Re: [MySQL] Table structure and foreign keys

Bump?
__________________
Copyright 2016 vR Studios
Currently working with vR Studios
Copying and pasting doesn't allow you to learn the things that reading and understanding will.
Tass007 is offline   Reply With Quote
Old 22/07/2017, 04:48 AM   #35
Tass007
Gangsta
 
Tass007's Avatar
 
Join Date: May 2011
Location: Somewhere not near you
Posts: 672
Reputation: 30
Default Re: [MySQL] Table structure and foreign keys

Bump?
__________________
Copyright 2016 vR Studios
Currently working with vR Studios
Copying and pasting doesn't allow you to learn the things that reading and understanding will.
Tass007 is offline   Reply With Quote
Old 22/07/2017, 05:00 AM   #36
Kaperstone
High-roller
 
Kaperstone's Avatar
 
Join Date: May 2011
Location: Home
Posts: 3,119
Reputation: 645
Default Re: [MySQL] Table structure and foreign keys

read the red marks, he already stated that this tutorial is intended for people who already have general knowledge in MySQL (who already are familiar with MySQL functions and how to work with the), this is why you're not getting answers, nor from Vince.
Its not intended to guide people through MySQL, but rather give tips&tricks on how to improve your existing knowledge.
You're simply - kinda of- showing off you ignored the first post.


Refer to this tutorial, I haven't read the tutorial, but he do guide through table creation, structure, how to modify them and manage.
http://forum.sa-mp.com/showthread.php?t=485633
__________________
Find me on GitHub SEF community (Official NEF Successor) Ko ko nut.
Kaperstone is offline   Reply With Quote
Old 22/07/2017, 06:04 AM   #37
Tass007
Gangsta
 
Tass007's Avatar
 
Join Date: May 2011
Location: Somewhere not near you
Posts: 672
Reputation: 30
Default Re: [MySQL] Table structure and foreign keys

I think you misunderstood me. I know how to save to databases and I know MySQL functions, I'm just new to the idea of having multiple tables for the same sort of thing. I think I reworded my question wrong; I was just wanting advice on how to insert the car mod into the child board of vehicle_mods and how to load from it. Plus if this isn't the place to ask questions where do I go? Go post a topic on the scripting help board? Well...Aren't you meant to look around for a similar situation you're in or topic that you're looking for and read it and if you have any questions ask? Because if I'm not allowed to post here then I'll go make another topic on the exact same thing in the Scripting Help board.
__________________
Copyright 2016 vR Studios
Currently working with vR Studios
Copying and pasting doesn't allow you to learn the things that reading and understanding will.
Tass007 is offline   Reply With Quote
Old 22/07/2017, 05:25 PM   #38
Kaperstone
High-roller
 
Kaperstone's Avatar
 
Join Date: May 2011
Location: Home
Posts: 3,119
Reputation: 645
Default Re: [MySQL] Table structure and foreign keys

Quote:
Originally Posted by Tass007 View Post
I think you misunderstood me. I know how to save to databases and I know MySQL functions, I'm just new to the idea of having multiple tables for the same sort of thing. I think I reworded my question wrong; I was just wanting advice on how to insert the car mod into the child board of vehicle_mods and how to load from it. Plus if this isn't the place to ask questions where do I go? Go post a topic on the scripting help board? Well...Aren't you meant to look around for a similar situation you're in or topic that you're looking for and read it and if you have any questions ask? Because if I'm not allowed to post here then I'll go make another topic on the exact same thing in the Scripting Help board.
I referred to the misknowledge in how to add columns.

So for the answer for your question, I don't really understand deeply the code you've sent, but I can extend the explanation on the first post.

Which is basically kind of building a NoSQL, but in SQL


Say you're given an entire row, you have many columns.
You have player data, name, password etc, and then you have a set of columns for the private vehicle, private house and many other things.
Everything in one row.

Now, a few months pass, and you feel like having to increase the number of private things.
Like, 2 private houses, 2 private vehicles, two private weapons on spawn.

So what would you typically do if you already have everything in one table? You'd more columns
But hey, there are already HouseID and HousePosX Y and Z, how do I scale that?
Most people would just add a number to it, to indicate a multi set of things.

So now you're basically building a really long table horizontally. ( <- (horizontal) -> )

So now we approach the first issue, and that's a mess of data, you open your table and see lots of data and it only grows bigger downside and rightside, because you're building horizontally as well with columns.


And here comes the the foreign keys in:
Lets erase all the things we had until now except the user data.

You by now, probably have the player id, name, password and a bit of other tasty data you've collected.

Now, lets imagine we're creating a separate table called `houses`, `vehicles`.
Houses will hold a preset of house data that we created all over GTA and vehicles will need to be created dynamically - as people buy more and more vehicles.

Now, you'd probably create the normal columns for them, and add to the end of each table an id, such as `OwnerId` which will hold the account id of the owner.

And here comes the important stuff, make the ownerid be a foreign key which refrences to the player's account id.
Which means

PHP Code:
CREATE TABLE Vehicles (
    
ID int NOT NULL,
    
VehicleID int NOT NULL,
    
VehiclePlate int NOT NULL,
    
AccountID int,
    
PRIMARY KEY (ID),
    
FOREIGN KEY (AccountIDREFERENCES accounts(ID)
); 
Which basically means:

Create a table with this data:
ID,
VehicleID,
VehiclePlate
AccountID,

Now make a conection between AccountID column here with `table` accounts ( column `ID` )

And now, the PRIMARY KEY is the referenced key.
in `accounts` table, the ID should be a primary key.

Primary key basically is the key which represents these rows, and you can link from any other table to this
In our case, the account ID is the one we link to.

Now, as for the houses, we do the same, but for OwnerID

PHP Code:
CREATE TABLE Houses (
    
ID int NOT NULL,
    
HouseLabel int NOT NULL,
    
HouseCost int NOT NULL,
    
OwnerID int,
    
PRIMARY KEY (ID),
    
FOREIGN KEY (OwnerIDREFERENCES accounts(ID)
); 

Now, what are the advantages of this after all
First of all its not messy.
Second, its scalable, which means you can link as many rows you wish to one player.
So you can now give a player buy 2 houses or 1337 houses, as long as you link the account right with the foreign keys and primary.

(Remember that you have to create a primary key for the "reference to this")

But be aware of the other things I haven't mentioned, but is written in the first post under `Creating relations`


I hope I explained it well enough and correctly
__________________
Find me on GitHub SEF community (Official NEF Successor) Ko ko nut.
Kaperstone is offline   Reply With Quote
Old 22/07/2017, 08:30 PM   #39
Tass007
Gangsta
 
Tass007's Avatar
 
Join Date: May 2011
Location: Somewhere not near you
Posts: 672
Reputation: 30
Default Re: [MySQL] Table structure and foreign keys

Hi, thanks for trying to help me out by putting all that time into the post unfortunately I already know that stuff. I don't think I'm asking the right question in order to get the answer I'm looking for but I'll try again. I have two tables vehicles and vehicle mods they have primary keys and all that so they will update with one another etc... I understand that in order to save data into the vehicle mods table that I need to use insert into, however what I don't know is what I'm meant to be saving as model id and where I would get said modelid from and how would I load multiple tables and adding that modelid onto the car. Hopefully that helps you understand the answer I'm looking for.
__________________
Copyright 2016 vR Studios
Currently working with vR Studios
Copying and pasting doesn't allow you to learn the things that reading and understanding will.
Tass007 is offline   Reply With Quote
Old 23/07/2017, 07:11 AM   #40
nG Inverse
Big Clucker
 
Join Date: Feb 2012
Posts: 57
Reputation: 20
Default Re: [MySQL] Table structure and foreign keys

Nicely put together tutorial with a useful topic. Well done.
nG Inverse is offline   Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
MySQL Table JaKe Elite Scripting Help 5 13/11/2012 08:53 AM
MySQL: Update all values in table from another table kurta999 Scripting Help 8 23/08/2012 09:55 PM
MYSQL Table thefatshizms Scripting Help 3 31/07/2012 03:06 AM
mysql table bug juraska Scripting Help 3 15/12/2011 08:34 AM


All times are GMT. The time now is 02:25 PM.


Powered by vBulletin® Version 3.8.6
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.