Delicious Bookmark this on Delicious Share on Facebook SlashdotSlashdot It! Digg! Digg



PHP : Function Reference : MySQL Functions

MySQL Functions

Introduction

These functions allow you to access MySQL database servers. More information about MySQL can be found at » http://www.mysql.com/.

Documentation for MySQL can be found at » http://dev.mysql.com/doc/.

Requirements

In order to have these functions available, you must compile PHP with MySQL support.

Installation

For compiling, simply use the --with-mysql[=DIR] configuration option where the optional [DIR] points to the MySQL installation directory.

Although this MySQL extension is compatible with MySQL 4.1.0 and greater, it doesn't support the extra functionality that these versions provide. For that, use the MySQLi extension.

If you would like to install the mysql extension along with the mysqli extension you have to use the same client library to avoid any conflicts.

Installation on Linux Systems

PHP 4

The option --with-mysql is enabled by default. This default behavior may be disabled with the --without-mysql configure option. If MySQL is enabled without specifying the path to the MySQL install DIR, PHP will use the bundled MySQL client libraries.

Users who run other applications that use MySQL (for example, auth-mysql) should not use the bundled library, but rather specify the path to MySQL's install directory, like so: --with-mysql=/path/to/mysql. This will force PHP to use the client libraries installed by MySQL, thus avoiding any conflicts.

PHP 5+

MySQL is not enabled by default, nor is the MySQL library bundled with PHP. Read this FAQ for details on why. Use the --with-mysql[=DIR] configure option to include MySQL support. You can download headers and libraries from » MySQL.

Installation on Windows Systems

PHP 4

The PHP MySQL extension is compiled into PHP.

PHP 5+

