PHP & MySQL - MySQLとの連携(テーブルの作成、行の追加、問い合わせ、絞り込み、検索機能)

開発環境

OS X Lion - Apple(OS)
Apache (Web Server)
PHP (サーバーサイドプログラミング言語)
MySQL (データベース)
TextWrangler(Text Editor) (BBEditの無料機能制限版、light版)

『初めてのPHP & MySQL 第2版』(Micbele E. Davis、Jon A. Phillips 著、西沢直木訳、オライリー・ジャパン、2008年、ISBN978-4-87311-365-4)の7章(MySQLとの連携)の問題を解いてみる。

sample49.php(コード(TextWrangler))

<html> <head> <meta content='text/html; charset=UTF-8' http-equiv='Content-Type'/> <title></title> </head> <body> <?php $months = array("January","February","Marxh","April", "May","June","July","August", "September","October","November","December"); if( $months[0] == "January"){ echo "数値インデックスのMySQLとの連携で最初の要素のインデックスは0<br />"; } echo "<hr />"; $months_days; $days = array(31,28,31,30,31,30,31,31,30,31,30,31); for( $i = 0 ; $i < count($months); $i++){ echo "$months[$i]<br />"; $months_days[$months[$i]] = $days[$i]; } var_dump($months); echo "<hr />"; foreach($months_days as $key => $value){ echo "$key => $value<br />"; } var_dump($months_days); ?> </body> </html>

入出力結果(Terminal)

