LXXXIV. MySQL 函数

简介

可以使用本类函数访问 MySQL 数据库服务器。有关 MySQL 的详细信息可以到 http://www.mysql.com/ 查询。

MySQL 的文档可以在 http://dev.mysql.com/doc/ 找到。

需求

为了能够顺利的使用本类函数，必须在编译 PHP 的时候添加 MySQL 的支持。

安装

编译时，只要使用 --with-mysql[=DIR] 配置选项即可，其中可选的 [DIR] 指向 MySQL 的安装目录。

本 MySQL 扩展库不支持 MySQL 4.1.0 以上版本全部功能。对此，可用 MySQLi。

如果要同时安装 mysql 扩展库和 mysqli 扩展库，必须使用同一个客户端库以避免任何冲突。

在 Linux 系统下安装

PHP 4

默认开启了 --with-mysql 选项。此默认行为可以用 --without-mysql 配置选项来禁止。如果启用 MySQL 而不指定安装目录的话，PHP 将使用绑定的 MySQL 客户端连接库。

还有其它应用程序使用 MySQL（例如 auth-mysql）的用户不要用绑定的库，而要指定 MySQL 的安装目录，如这样：--with-mysql=/path/to/mysql。这将强制 PHP 使用随 MySQL 安装的客户端连接库，就可以避免任何冲突。

PHP 5+

MySQL 默认未启用，也没有绑定的 MySQL 库。其原因见此 FAQ。使用 --with-mysql[=DIR] 配置选项来加入 MySQL 的支持。可以从 MySQL 下载头文件和库。

MySQL 默认未启用，因此必须在 php.ini 中激活 php_mysql.dll 动态连接库。此外，PHP 还需要访问 MySQL 客户端连接库。PHP 的 Windows 发行版包括了一个 libmysql.dll，为了让 PHP 能和 MySQL 对话，此文件必须放在 Windows 的系统路径 PATH 中。怎样做见 FAQ 中的“怎样把 PHP 目录加入到 Windows 路径中”。尽管将 libmysql.dll 拷贝到 Windows 系统目录中也可以（因为系统目录默认在系统路径 PATH 中），但不推荐这样做。

要激活任何 PHP 扩展库（例如 php_mysql.dll），PHP 指令 extension_dir 要被设为 PHP 扩展库所在的目录。参见手工 Windows 安装指南。PHP 5 下 extension_dir 取值的一个例子是 c:\php\ext。

注: 如果启动 web 服务器时出现类似如下的错误："Unable to load dynamic library './php_mysql.dll'"，这是因为系统找不到 php_mysql.dll 和／或 libmysql.dll。

MySQL 安装注意事项

警告

当同时加在本扩展库和 recode 扩展库时 PHP 可能会崩溃。更多信息见 recode 扩展库。

注: 如果需要不同于 latin（默认值）的字符集，必须安装外部的（非绑定的）已编译入所需字符集支持的 libmysql。

运行时配置

这些函数的行为受 php.ini 的影响。

表格 1. MySQL 配置选项

选项名称	默认值	可修改范围	更新记录
mysql.allow_persistent	"1"	PHP_INI_SYSTEM
mysql.max_persistent	"-1"	PHP_INI_SYSTEM
mysql.max_links	"-1"	PHP_INI_SYSTEM
mysql.trace_mode	"0"	PHP_INI_ALL	自 PHP 4.3.0 起可用
mysql.default_port	NULL	PHP_INI_ALL
mysql.default_socket	NULL	PHP_INI_ALL	自 PHP 4.0.1 起可用
mysql.default_host	NULL	PHP_INI_ALL
mysql.default_user	NULL	PHP_INI_ALL
mysql.default_password	NULL	PHP_INI_ALL
mysql.connect_timeout	"60"	PHP_INI_ALL	自 PHP 4.3.0 起可用。在 PHP <= 4.3.2 时为 PHP_INI_SYSTEM

有关 PHP_INI_* 常量进一步的细节与定义参见附录 G。

以下是配置选项的简要解释。