MySQL is no longer enabled by default, so the php_mysql.dll DLL must be enabled inside of php.ini. Also, PHP needs access to the MySQL client library. A file named libmysql.dll is included in the Windows PHP distribution and in order for PHP to talk to MySQL this file needs to be available to the Windows systems PATH. See the FAQ titled "How do I add my PHP directory to the PATH on Windows" for information on how to do this. Although copying libmysql.dll to the Windows system directory also works (because the system directory is by default in the system's PATH), it's not recommended.

As with enabling any PHP extension (such as php_mysql.dll), the PHP directive extension_dir should be set to the directory where the PHP extensions are located. See also the Manual Windows Installation Instructions. An example extension_dir value for PHP 5 is c:\php\ext

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.

MySQL Installation Notes

Warning:

Crashes and startup problems of PHP may be encountered when loading this extension in conjunction with the recode extension. See the recode extension for more information.

Note:

If you need charsets other than latin (default), you have to install external (not bundled) libmysql with compiled charset support.

Runtime Configuration

The behaviour of these functions is affected by settings in php.ini.

Table 181. MySQL Configuration Options

Name Default Changeable Changelog
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 Available since PHP 4.3.0.
mysql.default_port NULL PHP_INI_ALL  
mysql.default_socket NULL PHP_INI_ALL Available since 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_INI_SYSTEM in PHP <= 4.3.2. Available since PHP 4.3.0.


For further details and definitions of the PHP_INI_* constants, see the Appendix I, php.ini directives.

Here's a short explanation of the configuration directives.

mysql.allow_persistent boolean

Whether to allow persistent connections to MySQL.

mysql.max_persistent integer

The maximum number of persistent MySQL connections per process.

mysql.max_links integer

The maximum number of MySQL connections per process, including persistent connections.

mysql.trace_mode boolean

Trace mode. When mysql.trace_mode is enabled, warnings for table/index scans, non free result sets, and SQL-Errors will be displayed. (Introduced in PHP 4.3.0)

mysql.default_port string

The default TCP port number to use when connecting to the database server if no other port is specified. If no default is specified, the port will be obtained from the MYSQL_TCP_PORT environment variable, the mysql-tcp entry in /etc/services or the compile-time MYSQL_PORT constant, in that order. Win32 will only use the MYSQL_PORT constant.

mysql.default_socket string

The default socket name to use when connecting to a local database server if no other socket name is specified.

mysql.default_host string

The default server host to use when connecting to the database server if no other host is specified. Doesn't apply in SQL safe mode.

mysql.default_user string

The default user name to use when connecting to the database server if no other name is specified. Doesn't apply in SQL safe mode.

mysql.default_password string

The default password to use when connecting to the database server if no other password is specified. Doesn't apply in SQL safe mode.

mysql.connect_timeout integer

Connect timeout in seconds. On Linux this timeout is also used for waiting for the first answer from the server.

Resource Types

There are two resource types used in the MySQL module. The first one is the link identifier for a database connection, the second a resource which holds the result of a query.

Predefined Constants

The constants below are defined by this extension, and will only be available when the extension has either been compiled into PHP or dynamically loaded at runtime.

Since PHP 4.3.0 it is possible to specify additional client flags for the mysql_connect() and mysql_pconnect() functions. The following constants are defined:

Table 182. MySQL client constants

Constant Description
MYSQL_CLIENT_COMPRESS Use compression protocol
MYSQL_CLIENT_IGNORE_SPACE Allow space after function names
MYSQL_CLIENT_INTERACTIVE Allow interactive_timeout seconds (instead of wait_timeout) of inactivity before closing the connection.
MYSQL_CLIENT_SSL Use SSL encryption. This flag is only available with version 4.x of the MySQL client library or newer. Version 3.23.x is bundled both with PHP 4 and Windows binaries of PHP 5.


The function mysql_fetch_array() uses a constant for the different types of result arrays. The following constants are defined:

Table 183. MySQL fetch constants

Constant Description
MYSQL_ASSOC Columns are returned into the array having the fieldname as the array index.
MYSQL_BOTH Columns are returned into the array having both a numerical index and the fieldname as the array index.
MYSQL_NUM Columns are returned into the array having a numerical index to the fields. This index starts with 0, the first field in the result.


Notes

Note:

Most MySQL functions accept link_identifier as the last optional parameter. If it is not provided, last opened connection is used. If it doesn't exist, connection is tried to establish with default parameters defined in php.ini. If it is not successful, functions return FALSE.

Examples

This simple example shows how to connect, execute a query, print resulting rows and disconnect from a MySQL database.

Example 1410. MySQL extension overview example

copy to clipboard
<?php
// Connecting, selecting database
$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');

// Performing SQL query
$query = 'SELECT * FROM my_table';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

// Printing results in 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";

// Free resultset
mysql_free_result($result);

// Closing connection
mysql_close($link);
?>


Table of Contents

mysql_affected_rows — Get number of affected rows in previous MySQL operation
mysql_change_user — Change logged in user of the active connection
mysql_client_encoding — Returns the name of the character set
mysql_close — Close MySQL connection
mysql_connect — Open a connection to a MySQL Server
mysql_create_db — Create a MySQL database
mysql_data_seek — Move internal result pointer
mysql_db_name — Get result data
mysql_db_query — Send a MySQL query
mysql_drop_db — Drop (delete) a MySQL database
mysql_errno — Returns the numerical value of the error message from previous MySQL operation
mysql_error — Returns the text of the error message from previous MySQL operation
mysql_escape_string — Escapes a string for use in a mysql_query
mysql_fetch_array — Fetch a result row as an associative array, a numeric array, or both
mysql_fetch_assoc — Fetch a result row as an associative array
mysql_fetch_field — Get column information from a result and return as an object
mysql_fetch_lengths — Get the length of each output in a result
mysql_fetch_object — Fetch a result row as an object
mysql_fetch_row — Get a result row as an enumerated array
mysql_field_flags — Get the flags associated with the specified field in a result
mysql_field_len — Returns the length of the specified field
mysql_field_name — Get the name of the specified field in a result
mysql_field_seek — Set result pointer to a specified field offset
mysql_field_table — Get name of the table the specified field is in
mysql_field_type — Get the type of the specified field in a result
mysql_free_result — Free result memory
mysql_get_client_info — Get MySQL client info
mysql_get_host_info — Get MySQL host info
mysql_get_proto_info — Get MySQL protocol info
mysql_get_server_info — Get MySQL server info
mysql_info — Get information about the most recent query
mysql_insert_id — Get the ID generated from the previous INSERT operation
mysql_list_dbs — List databases available on a MySQL server
mysql_list_fields — List MySQL table fields
mysql_list_processes — List MySQL processes
mysql_list_tables — List tables in a MySQL database
mysql_num_fields — Get number of fields in result
mysql_num_rows — Get number of rows in result
mysql_pconnect — Open a persistent connection to a MySQL server
mysql_ping — Ping a server connection or reconnect if there is no connection
mysql_query — Send a MySQL query
mysql_real_escape_string — Escapes special characters in a string for use in a SQL statement
mysql_result — Get result data
mysql_select_db — Select a MySQL database
mysql_set_charset — Sets the client character set
mysql_stat — Get current system status
mysql_tablename — Get table name of field
mysql_thread_id — Return the current thread ID
mysql_unbuffered_query — Send an SQL query to MySQL, without fetching and buffering the result rows

Code Examples / Notes » ref.mysql

allan666
[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;
           }
       }
   }
}
?>