$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.18 MySQL Community Server (GPL) Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sample_store | | store | | test | +--------------------+ 6 rows in set (0.38 sec) mysql> use store; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> create table months( -> mont_id int not null auto_increment, -> name varchar -> ,days int, -> primary key (month_id)); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'days int, primary key (month_id))' at line 4 mysql> create table months( -> month_id int not null auto_increment, -> name varchar -> ,days int, -> primary key (month_id)); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'days int, primary key (month_id))' at line 4 mysql> create table months( -> month_id int not null auto_increment, -> name varchar, -> dyas int, -> primary key (month_id)); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' dyas int, primary key (month_id))' at line 3 mysql> create table months( -> month_id int not null auto_increment, -> name varchar(10), -> days int, -> primary key (month_id)); ERROR 1050 (42S01): Table 'months' already exists mysql> select * from months; +----------+-----------+------+ | month_id | month | days | +----------+-----------+------+ | 1 | January | 31 | | 2 | Febrary | 28 | | 3 | March | 31 | | 4 | April | 30 | | 5 | May | 31 | | 6 | June | 30 | | 7 | July | 31 | | 8 | August | 31 | | 9 | September | 30 | | 10 | October | 31 | | 11 | November | 30 | | 12 | December | 31 | +----------+-----------+------+ 12 rows in set (0.44 sec) mysql> delete months -> ; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 mysql> drop table months; Query OK, 0 rows affected (0.14 sec) mysql> Bye $ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.5.18 MySQL Community Server (GPL) Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use store; Database changed mysql> create table months( -> month_id int not null auto_increment, -> name varchar(10), -> dyas int, -> primary key (month_id)); Query OK, 0 rows affected (0.12 sec) mysql> describe months; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | month_id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | YES | | NULL | | | dyas | int(11) | YES | | NULL | | +----------+-------------+------+-----+---------+----------------+ 3 rows in set (0.48 sec) mysql> insert into months values (,'January',31); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''January',31)' at line 1 mysql> insert into months values (1,'January',31); Query OK, 1 row affected (0.41 sec) mysql> insert into months values (1,'March',31); ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' mysql> insert into months values (,'March',31); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''March',31)' at line 1 mysql> insert into months values ('March',31); ERROR 1136 (21S01): Column count doesn't match value count at row 1 mysql> insert into months values (null,'feburary',28); Query OK, 1 row affected (0.37 sec) mysql> insert into months values (null,'March',31); Query OK, 1 row affected (0.02 sec) mysql> insert into months values (null,'April',30); Query OK, 1 row affected (0.00 sec) mysql> insert into months values (null,'May',31); Query OK, 1 row affected (0.02 sec) mysql> insert into months values (null,'June',30); Query OK, 1 row affected (0.02 sec) mysql> insert into months values (null,'July',31); Query OK, 1 row affected (0.01 sec) mysql> insert into months values (null,'August',31); Query OK, 1 row affected (0.00 sec) mysql> insert into months values (null,'September',30); Query OK, 1 row affected (0.01 sec) mysql> insert into months values (null,'October',31); Query OK, 1 row affected (0.38 sec) mysql> insert into months values (null,'November',30); Query OK, 1 row affected (0.00 sec) mysql> insert into months values (null,'December',31); Query OK, 1 row affected (0.00 sec) mysql> describe months; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | month_id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | YES | | NULL | | | dyas | int(11) | YES | | NULL | | +----------+-------------+------+-----+---------+----------------+ 3 rows in set (0.41 sec) mysql> select * from moanths; ERROR 1146 (42S02): Table 'store.moanths' doesn't exist mysql> select * from months; +----------+-----------+------+ | month_id | name | dyas | +----------+-----------+------+ | 1 | January | 31 | | 2 | feburary | 28 | | 3 | March | 31 | | 4 | April | 30 | | 5 | May | 31 | | 6 | June | 30 | | 7 | July | 31 | | 8 | August | 31 | | 9 | September | 30 | | 10 | October | 31 | | 11 | November | 30 | | 12 | December | 31 | +----------+-----------+------+ 12 rows in set (0.00 sec) mysql> update months set name='January' where month_id=2; Query OK, 1 row affected (0.37 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select month_id,name,days from months; ERROR 1054 (42S22): Unknown column 'days' in 'field list' mysql> alter table months change days dyas; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 mysql> alter table months change days dyas varchar(10); ERROR 1054 (42S22): Unknown column 'days' in 'months' mysql> alter table months change days days varchar(10); ERROR 1054 (42S22): Unknown column 'days' in 'months' mysql> alter table months change dyas days varchar(10); Query OK, 12 rows affected (0.58 sec) Records: 12 Duplicates: 0 Warnings: 0 mysql> select month_id,name,days from months; +----------+-----------+------+ | month_id | name | days | +----------+-----------+------+ | 1 | January | 31 | | 2 | January | 28 | | 3 | March | 31 | | 4 | April | 30 | | 5 | May | 31 | | 6 | June | 30 | | 7 | July | 31 | | 8 | August | 31 | | 9 | September | 30 | | 10 | October | 31 | | 11 | November | 30 | | 12 | December | 31 | +----------+-----------+------+ 12 rows in set (0.00 sec) mysql> select name from months; +-----------+ | name | +-----------+ | January | | January | | March | | April | | May | | June | | July | | August | | September | | October | | November | | December | +-----------+ 12 rows in set (0.00 sec) mysql> select * from months where days = 28; +----------+---------+------+ | month_id | name | days | +----------+---------+------+ | 2 | January | 28 | +----------+---------+------+ 1 row in set (0.00 sec) mysql> from month_id,name,days from months where name='%ber'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from month_id,name,days from months where name='%ber'' at line 1 mysql> select month_id,name,days from months where name='%ber'; Empty set (0.00 sec) mysql> select month_id,name,days from months where name like '%ber'; +----------+-----------+------+ | month_id | name | days | +----------+-----------+------+ | 9 | September | 30 | | 10 | October | 31 | | 11 | November | 30 | | 12 | December | 31 | +----------+-----------+------+ 4 rows in set (0.00 sec) mysql> quit Bye $

エラーを連発しつつも全問終了！

併せて読んでいる書籍。

Kamimura's blog

2012年2月10日金曜日

PHP & MySQL - MySQLとの連携(テーブルの作成、行の追加、問い合わせ、絞り込み、検索機能)

0 コメント:

コメントを投稿