MySQL Clients: Creating DB and table

This entry is part 8 of 8 in the series MySQL Clients

Using an earlier example, let’s go ahead and create a database and a table.

We will use the same code illustrated in the example.

CREATE TABLE artist
    (
 id int auto_increment primary key, 
 artiste varchar(30), 
 album varchar(50),
 price decimal(3, 2) 
 );
  
INSERT INTO artist
(artiste, album, price)
VALUES
('Rod Stewart', 'Maggie May', 8.90 ),
('New Order', 'Substance', 7.50),
('Nirvana', 'Smell Like Teen Spirit', 9.80);

First create the database using

create database allartist

console_create

Now we create a table and insert a row into it.

We need to use use allartist to ensure that the table is created under the table allartist.

console_createtable

console_insert

We can use select * from artist to check if the table has the inserted data.

PHP Form & MySQL: Enter data through form

This entry is part 2 of 3 in the series PHP Form & MySQL

IDmovieactoryear
1Independence DayWill Smith1996

We will build a form and enter the above record to the table.

We will need to go through the usual steps before executing the insert query string.

The steps are:

  • Set up the db connection
  • Create the database
  • Use the database
  • Create the table

And finally, we execute the query string in line 46.

<html>
<body>

<form action='' method='post'>
    Movie: <input type='text' name='title'><br>
    Actor: <input type='text' name='actor'><br>
    Release Date yyyy-mm-dd: <input type='text' name='release_date'><br>
    <input type='submit'>
</form>

</body>
</html>
<html>
<body>

<?php
$title = $_POST['title'];
$actor = $_POST['actor'];
$release_date = $_POST['release_date'];

echo "Title: " . $title . "<br>";
echo "Actor: " . $actor . "<br>";
echo "Release Date: " . $release_date . "<br>";


$mysqli = new mysqli("localhost", "root", "");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL";
}

$sql_db = "create database if not exists allmovie";
mysqli_query($mysqli, $sql_db);

$sql_use_db = "use allmovie";
mysqli_query($mysqli, $sql_use_db);

$sql_tb = "create table if not exists movie (id int auto_increment primary key, title varchar(30), actor varchar(50), rel_date datetime)";
mysqli_query($mysqli, $sql_tb);

$release_date = DateTime::createFromFormat('Y-m-d', $release_date);


if ($release_date) {
    $release_date = $release_date->format('Y-m-d');
    $sql_in = "insert into movie (title, actor, rel_date) values ('$title', '$actor', '$release_date')";
    mysqli_query($mysqli, $sql_in);
}

mysqli_close($mysqli);

?>

</body>
</html>

Using what we have learnt about php form and db insert, the above script could be developed quite easily.

The form generated by the above script is as shown below:

mysql_form1

After running the above script, and typing in the necessary inputs we can check the database using Toad.

form_insert

PHP MySQL: Making a connection to MySQL OOP Style

This entry is part 2 of 17 in the series PHP & MySQL

First, start the WAMP server so that Apache and MySQL servers are started.

If you have removed the allartist database in MySQL, you can create it again using phpMyAdmin.

phpmyadmin_createdb

<?php
$mysqli = new mysqli("localhost", "root", "", "allartist");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL";
}
echo $mysqli->host_info . "<br>";

$mysqli = new mysqli("127.0.0.1", "root", "", "allartist", 3306);
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL";
}

echo $mysqli->host_info . "<br>";
?>

Since we have installed WAMP on the local PC, we can use localhost or 127.0.0.1.  Notice we have passed the connection to a variable $mysqli.

With this variable $mysqli, we can print out the host info using $mysqli->host_info.

When we run the above script, the output is as below.  It is basically how the script accesses the MySQL database allartist through TCP/IP protocol.

localhost via TCP/IP
127.0.0.1 via TCP/IP

phpmyadmin_createdb2

Object Oriented Style (OOP)

In OOP style, we have used new to create an object.  In OOP, a variable is treated as an object and so we can assign the object to $mysql.

In PHP, the -> is a referencing method. Other languages use a dot for this operator.

PHP couldn’t as PHP is using dot for string concat, hence the -> instead.

PHP MySQL: Introduction

This entry is part 1 of 17 in the series PHP & MySQL

Now that we have learnt some basic PHP and gone through CRUD functions in SQL, we will now move to learning how to retrieve and store data into database using PHP.

It’s been said that PHP works very well with MySQL.

That is mainly because of the many functions available in PHP for manupulating MySQL database.

A learning of PHP & MySQL is a learning of those PHP functions.

In PHP 5.3, a new mysqli class was introduced allowing persistent connection support.  This is to reduce overhead created by multiple connections.

We will use this new mysqli class in this tutorial.

SQL: Database and tables

This entry is part 2 of 20 in the series SQL Tutorial

In a database, the main thing that we need to know is that database can contain many tables.

For learning purposes, the examples that we are going to use will contain only one or two tables.

In a table, there are rows and columns just like an Excel sheet.

Below is a screenshot of using SQL Advantage to access a Sybase database.

sql_advantage

Sybase and Oracle are two big corporations making database related products.  MySQL database is also an Oracle product.

MySQL is open source and it powers many web sites and blogs including this codecrawl.com.