pat
[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

jeyoung
[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.

yp
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

nleippe
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
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;
?>

nobody special
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);
?>

jonathon hibbard
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;
 }
?>

mw-php
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.

gyohng
The following page contains a complete easy to read tutorial of MySQL programming with PHP.
http://www.yohng.com/phpm/

mbabcock-php
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.

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

sabin
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
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!');
 }
?>

duimdog
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;
           }
       }

26-jan-2007 09:14
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).

irn-bru
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

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

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

martijn
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;
}
?>

arjen
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/

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

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

conrad decker
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.

philip
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

aidan
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

php comments of fuzzyworm co., uk
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

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

06-mar-2005 07: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.

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

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

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

nick smith
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.

ravenal
I was working with a friend of mine and he happened wanted to sort data based on the column names, well this function will basically sort the Field Names in ABC order and then from there on it'll add the data based on the field names assigned to...
You can use this to create easily forms and exclude what you don't want to be as entered data or sort it based on what order you want...
<?php
/*
* By Benjamin Willard (c) 2006
*
* Sorts a Field by the Column Name (in ABC Order)
*
* @param string : Table Name of where to Display the Values
* @param integer : Database Link
* (Optional) @param array : This will sort whatever you wish to be displayed on the top of the list
* (Optional) @param array : This will exclude what you do not want displayed
*
* return @param array : Returns an Array of the Column Data
* [Usage]
* mysql_sort_field_column( string tablename, int database_link [, array sortfirst, array exclude] )
* [Example]
* $array = mysql_sort_field_column( "test", $c, array( "id", "name" ), array( "password" ) );
*
* [Output]
* Array
* (
* [0] => id
* [Field] => id
* [1] => int(11)
* [Type] => int(11)
* [2] =>
* [Null] =>
* [3] => PRI
* [Key] => PRI
* [4] =>
* [Default] =>
* [5] => auto_increment
* [Extra] => auto_increment
* )
* [id] => 1;
*
*/
function mysql_sort_field_column( $tablename, $link, $sortfirst = '', $exclude = '' )
{        
   $show=mysql_query("SHOW COLUMNS FROM $tablename", $link);
   
   $array=array();
   
   while( $fetch = mysql_fetch_array($show ) )
   {
       $array[$fetch['Field']] = $fetch;
   }
   
   ksort($array);
   
   if( $sortfirst )
   {
       if( is_array($sortfirst) )
       {
           $newarray = array();
           foreach( $sortfirst AS $i2 => $v )
               $newarray[$v] = $array[$v];
           
           foreach( $array AS $index => $val2 )
           {
               if( in_array($index, $sortfirst ) )
                   continue;
                   
               $newarray[$index] = $val2;
           }
           
           $array = $newarray;
       }
   }
   
   if( $exclude )
   {
       if( is_array($exclude) )
       {
           $out = array();
           
           foreach( $array AS $index2 => $val2 )
           {
               if( in_array( $index2, $exclude ) )
                   continue;
                   
               $out[$index2] = $val2;
           }
           
           $array = $out;
       }
   }
   
   return $array;
}
?>

