What we will learn is sending a database query from php.
Connect to mysql(as root) and create a database [myclass].
CREATE DATABASE myclass;
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
Result
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.
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
Result
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
Result
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
Result
