Basic database operations

Here's how to log into mysql on Linux, create a database, and create a table.

Yin @yin-ubuntu 10:~$mysql-u root-p
Enter the password:
Welcome to the MySQL monitor. Commands end with; Or \ g.
Your MySQL connection id is 360
Server version 5.1.41 was - 3 ubuntu12. 1 (Ubuntu)

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

Mysql> The create database UseCase.
Query OK, 1 row affected (0.00 secant)

Mysql> Use UseCase.
The Database changed

Mysql> Create table User(UserName varchar(20) primary key,Password varchar(20) not null,CreateTime timestamp default current_timestamp);
Query OK, 0 rows affected (0.01 SEC) now creates a page to complete the page for the new user. The first is a simple form:

 
<form action="db.php" method="post"> 
<dl> 
<dt>UserName</dt><dd><input name="UserName" maxlength="20" type="text"/></dd> 
<dt>Password</dt><dd><input name="Password" maxlength="20" type="password"/></dd> 
<dt>Confirm Password</dt><dd><input name="ConfirmPassword" maxlength="20" type="password"/></dd> 
</dl> 
<input type="submit" name="ok" value="ok"/> 
</form> 

PHP USES the $_POST array to get the data in the form submitted through the post method. In a PHP program, we first determine whether there is an OK field to determine whether the page was first accessed or submitted by the user after he clicked OK, and then whether the two password entries are consistent. You can then get the username and password and insert them into the database. PHP connections to MySQL databases can usually be made using the MySQL extension or the mysqli extension, which is a little bit newer, and we'll do it this way. Mysqli may require installation configuration, but it is installed by default in my environment. Using the mysqli extension to operate a database is generally divided into the following steps: constructing a mysqli object, constructing a statement, binding parameters, executing, and closing. The code is as follows:

 
<?php 
$match=true; 
if(isset($_POST["ok"])) { 
$pwd=$_POST["Password"]; 
$pwdConfirm=$_POST["ConfirmPassword"]; 
$match=($pwd==$pwdConfirm); 
$conn=new mysqli("localhost","root","123","UseCase"); 
if (mysqli_connect_errno()) { 
printf("Connect failed: %sn", mysqli_connect_error()); 
exit(); 
} 
$query="insert into User(UserName,Password) values(?,?)"; 
$stmt=$conn->stmt_init(); 
$stmt->prepare($query); 
$stmt->bind_param('ss',$name,$pwd); 
$name=$_POST["UserName"]; 
$pwd=$_POST["Password"]; 
$stmt->execute(); 
if($stmt->errno==0) { 
$success=true; 
}else { 
$success=false; 
} 
$stmt->close(); 
$conn->close(); 
} 
?> 

The bind_param method needs to be explained a little bit, and the meaning of the first parameter is the parameter type. Each character corresponds to a parameter, s for string, I for integer, d for floating point, and b for blob. Finally, add a hint to the page:

 
<?php 
if(!$match) { ?> 
<p>Password and Confirm Password must match.</p> 
<?php 
} 
?> 
<?php 
if(isset($success)) { 
if($success) { 
echo '<p>User Created Successfully!'; 
}elseif($sucess==false) { 
echo '<p>User Name existed.'; 
} 
} 
?> 

Next, let's write a list of users page.

 
<table> 
<tr><th>User Name</th><th>CreateTime</th><th>Action</th> 
</tr> 
<?php 
include 'conn.php'; 
$query="select * from User;"; 
$res=$mysql->query($query); 
while($row=$res->fetch_array()) { 
?> 
<tr> 
<td><?= $row['UserName'] ?></td> 
<td><?= date('Y-m-d',strtotime($row['CreateTime']))?> </td> 
<td><a href="UserEdit.php?action=update&ID=<?= $row['UserName'] ?>">Edit</a> 
<a href="action=delete&ID=<?= $row['UserName'] ?>">Delete</a> 
</td> 
</tr> 
<?php 
} 
$res->close(); 
$mysql->close(); 
?> 
</table>