microsoul v3
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.

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

davesteinb
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>

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

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

tumaine no
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.

jimmy dot a dot gunawan
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

avis_del
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

disappear dot nz
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 ) ;
?>

nathan brizzee
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!

skelley
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]

admin
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 '<b>MySQL Error</b>: '.mysql_error().'<br/>';
}
}
//+======================================================+
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 '<b>MySQL Error</b>: 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);
}
}
//+======================================================+
}
/* Example */
$DB = new mysql_db();
$DB->sql_connect('sql_host', 'sql_user', 'sql_password', 'sql_database_name');
$DB->query("SELECT * FROM `members`");
$DB->sql_close();
?>

mega-squall
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.

jeremy hepler
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.

protik mukherjee
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!

nospam
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"];

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

tomasz dot rup
Correct algorithm to generate MySQL password hash is:
<?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);
               $nr &= 0x7fffffff;
               $nr2 += ($nr2 << 8) ^ $nr;
               $nr2 &= 0x7fffffff;
               $add += $charVal;
       }
       return sprintf("%08x%08x", $nr, $nr2);
}
?>

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

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

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

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

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

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

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

rad14701
@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...

amanda
// Connecting, selecting database
$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')
   or die('Could not connect: ' . mysql_error());
echo 'Connected successfully';
I'm confused about this part of the code. If you don't connect to the MySQL server, how will it know what error code to report back? Do you not need an active connection?

bahatest
/*
* 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);
}

sid trivedi
<?php
/*
MySQL (Community) Server Installation on 32-bit Windows XP running Apache
On Windows, the recommended way to run MySQL is to install it as a Windows service, whereby MySQL starts and stops automatically when Windows starts and stops. A MySQL server installed as a service can also be controlled from the command line commands, or with the graphical Services utility like phpMyAdmin.
PHP ---> MySQL CONNECTORS (php_mysql.dll and php_mysqli.dll as extensions)
MySQL provides the mysql and mysqli extensions for the Windows operating system on http://dev.mysql.com/downloads/connector/php/ for MySQL version 4.1.16 and higher, MySQL 5.0.18, and MySQL 5.1. As with enabling any PHP extension in php.ini (such as php_mysql.dll), the PHP directive extension_dir should be set to the directory where the PHP extensions are located.
MySQL is no longer enabled by default, so the php_mysql.dll DLL must be enabled inside of php.ini. Also, PHP needs access to the MySQL client library. A file named libmysql.dll is included in the Windows PHP distribution and in order for PHP to talk to MySQL this file needs to be available to the Windows systems PATH.
Following PHP Script is useful to test PHP connection with MySQL.
*/
//$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();
?>

Change Language