mysql.allow_persistent boolean: 是否允许 MySQL 的持久连接。
mysql.max_persistent integer: 每个进程中最大的持久连接数目。
mysql.max_links integer: 每个进程中最大的连接数，包括持久连接。
mysql.trace_mode boolean: 跟踪模式。当激活 mysql.trace_mode 时，将会显示 table/index 扫描的警告，未释放的结果集以及 SQL 错误。（PHP 4.3.0 引进）
mysql.default_port string: 指定默认连接数据库的 TCP 端口号。如果没有指定默认端口号，则按顺序从 MYSQL_TCP_PORT 环境变量，/etc/services 文件中的 mysql-tcp 项或者编译时指定的 MYSQL_PORT 常量中取得。Win32 环境下只会使用 MYSQL_PORT 常量。
mysql.default_socket string: 当使用本地连接的时候，默认的 socket 名称。
mysql.default_host string: 默认连接的数据库服务器地址。不适用于 SQL 安全模式。
mysql.default_user string: 默认连接数据库时使用的用户名。不适用于 SQL 安全模式。
mysql.default_password string: 默认连接数据库时使用的密码。不适用于 SQL 安全模式。
mysql.connect_timeout integer: 连接超时秒数。在 Linux 中，此参数设定了等候来自服务器的响应的时长。

资源类型

在 MySQL 模块中使用了两种资源类型。第一种是数据库的连接句柄，第二种是 SQL 查询返回的结果集。

预定义常量

以下常量由本扩展模块定义，因此只有在本扩展模块被编译到 PHP 中，或者在运行时被动态加载后才有效。

在 PHP 4.3.0 以后的版本中，允许在 mysql_connect() 函数和 mysql_pconnect() 函数中指定更多的客户端标记。下面列出所定义的常量：

表格 2. MySQL 客户端常量

常量	描述
MYSQL_CLIENT_COMPRESS	使用压缩的通讯协议
MYSQL_CLIENT_IGNORE_SPACE	允许在函数名后留空格位
MYSQL_CLIENT_INTERACTIVE	允许设置断开连接之前所空闲等候的 interactive_timeout 时间（代替 wait_timeout）。
MYSQL_CLIENT_SSL	使用 SSL 加密。本标志仅在 MySQL 客户端库版本为 4.x 或更高版本时可用。在 PHP 4 和 Windows 版的 PHP 5 安装包中绑定的都是 3.23.x。

mysql_fetch_array() 函数使用一个常量来表示所返回数组的类型。下面是常量的定义：

表格 3. MySQL fetch 常量

常量	描述
MYSQL_ASSOC	返回的数据列使用字段名作为数组的索引名。
MYSQL_BOTH	返回的数据列使用字段名及数字索引作为数组的索引名。
MYSQL_NUM	返回的数据列使用数字索引作为数组的索引名。索引从 0 开始，表示返回结果的第一个字段。

注释

注: 大多数 MySQL 函数都接受 link_identifier 作为最后一个可选参数。如果未提供此参数，则使用最后一个打开的连接。如果不存在连接，则会用 php.ini 中定义的默认参数去尝试建立连接。如果连接不成功，函数返回 FALSE。

范例

下面的简单例子演示如何连接数据库，执行查询语句，打印返回结果集和断开数据库等一系列基本的 MySQL 操作。
例子 1. MySQL 例子
<?php // 连接，选择数据库 $link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password') or die('Could not connect: ' . mysql_error()); echo 'Connected successfully'; mysql_select_db('my_database') or die('Could not select database'); // 执行 SQL 查询 $query = 'SELECT * FROM my_table'; $result = mysql_query($query) or die('Query failed: ' . mysql_error()); // 用 HTML 显示结果 echo "<table>\n"; while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) { echo "\t<tr>\n"; foreach ($line as $col_value) { echo "\t\t<td>$col_value</td>\n"; } echo "\t</tr>\n"; } echo "</table>\n"; // 释放结果集 mysql_free_result($result); // 关闭连接 mysql_close($link); ?>

目录
mysql_affected_rows -- 取得前一次 MySQL 操作所影响的记录行数
mysql_change_user -- 改变活动连接中登录的用户
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_stat -- 取得当前系统状态
mysql_tablename -- 取得表名
mysql_thread_id -- 返回当前线程的 ID
mysql_unbuffered_query -- 向 MySQL 发送一条 SQL 查询，并不获取和缓存结果的行

add a note User Contributed Notes

mdhafen at x.washk12.org
20-Oct-2006 02:00


In response to martijn at elicit dot nl



Actually it is possible as a query, even with an auto_increment key field.  Like so...



INSERT

  INTO

   tablename

   ( list, of, ALL, similar, columns ) 

( SELECT

  *

FROM

  tablename

WHERE

  somefield = 'somevalue' )



That should work.

martijn at elicit dot nl
29-Aug-2006 03:52


Keeping history records usually requires you to duplicate rows. With standard MySQL syntax you can create a query like this:



INSERT

  INTO

    tablename

SELECT

  *

FROM

  tablename

WHERE

  somefield = 'somevalue'



The problem is that this won't work if you are using an auto_numbering key id field. There is no way to do this with standard MySQL syntax and I haven't been able to find a standard php function as well. So here's the function I wrote for that very purpose:



<?php



// function to create a duplicate record in a table with 1 auto_increment id.

function mysql_duplicate_record($table, $id_field, $id)

{

    // load original record into array

    $query = 'SELECT * FROM ' . $table . ' WHERE ' . $id_field . ' = ' . $id . ' LIMIT 1;';

    $r = mysql_query( $query ) or die('Error, query failed. ' . mysql_error());

    $ar = mysql_fetch_array( $r, MYSQL_ASSOC );

    

    // insert new record and get new auto_increment id

    mysql_query ('LOCK TABLES ' . $table . ' WRITE;') or die('Error, query failed. ' . mysql_error());

    mysql_query ('INSERT INTO ' . $table . ' ( `' . $id_field . '` ) VALUES ( NULL );') or die('Error, query failed. ' . mysql_error());

    $id = mysql_insert_id();

    mysql_query ('UNLOCK TABLES;') or die('Error, query failed. ' . mysql_error());

    

    // update new record with values from previous record

    $query = 'UPDATE ' . $table . ' SET ';

    while ($value = current($ar))

    {

        if (key($ar) != $id_field)

        {

            $query .= '`'.key($ar).'` = "'.$value.'", ';

        }

        next($ar);

    } 

    $query = substr($query,0,strlen($query)-2).' ';

    $query .= 'WHERE ' . $id_field . ' = "' . $id . '" LIMIT 1;';

    mysql_query($query) or die('Error, query failed. ' . mysql_error());

    

    // return the new id

    return $id;

}



?>

davesteinb at yahoo dot com
14-Aug-2006 06:32


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.



<?



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=<? echo ($_REQUEST["dir"]=="ASC") ? "DESC":"ASC"; ?>">First</a></td>

  <td><a href="page.php?sort=l_name&dir=<? echo ($_REQUEST["dir"]=="ASC") ? "DESC":"ASC"; ?>">Last</a></td>

</tr>



<? foreach ($_SESSION["res"] as $r) {?>

<tr>

  <td><?=$r["f_name"]?></td>

  <td><?=$r["l_name"]?></td>

</tr>

<? } ?>

</table>

richard at NOSPAM dot dimax dot com
20-Jul-2006 07:58


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.

vbolshov at rbc dot ru
10-Jul-2006 02:27


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.

Conrad Decker
24-May-2006 04:56


In regards to the previous post...you should actually be able to pipe a mysql dump directly back into mysql.



From the command line something like

mysql -u username -p databasename < mysqldumpfilename

should rebuild the database. 



There are some additional options one can use, and I believe mysqldump from different versions of mysql may not be completely compatible.

sb at stephenbrooks dot org
02-May-2006 04:43


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.

caladorn at ugcs dot caltech dot edu
20-Apr-2006 03:33


After configuring php.ini as outlined above (set the extension_dir variable and uncomment the appropriate mysql library) I kept receiving the following error on every Apache restart:



PHP Startup: Unable to load dynamic library './ext\\php_mysql.dll'



After trawling the web, most suggestions seemed to center on copying the libmysql.dll from the php install directory to c:\windows\system32.  While this can work, it can also complicate future upgrades, since you'll always have to copy the new libmysql.dll to insure proper operation.



Instead, if you run a search for "libmysql.dll", you'll likely find several versions in various directories.  After removing the older (smaller) versions from the other directories in my PATH, (c:\windows, c:\windows\system32) - and making sure the php install directory was in the PATH, apache/php was able to successfully locate the correct DLL and start without errors.



Hopefully someone will find this helpful.  ;)

Nathan Brizzee
04-Apr-2006 01:42


Hi,

For all those who may still be struggling to get PHP to work with Windows and IIS, I finally found something that works.



If you are getting the error message that function mysql_connect could not be found and you're sure you enabled php_mysql in php.ini this is what finally solved my problem.



The post from mmw_canada at yahoo dot com on 10-Jul-2005 06:15 pointed me in the right direction.  Thanks mmw_canada!



1.) If you are connected to your Windows box via Remote Desktop, there is a switch to Remote Desktop that will connect you to the console session.  This is important because when IIS starts up, it's error messages go to the console.  If you are sitting at the physical machine, you can ignore this step.



Here is the command

mstsc -v:10.192.186.xxx  /F -console



For a more complete list of supported commands, type this at a dos command window:

mstsc /?



2.) Add a registry entry that tells PHP where to find its php.ini file.  Copy the following text and paste it in a text file.  Rename the text file to something like phpini.reg and double-click it to add it to your registry.



Windows Registry Editor Version 5.00



[HKEY_LOCAL_MACHINE\\SOFTWARE\\PHP]

