mysql

Mysql

Mysql Rules

Usage

Mysql Basics

Create Database

CREATE DATABASE databasename;

A structure that allows us to identify and access data in relation to another piece of data in the database..

Create Table

CREATE TABLE Persons ( PersonID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) );

The data is organized into tables n rows

unique id for every row n auto increments

Three

Mysql Data types

Integers

int and float

integer = whole number float = decima

String

char, varchar, and text

char = lettestatementonly. varchar = lettestatementand numbers. text = text usually a large block of text.

blob

A field for large amount of text or images

Time

time = hh:mm:ss format. date = CCYY-MM-DD format

time and date

dateTime

YYYY-MM-DD HH:MM:SS

A date and time combination shows time data input.

Timestamp

( YYYYMMDDHHMMSS )

shows current year, month, day, hours, minutes, and seconds data input..

Xammp -- The Server and its contents

Xammp contains a server, php , mysql & PhpMyAdmin

PhpMyAdmin: Powerful Free Mysql Editor

It can show you structure & data in tables. Download avaiable in unix, windows 64 &32 bit format.

Add Primary or Unique id

Simply click on primary key icon or unique id.

Yes a table must have a primary key but you may add unique keys too.

Run Mysql Queries

Simply click on mysql query tab from friendly user interface

Change privileges easily

Create and edit tables and drop databases

Simply click on structure tab when in table view to edit data.

Click operations tab to delete database or entire table.

Add New User & Privileges

Simply click privileges tab from friendly user interface

Here is where you can run traidtion queries such as create database.

import

Simply click import tab from friendly user interface.

pulls in new data: csv, mysql, or txt format.

export

Simply click export tab from friendly user interface

pushes out new data easily in csv, mysql, or txt format.

Mysql Queries (CRUD)

Create(Insert)

INSERT INTO music ( field1, field2,...field3 ) VALUES ( value1, value2,...value3 );

Inserts data into specific columns in your table.

Read(Select)

SELECT column_name(s) FROM music or SELECT * FROM music

selects specific data from specific columns your database. If you want all data use the '*'.

Update

UPDATE music SET field1 = new-value1, field2 = new-value2 [WHERE Clause]

Modifies specific data in specific columns in your database.

Delete

DELETE FROM table_name [WHERE Clause]

Deletes specific data from specific columns in your database.

Mysqli Prepared Statements

Prepared Statements

$stmt = $conn->prepare("INSERT INTO MyGuests
(firstname, lastname, email) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $firstname, $lastname, $email); $firstname = "John"; $lastname = "Doe"; $email = "john@example.com";
$stmt->execute();$stmt->close();

Here we insert data into the students table. The column names match the variable names to insure correct insertion. Data types [S = string, other choices are i = integer or b=blob(large amount of data)]. The question marks represent the data to be inserted.

PDO Prepared Statements

PDO

PDO More versitile than Mysqli. PDO connects to many different db without additional driver downloads.

Mysqli can enter arrays into db using foreach loops. PDO can insert arrays directly into db.

PDO Named parameters

I know kung fu

$qry = "select * from customers where

first_name = :fname and last_name=:lname";

$statement = $db->prepare($qry);

$statement->bindParam(':fname',$firstName);

$statement->bindParam(':lname',$lastName);

$firstName = 'Mikey'; $lastName = 'Ginnelly';

$statement->execute();

$rowsAssoc = $statement->fetch(PDO::FETCH_ASSOC);

print_r($rowsAssoc);

the table name (students) and column names (:fname and :lname) are preceded by a colon(:)
the actual data in the example followed by (=>)
then entered into database using execute.

PDO Positional placeholders

I know kung fu

$qry = "select * from customers

where fname = ? and lname= ?";

$statement = $db->prepare($qry);

$statement->bindParam(1,$fname);

$statement->bindParam(2,$lname);

$fname = 'Mikey'; $lname = 'Ginnelly';

$statement->execute();

$rowsAssoc = $statement-> fetch(PDO::FETCH_ASSOC);

print_r($rowsAssoc);

The table [customers] is selected and all data from columns (fname & lname) is displayed in rows which have 'Mikey' in fname column and 'Ginnelly' in lname column.