PHP: mysqli の使い方

実行環境

MariaDB に次のデータベースを作成します。

ユーザー　scott
パスワード　tiger123
データベース city

作成する SQL コマンド

create user 'scott'@'localhost' identified by 'tiger123';
create schema city;
grant all on city.* to 'scott'@'localhost';

インターアクティブにデータベースを作成

$ sudo mysql -uroot
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 50
Server version: 10.11.2-MariaDB Arch Linux

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create user 'scott'@'localhost' identified by 'tiger123';
Query OK, 0 rows affected (0.044 sec)

MariaDB [(none)]> create schema city;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> grant all on city.* to 'scott'@'localhost';
Query OK, 0 rows affected (0.004 sec)

MariaDB [(none)]> exit
Bye

データの作成

mysqli_create.php

#! /usr/bin/php
<?php
//
//	mysqli_create.php
//
//					Mar/11/2023
//
// ----------------------------------------------------------------
function dict_append_proc ($dict_aa,$id,$name,$population,$date_mod)
{
	$dict_unit = array ();
	$dict_unit['name'] = $name;
	$dict_unit['population'] = $population;
	$dict_unit['date_mod'] = $date_mod;
	$dict_aa[(string)$id]= $dict_unit;

	return	$dict_aa;
}

// ----------------------------------------------------------------
function data_prepare_proc ()
{
$dict_aa = array ();

$dict_aa = dict_append_proc ($dict_aa,'t3321','岡山',725139,'2002-3-9');
$dict_aa = dict_append_proc ($dict_aa,'t3322','倉敷',417628,'2002-5-12');
$dict_aa = dict_append_proc ($dict_aa,'t3323','津山',891654,'2002-7-21');
$dict_aa = dict_append_proc ($dict_aa,'t3324','玉野',265981,'2002-11-12');
$dict_aa = dict_append_proc ($dict_aa,'t3325','笠岡',284597,'2002-4-24');
$dict_aa = dict_append_proc ($dict_aa,'t3326','井原',671942,'2002-2-8');
$dict_aa = dict_append_proc ($dict_aa,'t3327','総社',265481,'2002-8-3');
$dict_aa = dict_append_proc ($dict_aa,'t3328','高梁',792356,'2002-3-14');
$dict_aa = dict_append_proc ($dict_aa,'t3329','新見',315892,'2002-1-17');

	return	$dict_aa;
}

// ----------------------------------------------------------------
function insert_proc ($db_link,$id_in,$name_in,$population_in,$date_mod_in)
{
	$stmt=mysqli_prepare( $db_link, "insert into cities (id, Name, Population, date_mod) "
		. "values ('" . $id_in . "','" . $name_in . "',"
		. $population_in .  ",'" . $date_mod_in . "')");

	mysqli_stmt_execute($stmt);
}
// ----------------------------------------------------------------
function dict_to_db_proc ($dict_aa,$db_link)
{
	foreach ($dict_aa as $key => $value)
		{
		$name = $value['name'];
		$population = $value['population'];
		$date_mod = $value['date_mod'];
		insert_proc ($db_link,$key,$name,$population,$date_mod);
		}
}

// ----------------------------------------------------------------
fputs (STDERR,"*** 開始 ***\n");

$dict_aa = data_prepare_proc ();

$host = 'localhost';
$user = 'scott';
$password = 'tiger123';
$db = 'city';

$db_link = mysqli_connect($host, $user, $password, $db);

try
	{
	$stmt = mysqli_prepare($db_link, "drop table cities");
	$stmt->execute();

	$stmt = mysqli_prepare( $db_link, "create TABLE cities (id varchar(10) NOT NULL PRIMARY KEY,name text, population int,date_mod text)");
	$stmt->execute();

	dict_to_db_proc ($dict_aa,$db_link);
	}
catch  (mysqli_sql_exception $e)
	{
	fputs (STDERR,"*** error *** mysqli_sql_exception ***\n");
	print('Error:'.$e->getMessage() . "\n");
	fputs (STDERR,"*** error ***\n");
	}

mysqli_close($db_link);

fputs (STDERR,"*** 終了 ***\n");
// ----------------------------------------------------------------
?>

実行結果

$ ./mysqli_create.php 
*** 開始 ***
*** 終了 ***

データを読む

mysqli_read.php

#! /usr/bin/php
<?php
//
//	mysqli_read.php
//
//					Mar/11/2023
//
// --------------------------------------------------------------------
fputs (STDERR,"*** 開始 ***\n");

$host = 'localhost';
$user = 'scott';
$password = 'tiger123';
$db = 'city';

$db_link = mysqli_connect($host, $user, $password);

mysqli_select_db( $db_link,$db);

$data = mysqli_query($db_link, 'SELECT * FROM cities');

foreach ($data as $row)
	{
	print $row['id'] . "\t";
	print $row['name'] . "\t";
	print $row['population'] . "\t";
	print $row['date_mod'] . "\n";
	}

mysqli_close($db_link);

fputs (STDERR,"*** 終了 ***\n");
// --------------------------------------------------------------------
?>

実行結果

$ ./mysqli_read.php 
*** 開始 ***
t3321	岡山	725139	2002-3-9
t3322	倉敷	417628	2002-5-12
t3323	津山	891654	2002-7-21
t3324	玉野	265981	2002-11-12
t3325	笠岡	284597	2002-4-24
t3326	井原	671942	2002-2-8
t3327	総社	265481	2002-8-3
t3328	高梁	792356	2002-3-14
t3329	新見	315892	2002-1-17
*** 終了 ***

確認したバージョン

$ php -v
PHP 8.2.3 (cli) (built: Feb 15 2023 16:48:38) (NTS)
Copyright (c) The PHP Group
Zend Engine v4.2.3, Copyright (c) Zend Technologies