"IniFilePath"="C:\\\\PHP"



2.) Add c:\\php; (or wherever you unzipped PHP to) to your environment variable "Path".  Here is the important part that caused me hours and hours of grief.  When adding c:\\php; to the PATH environment variable, make SURE you add it BEFORE the path to CVSNT or MYSQL.  PHP, CVSNT, and MYSQL all have a file called libmysql.dll.  Search order is important for PHP to find its file libmysql.dll first.  PHP must come first in the search path.  Otherwise you will get an error from the DLL on the console screen when IIS starts up saying it can't find the specified function call.



To see the order of your environment variables, open up a dos window and type "set" and press enter.  Look at what Path is equal to.  c:\\php; better come before cvsnt and mysql or it will never work properly.



Hope this helps!

brjann at gmail dot com
01-Apr-2006 05:11


After tearing my eyes out in frustration, I've finally found a way to enable extensions that always works for me. It's pretty straightforward.



1. Download the latest zip-version of PHP. 

2. Delete (or backup) the contents of your PHP folder (usually c:\php). (remember to save any ext's not included in the zip)

3. Copy everything in the zip-file into your PHP folder

4. Be sure that extension_dir is set to "./ext" in php.ini

5. Enable whichever extensions you want to use in php.ini



I guess that deleting everything is quite unnecessary, the important thing is that the php.exe and extensions in the same zip are of compatible versions. I've never found any need to change the PATH environment variable.



Hope it helps!

yp
16-Mar-2006 06:08


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

php comments of fuzzyworm co., uk
17-Feb-2006 07:13


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

sabin at sabin dot com dot np
14-Feb-2006 08:29


php_mysql.dll and libmysql.dll for windows systems can be downloaded from http://dev.mysql.com/downloads/connector/php/



It can be useful to those who are using the PHP Windows installer, which does not have any external extensions included.

elliotthird at tiscali dot co dot uk
04-Feb-2006 01:20


Oops! Don't use that one, use this:

<?php

  class database {

   function database($server, $username, $password, $database) {

     $this->connection = mysql_connect($server, $username, $password);

     if (!$this->connection || !mysql_select_db($database, $this->connection)) {

       return false;

     } else {

       return true;

     }

   }

   function query($sql, $type = MYSQL_BOTH) {

     $query = mysql_query($sql, $this->connection);

     while ($row = mysql_fetch_row($query)) {

       $return[] = $row;

     }

     return $return;

   }

  }

  /* Change this to reflect your database setup */

  $database = new database('localhost', 'root', '', 'forum');

  if (!$database) {

    exit('Could not connect to the database!');

  }

?>

jeremy hepler
21-Dec-2005 04:12


For those of you on an OS that has older mysql libs and want to properly authenticate to the modern mysql versions, without the OLD_PASSWORD hack; compile php with where you have installed the latest mysql binary package: 



./configure --with-mysql=/usr/local/mysql



if you have mysql installed on seperate server, just drop it into the target directory on what ever machine you are compiling it on.



Be sure to "make clean" before building or it may look in the previous place.



Use the output of mysql_get_client_info(); to test before and after.

Joe Greklek
16-Dec-2005 05:15


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.:)

phil at pelanne dot com
03-Nov-2005 11:46


If you are experiencing extremely slow connections to MySQL from PHP from a Windows 2003 Server it may well not be PHP or IIS - try loading mysql in in 'skip-name-resolve'-Mode.  



It may mean the difference between a 10 second load time and a split second load time.

Jonathon Hibbard
22-Sep-2005 01:47


This is a small function I wrote to handle queries on a table.

It can query a table, order and sort, and supports inner joins.



This function also returns the result as a single row or all rows.



Enjoy : 



