① 如何利用php執行.SQL文件
本篇文章是對使用PHP執行 SQL文件的實現代碼進行了詳細的分析介紹 需要的朋友參考下demo php:
復制代碼 代碼如下: <?php /** * 讀取 sql 文件並寫入資料庫 * @version demo php */ class DBManager { var $dbHost = ; var $dbUser = ; var $dbPassword = ; var $dbSchema = ; function __construct($host $user $password $schema) { $this >dbHost = $host; $this >dbUser = $user; $this >dbPassword = $password; $this >dbSchema = $schema; } function createFromFile($sqlPath $delimiter = (;/n)|((;/r/n))|(;/r) $prefix = $menter = array( # )) { //判斷文件是否存在 if(!file_exists($sqlPath)) return false; $handle = fopen($sqlPath rb ); $sqlStr = fread($handle filesize($sqlPath)); //通過sql語法的語句分割符進行分割 $segment = explode(";" trim($sqlStr)); //var_mp($segment); //去掉注釋和多餘的空行 foreach($segment as & $statement) { $sentence = explode("/n" $statement); $newStatement = array(); foreach($sentence as $subSentence) { if( != trim($subSentence)) { //判斷是會否是注釋 $isComment = false; foreach($menter as $er) { if(eregi("^(" $er ")" trim($subSentence))) { $isComment = true; break; } } //如果不是注釋 則認為是sql語句 if(!$isComment) $newStatement[] = $subSentence; } } $statement = $newStatement; } //對表名加前綴 if( != $prefix) { //只有表名在第一行出現時才有效 例如 CREATE TABLE talbeName $regxTable = "^[/`/ /"]{ }[/_a zA Z]+[/_a zA Z ]*[/`/ /"]{ }$";//處理表名的正則表達式 $regxLeftWall = "^[/`/ /"]{ }"; $sqlFlagTree = array( "CREATE" => array( "TABLE" => array( "$regxTable" => ) ) "INSERT" => array( "INTO" => array( "$regxTable" => ) ) ); foreach($segment as & $statement) { $tokens = split(" " $statement[ ]); $tableName = array(); $this >findTableName($sqlFlagTree $tokens $tableName); if(empty($tableName[ leftWall ])) { $newTableName = $prefix $tableName[ name ]; } else{ $newTableName = $tableName[ leftWall ] $prefix substr($tableName[ name ] ); } $statement[ ] = str_replace($tableName[ name ] $newTableName $statement[ ]); } } //組合sql語句 foreach($segment as & $statement) { $newStmt = ; foreach($statement as $sentence) { $newStmt = $newStmt trim($sentence) "/n"; } $statement = $newStmt; } //用於測試 //var_mp($segment); //writeArrayToFile( data txt $segment); // self::saveByQuery($segment); return true; } private function saveByQuery($sqlArray) { $conn = mysql_connect($this >dbHost $this >dbUser $this >dbPassword); mysql_select_db($this >dbSchema); foreach($sqlArray as $sql) { mysql_query($sql); } mysql_close($conn); } private function findTableName($sqlFlagTree $tokens $tokensKey= & $tableName = array()) { $regxLeftWall = "^[/`/ /"]{ }"; if(count($tokens)<=$tokensKey) return false; if( == trim($tokens[$tokensKey])) { return self::findTableName($sqlFlagTree $tokens $tokensKey+ $tableName); } else { foreach($sqlFlagTree as $flag => $v) { if(eregi($flag $tokens[$tokensKey])) { if( ==$v) { $tableName[ name ] = $tokens[$tokensKey]; if(eregi($regxLeftWall $tableName[ name ])) { $tableName[ leftWall ] = $tableName[ name ]{ }; } return true; } else{ return self::findTableName($v $tokens $tokensKey+ & $tableName); } } } } return false; } } function writeArrayToFile($fileName $dataArray $delimiter="/r/n") { $handle=fopen($fileName "wb"); $text = ; foreach($dataArray as $data) { $text = $text $data $delimiter; } fwrite($handle $text); } //測試 $dbM = new DBManager( localhost w f test ); $dbM >createFromFile( data sql null fff_ ); ?> lishixin/Article/program/PHP/201311/21281
② php 如何把一條sql語句寫入資料庫
<?php
$dbhost = 'localhost:3306'; // mysql伺服器主機地址
$dbuser = 'root'; // mysql用戶名
$dbpass = '123456'; // mysql用戶名密碼
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn ){
die('連接失敗: ' . mysqli_error($conn))
;}
echo '連接成功<br />'; // 設置編碼，防止中文亂碼mysqli_query($conn , "set names utf8");
$runoob_title = '學習 Python';
$runoob_author = 'RUNOOB.COM';
$submission_date = '2016-03-06';
$sql = "INSERT INTO runoob_tbl ".
"(runoob_title,runoob_author, submission_date) ".
"VALUES ".
"('$runoob_title','$runoob_author','$submission_date')";
mysqli_select_db( $conn, 'RUNOOB' );$retval = mysqli_query( $conn, $sql );
if(! $retval ){
die('無法插入數據: ' . mysqli_error($conn))
;}
echo "數據插入成功\n";
mysqli_close($conn);
?>
按照步驟開始，多看PHP手冊。
③ php寫入資料庫
PHP向MySQL資料庫中寫入數據有三個步驟：
1，PHP和MySQL建立連接關系
2，打開MySQL資料庫
3，接受頁面數據，PHP錄入到指定的表中
1、2兩步可直接使用一個資料庫鏈接文件即可：conn.php
代碼如下
<?php
mysql_connect("localhost","root","");//連接MySQL
mysql_select_db("hello");//選擇資料庫
?>
當然，前提是已經安裝WEB伺服器、PHP和MySQL，並且建立MySQL表「cnbruce」
mysql_connect()中三個參數分別為MySQL地址、MySQL用戶名和MySQL密碼
然後就是通過WEB頁面傳遞數據，讓PHP通過SQL語句將數據寫入MySQL資料庫指定的表中，比如新建文件 post.php
代碼如下
<?php
require_once("conn.php");//引用資料庫鏈接文件
$uname = $_GET['n'];//GET方法為URL參數傳遞
$psw = $_GET['p'];
$psw=md5($psw);//直接使用MD5加密
$sql = "insert into members(username,password) values ('$uname','$psw')";
mysql_query($sql);//借SQL語句插入數據
mysql_close();//關閉MySQL連接
echo "成功錄入數據";
?>
測試頁面： http://localhost/post.php?n=cnbruce&p=i0514
即可向MySQL資料庫hello的members表中插入新的數據「cnbruce」到username欄位、「i0514」到password欄位
補充：讀取表
讀取表中的內容,這里我們用while,可以根據具體情況,用for 或其他的.
代碼如下
while($row = mysql_fetch_array($result))
{
echo "<div style="height:24px; line-height:24px; font-weight:bold;">"; //排版代碼
echo $row['Topic'] . "<br/>";
echo "</div>"; //排版代碼
④ php中插入MySQL資料庫的語句怎麼寫
顯示資料庫或表:
showdatabases;//然後可以usedatabase_name;
showtables;
更改表名:
altertabletable_namerenamenew_t;
添加列:
altertabletable_nameaddcolumnc_ncolumnattributes;
刪除列:
altertabletable_namedropcolumnc_n;
創建索引:
altertablec_tableaddindex(c_n1,c_n2);
altertablec_tableadniqueindex_name(c_n);
altertablec_tableaddprimarykey(sid);
刪除索引:
altertablec_tabledropindexc_n1;
更改列信息:
altertablet_tablechangec_1c_1varchar(200);
altertablet_tablemodify1c_1varchar(200);
insert插入語句:
insertintotable_name(c_1,c_2)
values('x1',1);
update語句:
updatetable_namesetc_1=1wherec_2=3;
刪除資料庫或者表:
droptabletable_name;
dropdatabasedatabase_name;//使用mysql_drop_db()可以刪除的.