Follow Navioo On Twitter
.NET Functions
Apache-specific Functions
Alternative PHP Cache
Advanced PHP debugger
Array Functions
Aspell functions [deprecated]
BBCode Functions
BCMath Arbitrary Precision Mathematics Functions
PHP bytecode Compiler
Bzip2 Compression Functions
Calendar Functions
CCVS API Functions [deprecated]
Class/Object Functions
Classkit Functions
ClibPDF Functions [deprecated]
COM and .Net (Windows)
Crack Functions
Character Type Functions
CURL
Cybercash Payment Functions
Credit Mutuel CyberMUT functions
Cyrus IMAP administration Functions
Date and Time Functions
DB++ Functions
Database (dbm-style) Abstraction Layer Functions
dBase Functions
DBM Functions [deprecated]
dbx Functions
Direct IO Functions
Directory Functions
DOM Functions
DOM XML Functions
enchant Functions
Error Handling and Logging Functions
Exif Functions
Expect Functions
File Alteration Monitor Functions
Forms Data Format Functions
Fileinfo Functions
filePro Functions
Filesystem Functions
Filter Functions
Firebird/InterBase Functions
Firebird/Interbase Functions (PDO_FIREBIRD)
FriBiDi Functions
FrontBase Functions
FTP Functions
Function Handling Functions
GeoIP Functions
Gettext Functions
GMP Functions
gnupg Functions
Net_Gopher
Haru PDF Functions
hash Functions
HTTP
Hyperwave Functions
Hyperwave API Functions
i18n Functions
IBM Functions (PDO_IBM)
IBM DB2
iconv Functions
ID3 Functions
IIS Administration Functions
Image Functions
Imagick Image Library
IMAP
Informix Functions
Informix Functions (PDO_INFORMIX)
Ingres II Functions
IRC Gateway Functions
PHP / Java Integration
JSON Functions
KADM5
LDAP Functions
libxml Functions
Lotus Notes Functions
LZF Functions
Mail Functions
Mailparse Functions
Mathematical Functions
MaxDB PHP Extension
MCAL Functions
Mcrypt Encryption Functions
MCVE (Monetra) Payment Functions
Memcache Functions
Mhash Functions
Mimetype Functions
Ming functions for Flash
Miscellaneous Functions
mnoGoSearch Functions
Microsoft SQL Server Functions
Microsoft SQL Server and Sybase Functions (PDO_DBLIB)
Mohawk Software Session Handler Functions
mSQL Functions
Multibyte String Functions
muscat Functions
MySQL Functions
MySQL Functions (PDO_MYSQL)
MySQL Improved Extension
Ncurses Terminal Screen Control Functions
Network Functions
Newt Functions
NSAPI-specific Functions
Object Aggregation/Composition Functions
Object property and method call overloading
Oracle Functions
ODBC Functions (Unified)
ODBC and DB2 Functions (PDO_ODBC)
oggvorbis
OpenAL Audio Bindings
OpenSSL Functions
Oracle Functions [deprecated]
Oracle Functions (PDO_OCI)
Output Control Functions
Ovrimos SQL Functions
Paradox File Access
Parsekit Functions
Process Control Functions
Regular Expression Functions (Perl-Compatible)
PDF Functions
PDO Functions
Phar archive stream and classes
PHP Options&Information
POSIX Functions
Regular Expression Functions (POSIX Extended)
PostgreSQL Functions
PostgreSQL Functions (PDO_PGSQL)
Printer Functions
Program Execution Functions
PostScript document creation
Pspell Functions
qtdom Functions
Radius
Rar Functions
GNU Readline
GNU Recode Functions
RPM Header Reading Functions
runkit Functions
SAM - Simple Asynchronous Messaging
Satellite CORBA client extension [deprecated]
SCA Functions
SDO Functions
SDO XML Data Access Service Functions
SDO Relational Data Access Service Functions
Semaphore
SESAM Database Functions
PostgreSQL Session Save Handler
Session Handling Functions
Shared Memory Functions
SimpleXML functions
SNMP Functions
SOAP Functions
Socket Functions
Standard PHP Library (SPL) Functions
SQLite Functions
SQLite Functions (PDO_SQLITE)
Secure Shell2 Functions
Statistics Functions
Stream Functions
String Functions
Subversion Functions
Shockwave Flash Functions
Swish Functions
Sybase Functions
TCP Wrappers Functions
Tidy Functions
Tokenizer Functions
Unicode Functions
URL Functions
Variable Handling Functions
Verisign Payflow Pro Functions
vpopmail Functions
W32api Functions
WDDX Functions
win32ps Functions
win32service Functions
xattr Functions
xdiff Functions
XML Parser Functions
XML-RPC Functions
XMLReader functions
XMLWriter Functions
XSL functions
XSLT Functions
YAZ Functions
YP/NIS Functions
Zip File Functions
Zlib Compression Functions
Terms of Service Privacy Policy Sitemap

©2024 -EPSILONSYS SOFTWARE inc

Other EPSILONSYS SOFTWARE Web Sites : Programming Resources | News Headline | Free local classifieds

eXTReMe Tracker