<?php

  /**

   * Selects Fields from a database/table.  

   * Also supports INNER JOINS, GROUP BY, ORDER BY.

   *

   * @Author : Jonathon Hibbard

   *

   * @param string $db_name         //Name of Database

   * @param string $table           //Name of Table

   * @param array $fields           //Field Names we want to select

   * @param array $inner            //Fields to be INNER JOINED

   * @param array $where            //WHERE fields.

   * @param string $group_by        //Field to group by.

   * @param string $order_by        //Field to order by

   * @param string $asc             //Asc or Desc order

   * @param string $return_array    //Defines $rst as an array or single row 

   * @return $rst                   //Returns the result

   *

   * @example : (1) getFields('mytool','master',

   *                          'array('master.master_id','master.fname_id'));

   *            (2) getFields('mytool','master',

   *                          'array('master.master_id','master.fname_id',

   *                          'fname.fname'), array(0=>array('mytool',

   *                          'fname','master','fname_id',)),

   *                          'master.default_value <> "", false);

   *            (3) getFields('mytool','master','master.master_id',NULL,

   *                          'master.fname_id = "3",NULL,NULL,NULL,false);

   */

  function getFields($db_name=NULL,$table=NULL,$fields=NULL,$inner=NULL, 

                     $where=NULL,$group_by=NULL,$order_by=NULL,$asc=NULL, 

                     $return_array=true) {

    $C_NAME = __CLASS__."::".__FUNCTION__;

    $sql = "SELECT ";

    is_array($fields) ? $sql .= implode(',',$fields) : $sql .= $fields;

    $sql .= " FROM ".$db_name.".".$table;

    if(!empty($inner)) {

      $total_inners = count($inner);

      for($i=0; $i<$total_inners; $i++)

        $sql .= " INNER JOIN ".$inner[$i][0].".".$inner[$i][1]." 

                  ON ".$inner[$i][1].".".$inner[$i][2]." = 

                     ".$inner[$i][3].".".$inner[$i][2];

    }

    if(!empty($where)) {

      is_array($where) ? $sql .= " WHERE ".implode(' AND ',$where)

                       : die("Fatal Error : Variable must be an array.  

                         Variable type is : ".gettype($where));

    }

    !empty($group_by) ? $sql .= " GROUP BY ".$group_by : '';

    !empty($order_by) ? $sql .= " ORDER BY ".$order_by : '';

    !empty($asc)      ? $sql .= " ".$asc : '';

    $sql .= " # Query resides in ".__FILE__."->".$C_NAME."

                on line ".__LINE__;

    if($return_array === true) {

      $rst = mysql_query($sql)

             or die("An Error Has Occurred!

                  \n MySQL Error Reports : ".mysql_error()."

                  \n The Error Occured in ".__FILE__." -> ".$C_NAME);

      $i=0;

      while($row = mysql_fetch_assoc($rst)) {

        $rowRst[] = $row[$fields[$i]];

        $i++;

      }

    } else {

      $rst = mysql_query($sql)

             or die("An Error Has Occurred! 

                  \n MySQL Error Reports : ".mysql_error()." 

                  \n The Error Occured in ".__FILE__." -> ".$C_NAME);

      $rowRst = mysql_fetch_assoc($rst);

    }

    return $rowRst;

  }

?>

nick smith
20-Sep-2005 03:07


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.

avis_del at yahoo dot com
01-Sep-2005 04:14


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

claude(at)claude(dot)nl
25-Aug-2005 10:23


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);

?>

arjen at mysql dot com
05-Aug-2005 04:26


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/

allan666 at NOSPAM dot gmail dot com
20-Jul-2005 06:37


[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) {





/** rules to a corect parse:


 *


 *     1 - primary key must be named "id"


 *     2 - foreign key must be named "id..." eg.: id_field


 *     3 - lowercase is recomended


 */





    $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;


            }


        }


    }


}


?>

mmw_canada at yahoo dot com
11-Jul-2005 09:15


I Quote:



"Note: If when starting the web server an error similar to the following occurs: "Unable to load dynamic library './php_mysql.dll'", this is because php_mysql.dll and/or libmysql.dll cannot be found by the system."



Actully, the file may have been found, it is just the wrong version.



The "libmysql.dll" is packaged and sent with a number of applications and it can end up in different place on your computer.  The file may even be completely different, so just replacing it may cause problems with the other applications.



I searched for the this file and found that I had a number of different versions on my machine at once.  PHP was trying to use the WRONG ONE. 



Maybe the name should be changed in later versions so that it doesn't encourage people to commit suicide or pull their hair out.

bahatest at ifrance dot com
07-Jul-2005 01:38


/*

* before mysql 4.1, timestamp have a defined size, but after 4.1, timestamp is equal to... datetime

* so, conversion is automatic when you import your dump in new mysql version, but your script cannot echo dates like before.

* this function took one date on DATETIME format, a size of old timestamp format and return the date in old timestamp format before 4.1

* Note : if $date is in TIMESTAMP format, the function return it unaltered. So we can patch our php code before migrate to 4.1

* @param $date a date in DATETIME format, or TIMESTAMP format after mysql 4.1

* @param $size a size of old TIMESTAMP format (14,12,10,8,6,4 or 2)

* @return the date in old TIMESTAMP format

*/

function mysql_convert_timestamp_to_previous_usage($date, $size) {

    $tab=split('[- :]', $date);

    if (sizeof($tab) != 6) return $date;

    if (($size!= 8) && ($size!= 14)) {

        $tab[0]=substr($tab[0], -2);

    }

    for ($i = 12; $i>$size; $i-=2) {

        unset($tab[sizeof($tab)-1]);

    }

    if ($size== 8) unset($tab[sizeof($tab)-1]);

    return implode("", $tab);

}

