Working MariaDb or Mysql databse
<?php
PDBC and WebApi`s
````````````````````````
Generally if you want to store data like username,email,password we make variable
but if there are 100`s of record we cannot do anything with variable then we required array.
But this data storage Mechanism only remains in stack memory.
or
Even if it is stored in Heap data is not permanent what i mean is.
Till the php Virtual Machine is on and script is Executing the data remains and As soon script is terminates PHP vertual MAchine comes to Halt(Pause or Terminate State) and data at Primary Memory flush Out that means data is destroyed.
Note: PHP vertual Machine means PHP Interpreter.
In Order to make data permanent we must use permanent storage machenism.
1. File System Mechanism: Data written into a file is called File Based Storage
Most Common Feature of any framework is to store the exeption and data in Logs file.
In Apache or Xampp we have error.log
In order to store the in log files we need to use File Handling
WAP to make a logger
Let us make a function write_log($msg);
Advantage:
``````````````````
To Log those Request and Output which are implicitly called or hit.
For example:
OTP Email Services,SMS Services.
Do our System or Apache Use Logging
Yes :
error.log : gemerated by Apache
php.log
#Which Logging Technique is used by Server for Authentication
SESSION Logging : Logging a session data into a file.
2. Database Mechanism:
Data written to a tables in rows and columns
Amp stack provides Concept of PDBC (PHP Database Collection)
php can be connected with Any database
Oracle
sybase
db2
informix
monogodb
mariadb
....
When To Use:
when data is In Lacs and Crores
php is confertable with mysql only
but it can match with any databse
It is Apple to apple comparision
for ex..
Romio Julette
Java Oracle
php mysql(past)
php MariaDB
Connection with php to any Database is called PDBC.
Note:
PHP This! - A Beginners Guide to Learning Objected PHP
3. DataWare Houses
4. BigData
5. Data Structures
Working MariaDb or Mysql databse
`````````````````````````
Here Our database is Mysql but owned MariaDB
There are two types of database
1. Logical Database : Oracle is a Logical Database
2. Physical Database : Mysql or Maria db is Physical Database. (Write the data in form files)
What is the Proof
```````````````````
goto c:/xampp/mysql/data
|
| here you will get all the data of the database
Database Logging : Writing the database related stuff into file system
Database Name: Foldername
tablename : tbl_tablename.ibd or *.ibd
columns_schema : *.frm
How many Tables you can store or how many .ibd files can be made
4 Billions approx
How many *.frm files or columns can be stored in a table 4096 Max
How Many ways you can use mysql database
1. Using Heidi SQL(Remote Access)
2. Using MYSQL GUI TOOL (Mysql Workbench)
3. Using Mysql Terminal(console) or cmd
4. using WebBrower (PHPMYADMIN)
Working with mysql console
```````````````````````````
mysql> Console or Query
mariaDB> Commands or Query
1. Connect to mysql>
goto the Root Loaction
Open cmd
cd c:/xampp/mysql/bin/
Command to connect
Every database has a super user
mysql super user:root
You need to connect Root
syntax:
mysql -u <user-name> -p <password>
How to connect by hidden mode
mysql -u <user-name>
Enter Password: ****
By default Password is Blank
Hit Enter
mysql -u <user-name>
Enter Password: <hit Enter>
Never ever set the Mysql path to ENV VARIABLES
it can be access to by Any Third party service
Write a Kernal Program to Execute the Mysql from temperory Environment
Kernal Programming can be done by using CMD
@echo off
set /p name=Enter the Username:
set path=c:/xampp/mysql/bin/
mysql.exe -u %name% -p
pause
write the code to any file
with extension mysql.cmd file..Type all files
How to show all the database
`````````````````````
MariaDB [(none)] show databases;
|
Current database name
Mysql Comments
--comment line--
Create the Database
MariaDB [(none)] create database <db-name>;
|
Create command is used to create database Object
database Objects
1. sequence
2. trigger
3. cursor
4. view
5. database
6. table
7. user
8. privaleges
9. schemas
10. rights
11. permission
12. temperory Table
....etc
How to use database
`````
MariaDB [(none)]> --this is a comment--
MariaDB [(none)]> use logs_db;
Database changed
MariaDB [logs_db]>
Since Database is collection of tables related Entities
for example
database can contain views (virtual Table),schemas,(Tables)
There is table inside a database what is its name
dual table = it is a dummy table which has only record no schema(structure) this table generated at runtime for calculation part
#Describe <tablename>
You will get the schema.
desc dual; #ERROR because dual not have structure.
dual has one row and one column
performing calculation using dual
MariaDB [logs_db]> select 100+100+100 as total_salary from dual;
----------------
| total_salery |
----------------
| 300 |
---------------
MariaDB [logs_db]>
#show all the tables
Syntax: show tables;
if no table is there... Empty set
Data Structure of tables
Array : datatype(ENUM SET)
variables
DataTypes
hash
Btree+
stack
Queue
Index
set = Rows or Records
tuple = this data structure when you insert multiple records in a table.
Creating the Tables
`````````````````````
Syntax:
CREATE table <table-name>(
-><field_name> <datatype>(size) <constrant1> <constrant2> <const...>,
<field_name> <datatype>(size) <constrant1> <constrant2> <const...>,
<field_name> <datatype>(size) <constrant1> <constrant2> <const...>,
<field_name> <datatype>(size) <constrant1> <constrant2> <const...>,
<field_name> <datatype>(size) <constrant1> <constrant2> <const...>
);
CREATE table tbl_logs(
id int(11) not null primary key auto_increment,
name varchar(255) not null,
action varchar(255) not null
);
How to see table schema
desc <tablename>
describe <tablename>
#Query Created Table
syntax:
show create table <tablename>
#Inserting the Records:
insert in specific columns
insert in specific columns
insert into <tablename>(
Fieldname-1,
Fieldname-2,
Fieldname-3,
.....
) values(
'value1',
'value2',
'value3',
);
into:: into a
All these function are used when you want to get the data
1. mysqli_num_rows()
2. mysqli_fetch_array()
3. mysqli_fetch_assoc()
4. mysqli_fetch_row()
5. mysqli_fetch_field()
All these function will used during write operation
writable operation
update or delete
6. mysqli_affected_rows() ::no of affected rows in case delete and update
-1 0 1 min one Affected => True
-1 0 (n) max n affected => True
true
|----> when Query is failed
$check_count = mysqli_affected_rows($con);
if($check_count>0){
}
insert
7. mysqli_insert_id($con)
gives Id of last inserted record
select max(id) from any_table
Postmartem of result set functions
*******************************
1. mysqli_num_rows()
2. mysqli_fetch_array()
3. mysqli_fetch_assoc()
4. mysqli_fetch_row()
5. mysqli_fetch_field()
This result set is also called resource and denoted by #n
where n=1,2,3,4,5,6,7,8.......
$con :: is reponsible for profiling
SQL Query
Explain <Any-Query>
In order to check the unique whether a record exist or not
we use $count > 0 record exist
Select Query::
$count=mysqli_num_rows($result_set);
/*
write to the database
following all the step to perform Crud Opearation using PHP Database Connection
Step-1:
create the database connection
mysqli_connect($host,$user,$pass,$dbname);
Return $connection_str or $Connection Object
$con = mysqli_connect($host,$user,$pass,$dbname);
$con = mysqli_pconnect($host,$user,$pass,$dbname);
|
persistant Connection
(Permanent Connection)
#dbconnect.php
since connection is made only once
$host = '127.0.0.1'; localhost
$user = 'root';
$pass = '';
$dbname = '';no Error
$dbname = 'dbname'; #database Name is correct then ok if
#wrong Exception will be raised
note:: i here means Improved version
Steps2: Check if connection is made or not
method 1:
try{
$con = mysqli_connect($host,$user,$pass,$dbname);
echo 'connection Created';
}catch(Exception $e){
if(!$con){
die("Connection Error".mysqli_error($con));
}
}
Method2:
if($con){
echo 'connection Created';
}else{
echo "Connection Error".mysqli_error($con);
exit;
}
method 3:
$con = mysqli_connect($host,$user,$pass,$dbname) or die('connection Error');
Step3:
prepare the Query and Fire the Query
Step4: justify the type of Query
if it is writable or readable
if it is writable or readable
if query is readable return type will be Result Set select = Result set
If query is writable
return type no of affected Rows
minimum Affected rows 1
1 => true
if($check==true){
echo 'Insert Ok'; #insert
echo 'Update Ok'; #update
echo 'Deleted Ok'; #delete
}else{
echo 'Insert|Delete|Update Error';
}
#Preparing the Query
$sql = "INSERT OR UPDATE OR SELECT OR DELETE QUERY";
#Fire the Query
1. writable
insert,delete,update,alter,create,drop,truncate....
mysqli_query($con,$sql)
| |--->sql
|------>connection object
Syntax:
$result = mysqli_query($con,$sql);
return type: true|false
2.Readable
select,joins,projection,selection,transaction
mysqli_query($con,$sql)
|--->sql
|----->connection object
return type : Result Set :: Multi-Dimension Array of N
Records
Note::
$sql = "Query 1;";
$sql = "Query 1;";
$sql = "Query 1;";
$sql = "Query 1;";
mysqli_query($con,$sql);
*/
database connection
0 Comments