Prepared By : Uday Shah (HOD - IT)
E-Mail : rupareleducation@gmail.com
Contact No : 7600044051
Introduction To SQL
Q-1: Explain
MYSQL with php.
Ans: PHP provides
various functions to access mysql
database and to manipulate data records inside mysql database.
The PHP functions
with mysql have the general format..
Mysql_function(value1,value2).
The second part of
the function name is specific to the function, usually a word then describe
what the function does actually has appear , its pronunciation the function
will use like,
mysqil_connect($connect);
mysqil_query($connect,”sql statement”);
Using mysql within
PHP is a simple business. The general uses are as follow.
1.
connect
to database server
2.
Select
the database with which you want to work
3.
Query
table within the selected database
4.
Retrieve
the query result
5.
Disconnect
from the database server
SQL DML
Statement
Data Manipulation Language (DML) statements are used for managing
data in database. DML commands are not auto-committed. It means changes made by
DML command are not permanent to database, it can be rolled back.
Talking
about the Insert command, whenever we post a Tweet on Twitter, the text is
stored in some table, and as we post a new tweet, a new record gets inserted in
that table.
DML is short name of Data
Manipulation Language which deals with data manipulation
and includes most common SQL statements such SELECT, INSERT, UPDATE, DELETE
etc, and it is used to store, modify, retrieve, delete and update data in a
database.
·
SELECT - retrieve data from a database
·
INSERT - insert data into a table
·
UPDATE - updates existing data within a
table
· DELETE -
Delete all records from a database table
What is MySQL?
·
MySQL is a database system used on the web
·
MySQL is a database system that runs on a
server
·
MySQL is ideal for both small and large
applications
·
MySQL is very fast, reliable, and easy to use
·
MySQL uses standard SQL
·
MySQL compiles on a number of platforms
·
MySQL is free to download and use
·
MySQL is developed, distributed, and supported
by Oracle Corporation
·
MySQL is named after co-founder Monty
Widenius's daughter: My
The
data in a MySQL database are stored in tables. A table is a collection of
related data, and it consists of columns and rows.
Databases
are useful for storing information categorically. A company may have a database
with the following tables:
Explain MYSQL FUNCTION
|
mysqli_connect()
|
it opens a connection to a MYSQL server. It returns a
MYSQL link identifier on success or false on failure.
Syntax:
mysql_connect ([string server[user name[password]]])
|
|
mysqli_select_db()
|
it selects the mysql database.
Syntax:
mysql_select_db(string database name[link identifier])
|
|
mysql_query()
|
it sends a query to mysql to the database. For select
,show ,describe
or explain statement mysql_query returns a resource on
success or false on failure.
Syntax:
mysql_query (string query[link identifier])
|
|
Mysql_num_rows()
|
it gets the number of rows in result. It retrieves the
number of rows from a result set. This command is only valid for SELECT
statements. To retrieve the number of rows affected by INSERT, UPDATE or
DELETE query use mysql_affected_rows().
Syntax: mysql_num_rows (resource result)
|
|
Mysql_affected_rows()
|
It gets the number of affected rows in
a previous MYSQL operation.
Syntax: mysql_affected_rows([resource link identifier])
Get the number of affected rows by the last INSERT ,
UPDATE or DELETE query associated with link identifier.
|
|
Mysql_fetch_array()
|
It fetch a result row as an associative array , a
numeric array or both.
Syntax: mysql_fetch_array(resource result[int result
type])
It returns an array that corresponds to the fetched row
and moves the internal data pointer ahead.
|
|
mysql_fetch_row()
|
It gets the result row as an enumerated array.
Syntax: mysql_fetch_row(resource result)
It returns a numerical resource that is being
evaluated. This result comes from a call to mysql_query().
|
|
Mysql_fetch_assoc()
|
It fetches a row of data from a result handle and
returns it as an associative array. It returns associative array containing
one row of query data false when out of data or an error.
Syntax: mysql_fetch_assoc(mysql result result_handle)
|
|
mysql_fetch_object()
|
It fetches a row of data from a result
handle and returns it as an object. It returns object containing one row of
query data or false when out of data or on error.
Syntax: object mysql_fetch_object(mysql result
result_handle, [int result_type])
|
|
mysql_autocommit()
|
Turns on or off auto-committing
database modifications.
Syntax: mysqli_autocommit($con,FALSE);
|
|
mysql_commit()
|
It is a mysql function it is
used to commit all the changes of
the database. this function require only one parameter that
is connection object.
// Commit transaction
mysqli_commit($con);
|
|
mysqli_prepare()
|
It is a SQL Function it is used to prepare sql statement
for executions.
Syntax :
mysqli_prepare(String query);
This function return string object or FALSE if an error
accured.
|
PHP MySQL Connectivity
After a
database and a table have been created, we can start adding data in them.
Here are some syntax rules to
follow:
- The
SQL query must be quoted in PHP
- String
values inside the SQL query must be quoted
- Numeric
values must not be quoted
- The
word NULL must not be quoted
The
INSERT INTO statement is used to add new records to a MySQL table:
INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)
·
SQL stands for Structured Query
Language
·
SQL lets you access and
manipulate databases
·
SQL is an ANSI (American
National Standards Institute) standard
The SQL INSERT INTO Statement
The
INSERT INTO statement is used to insert new records in a table.
INSERT INTO Syntax
It is
possible to write the INSERT INTO statement in two ways.
The first way specifies both
the column names and the values to be inserted:
INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,
...);
If you
are adding values for all the columns of the table, you do not need to specify
the column names in the SQL query. However, make sure the order of the values
is in the same order as the columns in the table. The INSERT INTO syntax would
be as follows:
INSERT INTO table_name
VALUES (value1, value2, value3,
...);
The SQL UPDATE Statement
The
UPDATE statement is used to modify the existing records in a table.
UPDATE Syntax
UPDATE table_name SET column1 = value1, column2 = value2,
...
WHERE condition;
Note: Be
careful when updating records in a table! Notice the WHERE clause in the UPDATE
statement. The WHERE clause specifies which record(s) that should be updated.
If you omit the WHERE clause, all records in the table will be updated!
The SQL DELETE Statement
The DELETE statement is used to
delete existing records in a table.
DELETE Syntax
DELETE FROM table_name WHERE condition;
Note: Be
careful when deleting records in a table! Notice the WHERE clause in the DELETE
statement. The WHERE clause specifies which record(s) that should be deleted.
If you omit the WHERE clause, all records in the table will be deleted!
The SQL SELECT Statement
The SELECT statement is used to
select data from a database.
The data returned is stored in
a result table, called the result-set.
SELECT Syntax
SELECT column1, column2, ...FROM table_name;
Here,
column1, column2, ... are the field names of the table you want to select data
from. If you want to select all the fields available in the table, use the
following syntax:
SELECT * FROM table_name;
General Features of PHP
MyAdmin
What
is PHP MyAdmin?
phpMyAdmin is a free web
application that provides a convenient GUI for working with the MySQL database
management system. It is the most popular MySQL administration tool that is
used by millions of users worldwide and has won numerous awards and honors.
Written
in PHP, it has all common functions that you may need when developing a
MySQL-based application or website. It also gave birth to several similar
products, for example phpPgAdmin which
provides similar functionality for the PostgreSQL DBMS.
General Features of PHP
MyAdmin
With phpMyAdmin you can:
· Create
and remove users, manage user permissions
· Create,
alter and drop databases, tables, fields and rows
· Search
objects in the entire database or any particular tables
· Import
and export data in different formats, including SQL, XML and CSV
· Monitor
processes and track performance of different queries
· Execute
custom SQL queries
· Back
up your MySQL databases in manual mode
The tool is very simple to set up and use. It is usually
installed on the same computer with MySQL database, so all commands are
executed very quickly and don’t rely on network connectivity.
