PHP中MySQL的使用
连接到MySQL：
mysql_connect():
$dbc=mysql_connect($host,$user,$password);
选择当前数据库：
mysql_select_db($database_name);
mysql_select_db('$database_name',$conn);
eg:
<body> <?php DEFINE('DB_USER','root');DEFINE('DB_PWD','mysql');DEFINE('DB_HOST','localhost');DEFINE('DB_NAME','content'); $dbc=@mysql_connect(DB_HOST,DB_USER,DB_PWD) OR die ('Could not connect to MySQL:'.mysql_error());@mysql_select_db(DB_NAME) or die ('Could not select the database:'.mysql_error()); ?></body>
如果函数不能返回有效的资源链接，则会执行 or die(), die()函数会终止脚本的执行。
执行简单的查询：
$result=mysql_query($query);
对于像insert、update、delete等简单的查询，它们不会返回记录，$result变量将会返回true或false，这取决于查询是否执行成功。对于确实会返回记录的复杂查询(select、show、describe和explain)，如果查询有效，则$result变量将是一个指向查询结果的资源链接；如果查询无效，则$result变量将为false。
1 <body> 2 <?php 3 $page_title='Register'; 4 include('Noname1.php'); 5 if(!empty($_POST['submit'])) 6 { 7 $errors=array(); 8 } 9 if(empty($_POST['name']))10 {11 $errors[]='You forgot to enter your name.';12 }13 else14 {15 $name=$_POST['name'];16 }17 18 if(empty($_POST['email']))19 {20 $errors[]='You hava fotgot to enter your email';21 }22 else23 {24 $email=$_POST['email'];25 }26 27 if(!isset($_POST['gender']))28 {29 $errors[]='You hava forgot to enter your gender';30 }31 else32 {33 $gender=$_POST['gender'];34 }35 36 /*if(!empty($POST['comments']))37 {38 $comments=$_POST['comments'];39 }40 else41 {42 $errors[]='You hava forgot to set the comments';43 }*/44 if(empty($errors))45 {46 require_once('connectdb.php');47 $query="insert into urls(url,title,description) values('$name','$email','$gender')";48 $result=@mysql_query($query);49 if($result)50 {51 echo '<h1 id="Thank-you"> Thank you!</h1>';52 }53 else54 {55 echo 'System error'.mysql_error();56 }57 }58 else59 {60 echo '<h1 id="Error">Error!</h1>';61 foreach($errors as $msg)62 {63 echo "-$msg<br>\n";64 }65 echo 'Please try again!';66 }67 ?>68 </body>
关闭现有的MySQL链接：
mysql_close(); //php会在脚本的末尾自动关闭连接，所以这不是必须的。
检索查询结果
格式：
mysql_fetch_row(result);
说明：mysql_fetch_row用来查询结果的一行保存至数组，该数组下标从0开始，每一个数组元素对应
一个域。通过循环，可以将查询结果全部获得。
格式：
1 mysql_fetch_array(result);
说明：mysql_fetch_array和mysql_fetch_row功能基本相同，只不过它除了可以用从0开始的偏移量作
索引，还可以用域名作索引。
值返回下一行的所有域值，并将其保存至一个数组中，没有行时返回false。
mysql_query("set name 'GBK'")
//使用ＧＢＫ中文编码
处理select查询结果的主要工具是mysql_fetch_array(),它带有一个查询结果变量（$result）,并以数组格式一次返回一行数据。
从查询读取每条记录：
while($row=mysql_fetch_array($result)) { ......... }
mysql_fetch_array()函数还带有一个可选项，用于指定返回的数组的类型：联合数组、索引数组，或则两者。联合数组允许通过名称引用列值，而索引数组则要求只使用数字，他们如下表：
| 常量 | 示例 |
| MYSQL_ASSOC | $row['column'] |
| MYSQL_NUM | $row[0] （速度稍快） |
| MYSQL_BOTH | $row[0]或$row['column'] (默认) |
释放查询信息：
mysql_free_result($result);
这一步不是必需的，因为PHP将在脚本末尾自动释放资源。
eg:
1 <?php 2 require_once('connectdb.php'); 3 $query="select concat(url,',',title) as name ,url_id as id from urls"; 4 $result=@mysql_query($query); 5 if($result) 6 { 7 echo '<table align="center" cellspacing="0" cellpadding="5"> 8 <tr><td align="left"><b>NAME</b></td><td align="left"><b>id</b></td>'; 9 10 while($row=mysql_fetch_array($result,MYSQL_ASSOC))11 {12 echo '<tr><td align="left">'.$row['name'].'</td><td align="left">'.$row['id'].'</td></tr>';13 }14 echo '</table>';15 mysql_free_result($result);16 }17 18 else19 {20 echo '<p>The current users could not be retrieved.We apologize for any inconvenience</p>';21 echo mysql_error();22 }23 mysql_close();24 25 ?>
运行结果为：
技巧总结
mysql_fetch_array()函数与mysql_fetch($result,MYSQL_NUM)函数等价
mysql_fetch_assoc()函数与mysql_fetch_array($result,MYSQL_ASSOC)函数等价
必需使用mysql_query()执行查询，然后使用mysql_fetch_array()来检索单行信息，如果要检索多行，则可使用while循环(而不要使用for或foreach循环)。
确保SQL安全
关于PHP的数据库安全可归纳为两大类问题：
1.保护数据库访问信息
2.在运行查询时要小心。
使用特定的
mysql_real_escape_string()
函数，转义那些有可能有问题的字符来清理数据：
$data=mysql_real_escape_string($data,$dbc);
这个函数与addslashes()一样。它更特定于数据库。
使用ini_get()函数测试Magic Quotes状态，这个函数将为特定的选项返回PHP的配置文件中的配置。如果ini_get() 函数返回true，就说明打开了Magic Quotes??则将在应用mysql_real_escape_string()之前，去除所有的斜杠。
在pHP的当前版本中，Magic Quotes默认是关闭的，因此ini_get()将返回FALSE，并且不需要去除任何现有的斜杠。
如果Magic Quotes是开启的情况下，它会自动在需要转义的字符前加上“\”,而我们向数据库中写入的时候这往往会引起错误，所以需要使用stripslashes($string)把那些\去掉。然后再调用mysql_real_escape_string()函数。
eg：
1 function escape_data($data)2 {3 global $dbc;4 if(ini_get('magic_quotes_gpc'))5 {6 $data=stripslashes($data);7 }8 retrun mysql_real_escape_string(trim($data),$dbc);9 }
技巧总结
mysql_real_escape_String()函数一招所用的语言对字符串进行转义
mysql_real_escape_string()函数需要一个数据库连接。
get_magic_quotes_gpc()函数也可用于返回当前的Magic Quotes设置。
ini_get()可用于获取许多不同的php.ini设置的内容，不只是Magic Quotes的设置。
统计返回的记录
mysql_num_rows()，返回select查询索引的行数，并取查询的结果作为一个参数。
eg:
$num=mysql_num_rows($result);
其他常用Mysql函数介绍
mysql_insert_id
传回最后一次使用 INSERT 指令的 ID。
mysql_tablename
取得数据库名称
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1386
52
Alipay PHP SDK transfer error: How to solve the problem of 'Cannot declare class SignData'?
Apr 01, 2025 am 07:21 AM
Alipay PHP...
Explain JSON Web Tokens (JWT) and their use case in PHP APIs.
Apr 05, 2025 am 12:04 AM
JWT is an open standard based on JSON, used to securely transmit information between parties, mainly for identity authentication and information exchange. 1. JWT consists of three parts: Header, Payload and Signature. 2. The working principle of JWT includes three steps: generating JWT, verifying JWT and parsing Payload. 3. When using JWT for authentication in PHP, JWT can be generated and verified, and user role and permission information can be included in advanced usage. 4. Common errors include signature verification failure, token expiration, and payload oversized. Debugging skills include using debugging tools and logging. 5. Performance optimization and best practices include using appropriate signature algorithms, setting validity periods reasonably,
Describe the SOLID principles and how they apply to PHP development.
Apr 03, 2025 am 12:04 AM
The application of SOLID principle in PHP development includes: 1. Single responsibility principle (SRP): Each class is responsible for only one function. 2. Open and close principle (OCP): Changes are achieved through extension rather than modification. 3. Lisch's Substitution Principle (LSP): Subclasses can replace base classes without affecting program accuracy. 4. Interface isolation principle (ISP): Use fine-grained interfaces to avoid dependencies and unused methods. 5. Dependency inversion principle (DIP): High and low-level modules rely on abstraction and are implemented through dependency injection.
How to automatically set permissions of unixsocket after system restart?
Mar 31, 2025 pm 11:54 PM
How to automatically set the permissions of unixsocket after the system restarts. Every time the system restarts, we need to execute the following command to modify the permissions of unixsocket: sudo...
How to debug CLI mode in PHPStorm?
Apr 01, 2025 pm 02:57 PM
How to debug CLI mode in PHPStorm? When developing with PHPStorm, sometimes we need to debug PHP in command line interface (CLI) mode...
Explain the concept of late static binding in PHP.
Mar 21, 2025 pm 01:33 PM
Article discusses late static binding (LSB) in PHP, introduced in PHP 5.3, allowing runtime resolution of static method calls for more flexible inheritance.Main issue: LSB vs. traditional polymorphism; LSB's practical applications and potential perfo
How to send a POST request containing JSON data using PHP's cURL library?
Apr 01, 2025 pm 03:12 PM
Sending JSON data using PHP's cURL library In PHP development, it is often necessary to interact with external APIs. One of the common ways is to use cURL library to send POST�...
Explain late static binding in PHP (static::).
Apr 03, 2025 am 12:04 AM
Static binding (static::) implements late static binding (LSB) in PHP, allowing calling classes to be referenced in static contexts rather than defining classes. 1) The parsing process is performed at runtime, 2) Look up the call class in the inheritance relationship, 3) It may bring performance overhead.
