Head First PHP&MySQL 学习笔记（二） —— 创建和填充数据库

PHP中如何查询并显示结果

mysqli_fetch_array + while循环

mysqli_fetch_array 

<?php
    $query = "SELECT * from email_list";
    $result = mysqli_query($dbc, $query); /* $result 变量不包含任何查询数据，保存的是SQL查询的资源ID。每个SQL查询都有自己的资源ID，用于访问和查询结果关联的数据 */
    $row = mysqli_fetch_array($result); /* 获取查询到的数据并存储到数组$row中，每次一行。数组$row的数组下标是表的列名 */
?>

while循环

<?php
    while ($row = mysqli_fetch_array($result)) 
    { 
        $firstname = $row['first_name']; 
        $lastname = $row['last_name']; 
        $msg = "Dear $firstname $lastname,\n$text"; 
        $to = $row['email']; 
                                                                                   
        mail($to, $subject, $msg, 'From:' . $from); /* 发送email，要想成功发送email需要安装和配置sendmail */
        echo 'Email sent to:' . $to . '<br />'; 
    }
?>

MySQL 脚本

CREATE DATABASE elvis_store;

USE elvis_store;

CREATE TABLE email_list
(
	first_name VARCHAR(20),
	last_name VARCHAR(20),
	email VARCHAR(60)
);

DESCRIBE email_list;

/* 删除一个表和其中的所有数据 */
DROP TABLE email_list;

/* 修改一个表的定义
ALTER TABLE email_list ...;
 */

CREATE TABLE `email_list` (
  `id` INT AUTO_INCREMENT,
  `first_name` VARCHAR(20),
  `last_name` VARCHAR(20),
  `email` VARCHAR(60),
  PRIMARY KEY (`id`)
);

示例程序

向数据库中添加数据

<?php                                                                              
                                                                                   
    $dbc = mysqli_connect('localhost', 'root', '123456', 'elvis_store')            
        or die('Error connecting to MySQL server.');                               
                                                                                   
    $firstname = $_POST['firstname'];                                              
    $lastname = $_POST['lastname'];                                                
    $email = $_POST['email'];                                                      
                                                                                   
    $query = "INSERT INTO email_list(first_name, last_name, email)" .              
             "VALUES('$firstname', '$lastname', '$email')";                        
                                                                                   
    mysqli_query($dbc, $query) or die("Error querying database.");                 
                                                                                   
    echo "Customer added:\n $firstname, \n $lastname, \n $email"                   
?>

查询表中的数据

<?php                                                                              
                                                                                   
    $from = 'zhang3@163.com';                                           
    $dbc = mysqli_connect('localhost', 'root', '123456', 'elvis_store')            
        or die('Error connecting to MySQL server.');                               
                                                                                   
                                                                                   
    $subject = $_POST['subject'];                                                  
    $text    = $_POST['elvismail'];                                                
    $query = "SELECT* FROM  email_list";                                           
    $result = mysqli_query($dbc, $query) or die("Error querying database.");       
                                                                                   
    while ($row = mysqli_fetch_array($result))                                     
    {                                                                              
        $firstname = $row['first_name'];                                           
        $lastname  = $row['last_name'];                                            
        $msg       = "Dear $firstname $lastname,\n$text";                          
        $to        = $row['email'];                                                
                                                                                   
        mail($to, $subject, $msg, 'From:' . $from); /* Note: sendmail need installed the configured correctly */
        echo 'Email sent to:' . $to . '<br />';                                    
    }                                                                              
                                                                                   
    mysqli_close($dbc);                                                            
    //echo "Customer added:\n $firstname, \n $lastname, \n $emal"                  
?>

删除表中数据

<?php                                                                              
                                                                                   
    $dbc = mysqli_connect('localhost', 'root', '123456', 'elvis_store')            
        or die('Error connecting to MySQL server.');                               
                                                                                   
    $email = $_POST['email'];                                                      
                                                                                   
    $query = "DELETE FROM email_list WHERE email='$email'";                        
    //echo "$query";                                                               
                                                                                   
    mysqli_query($dbc, $query) or die("Error querying database.");                 
                                                                                   
    echo "Customer deleted: $email";                                               
?>