Hello. I thought i may explain how to work with the Database in 2moons for the newbies. Somehow i may want to give something back after those "dying years of 2moons". May someone will find this useful.
Display All
The next one is Update:
Display All
The next one is delete:
Display All
How to select and create a list of a table in php with mysql?
Display All
How to select only one row of a table:
And the last one is a bit more advanced and is "how to connect 2 tables?"
What can be used to shorten code?
- use a variable at the start of the function
$db = database::get();
In that case you would use $result = $db->select($sql); for example.
- As also you can use the db table to shorten table names
So instead of uni1_vars_requriements as table you write %%VARS_REQUIRE%%.
- Place static content inside the second brackets at insert to avoid more code.
So instead of "(name, surname, country) VALUES (:name, :surname, "England"); So in that case it is always england and you do not define it at: database::get()->insert($sql, [':name' => $name, ':surname' => $surname]);
There are some more methods but those are enough to code in a good standard.
I hope this Tutorial is helping someone who is learning and is helpless. The Code is not tested but it should be fine. May someone wanna add some more options below
Source Code
- So how to perform an Insert in the Database.
- $sql = "INSERT INTO TABLE NAME (Inside here you write the Database column names you want to set, columns with no default values must have a value otherwise an error will be thrown) VALUES (Inside here you only set your param link OR static values for the values. the third value in in the first brackets must be also the third value in those brackets)";
- database::get()->insert($sql, array(Inside here you define the params from the second brackets));
- Working but unuseful example:
- $randValue = mt_rand(1,500);
- $sql = "INSERT INTO uni1_aks (name, target, ankunft) VALUES (:name, :param2, :otherParam)";
- database::get()->insert($sql, array(
- ':name' => 'ACS_'.$randValue,
- ':param2' => $randValue,
- ':otherParam' => TIMESTAMP,
- ));
- // You do not need to insert the id as it's ai (auto increment) and its value will be increased by one by default
The next one is Update:
Source Code
- In this case we are going to update the acs.
- $sql = "UPDATE TABLE SET COLUMN_NAME = :NewParam", COLUMN_NAME = :newParam2 WHERE COLUMN_NAME = :variable";
- database::get()->update($sql, array(
- ':NewParam' => any Variable or static value,
- ':newParam2' => same here
- ));
- Working example:
- $newRandomValue = mt_rand(500,150000); // You can also use only rand()
- $sql = "UPDATE uni1_aks set name = :newName, target = :newTarget, ankunft = :otherAnkufnt WHERE id = 5";
- database::get()->update($sql, array(
- ':newName' => 'ACS_'.$newRandomValue,
- ':newTarget' => 1,
- ':otherAnkunft' => TIMESTAMP + 60*60
- ));
The next one is delete:
Source Code
- In this example we are going to delete an acs entry.
- $sql = "DELETE FROM TABLE WHERE COLUMN_NAME = :yourValue";
- database::get()->delete($sql, [
- ':yourValue' => any Variable or static content.
- ]);
- To delete everything inside the table you do:
- $sql = "DELETE FROM TABLE";
- database::get()->delete($sql);
- Working example to delete everything:
- $sql = "Delete FROM uni1_acs";
- database::delete($sql);
- Working example for only one row:
- $numberFour = 4;
- $sql = "DELETE FROM uni1_aks WHERE id = :value OR id = :static or id = 3";
- database::get()->delete($sql, [
- ':value' => 2,
- ':static' => $numberFour
- ]);
How to select and create a list of a table in php with mysql?
Source Code
- In this example ill show you how to create a list of results from sql.
- first you define an array.
- $array_name = array();
- $sql = "SELECT * FROM uni1_aks WHERE id > 5";
- $query_result = database::get()->select($sql);
- foreach($query_result as $result){
- $array_name[$result['id']] = array(
- 'id' => $result['id'],
- 'ankunft' => $result['ankunft],
- );
- //If you do not want to use specific columns and you need all. you do not need to use a foreach loop. In case you do used specific columns you can submit the values to smarty (2moons template engine) trough that.
- $this->assign(array(
- 'template_variable' => $array_name,
- ));
- }
- In the template you can use {foreach} as also, an example can be found at smarty api (just google smarty template php)
How to select only one row of a table:
And the last one is a bit more advanced and is "how to connect 2 tables?"
Source Code
- In this example we connect the acs and planets table to get some values out of the acs table
- $sql = "SELECT acsData.*, p.username, u.ally_id FROM uni1_aks acsData LEFT JOIN %%PLANETS%% p ON acsData.target = p.id WHERE acsData.id = :randomId";
- $result = database::get()->selectSingle($sql, [
- ':randomId' => mt_rand(1,12),
- ]);
What can be used to shorten code?
- use a variable at the start of the function
$db = database::get();
In that case you would use $result = $db->select($sql); for example.
- As also you can use the db table to shorten table names
So instead of uni1_vars_requriements as table you write %%VARS_REQUIRE%%.
- Place static content inside the second brackets at insert to avoid more code.
So instead of "(name, surname, country) VALUES (:name, :surname, "England"); So in that case it is always england and you do not define it at: database::get()->insert($sql, [':name' => $name, ':surname' => $surname]);
There are some more methods but those are enough to code in a good standard.
I hope this Tutorial is helping someone who is learning and is helpless. The Code is not tested but it should be fine. May someone wanna add some more options below