happy cat image

Start Web Coding

Your First step toward web coding,
Start Web Coding

change UI

Send query statement from PHP to database

What we will learn is sending a database query from php.
Connect to mysql(as root) and create a database [myclass].

creating databaes myclass

CREATE DATABASE myclass;
php image

and Select database

USE myclass;

And put the following table in myclass.
Let's name this table myclass.

id name gender age point maker

mickey

Mickey Mouse

m

100

100

Disney US

minnie

Minnie Mouse

w

100

200

Disney US

duffy

Duffy

m

5

180

Disney JP

lou

Stella lou

w

3

300

Disney JP

cin

cinderella

w

14

500

Disney US

snow

snow white

w

14

460

Disney US

gela

Gelatoni

m

4

400

Disney JP

asimo

asimo

m

30

320

HONDA JP

atlas

atlas

m

5

500

SoftBank JP

aibo

AIBO

m

15

140

SONY JP

next is table creating query statement

CREATE TABLE `myclass_tb`(
    `myclassID` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `id` varchar(30) NOT NULL,
    `name` varchar(30) NOT NULL,
    `sex` char(2) DEFAULT NULL,
    `age` int(11) DEFAULT NULL,
    `point` int(11) DEFAULT NULL,
    `maker` int(11) DEFAULT NULL,
    `address` varchar(50) DEFAULT NULL,
    PRIMARY KEY(`myclassID`)
);

Create a file in the htdocs folder. File name: tb_myclass.php
path
MacOS : /Applications/MAMP/htdocs/
Windows : c(your drive): > MAMP > htdocs/

Then I'll create a table. Create a myclass_tb table in the myclass database by using query().
After completing this source, let's practice creating tables separately.

<?php
    include $_SERVER['DOCUMENT_ROOT'].'/connect.php';

    echo "Creating Tables Using the query() Function<br />";

    $sql = "CREATE TABLE `myclass_tb`(";
    $sql .= "`myclassID` int(10) unsigned NOT NULL AUTO_INCREMENT,";
    $sql .= "`id` varchar(30) NOT NULL,";
    $sql .= "`name` varchar(30) NOT NULL,";
    $sql .= "`sex` char(2) DEFAULT NULL,";
    $sql .= "`age` int(11) DEFAULT NULL,";
    $sql .= "`point` int(11) DEFAULT NULL,";
    $sql .= "`maker` varchar(50) DEFAULT NULL,";
    $sql .= "PRIMARY KEY(`myclassID`)";
    $sql .= ");";

    if($mysqli->query($sql)) {

        echo '<br />table creation complete <br />';

        $sql = "INSERT INTO myclass_tb(id, name, sex, age, point, maker) VALUES";
        $sql .= "('mickey', 'Mickey Mouse', 'm', 100, 100, 'Disney US')";
        $sql .= ",('minnie', 'Minnie Mouse', 'w', 100, 200, 'Disney US')";
        $sql .= ",('duffy', 'Duffy', 'm', 5, 180, 'Disney JP')";
        $sql .= ",('lou', 'Stella lou', 'w', 3, 300, 'Disney JP')";
        $sql .= ",('cin', 'cinderella', 'w', 14, 500, 'Disney US')";
        $sql .= ",('snow', 'snow white', 'w', 14, 460, 'Disney US')";
        $sql .= ",('gela', 'Gelatoni', 'm', 4, 400, 'Disney JP')";
        $sql .= ",('asimo', 'asimo', 'm', 30, 320, 'HONDA JP')";
        $sql .= ",('atlas', 'atlas', 'm', 5, 500, 'SoftBank JP')";
        $sql .= ",('aibo', 'AIBO', 'm', 15, 140, 'SONY JP')";

        if($mysqli->query($sql)) {
            echo "input data success";
        } else {
            echo "input data filed";
        }

    } else {
        echo 'table creation failed';
    }
?>

ATOM

php image

Result

php image

Entering the above allows you to enter tables and records without connecting to MySQL from the console.
Run that source in a web browser and connect to mysql to see if the table exists and all the records have been entered.

php image

Now let's get the number of records and fields.
Use num_rows to count the number of records.

fileName is count.php

<?php
    include $_SERVER['DOCUMENT_ROOT'].'/connect.php';

    $sql = 'SELECT * FROM myClass_tb';
    $res = $mysqli->query($sql);

    echo 'the number of records is '.$res->num_rows;
?>

Atom

php image

Result

php image

or
You can use COUNT(*)

fileName is count2.php

<?php
    include $_SERVER['DOCUMENT_ROOT'].'/connect.php';

    $sql = 'SELECT COUNT(*) AS cnt FROM myClass_tb';
    $res = $mysqli->query($sql);
    $data = $res->fetch_array(MYSQLI_ASSOC);

    echo '<br>the number of records is '.$data['cnt'];
?>

Atom

php image

Result

php image

Now let's count the fields.
To count the number of fields, use field_count.

fileName is fieldcount.php

<?php
    include $_SERVER['DOCUMENT_ROOT'].'/connect.php';

    $sql = 'SELECT * FROM myClass_tb';
    $res = $mysqli->query($sql);

    echo '<br>count is '.$res->field_count;
?>

Atom

php image

Result

php image

강좌로 돌아가기