大佬教程收集整理的这篇文章主要介绍了php-使用PDO查询代替mysql,即已弃用,大佬教程大佬觉得挺不错的,现在分享给大家,也给大家做个参考。
众所周知,PHP v7.0中已删除了MysqL,我正在尝试使用pdo使用以下示例通过数据表获取数据(服务器端)(但在MysqL中需要使用pdo):
码:
列:
/* Array of database columns which should be read and sent BACk to DataTables. Use a space where
* you want to insert a non-database field (for example a counter or static imagE)
*/
$acolumns = array( 'first_name', 'last_name', 'position', 'office', 'salary' );
/* Indexed column (used for fast and accurate table cardinality) */
$sIndexcolumn = "id";
/* DB table to use */
$sTable = "datatables_demo";
创建PDO连接:
$db_host = "localhost";
$db_name = "sadad";
$db_user = "root";
$db_pass = "root";
try{
$db_con = new PDO("MysqL:host={$db_host};dbname={$db_name}",$db_user,$db_pass);
$db_con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $E){
echo $e->getmessage();
}
以下代码:
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
$sLimit = "LIMIT ".$db_con->quote( $_GET['iDisplayStart'] ).", ".
$db_con->quote( $_GET['iDisplayLength'] );
}
/*
* Ordering
*/
if ( isset( $_GET['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i<intval( $_GET['iSorTingCols'] ) ; $i++ )
{
if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" )
{
$sOrder .= $acolumns[ intval( $_GET['iSortCol_'.$i] ) ]."
".$db_con->quote( $_GET['sSortDir_'.$i] ) .", ";
}
}
$sOrder = substr_replace( $sOrder, "", -2 );
if ( $sOrder == "ORDER BY" )
{
$sOrder = "";
}
}
/*
* Filtering
* NOTE this does not match the built-in DataTables filtering which does it
* word by word on any field. It's possible to do here, but concerned about efficiency
* on very large tables, and MysqL's regex functionality is very limited
*/
$sWhere = "";
if ( $_GET['sSearch'] != "" )
{
$sWhere = "WHERE (";
for ( $i=0 ; $i<count($acolumns) ; $i++ )
{
$sWhere .= $acolumns[$i]." LIKE '%".$db_con->quote( $_GET['sSearch'] )."%' OR ";
}
$sWhere = substr_replace( $sWhere, "", -3 );
$sWhere .= ')';
}
/* Individual column filtering */
for ( $i=0 ; $i<count($acolumns) ; $i++ )
{
if ( $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
{
if ( $sWhere == "" )
{
$sWhere = "WHERE ";
}
else
{
$sWhere .= " AND ";
}
$sWhere .= $acolumns[$i]." LIKE '%".$db_con->quote($_GET['sSearch_'.$i])."%' ";
}
}
$my = str_replace(" , ", " ", implode(", ", $acolumns));
/*
* sql queries
* Get data to display
*/
$sQuery = $db_con->query("SELECT {$my} FROM {$sTablE} {$sWherE} {$sOrder} {$sLimit}")->fetchAll();
//$rResult = ( $sQuery );
/* Data set length after filtering */
$sQuery = "
SELECT FOUND_ROWS()
";
//$rResultFilter@R_247_10586@l = $sQuery;
$aResultFilter@R_247_10586@l = $sQuery;
$iFiltered@R_247_10586@l = $aResultFilter@R_247_10586@l[0];
/* @R_247_10586@l data set length */
$sQuery = "
SELECT COUNT(".$sIndexcolumn.")
FROM $sTable
";
$rResult@R_247_10586@l = $db_con->query( $sQuery ) or die(MysqL_error());
$aResult@R_247_10586@l = $rResult@R_247_10586@l->fetchAll();
$i@R_247_10586@l = $aResult@R_247_10586@l[0];
/*
* Output
*/
$output = array(
"sEcho" => intval($_GET['sEcho']),
"i@R_247_10586@lRecords" => $i@R_247_10586@l,
"i@R_247_10586@lDisplayRecords" => $iFiltered@R_247_10586@l,
"aaData" => array()
);
while ( $aRow = $rResult->fetchAll() )
{
$row = array();
for ( $i=0 ; $i<count($acolumns) ; $i++ )
{
if ( $acolumns[$i] == "version" )
{
/* Special output formatTing for 'version' column */
$row[] = ($aRow[ $acolumns[$i] ]=="0") ? '-' : $aRow[ $acolumns[$i] ];
}
else if ( $acolumns[$i] != ' ' )
{
/* General output */
$row[] = $aRow[ $acolumns[$i] ];
}
}
$output['aaData'][] = $row;
}
echo json_encode( $output );
错误:
但是我遇到了错误,我不知道上面要更改什么,请开始使用pdo,请使用代码更新答案:
[28-Aug-2018 16:58:39 UTC] PHP Fatal error: Uncaught exception 'PDOException' with message 'sqlSTATE[42000]: @L_450_18@ error or access violation: 1064 You have an error in your sql @L_450_18@; check the manual that corresponds to your MysqL server version for the right @L_450_18@ to use near ''asc' LIMIT '0', '50'' at line 2' in C:\MAMP\htdocs\BACkend\my.PHP:131
Stack trace:
#0 C:\MAMP\htdocs\BACkend\my.PHP(131): PDO->query('SELECT first_na...')
#1 {main}
thrown in C:\MAMP\htdocs\BACkend\my.PHP on line 131
**ERROR:**
解决方法:
$sWhere .= $acolumns[$i]." LIKE '%".$db_con->quote($_GET['sSearch_'.$i])."%' ";
PDO :: quote()函数的输出与旧的MysqL_real_escape_String()函数不同.
假设您的字符串是“ O’Reilly”,并且您需要转义撇号字符.
MysqL_real_escape_String(“ O’Reilly”)将返回:
O\'Reilly
而$db_con-" quote(“ O’Reilly”)将返回:
'O\'Reilly'
quote()函数将字符串定界符添加到字符串的开头和结尾.这使其与MysqL内置函数QUOTE()相同
因此,当您使用PDO :: quote()的方式时:
$sWhere .= $acolumns[$i]." LIKE '%".$db_con->quote($_GET['sSearch_'.$i])."%' ";
产生的sql子句如下所示:
... WHERE mycolumn LIKE '%'search'%' ...
这是行不通的.您需要它是:
... WHERE mycolumn LIKE '%search%' ...
$sWhere .= $acolumns[$i]." LIKE ".$db_con->quote('%'.$_GET['sSearch_'.$i].'%') ;
现在,它利用PDO :: quote()添加字符串定界符.
顺便说一下,我找到了所有.字符串连接使PHP看起来很糟糕.很难编写代码,也很难阅读和调试代码.我更喜欢直接在字符串内部使用变量.并且不要害怕用两行代码来完成这项工作.对于代码可读性而言,将太多内容填入一行并不总是最好的.
$pattern = $db_con->quote("%{$_GET["sSearch_$i"]}%");
$sWhere .= "{$acolumns[$i]} LIKE {$pattern}";
但是还有另一种更容易,更安全的方式.
使用查询参数代替转义/引用.
$params[] = "%{$_GET["sSearch_$i"]}%";
$sWhere .= "{$acolumns[$i]} LIKE ?";
后来…
$stmt = $db_con->prepare($sQuery);
$stmt->execute($params);
while ($row = $stmt->fetchAll()) {
...
}
使用参数比使用转义/引用更简单.您不必费神去问报价是否正确平衡,因为参数占位符?不需要字符串定界符.
如果您正在学习PDO,建议您阅读以下内容:
> https://phpdelusions.net/pdo-一个很好的教程.
> http://php.net/pdo-参考文档.
两者都是重要和有用的.参考文档并不适合学习,但是在您学习本教程之后,它们对您有用,以提醒您自己语法,参数,返回值等.我已经做了很多PDO编码,但是我经常打开参考文档.
以上是大佬教程为你收集整理的php-使用PDO查询代替mysql,即已弃用全部内容,希望文章能够帮你解决php-使用PDO查询代替mysql,即已弃用所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错,欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。