jimmy dot a dot gunawan at gmail dot com
04-Jul-2005 03:18


I almost desperately connect my apache 2.0.54 winversion to PHP 5.0.4 and MYSQL 4.1.12 under windows, since there are lack of any accurate information to setup them properly.

If you got any error message said: "Call to undefined function mysql_connect()", here is the right tips.



Apache installed in c:\apache\apachegroup

PHP installed in c:\php

Mysql installed in c:\mysql



1. Find and add c:\apache\apachegroup\conf\httpd.conf

    with:

      ScriptAlias /php/ "c:/php/"

      AddType application/x-httpd-php .php

      Action application/x-httpd-php "/php/php-cgi.exe"

2. Go to php directory c:\php then rename php.ini-dist to php.ini 

    (or make a new php.ini based on old php.ini file),

    make sure we set the right extension:

       extension_dir = "c:\php\ext" 

       extension=php_mysql.dll



If you setup an php script that call function php_info();

and you can find table:

MySQL Support enabled 

Active Persistent Links  0  

Active Links  0  

Client API version  4.1.7



that mean the mysql.dll properly attached to suppport the php, happy SQL-ing

Microsoul V3
05-Jun-2005 06:47


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.

noel at nettensity dot com
29-May-2005 02:20


More On Windows 2003 Server / IIS 6.0:



It might (probably) be necessary to add both the path to PHP and the path to the extensions dir to your environmental variables.



To do this:

1) Right-Click on 'My Computer'

2) Select 'Properties'

3) Click the 'Advanced' tab

4) At the bottom of this dialog box, click the 'Environmental Variables' button

5) In the frame titled 'System Variables', scroll-down until you find 'Path'

6)Add your path to PHP and your path to the extensions folder i.e. c:\php;c:\php\ext. Make sure all the paths are seperated by semi-colons, and make sure not to erase any of the other paths already in there.



I meant to include this in my note below, apologies for any confusion.

noel at nettensity dot com
29-May-2005 01:04


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:

<? 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!

disappear dot nz at gmail dot com
22-May-2005 12:39


Hi,



this is a small OOP script to provide an easy connction to the mysql db and to get associative / logical / both arrays.



<?php



    class mysql_array

    {

    

        public function __construct ( $s_host , $s_user , $s_pass , $s_db )

        {

            $this -> r_conn = mysql_connect ( $s_host , $s_user , $s_pass ) or die ( mysql_error ( ) ) ;

            mysql_select_db ( $s_db ) ;

        }

        

        private function array_make ( $s_sql , $i_type )

        {

            $r_rs = mysql_query ( $s_sql , $this -> r_conn ) or die ( mysql_error ( ) ) ;

            while ( $a_col = mysql_fetch_array ( $r_rs , $i_type ) )

            {

                $a_rs [ ] = $a_col ;

            }

            mysql_free_result ( $r_rs ) ;

            return ( $a_rs ) ;

        }



        public function array_logic ( $s_sql )

        {

            $a_rs = $this -> array_make ( $s_sql , MYSQL_NUM ) ;

            return ( $a_rs ) ;

        }



        public function array_assoc ( $s_sql )

        {

            $a_rs = $this -> array_make ( $s_sql , MYSQL_ASSOC ) ;

            return ( $a_rs ) ;

        }



        public function array_both ( $s_sql )

        {

            $a_rs = $this -> array_make ( $s_sql , MYSQL_BOTH ) ;

            return ( $a_rs ) ;

        }



    }



    $o_mysql = new mysql_array ( 'localhost' , 'user' , 'pass' , 'db' ) ;    

    $s_sql = "SHOW TABLES" ;

    $a_rs = $o_mysql -> array_assoc ( $s_sql ) ;

    

    echo '<pre>' ;

    print_r ( $a_rs ) ;



?>

medic at setiherbipolis dot de
15-May-2005 11:42


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();

Chad
07-May-2005 10:58


I had the same problem with the new Mac OS Tiger Server. Refer to http://dev.mysql.com/doc/mysql/en/old-client.html



Worked for me.

06-Mar-2005 03:01


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.

Protik Mukherjee
04-Mar-2005 01:34


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!

tumaine no at spam verizon net
24-Dec-2004 12:21


I had a hard time with upgrading to php version 5.2.0 in Windows XP Pro since mySQL queries all of a sudden stopped working and led to blank pages on my site.  I spent a good half day searching google trying to figure out this problem, and didn't quite know how compiling PHP would help me.  It is not necessary.  Set up PHP manually with the ZIP folder download.  



