文字

MySQL 函数

注释

Note:

大多数 MySQL 函数的最后一个可选参数是 link_identifier。如果没有提供这个参数，则会使用最后一个打开的连接。若不存在这个最后打开的连接，则会尝试用 php.ini 里定义的默认参数来连接。如果没有成功连接，函数会返回 FALSE 。

mysql_affected_rows — 取得前一次 MySQL 操作所影响的记录行数
mysql_client_encoding — 返回字符集的名称
mysql_close — 关闭 MySQL 连接
mysql_connect — 打开一个到 MySQL 服务器的连接
mysql_create_db — 新建一个 MySQL 数据库
mysql_data_seek — 移动内部结果的指针
mysql_db_name — 取得结果数据
mysql_db_query — 发送一条 MySQL 查询
mysql_drop_db — 丢弃（删除）一个 MySQL 数据库
mysql_errno — 返回上一个 MySQL 操作中的错误信息的数字编码
mysql_error — 返回上一个 MySQL 操作产生的文本错误信息
mysql_escape_string — 转义一个字符串用于 mysql_query
mysql_fetch_array — 从结果集中取得一行作为关联数组，或数字数组，或二者兼有
mysql_fetch_assoc — 从结果集中取得一行作为关联数组
mysql_fetch_field — 从结果集中取得列信息并作为对象返回
mysql_fetch_lengths — 取得结果集中每个输出的长度
mysql_fetch_object — 从结果集中取得一行作为对象
mysql_fetch_row — 从结果集中取得一行作为枚举数组
mysql_field_flags — 从结果中取得和指定字段关联的标志
mysql_field_len — 返回指定字段的长度
mysql_field_name — 取得结果中指定字段的字段名
mysql_field_seek — 将结果集中的指针设定为制定的字段偏移量
mysql_field_table — 取得指定字段所在的表名
mysql_field_type — 取得结果集中指定字段的类型
mysql_free_result — 释放结果内存
mysql_get_client_info — 取得 MySQL 客户端信息
mysql_get_host_info — 取得 MySQL 主机信息
mysql_get_proto_info — 取得 MySQL 协议信息
mysql_get_server_info — 取得 MySQL 服务器信息
mysql_info — 取得最近一条查询的信息
mysql_insert_id — 取得上一步 INSERT 操作产生的 ID
mysql_list_dbs — 列出 MySQL 服务器中所有的数据库
mysql_list_fields — 列出 MySQL 结果中的字段
mysql_list_processes — 列出 MySQL 进程
mysql_list_tables — 列出 MySQL 数据库中的表
mysql_num_fields — 取得结果集中字段的数目
mysql_num_rows — 取得结果集中行的数目
mysql_pconnect — 打开一个到 MySQL 服务器的持久连接
mysql_ping — Ping 一个服务器连接，如果没有连接则重新连接
mysql_query — 发送一条 MySQL 查询
mysql_real_escape_string — 转义 SQL 语句中使用的字符串中的特殊字符，并考虑到连接的当前字符集
mysql_result — 取得结果数据
mysql_select_db — 选择 MySQL 数据库
mysql_set_charset — 设置客户端的字符集
mysql_stat — 取得当前系统状态
mysql_tablename — 取得表名
mysql_thread_id — 返回当前线程的 ID
mysql_unbuffered_query — 向 MySQL 发送一条 SQL 查询，并不获取和缓存结果的行

用户评论:

[#1] Vladimir Kosmala [2010-10-27 07:57:41]

Here a mysql helper containing the main functions of the mysql extension. It's easy to understand for a beginner and quite useful because queries are secure. It understands what you want, just write your sql query. I called it mysql_magic.

<?php // Examples $nb_affected = mysql_magic('delete from users'); $nb = mysql_magic('select count(*) from users'); $one_row = mysql_magic('select * from users limit 1'); $all_rows = mysql_magic('select * from users where name = ?', 'John'); $id = mysql_magic('insert into users(name,rank) values(?,?)', 'Vincent', 3); ?>

<?php // Usage: mysql_magic($query [, $arg...]); function mysql_magic() { global $dblink, $sqlhost, $sqluser, $sqlpass, $sqlbase; $narg = func_num_args(); $args = func_get_args(); if (!$dblink) { $dblink = mysql_connect( $sqlhost, $sqluser, $sqlpass ); mysql_select_db( $sqlbase, $dblink ); } $req_sql = array_shift($args); $req_args = $args; $req_query = mysql_bind($req_sql, $req_args); $req_result = mysql_query($req_query); if (!$req_result) { trigger_error(mysql_error()); return false; } if (startsWith($req_sql, 'delete') || startsWith($req_sql, 'update')) { return mysql_affected_rows(); // -1 || N } else if (startsWith($req_sql, 'insert')) { return mysql_insert_id(); // ID || 0 || FALSE } else if (endsWith($req_sql, 'limit 1')) { return mysql_fetch_assoc($req_result); // [] || FALSE } else if (startsWith($req_sql, 'select count(*)')) { $line = mysql_fetch_row($req_result); return $line[0]; // N } else { return mysql_fetch_all($req_result); // [][] } } function mysql_bind($sql, $values=array()) { foreach ($values as &$value) $value = mysql_real_escape_string($value); $sql = vsprintf( str_replace('?', "'%s'", $sql), $values); return $sql; } function mysql_fetch_all($result) { $resultArray = array(); while(($resultArray[] = mysql_fetch_assoc($result)) || array_pop($resultArray)); return $resultArray; } function startsWith($haystack,$needle,$case=false) { if($case){return (strcmp(substr($haystack, 0, strlen($needle)),$needle)===0);} return (strcasecmp(substr($haystack, 0, strlen($needle)),$needle)===0); } function endsWith($haystack,$needle,$case=false) { if($case){return (strcmp(substr($haystack, strlen($haystack) - strlen($needle)),$needle)===0);} return (strcasecmp(substr($haystack, strlen($haystack) - strlen($needle)),$needle)===0); } ?>

Don't forget to set $sqlhost, $sqluser, $sqlpass and $sqlbase.

With help of :
mysql_bind : http://php.net/manual/en/function.mysql-real-escape-string.php#96391
mysql_fetch_all : http://php.net/manual/en/function.mysql-fetch-assoc.php#90030

trigger_error may be enhanced with this tip : http://php.net/manual/en/function.trigger-error.php#98910

[#2] joachimb at gmail dot com [2008-05-16 14:28:41]

Lazy Man's Logging:

<?php /// Creates a table called $table as (id, when, message) if none such exists, and inserts a row with $message in it. /// If no connection details are given, it uses the current database connection. Same goes for $database and $when. /// /// @returns TRUE on success or FALSE on failure. /// /// @example mysql_put_contents("orders", "I CAN HAZ CHEEZBURGER?", "mysite", NULL, "127.0.0.1:3306", "mysite_user", "secret") or die(mysql_error()); /// @example mysql_put_contents("guestbook", "Longcat says: I'm loooooooooooong") or die("Errorz!"); function mysql_put_contents($table, $message, $database = NULL, $when = NULL, $host = NULL, $user = NULL, $pass = NULL) { if($host) mysql_connect($host, $user, $pass); if($database) mysql_select_db($database); $qry = "CREATE TABLE IF NOT EXISTS `$table` ( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `when` TIMESTAMP DEFAULT NOW(), `message` TEXT NOT NULL );"; $result = mysql_query($qry); if($result === FALSE) return FALSE; $qry = "INSERT INTO `$table` VALUES(NULL, ".($when ? $when : 'NULL').", '".mysql_real_escape_string($message)."');"; $result = mysql_query($qry); if($result === FALSE) return FALSE; return TRUE; } ?>

[#3] dhirendrak at yahoo dot com [2008-02-04 23:03:50]

<?php # Created by dhirendra can be reached at dhirendrak at yahoo dot com # This script is created to check the data difference between two tables # when the structure of both tables are same. # Limitation : # 1) Structure of both tables should be same. # 2) Name of both table should be different but if same than obviously # second table should be if different database. # 3) If use two database than both database permission should be same # as i am using aliases to get the information. # # USES:: # 1) This may be useful when you did some changes in your existing # script and you expect the certain output. So with the help of this # function you may compare the impact due to your changes in script. # # $host=""; # host name or ip address $user=""; # database user name $pass=""; # database password $database=""; # dateabase name with which you want to connect # get connection with mysql $dblink = @mysql_connect($host,$user,$pass); # select and open database mysql_select_db($database,$dblink); $db1="< your db1 >"; // first database // second database if database are same for both tables than use the same as db1 $db2="< your db2 >"; $table1="< your table1 >"; // first table // second table if database is same for both tables than table name # must be different but fields name are same and order of the fields are same. $table2="< your table2 >"; // function starts here function table_data_difference($first,$second) { global $dblink; $sql1 = "SHOW FIELDS FROM $first"; $result = mysql_query($sql1,$dblink) or die("Having error in execution 1 ==".mysql_error()); while($row = mysql_fetch_object($result)) { $from_fields[]=$row->Field; } $sql="select * from $first"; $res=mysql_query($sql,$dblink) or die("Having error in execution 2==".mysql_error()); $j=1; while($row=mysql_fetch_array($res)) { $num=count($from_fields); $sql_next="select $second.* from $second where"; for($i=0;$i < $num;$i++) { $sql_next=$sql_next." ".$second.".".$from_fields[$i]."='".$row[$from_fields[$i]]."' and "; } $sql_next=substr($sql_next,0,strlen($sql_next)-5); $res_next=mysql_query($sql_next,$dblink) or die("Having error in execution 3==".mysql_error()); $num1=mysql_num_rows($res_next); if($num1==0) { for($i=0;$i < count($from_fields);$i++) { $val=$val." ".$from_fields[$i]."=".$row[$from_fields[$i]]; } // Display the record which are not matched. echo " \n".$j.".".$val; echo " -----------------------------------------------------"; $j++; } } } $first=$db1.'.'.$table1; $second=$db2.'.'.$table2; table_data_difference($first,$second); ?>

[#4] matiyahoo-publico at yhoo dot com dot ar [2007-11-17 20:26:00]

David:

In this line:

$query = "SELECT username FROM users WHERE username REGEXP '$username[0-9*]'";

PHP may read $username[, *including* the open square bracket, and may think you are trying to get into an array.

You should use this instead:

$query = "SELECT username FROM users WHERE username REGEXP '${username}[0-9*]'";

Or maybe this:

$query = "SELECT username FROM users WHERE username REGEXP '$username" . "[0-9*]'";

[#5] david at kiwi dot com [2007-11-09 20:13:41]

I am wanting to increment usernames which are to be alphabetic characters A-Z or a-z and if someone uses "abc" it will automatically be given "abc1" and the next applicant to apply for that same username will automatically be given "abc2", the next "abc3" and so on. If I enter the following into MySQL;

SELECT username FROM users WHERE username REGEXP 'abc[0-9*]';

the result is

abc1
abc2
abc3

I have been given the following script from another forum but it does not work;

<?php $username = 'abc'; $query = "SELECT username FROM users WHERE username LIKE '$username%'"; $res = mysql_query($query); while($row = mysql_fetch_assoc($res)) { $n = str_replace($username, '', $row['username']); if ($old_n+1!=$n) { $new_user = $username . ($old_n+1); break; } } ?>

In addition to the script not working the "LIKE" function would not be suitable because if a username was applied as "blue" it would pick up usernames "blue1" and "bluebird1" therefore it appears as if "REGEXP '$username[0-9*]' is the only or correct option.

I have tried the REGEXP option in association with the balance of the code but it appears as if PHP will not interact with the "[" and "]" in the following;

$query = "SELECT username FROM users WHERE username REGEXP '$username[0-9*]'";

[#6] SID TRIVEDI [2007-11-07 04:33:53]

<?php //$connect = mysql_connect("Your Host Name", "MySQL root directory", 'MySQL password, if any'); //$connect = mysql_connect("Host Name or Address - 127.0.0.1", "root", 'password'); $connect = mysql_connect("localhost", "root", 'password'); if ($connect){ echo "Congratulations!\n "; echo "Successfully connected to MySQL database server.\n "; }else{ $error = mysql_error(); echo "Could not connect to the database. Error = $error.\n "; exit(); } // Closing connection $close = mysql_close($connect); if ($close){ echo "\n "; echo "Now closing the connection...\n "; echo "MySQL connection closed successfully as well.\n "; }else{ echo "There's a problem in closing MySQL connection.\n "; } exit(); ?>

[#7] rad14701 at yahoo dot com [2007-10-14 12:01:33]

@Amanda 12-Oct-2007 09:58

I almost had to ask myself if this was a real question... If the MySQL server rejects the connection attempt then, yes, MySQL would be able to send back an error to PHP... And if PHP can't access the target MySQL server at all then it is also smart enough to issue the appropriate error all by itself...

[#8] arnold_dba [2007-10-13 06:36:36]

Also, to secure MySQL data and to be able to comply with PCI standards, you should encrypt the data. There are many ways to do it. For hackers, you can use dm-crypt (www.saout.de/misc/dm-crypt) tool. It is basically used to encrypt the whole partition. If you want a sophisticated solution go with Security-GENERAL for MySQL from packet general (www.packetgeneral.com)
On windows, you can use disk encryption feature provided by windows itself or tool like trucrypt (www.truecrypt.org)

[#9] DuiMDog [2007-09-02 03:19:01]

On 11-Apr-2007 admin at mihalism dot com posted an 'easy to use MySql-class'.
In my opinion there is a bug in the get_affected_rows-function. It refers to query-id, but  it should refer to connect-id instead.

The corrected function should be:
function get_affected_rows($connect_id = ""){
            if($connect_id == NULL){
                $return = mysql_affected_rows($this->connect_id);
            }else{
                $return = mysql_affected_rows($connect_id);
            }
            if(!$return){
                $this->error();
            }else{
                return $return;
            }
        }

[#10] atk2 at hotmail dot com [2007-06-14 08:56:56]

After finally getting IIS, PHP, and MySQL on a new Windows XP machine, I decided to write the steps I took so you can see how it was done: http://www.atksolutions.com/articles/install_php_mysql_iis.html

Hope this helps.

[#11] Nobody Special [2007-04-14 11:20:54]

This PHP5-only class is used so that $db isn't passed. Kudos to arjen at queek dot nl for example.

<?php class SQL { private $db; public function __construct($host="localhost",$user="root",$pass="",$db="") { $this->db = @mysql_connect($host, $user, $pass); if(!$this->db) die(@mysql_error()); if($db != "") $dbs = @mysql_select_db($db); if(!$dbs) die(@mysql_error()); } public function __destruct() { @mysql_close($db); } public function __call($function, $arguments) { array_push($arguments, $this->db); $return = call_user_func_array("mysql_".$function, $arguments); if(!$return) die(@mysql_error()); } } $db = new SQL(); $query = $db->query("SELECT * FROM this_table WHERE my_variable='1'"); $fetch = $db->fetch_array($query); print_r($fetch); ?>

[#12] mega-squall at caramail dot com [2007-04-13 02:35:09]

For Windows users, please note:

If apache is installed as a service, and you change PATH variable so it can reach libmysql.dll, you will need to reboot your machine in order to have changes applied.

[#13] admin at mihalism dot com [2007-04-11 07:48:06]

Heres a easy to use MySQL class for any website

<?php class mysql_db{ //+======================================================+ function sql_connect($sqlserver, $sqluser, $sqlpassword, $database){ $this->connect_id = mysql_connect($sqlserver, $sqluser, $sqlpassword); if($this->connect_id){ if (mysql_select_db($database)){ return $this->connect_id; }else{ return $this->error(); } }else{ return $this->error(); } } //+======================================================+ function error(){ if(mysql_error() != ''){ echo 'MySQL Error: '.mysql_error().' '; } } //+======================================================+ function query($query){ if ($query != NULL){ $this->query_result = mysql_query($query, $this->connect_id); if(!$this->query_result){ return $this->error(); }else{ return $this->query_result; } }else{ return 'MySQL Error: Empty Query!'; } } //+======================================================+ function get_num_rows($query_id = ""){ if($query_id == NULL){ $return = mysql_num_rows($this->query_result); }else{ $return = mysql_num_rows($query_id); } if(!$return){ $this->error(); }else{ return $return; } } //+======================================================+ function fetch_row($query_id = ""){ if($query_id == NULL){ $return = mysql_fetch_array($this->query_result); }else{ $return = mysql_fetch_array($query_id); } if(!$return){ $this->error(); }else{ return $return; } } //+======================================================+ function get_affected_rows($query_id = ""){ if($query_id == NULL){ $return = mysql_affected_rows($this->query_result); }else{ $return = mysql_affected_rows($query_id); } if(!$return){ $this->error(); }else{ return $return; } } //+======================================================+ function sql_close(){ if($this->connect_id){ return mysql_close($this->connect_id); } } //+======================================================+ } $DB = new mysql_db(); $DB->sql_connect('sql_host', 'sql_user', 'sql_password', 'sql_database_name'); $DB->query("SELECT * FROM `members`"); $DB->sql_close(); ?>

[#14] davesteinb at yahoo dot com [2006-08-13 15:32:11]

I made this function to reduce DB calls. You can store Mysql results in a session var and sort the results on any column. Might work nice in an AJAX app.

<?php function mysql_sort($results, $sort_field, $dir="ASC") { $temp_array = array(); $i=0; foreach ($results as $res) { $temp_array[$i] = $res[$sort_field]; $i++; } if ($dir=="ASC") { asort($temp_array); } else { arsort($temp_array); } $new_results = array(); $i=0; foreach($temp_array as $k => $v) { $new_results[$i] = $results[$k]; $i++; } ksort($new_results); return $new_results; } //use if (count($_SESSION["res"])==0) { $_SESSION["res"] = [GET DATABASE RESULTS HOWEVER YOU MAY] } $_SESSION["res"] = mysql_sort($_SESSION["res"], $_REQUEST["sort"], $_REQUEST["dir"]); ?>
<table>
<tr>
 <td><a href="page.php?sort=f_name&dir=<?php echo ($_REQUEST["dir"]=="ASC") ? "DESC":"ASC"; ?>">First</a></td>
 <td><a href="page.php?sort=l_name&dir=<?php echo ($_REQUEST["dir"]=="ASC") ? "DESC":"ASC"; ?>">Last</a></td>
</tr>

<?php foreach ($_SESSION["res"] as $r) {?>
<tr>
 <td><?php=$r["f_name"]?></td>
 <td><?php=$r["l_name"]?></td>
</tr>
<?php } ?>
</table>

[#15] richard at NOSPAM dot dimax dot com [2006-07-19 16:58:45]

In response to Conrad Decker's post below:

If your tables contain foreign key constraints you will not be able to properly restore from a datafile created by mysqldump.

mysqldump dumps table data in alphabetical order, not in the logical order required by the foreign key constraints.

[#16] vbolshov at rbc dot ru [2006-07-09 23:27:15]

I have recently ran into a problem with configuring php extensions related to mysql (namely, mysql and pdo_mysql). Later I've discovered that it wasn't a PHP problem but that of MySQL - libmysqlclient doesn't ship with binary downloads. I've built MySQL from sources and both extensions then compiled successfully.

[#17] sb at stephenbrooks dot org [2006-05-01 13:43:52]

I'm in the process of changing web hosts and my previous host provided a "dump" of the database in the form of a sequence of SQL requests that (I assume) have to be executed in order to rebuild the database on another system.  It was generated using "MySQL dump 9.11".  Queries are finished by a semicolon and linefeed, while comment-lines begin with a double hyphen.  The script below opens a connection to an SQL server and loads a dump file $file of this format into the database $dest_db.

function load_db_dump($file,$sqlserver,$user,$pass,$dest_db)
{
  $sql=mysql_connect($sqlserver,$user,$pass);
  mysql_select_db($dest_db);
  $a=file($file);
  foreach ($a as $n => $l) if (substr($l,0,2)=='--') unset($a[$n]);
  $a=explode(";\n",implode("\n",$a));
  unset($a[count($a)-1]);
  foreach ($a as $q) if ($q)
    if (!mysql_query($q)) {echo "Fail on '$q'"; mysql_close($sql); return 0;}
  mysql_close($sql);
  return 1;
}

This may not be watertight if the ";\n" sequence appears inside queries, but I hope it helps others who are in posession of such dumps.

[#18] yp [2006-03-15 14:08:28]

when using Fedora Core linux Mysql rpm installation.
Configure as following to load the mysql header files.
./configure --with-mysql=/usr/
Remember to do make clean to remove old configurations before compile

[#19] php comments of fuzzyworm co., uk [2006-02-17 03:13:20]

If you want to get PHP working nicely with MySQL, even with Apache, under Windows based systems, try XAMPP, from Apache Friends. It saves messing about with config files, which is the only major problem with trying to get the three to work together under windows.

http://www.apachefriends.org/en/xampp-windows.html

[#20] Joe Greklek [2005-12-15 13:15:35]

I've seen alot of newbies getting frustrated over the extenstions not being installed initially, so heres a quick tut for Windows.

It's ok to use the php5 installer. Just be sure to also grab the .zip or "manual" version of php5.

Install it like any other app. It's pretty straight forward. Don't forget to set the securities mentioned in the last message box at the end of the install. Next, If you installed php to c:\php then you will need to add this to your PATH environment variable. This is a very important step.

Now open up the .zip manual version of php5 and extract the ext folder, and "libmysql.dll" to "c:\php\".

You *MUST* set your security permissions on libmysql.dll and the ext folder to READ/READ&EXECUTE for IUSR_"MACHINE_NAME". like my machine is called master<acct = IUSR_MASTER>. If you don't do this you will recieve an ******.dll - Access Denied. type message.

Next edit your php.ini file usually located in c:\windows\.
Find the line for extension_dir variable and make it say
extension_dir = "c:\php\ext\"

Then scroll down a little bit and find the lines that say
;extension=php_mbstring.dll
;extension=php_bz2.dll
;extension=php_curl.dll
;extension=php_dba.dll
;extension=php_dbase.dll
;extension=php_exif.dll
;extension=php_fdf.dll
;extension=php_filepro.dll
;extension=php_gd2.dll
;extension=php_gettext.dll
;extension=php_ifx.dll

just remove the semicolin for each extension you would like loaded. Such as
extention=php_mysql.dll
and so on.

Now just reboot and all should be well. Query away. you basically only need to reboot to update the PATH environment variable. Hope this helps at least 1 person.:)

[#21] nick smith [2005-09-19 12:07:21]

i'm fairly new to this but have just managed to set up (on windows xp) apache 2.0.54 with php 5.0.4 and mySQL 4.1.13, also phpMyAdmin 2.6.4 having had exactly the problems reported by so many others. i wasnt actually aware of mySQL not being loaded in php until i tried to use phpmyadmin and it told me to check my php/mysql configuration.

basically i just did everything that is mentioned by others (setting extension_dir to c:\php\ext ( not c:\php\ext\ ), uncommenting extension=php_mysql.dll from php.ini and putting c:\php into my PATH) but i was still getting the infuriating message when trying to start apache that php_mysql.dll could not be found.

i restarted my machine and it worked! It seems i had to restart windows after editing my PATH. i didnt bother restarting until it was the last resort because on xp i have NEVER had to do this before - changes to System Variables always took effect immediately. I could understand this on, say, windows 98 where you put the addition to your PATH into your autoexec.bat but why i had to do this with xp is a mystery.

Anyway, give it a go, it might save you tearing out prescious hair!

N.

[#22] avis_del at yahoo dot com [2005-09-01 01:14:43]

I aggree with j at jonathany.com

PHP 5.04 to MySQL,
php_mysql.dll will not found on windows installer (.msi)
just extract from .zip file (can download).
It works.

1. cgi.force_redirect = 0
2. extension_dir = "c:\php\ext"
3. extension=php_mysql.dll

untill on phpinfo.php show

MySQL Support enabled
Active Persistent Links  0
Active Links  0
Client API version  4.1.12

[#23] claude(at)claude(dot)nl [2005-08-25 07:23:00]

A note on resources

When a resource (e.g. a link identifier) runs out of scope, it is deleted and the associated computer resources (e.g. the tcp link to the database) will be terminated as well. So far so good!
However, in the following code the tcp mysql link persists until the end of execution:
<?php $conn = mysql_connect('hostname', 'username','password'); $conn = null; sleep (30); ?>
This is because the internally the link-identifier is being saved, so that subsequent mysql functions will work. There seems to be no way to delete this internal reference.
If you were, however, to open 2 connections, the oldest one will be deleted automatically (so the connection to hostname will terminate at the $conn=null statement, the connection to hostname2 will exist till the end of the script).
<?php $conn = mysql_connect('hostname', 'username','password'); $conn2 = mysql_connect('hostname2', 'username','password'); $conn = null; $conn2 = null; sleep (30); ?>

[#24] arjen at mysql dot com [2005-08-04 13:26:48]

John Coggeshall wrote a PHP5 ext/mysqli compatibility script for applications that still use the old ext/mysql functions. This prevents the hassle of trying to have both the mysql and mysqli extensions loaded in PHP5, which can be tricky.

The script is at:
http://www.coggeshall.org/oss/mysql2i/

[#25] allan666 at NOSPAM dot gmail dot com [2005-07-20 03:37:58]

[Editor's Note: In MySQL v5.0+, you can use the INFORMATION_SCHEMA tables to retrieve information on tables, views, databases and so on. --zak@php.net]

Here is a small function to parse a mysql creation table DDL. The function takes a string with the SQL code to create a table and returns the table name, table fields, table key fields and fields type, all in arrays (except by the name, obviously). The function requires that the primary key be named "id" and the foreign keys named "id...". All foreign key types are suposed to be int (or its variations, bigint, etc...). All those restrictions are easily modified to others needs.

Here is a example of a DDL code.

CREATE TABLE `telefones` (
 `id` int(11) NOT NULL auto_increment,
 `id_tipo_telefone` int(11) NOT NULL default '0',
 `numero` varchar(15) NOT NULL default '',
 `id_pessoa` int(11) NOT NULL default '0',
 PRIMARY KEY (`id`),
 KEY `id_tipo_telefone` (`id_tipo_telefone`),
 KEY `id_pessoa` (`id_pessoa`),
 CONSTRAINT `0_775` FOREIGN KEY (`id_tipo_telefone`) REFERENCES `tipos_telefone` (`id`),
 CONSTRAINT `0_776` FOREIGN KEY (`id_pessoa`) REFERENCES `pessoas` (`id`)
) TYPE=InnoDB

that returns

$tbname = "telefones"
$fields = array("numero");
$kfields = array("id_tipo_telefone","id_pessoa");
$tipos = array("varchar");

Hope it helps...

<?php function parseQuery($Q, &$tbname, &$fields, &$kfields, &$tipos) { $Q = str_replace(array(chr(10),chr(13))," ",$Q); $Q = str_replace(array("'","`")," ",$Q); preg_match("/([^(]*)$(.*)$(.*)/",$Q,$A); $part1 = $A[1]; $part2 = $A[2]; $part3 = $A[3]; preg_match("/(.*) ([a-zA-Z_]+)/",$part1,$A); $tbname = strtolower($A[2]); $temp = split(",",$part2); foreach ($temp as $t) { preg_match("/ *([a-zA-Z_]+) +([a-zA-Z_]+)(.*)/",$t,$A); $pcampo = strtolower($A[1]); $ptipo = strtolower($A[2]); if (!preg_match("/$pcampo/","primary key constraint id unique foreign") ) { if ( ($pcampo[0] == "i") && ($pcampo[1] == "d") ) $kfields[] = $pcampo; else { $fields[] = $pcampo; $tipos[] = $ptipo; } } } } ?>

[#26] Microsoul V3 [2005-06-04 15:47:46]

I use IIS 6, PHP 5.04, Windows Server 2003 and MySQL 4.1.11. and here is what I was able to figure out.
To get MySQL and PHP to talk to each other, in the php.cfg, dont forget to turn on the tag
cgi.force_redirect = 0, and make sure you set it at 0 (it is default at 1. just delete the semi-colon from in front of it to activate it in the code) Then the phpinfo will say its reading the cfg from your PHP install directory instead of your WINDOWS root. Then your phpinfo will show the MySQL entry that some people may be having a hard time with. No registry settings or copying of anything is needed. Also follow the rest of the excellent help from the user comments. Here's what I did, to make it simple:
I made a folder in the folder tree just above program files (explore your computer) and named it PHP. I extracted the .zip php version into it (not the auto install version). I edited the php.ini-recommended, renamed it to just php, added my sql username, database name, etc.( you really gotta look closely at the cfg file and make sure you dont overlook something). Turned on the extension=php_mysql.dll (just delete the semi-colon thats in front of it). Added the php folder to the PATH (instructions on how to do this are pretty simple, and documented above). I also made the ext folder available to the PATH, but Im not sure if it's really necessary. The user comments are what really helped me, so I thought I would return the favor, and try to expand on this topic a little bit.

[#27] noel at nettensity dot com [2005-05-28 22:04:53]

Enabling MySQL with Windows Server 2003/IIS 6.0:
Find your php.ini file first, check phpinfo() to see where php is currently looking for php.ini. (i.e. Using the Windows installer for PHP 5.0.4, the php.ini file was placed in the C:\Windows dir.) I recommend, however, that you don't use the installer - go with full manual install.

Set the following in php.ini:
display_errors = on
error_reporting = E_ALL
This will make sure that you'll see any errors that arise during configuration. MAKE SURE TO CORRECT THESE WHEN YOU ARE DONE PLAYING WITH PHP.INI! Do not leave these settings like this on a production machine.

In php.ini set the following:
extension_dir = "pathtoextensions (ususally [yourpathtophp]\ext)"
extension=php_mysql.dll (make sure this is un-commented if its already in your php.ini)

In IIS, open Web Service Extensions, click "Add a new Web service extension..."
Type-in PHP for the extension name
Under required files:
Add [yourpathtophp]\php5isapi.dll
Add [yourpathtophp]\php5ts.dll
Click 'Apply' then click 'OK'

Create web sites like you normally would, but make sure they have executable permissions, not just script access. Under the "Home Directory" tab, click 'Configuration'. Scroll down the list at the top and see if you can find PHP. If you do, make sure the path to the executable for PHP is correct. If you don't find PHP in the list, click 'Add...', then browse to the correct executable, [yourpathtophp]\php5isapi.dll, and click OK. In the extension field, enter 'PHP'. Verbs should already be set to 'All verbs', leave it like that.

Create a test page with this code:
<?php echo(phpinfo()); ?>
call it test.php, and place this file into the web site you just created. Browse to the page, with firefox preferably ;), and make sure that you have a MySql section with some MySql info in there. If not, then your paths are possibly screwed up, or you're still not editing the correct php.ini (again, look at the location that phpinfo() shows you, and just edit it there if you have to, then move it afterwards and re-config).

Hope this helps!

[#28] medic at setiherbipolis dot de [2005-05-15 08:42:12]

Client does not support authentication protocol requested by server; consider upgrading MySQL client

means that you're using an old version of MySQL Client ( possibly not mysqli)

Authentication protocol for MySQL has changed with version 4.1.

To get a hint at which mysql-client version you're using try phpinfo();

[#29] [2005-03-05 23:01:56]

If you are installing PHP5 on Windows 2003 server (AKA Win 2k3) and need MySQL to work using the either the php_mysql.dll or php_mysqli.dll or both of them at the same time, and MySQl isn't showing up in phpinfo, then your php.ini is probably not loading.  In the direction in the PHP 5 zip file, they will tell you to add your PHP install directory to your windows path.  This should tell php where to load its php.ini from but it doesn't.  If you want to get this to work, you don't have to copy any DLL's anywhere like everyone suggests.  All you have to do is add the folling regsitry key to windows:

[HKEY_LOCAL_MACHINE\SOFTWARE\PHP]
"IniFilePath"="C:\\PHP"

simply copy the above 2 lines of code into a text file and save the file as php_ini_path.reg

After you save the file it will look like a registry file.  Simply double click on it.

It will make it so PHP will look for your php.ini in C:\PHP.  I would assume you can edit this if you install php into a different location, but I haven't tried that.

After running the reg file, make sure your php.ini is in your PHP dir and make sure all the appropriate things are set.  This should get you up and running.  Make sure you also follow all the steps on how to make it work in IIS.  This is just an addition to the direction.

[#30] Protik Mukherjee [2005-03-03 09:34:06]

Fedora mysql problems!!
In Fedora 3 the php mysql module does not come with the default installation. To install it use $>yum install php_mysql
If u dont do this you will get errors with mysql functions like mysql_connect()

Hope this helps!

[#31] nleippe at integr8ted dot com [2004-10-12 15:22:33]

trace_mode breaks SQL_CALC_FOUND_ROWS.
This is because it emits an EXPLAIN <query> before sending the <query> by itself, thus the subsequent SELECT FOUND_ROWS() is no longer the next consecutive query, and the result is zero.
This was true for me for at least MySQL 4.0.21 and 4.1.5gamma.
(PHP 4.3.9)

[#32] aidan at php dot net [2004-08-15 05:59:46]

If you want to replicate the output of `mysql --html`, printing your results in a HTML table, see this function:

http://aidanlister.com/repos/v/function.mysql_draw_table.php

[#33] irn-bru at gmx dot de [2004-05-27 05:27:48]

Note, that the sql.safe_mode configuration setting does effect all mysql_* functions. This has nothing to to with the php safe mode, check the [SQL] section in php.ini.

I found out, that if you set sql.safe_mode = On, mysql_connect will ignore provided username and passwort and makes use of the script owner instead (checked on debian).

Brian

[#34] soren at byu dot edu [2003-03-14 13:23:44]

Let's say that you want to generate a MySQL password hash from a plain text password. Normally, you would just submit the MySQL query "SELECT PASSWORD('password')", but if for some reason you can't access to MySQL database directly, then you can use the following function (translated right out of the MySQL source code):

<?php function mysql_password($passStr) { $nr=0x50305735; $nr2=0x12345671; $add=7; $charArr = preg_split("//", $passStr); foreach ($charArr as $char) { if (($char == '') || ($char == ' ') || ($char == '\t')) continue; $charVal = ord($char); $nr ^= ((($nr & 63) + $add) * $charVal) + ($nr << 8); $nr2 += ($nr2 << 8) ^ $nr; $add += $charVal; } return sprintf("%08x%08x", ($nr & 0x7fffffff), ($nr2 & 0x7fffffff)); } ?>

example:

<?php print mysql_password("hello"); ?>

outputs:

70de51425df9d787

Which is the same result you get if you do "SELECT PASSWORD('hello')" directly in MySQL. Hopefully you'll never be in a situation where you have to use this, but if you need it (like I did), it's here.

[#35] mijnpc at xs4all dot nl [2002-11-20 14:33:49]

If you have a Windows machine running a webserver with PHP you don't need to install MySQL server to locally test scripts, if you are granted to establish a Secure Telnet connection (port 22) to the remote webserver.

To do this you need a Secure Telnet client, which supports port-forwarding.
Before you establish a connection, define the port-forward.
Forward local port 3306 to [name or ip of remote server]:3306
Make sure that local ports accept connections from other hosts
Save this session

Connect to remote server with username and password
Minimize the shell and that's it...

You can use the same username (and password) as if you were working on the remote server !
E.g. : $link = mysql_connect("localhost", "root", "") or die("no way jose");

You may get a shell-timeout after xx minutes depending on your remote server, just reconnect or press enter in the shell once in a while...

An example of a superb freeware Secure Telnet client is Putty : Putty : http://www.chiark.greenend.org.uk/~sgtatham/putty/

This 'discovery' really has saved me a lot of time because I don't have to upload the scripts to the remote server time and time again, pressing [save] is enough, heh (-:

[#36] skelley at diff dot nl [2001-09-25 14:11:09]

Hi, here's a nice little trick to select records in random order from a table in a MySQL database prior to version 3.23

SELECT *, (ItemID/ItemID)*RAND() AS MyRandom FROM Items ORDER BY MyRandom

[Editors note: And just "SELECT * FROM foo ORDER BY RAND()" after 3.23]

[#37] mbabcock-php at fibrespeed dot net [2001-07-28 19:41:52]

Since there aren't functions to start and end/rollback transactions, you'll have to use mysql_query("BEGIN"), mysql_query("COMMIT") and mysql_query("ROLLBACK"). These will only work properly on tables that support transactions. You may also wish to roll your own mysql_begin (etc) functions that run the above queries for you.

[#38] mw-php at ender dot com [2001-06-21 21:11:48]

The mysql_fetch_[row|object|array] functions return data as type string. Owing to the very flexible nature of php variables, this is normally not relevent, but if you happen to retrieve two integers from a database, then try to compare with bitwise operators, you'll run into trouble, because (19 & 2) == 2, but ("19" & "2") == 0. To remedy this, if you use variables from a database with bitwise operators, use the settype() function to explicitly cast your variables as integers before comparing.

MySQL 函数

注释

Table of Contents

用户评论: