Manipulating MySQL database using PHP

[blueprats]

MySQL Connection:
Here is how to connect to your MYSQL database using PHP. Be careful in filling out the connection variables. The slightest mistake and the connection will be refused. The login details are given by the host: server, password, database name and user.

Here we will cover the following three points
- MySQL : Connection to the server database
- MySQL : Select the database
- MySQL : Connection and transaction in the database

Mysql - connection to the server database: mysql_connect ()
This function takes 3 input arguments: the host name, user name, password.
The function returns a link that must stored in a variable for use later.
Here is the code for it

<? php
$ conn = mysql_connect("localhost","root","password");
if (! $ conn )
die ("conn impossible");
?>

To close the connection, use mysql_close (). This function takes as argument the variable connection to close.
Here is the code for it

<? php
mysql_close($ connection);
?>

Mysql - Select the database: mysql_select_db ()
This function takes as argument name database and a variable returned by mysql_connect () in our example $ conn. If this variable is not supplied, then the user uses the latest according to established connection.
Here is the code for it

<? php
$ Mydatabase="Client";
mysql_select_db($ Mydatabase) Or die ("no connection");
?>

Mysql - Connection and transaction in the database: mysql_query ()
This function takes as argument a string containing an SQL statement and a variable returned by mysq_connect (). If the latter is absent, then the variable takes the value returned by the last connection.
Here is the code for it

$ Mydatabase="dbname";
 
$ conn = mysql_connect("localhost","root","password");
/ / Test conn
if (! $ conn )
die ("conn impossible");
  / / Connect the base
  mysql_select_db($ Mydatabase) Or die ("no conn");

  $ query="SELECT * from customer"; / / Request
  mysql_query($ query,$ conn); / / Send the query
 
?>

[blueprats]

MySQL - Create, Alter and Drop
Here we will see to create a table, modify and delete fields. All these commands can be run online. I've set an example with the CREATE command to show you one of many possible torque PHP-MySQL.
We will cover the following topics here
- MySQL : Create Table
- MySQL : Different types of data
- MySQL : Modifying a table
- MySQL : Deleting a table

MySQL - Create Table: CREATE TABLE
To create a table, use the command CREATE
Syntax of the query is as follows

CREATE table tablename (
att1 type [NOT NULL] [AUTO_INCREMENT]
att2 type [not null]
.
.
attrNameN type [not null]
.
primary key (att1 ,..., attrNameN)
;

Two examples are shown below, the first is from your admin and the second is from a PHP file directly hosts on the server.

<? php

/ / Example 1
/ / Create a table

CREATE TABLE Member (
  no tinyint(4) unsigned NOT NULL auto_increment,
  em varchar(80) NOT NULL,
  pass varchar(32),
  PRIMARY KEY (no)
); 
?>

And this is the second example

$ ln = mysql_connect('localhost', 'root', 'pass');
if (!$ ln) {
die ('Could not connect:' . mysql_error());
}

/ / Make the database foo the current basis
$ db_sel = mysql_select_db('dbname', $ ln);
if (!$ db_sel) {
die ('Unable to select database:' . mysql_error());
}

  $ s = "CREATE TABLE IF NOT EXISTS VOTRETABLE (
no tinyint (4) unsigned NOT NULL auto_increment,
em varchar (80) NOT NULL,
pass varchar (32)
PRIMARY KEY (no)
    )";

mysql_query($ s,$ ln);
mysql_close();

?>

Mysql - The different types of data

- smallint: 16-bit integers
- int: 32-bit integers
- decimal (n, m): M with n digits after the decimal point
- float
- char (n): String of fixed length equal to n
- varchar (n): String of at most n characters (n <= 256)
- longvarchar (n): Idem with (n <= 32768)

[NOT NULL] implies that the field can not be zero.
[AUTO_INCREMENT] the whole will be increased by 1 at each insertion.

primary key (att1 ,..., attrNameN)
att1 attrNameN ,..., denotes the name of the Body attribute key.

foreign key (att1 ,..., attrNameN) references (tablename);
att1, attrNameN ,..., denotes the name of the Body attribute foreign key. The keyword Reference to specify the table name that contains these attributes. If the table has multiple foreign keys, you must repeat the previous steps.

Some additional ideas on foreign keys to maintain consistency in the database, you can add conditions when you declare a foreign key.
Examples
on delete cascade
on delete set null
ON UPDATE CASCADE

These options allow you to maintain consistency because when you delete a referenced key (or if you modify this key) on the repercussions spread through these options.
return table1: foreign key (name1) References Table2 on delete set null. If you delete a row in the table1, the lines that reference in table2 will last no.
Declaration of table1: foreign key (name1) References Table2 ON UPDATE CASCADE. If you change the key of a row of table1 then the changes will be reflected in Table2.
Here is an example of this

 <? php
create table Customer (
  no tinyint(3) unsigned not null AUTO_INCREMENT,
  nm varchar(10) not null default '',
  frnm varchar(10) not null default '',
  add varchar(50) not null default '',
  primary key (no)
  );
  ?>

Mysql - Changing a table with: ALTER
Here is the syntax

ALTER TABLE TableName RENAME AS NewName

The condition may be several forms.
Here are some examples
add (attribute type [null / NotNull]) : Adds a field named "attribute" type "type"
or
DROP attribute1, attribute2 ... attributN : You can remove one or more attributes
or
MODIFY (attribute type [null / not null]) : Changes the type of attribute

Mysql - Deleting a table: DROP
To delete a table using the statement DROP
Here is the syntax

DROP table tablename [restrict / cascade];

The optional clauses
restrict : (By default clause, it checks that the destruction of the table is not involved in the coherence of the table.
cascade : Destroyed the affected table and all its dependencies.

Reminder:
Attribute
Is the title of the column in a table.

Definition of key
Is the key attribute (or attribute group) which is the smallest possible, and which designates and defined uniquely, one element of the relationship.
The keys are conventionally said.

Definition of foreign key
Matches a set of attributes of a relation which denotes the primary key of another relation. Foreign keys are by convention a '#' before their names. A SQL statement always ends with a semicolon.