This is a good link to read and wish I found it earlier:



http://www.zend.com/manual/install.windows.extensions.php



If you are getting an error popup about not being able to load some mysql.dll when starting apache, you need to change this in your php.ini file:



extension_dir = "./" to something like "c:\php\ext"

 

Also what I was doing wrong was that I forgot to uncomment the following line in my php.ini file:



extension=php_mysql.dll



Restart apache, and everything should work.



Thought that I could save someone time and frustration when upgrading, since versions 5+ do not include mySQL support by default as earlier versions apparently do.

jon at mysql dot com
11-Dec-2004 06:32


Re Pat's note: You can add the --old-passwords option in the [mysqld] section of your MySQL my.cnf or my.ini configuration file. This option will force the MySQL server  to use the old-style password hashing for all connections. This is not really recommended, as it's less secure, but will allow you to use existing accounts without resetting the passwords.



Of course, as already mentioned, you can use the MySQL OLD_PASSWORD() function instead to handle this issue on an account-by-account basis.



The optimal solution when migrating to MySQL 4.1+ from a previous version is to upgrade to PHP 5 (if you're not using it already) and rewrite any code accessing MySQL using the mysqli extension, which is more secure and provides a much better API.



For more information, see the MySQL Manual: http://dev.mysql.com/doc/mysql/en/Application_password_use.html

lkujala at uniserve dot com
19-Nov-2004 05:43


PROBLEM:


Error Message: the specified module could not be found. 


When trying to load a php_mysql.dll / php_mysqli.dll / php_mssql.dll extension on a Windows platform.





CAUSE:


The standard windows installer package is rather incomplete; it does not include any of the DLL's needed for the optional extensions. In order to use any extension you need to install the FULL zip distribution (unless you like fooling around with dll hell), not just the php_*.dll extensions. You might as well include ALL of the DLL's since the dependencies as documented are wrong (i.e. you need more than libmysql.dll for the php_mysql.dll to load).





I did find the standard windows installer useful for the inital setup though.

nleippe at integr8ted dot com
13-Oct-2004 06:22


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)

Melvin Nava: e-4(at)venezolano.web.ve
13-Sep-2004 03:02


To count page hits, just put next code in a text file and include it in every one of your pages. It will log even different querystrings as different pages. (a MySQL database and table is needed first)



This can be a pretty good example of what you can do with PHP and MySQL. I made this script to log and show all hits in: http://www.venezolano.web.ve/



<?php

/************************

This needs a MySQL table you can create with this:



CREATE TABLE `stats_pagecounter` (

  `id` int(25) NOT NULL auto_increment,

  `page_name` varchar(255) NOT NULL default '',

  `page_hits` int(25) NOT NULL default '0',

  PRIMARY KEY  (`id`)

) TYPE=MyISAM; 



**************************

COUNTING STARTS

*************************/

function page_count($page) {

    $c_link        = mysql_connect('localhost', 'username', 'password')

        or die('counter CONNECT error: '.mysql_errno().', '.mysql_error());

    mysql_select_db('database_name');

    $c_query    = "SELECT * FROM stats_pagecounter

        WHERE (page_name = '$page')";

    $c_result    = mysql_query($c_query, $c_link) 

        or die('counter SELECT error: '.mysql_errno().', '.mysql_error());

    if (mysql_num_rows($c_result)) {

        $row=mysql_fetch_array($c_result,MYSQL_ASSOC);

        $pcounter = $row['page_hits']+1;

        $c_update = "UPDATE stats_pagecounter 

            SET page_hits = '$pcounter' WHERE page_name = '$page'";

        $c_hit = mysql_query($c_update, $c_link) 

            or die('counter UPDATE error: '.mysql_errno().', '.mysql_error());

    } else {

        $c_insert = "INSERT INTO stats_pagecounter

            VALUES ( '0', '$page', '1')";

        $c_page = mysql_query($c_insert, $c_link) 

            or die('counter INSERT error: '.mysql_errno().', '.mysql_error());

        $pcounter = 1;

    }

    mysql_free_result($c_result);

    mysql_close($c_link);

    return $pcounter;

}

$phpself_url = $_SERVER['SERVER_NAME'].

    $_SERVER['PHP_SELF'].'?'.

    $_SERVER['QUERY_STRING'];

$page_hits = page_count($phpself_url);

/************************

COUNTING ENDS

*************************/



/************************

Put next line in a page to show his page hits

(If you want to)

************************/

echo $page_hits;

?>

aidan at php dot net
15-Aug-2004 08:59


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



http://aidan.dotgeek.org/lib/?file=function.mysql_draw_table.php

