🔖 [PHP] MySQL for beginners + MySQLi with PHP

2018 - 04 - 08
🔖 [PHP] MySQL for beginners + MySQLi with PHP
1. [MySQL with PHP] MySQL is kind of RDBMS (relational database management system) where data or tables can be related by using primary keys or foreign keys. To install MySQL on MacOSX:
  • Go to https://dev.mysql.com/downloads
  • Download MySQL Community Server
  • Run the installer, copy the password before closing it
  • Open Settings, start MySql Server
  • Set Mysql command to bash file, open terminal,
    $touch .bash_profile
    And add this line to the bash_profile
    export PATH=${PATH}:/usr/local/mysql/bin/
  • Open a new terminal, and access the mysql server,
    $mysql -u root -p
    Enter password: <that password from installer>
  • Update password by typing on the same terminal
    mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '<your password>'
  • or alternatively:
    mysql>set password = password('<your password>')

2. [To use a database on terminal] Common commands for database administration on terminal:
  • show databses;
  • use <database name>
  • show tables;

3. [Manipulating database] To create/drop a database:
CREATE/DROP <database_name>
Enter password:

4. [Common SQL commands] Assume we have a database named "mydata", we are going to create and manipulate a table "mytable" in there. Create a table
CREATE TABLE mytable (
ID int(11) NOT NULL AUTO_INCREMENT,
username varchar(20) NOT NULL,
password varchar(20) NOT NULL,
PRIMARY KEY (ID)
);
*p.s. The max number of int is 11 digits only. More digits can be accepted by using bigint. Insert a new row
INSERT INTO mytable (ID, username, password)
VALUES('', 'john', '123');
Delete a row
DELETE FROM mytable
WHERE ID=1;
Update certain value of a row
UPDATE mytable
SET username='smith'
WHERE ID=1;
Change a column datatype or extra attributes *Let say we want to increase the characters for username from 20 to 100*
ALTER TABLE mytable
MODIFY username varchar(100) NOT NULL;
Add or Drop a column
--to DROP a column--
ALTER TABLE mytable
DROP COLUMN username;

--to ADD a column--
ALTER TABLE mytable
ADD COLUMN username varchar(100) NOT NULL;

5. [using PHP with MySQL] There are two way to connect PHP and MySQL:
  • procedural
  • Object oriented
We are focusing on object-oriented only. Connect to server
$conn = new mysqli('host', 'username', 'password', 'database_name');
if($conn->connect_error){
	echo $conn->connect_errno.": ".$conn->connect_error;
};
Requesting a query For instance, there is a row like this in mytable:
ID=1, username='smith', password='123'
$query = "SELECT * FROM mytable WHERE ID=1";
$result = $conn->query($query);
if($result){
	while($row = $result->fetch_assoc()){
		echo $row['ID']." ".$row['username'];
	};
};

Comments

There is no comment yet

New Comment

Please Login to comment