MySQLを操作するためのPHPの共通コードスニペットを整理してまとめてみた

<?php

$con = mysql_connect("[database_address]","[database_username]","[database_password]");//create MySQL connection

mysql_select_db("[database name]", $con);//select the MySQL database

$sql = "CREATE TABLE abc

(

id int NOT NULL AUTO_INCREMENT,

PRIMARY KEY(id),

openid varchar(32),

nickname varchar(32),

sex varchar(8)

)";//create a data table with the name abc, id can not be empty and automatically incremented and set as the primary key

mysql_query($sql,$con);//execute a MySQL statement

mysql_close($con);//close the mysql connection

? >

<?php

$datatime = date("Y-m-d H:i:s",time());//get the time

$con = mysql_connect("[database address]","[database username]","[database password]");//create MySQL connection

mysql_select_db("[database name]", $con);//select the MySQL database

mysql_query("SET NAMES 'UTF8'");//set the encoding (to solve the problem of inserting Chinese garbled code)

mysql_query("INSERT INTO [data table name] (openid, add_time, nickname)

VALUES ('123', '$datatime', 'abc')");//insert new record

mysql_close($con);//close mysql connection

? >

<?php

$con = mysql_connect("[database_address]","number[database_username]","[database_password]");//create MySQL connection

mysql_select_db("[database name]", $con);//select MySQL database

$result = mysql_query("SELECT * FROM [data table name]");//get all the data of the data table

while($row = mysql_fetch_array($result)){//Get a row from the result set as an associative array, return false if there are no more rows

    echo $row['openid']. "
";//output the value of all openid fields in the table

}

mysql_close($con);//close mysql connection

? >

<?php

$con = mysql_connect("[database_address]","[database_username]","[database_password]");//create MySQL connection

mysql_select_db("[database name]", $con);//select MySQL database

$result = mysql_query("SELECT * FROM [data table name] WHERE openid='123'");//get the data table with openid=123 data rows

while($row = mysql_fetch_array($result)){//get a row from the result set as an associative array, how no more rows then return false

    echo $row['nickname']. "
";//output the value of all openid fields in the table

}

mysql_close($con);//close mysql connection

? >

<?php

$con = mysql_connect("[database_address]","[database_username]","[database_password]");//create MySQL connection

mysql_select_db("[database name]", $con);//select the MySQL database

mysql_query("UPDATE [data table name] SET nickname='new' WHERE openid='123'");//Update the nickname field in the id=123 record row

mysql_close($con);//close mysql connection

? >

<?php

$con = mysql_connect("[database_address]","[database_username]","[database_password]");//create MySQL connection

mysql_select_db("Database name", $con);//select MySQL database

mysql_query("DELETE FROM data table name WHERE openid='123'");//delete the row with openid=123

mysql_close($con);//close mysql connection

? >

<?php

$con = mysql_connect("[database_address]","[database_username]","[database_password]");//create MySQL connection

mysql_select_db("Database name", $con);//select MySQL database

$sql = "DROP TABLE abc";//delete the data table named abc

mysql_query($sql,$con);//execute a MySQL statement

mysql_close($con);//close the mysql connection

? >

<?php

$host = "[database address]";

$username = "[database username]";

$password = "[database password]";

$dbname = "[database name]";

//The code to be executed is put into the try block, if an exception occurs during the execution of these codes, the program jumps directly to the catch block, and $e collects the error information and displays it.

try {

    $conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);//create connection

    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //Set PDO error mode for throwing exceptions

    $sql = "CREATE TABLE abc (

    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    openid varchar(32) NOT NULL,

    nickname varchar(32) NOT NULL,

    sex varchar(8) NOT NULL

    )";//create a data table with the name abc, id can not be empty and automatically incremented and set as the primary key

    $conn->exec($sql);//use exec() no results returned

}

catch(PDOException $e){

    echo $sql . "
" . $e->getMessage();//Display the exception message

}

$conn = null;//close the connection

? >