irn-bru at gmx dot de
27-May-2004 08:27


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

Pat
23-Jan-2004 07:02


[Editor Note:


The password hashing was updated in MySQL 4.1, you must use the MySQLi extension with MySQL 4.1+ (or use the following method to allow


pre 4.1 clients to connect).]





MySQL 5.0 has a new password system, and PHP cannot connect to it because it cannot send a correct password.  You must use the MySQL command OLD_PASSWORD() when adding a user to the database, or PHP cannot connect as of the library that comes with PHP 5.0Beta3

gyohng at netscape dot net
21-Jun-2003 01:16


The following page contains a complete easy to read tutorial of MySQL programming with PHP.



http://www.yohng.com/phpm/

soren at byu dot edu
15-Mar-2003 05:23


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:





<? 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.

past at sbox dot tugraz dot at
22-Feb-2003 06:17


As MySQL docs say, RAND() is not very usefull for generation of randomized result orders.



But this worked for me on Linux, however: 

Somewhere before:

mt_srand((double)microtime()*1000000);

 

"SELECT *, " RAND(".mt_rand(0,86622340).")*10000%100 AS randomvalue ORDER BY randomvalue"



The upper value for mt_rand() has to be Quite Big to see any effect on MySQL's RAND(). The exact number shouldn't be significant. Note the multiplication and modulo; MySQL seems to count steadily upwards when generating random numbers, so we take some numbers from between.

mijnpc at xs4all dot nl
21-Nov-2002 06:33


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 (-:

16-Jun-2002 03:38


Regarding transactions, you must use a recent MySQL version which supports InnoDB tables. you should read the mysql manual (the part about Innodb tables, section 7.5) and configure your server to use them. 

Some reading about how it works: 

http://php.weblogs.com/discuss/msgReader$1446?mode=topic

(Click where it says Part2, I can't put the direct URL here because it is too long)



Then in PHP you use commands like:



mysql_query("BEGIN");

mysql_query("COMMIT");

mysql_query("ROLLBACK");



You must make sure that you convert your existing tables to innodb or create new ones: CREATE TABLE (...) type=innodb;

jeyoung at priscimon dot com
26-Apr-2002 12:23


[Ed. Note:


This may be due to the fact that subsequent calls to mysql_connect with the same parameters return the same resource id for the connection, so in reality it is using the same connection.  In order to force a new link, you must specify the new_link parameter in mysql_connect.]





MySQL transactions





MySQL supports transactions on tables that are of type InnoDB. I have noticed a behaviour which is puzzling me when using transactions.





If I establish two connections within the same PHP page, start a transaction in the first connection and execute an INSERT query in the second one, and rollback the transaction in the first connection, the INSERT query in the second connection is also rolled-back. 





I am assuming that a MySQL transaction is not bound by the connection within which it is set up, but rather by the PHP process that sets it up.





This is a very useful "mis-feature" (bug?) because it allows you to create something like this:





class Transaction {


  var $dbh;





  function Transaction($host, $username, $password) {


    $this->dbh = mysql_connect($host, $username, $password);


  }





  function _Transaction() {


     mysql_disconnect($this->dbh);


  }





  function begin() {


    mysql_query("BEGIN", $this->dbh);


  }





  function rollback() {


     mysql_query("ROLLBACK", $this->dbh);


  }





  function commit() {


    mysql_query("COMMIT", $this->dbh);


  }


}





which you could use to wrap around transactional statements like this:





$tx =& new Transaction("localhost", "username", "password");


$tx->begin();


$dbh = mysql_connect("localhost", "username", "password");


$result = mysql_query("INSERT ...");


if (!$result) {


  $tx->rollback();


} else {


  $tx->commit();


}


mysql_disconnect($dbh);


unset($tx);





The benefit of such a Transaction class is that it is generic and can wrap around any of your MySQL statements.

nospam at nospam dot nos
20-Nov-2001 01:17


ever wanted to know the date a table was last updated? use this:





$info = mysql_fetch_array(mysql_query("show table status from databasename like 'tablename'"));


echo $info["Update_time"];

skelley at diff dot nl
26-Sep-2001 05:11


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]

mbabcock-php at fibrespeed dot net
29-Jul-2001 10:41


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.

philip at cornado dot com
24-Jul-2001 03:24


If you're new to this, you really should learn basic SQL before moving on.  PHP != SQL. Here's are a few good basic SQL tutorials:





  * http://www.sqlcourse.com/


  * http://www.w3schools.com/sql/


  * http://www.oreillynet.com/pub/ct/19

mw-php at ender dot com
22-Jun-2001 12:11


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.

add a note