<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<title>Database issues</title>
</head>
<body><div class="manualnavbar" style="text-align: center;">
<div class="prev" style="text-align: left; float: left;"><a href="faq.obtaining.html">Obtaining PHP</a></div>
<div class="next" style="text-align: right; float: right;"><a href="faq.installation.html">Installation</a></div>
<div class="up"><a href="faq.html">FAQ</a></div>
<div class="home"><a href="index.html">PHP Manual</a></div>
</div><hr /><div id="faq.databases" class="chapter">
<h1>Database issues</h1>
<p class="para">
This section holds common questions about relation between
PHP and databases. Yes, PHP can access virtually any
database available today.
</p>
<div class="qandaset"><ol class="qandaset_questions"><li><a href="#faq.databases.mssql">
I heard it's possible to access Microsoft SQL Server from PHP. How?
</a></li><li><a href="#faq.databases.access">
Can I access Microsoft Access databases?
</a></li><li><a href="#faq.databases.mysql.deprecated">
Why is the MySQL extension (ext/mysql) that I've been using for over
10 years discouraged from use? Is it deprecated? What do I use instead?
How can I migrate?
</a></li><li><a href="#faq.databases.mysql.php5">
PHP 5 no longer bundles MySQL client libraries, what does this mean to
me? Can I still use MySQL with PHP? I try to use MySQL and get
"function undefined" errors, what gives?
</a></li><li><a href="#faq.databases.shared-mysql">
After installing shared MySQL support, Apache dumps core as soon
as libphp4.so is loaded. Can this be fixed?
</a></li><li><a href="#faq.databases.mysqlresource">
Why do I get an error that looks something like this:
"Warning: 0 is not a MySQL result index in <file>
on line <x>" or "Warning: Supplied argument is not
a valid MySQL result resource in <file> on line <x>"?
</a></li></ol></div>
<dl class="qandaentry" id="faq.databases.mssql">
<dt><strong>
I heard it's possible to access Microsoft SQL Server from PHP. How?
</strong></dt>
<dd class="answer">
<p class="para">
On Windows machines, you can simply use the included ODBC support
and the correct ODBC driver.
</p>
<p class="para">
On Unix machines, you can use the Sybase-CT driver
to access Microsoft SQL Servers because they are (at
least mostly) protocol-compatible. Sybase has made a
<a href="http://www.php.net/extra/ctlib-linux-elf.tar.gz" class="link external">» free version of the necessary
libraries for Linux systems</a>. For other Unix operating
systems, you need to contact Sybase for the correct libraries.
Also see the answer to the next question.
</p>
</dd>
</dl>
<dl class="qandaentry" id="faq.databases.access">
<dt><strong>
Can I access Microsoft Access databases?
</strong></dt>
<dd class="answer">
<p class="para">
Yes. You already have all the tools you need if you are running
entirely under Windows 9x/Me, or NT/2000, where you can use
ODBC and Microsoft's ODBC drivers for Microsoft Access databases.
</p>
<p class="para">
If you are running PHP on a Unix box and want to talk to MS Access
on a Windows box you will need Unix ODBC drivers.
<a href="http://www.openlinksw.com/" class="link external">» OpenLink Software</a> has Unix-based
ODBC drivers that can do this.
</p>
<p class="para">
Another alternative is to use an SQL server that has
Windows ODBC drivers and use that to store the data, which you can
then access from Microsoft Access (using ODBC) and PHP (using the
built in drivers), or to use an intermediary file format that Access
and PHP both understand, such as flat files or dBase databases.
On this point Tim Hayes from OpenLink software writes:
<blockquote class="blockquote">
<p class="para">
Using another database as an intermediary is not a good idea, when you can
use ODBC from PHP straight to your database - i.e. with OpenLink's drivers. If
you do need to use an intermediary file format, OpenLink have now released
Virtuoso (a virtual database engine) for NT, Linux and other Unix platforms.
Please visit our <a href="http://www.openlinksw.com/" class="link external">» website</a> for a free download.
</p>
</blockquote>
</p>
<p class="para">
One option that has proved successful is to use MySQL and its
MyODBC drivers on Windows and synchronizing the databases. Steve Lawrence
writes:
</p>
<p class="para">
<ul class="itemizedlist">
<li class="listitem">
<span class="simpara">
Install MySQL on your platform according to instructions with MySQL.
Latest available from <a href="http://www.mysql.com/" class="link external">» http://www.mysql.com/</a>
No special configuration required except when you set up a database, and configure the
user account, you should put % in the host field, or the host name of the
Windows computer you wish to access MySQL with. Make a note of your server
name, username, and password.
</span>
</li>
<li class="listitem">
<span class="simpara">
Download the MyODBC for Windows driver from the MySQL site.
Install it on your Windows machine. You can test the operation with
the utilities included with this program.
</span>
</li>
<li class="listitem">
<span class="simpara">
Create a user or system dsn in your ODBC administrator, located in the
control panel. Make up a dsn name, enter your hostname, user name, password,
port, etc for you MySQL database configured in step 1.
</span>
</li>
<li class="listitem">
<span class="simpara">
Install Access with a full install, this makes sure you get the proper
add-ins... at the least you will need ODBC support and the linked table
manager.
</span>
</li>
<li class="listitem">
<span class="simpara">
Now the fun part! Create a new access database. In the table
window right click and select Link Tables, or under the file
menu option, select Get External Data and then Link Tables.
When the file browser box comes up, select files of type: ODBC.
Select System dsn and the name of your dsn created in step 3.
Select the table to link, press OK, and presto! You can now
open the table and add/delete/edit data on your MySQL server!
You can also build queries, import/export tables to MySQL,
build forms and reports, etc.
</span>
</li>
</ul>
</p>
<p class="para">
Tips and Tricks:
<ul class="itemizedlist">
<li class="listitem">
<span class="simpara">
You can construct your tables in Access and export them
to MySQL, then link them back in. That makes table
creation quick.
</span>
</li>
<li class="listitem">
<span class="simpara">
When creating tables in Access, you must have a primary
key defined in order to have write access to the table in
access. Make sure you create a primary key in MySQL before
linking in access
</span>
</li>
<li class="listitem">
<span class="simpara">
If you change a table in MySQL, you have to re-link it
in Access. Go to tools>add-ins>linked table manager,
cruise to your ODBC DSN, and select the table to re-link
from there. you can also move your dsn source around there,
just hit the always prompt for new location checkbox before
pressing OK.
</span>
</li>
</ul>
</p>
</dd>
</dl>
<dl class="qandaentry" id="faq.databases.mysql.deprecated">
<dt><strong>
Why is the MySQL extension (ext/mysql) that I've been using for over
10 years discouraged from use? Is it deprecated? What do I use instead?
How can I migrate?
</strong></dt>
<dd class="answer">
<p class="para">
There are three MySQL extensions, as described under the
<a href="mysqlinfo.api.choosing.html" class="link">Choosing a MySQL API</a> section. The old API
should not be used, and one day it will be deprecated and eventually removed from PHP.
It is a popular extension so this will be a slow process, but you are strongly encouraged
to write all new code with either <a href="book.mysqli.html" class="link">mysqli</a> or
<a href="ref.pdo-mysql.html" class="link">PDO_MySQL</a>.
</p>
<p class="para">
Migration scripts are not available at this time, although the mysqli API contains both
a procedural and OOP API, with the procedural version being similar to ext/mysql.
</p>
<p class="para">
It is not possible to mix the extensions. So, for example, passing a mysqli connection to
PDO_MySQL or ext/mysql will not work.
</p>
</dd>
</dl>
<dl class="qandaentry" id="faq.databases.mysql.php5">
<dt><strong>
PHP 5 no longer bundles MySQL client libraries, what does this mean to
me? Can I still use MySQL with PHP? I try to use MySQL and get
"function undefined" errors, what gives?
</strong></dt>
<dd class="answer">
<p class="para">
Yes. There will always be MySQL support in PHP of one kind or
another. The only change in PHP 5 is that we are no longer bundling
the client library itself. Some reasons in no particular order:
</p>
<ul class="itemizedlist">
<li class="listitem">
<p class="para">
Most systems these days already have the client library installed.
</p>
</li>
<li class="listitem">
<p class="para">
Given the above, having multiple versions of the library can get
messy. For example, if you link mod_auth_mysql against one version
and PHP against another, and then enable both in Apache, you get a
nice fat crash. Also, the bundled library didn't always play well
with the installed server version. The most obvious symptom of this
being disagreement over where to find the mysql.socket Unix domain
socket file.
</p>
</li>
<li class="listitem">
<p class="para">
Maintenance was somewhat lax and it was falling further and further
behind the released version.
</p>
</li>
<li class="listitem">
<p class="para">
Future versions of the library are under the GPL and thus we don't
have an upgrade path since we cannot bundle a GPL'ed library in a
BSD/Apache-style licensed project. A clean break in PHP 5 seemed
like the best option.
</p>
</li>
</ul>
<p class="para">
This won't actually affect that many people. Unix users, at least the
ones who know what they are doing, tend to always build PHP against
their system's libmysqlclient library simply by adding the <strong class="option configure">--with-mysql=/usr</strong>
option
when building PHP. Windows users may enable the extension
<var class="filename">php_mysql.dll</var> inside <var class="filename">php.ini</var>.
For more details, see the <a href="ref.mysql.html" class="link">MySQL Reference</a>
for installation instructions. Also, be sure
<var class="filename">libmysql.dll</var> is available to the systems PATH.
For more details on how, read the FAQ on
<a href="faq.installation.html#faq.installation.addtopath" class="link">setting up the Windows
systems PATH</a>. Because <var class="filename">libmysql.dll</var> (and
many other PHP related files) exist in the PHP folder, you'll want to
add the PHP folder to your systems PATH.
</p>
</dd>
</dl>
<dl class="qandaentry" id="faq.databases.shared-mysql">
<dt><strong>
After installing shared MySQL support, Apache dumps core as soon
as libphp4.so is loaded. Can this be fixed?
</strong></dt>
<dd class="answer">
<p class="para">
If your MySQL libs are linked against pthreads this will happen. Check
using ldd. If they are, grab the MySQL tarball and compile from source,
or recompile from the source rpm and remove the switch in the spec file
that turns on the threaded client code. Either of these suggestions will
fix this. Then recompile PHP with the new MySQL libs.
</p>
</dd>
</dl>
<dl class="qandaentry" id="faq.databases.mysqlresource">
<dt><strong>
Why do I get an error that looks something like this:
"Warning: 0 is not a MySQL result index in <file>
on line <x>" or "Warning: Supplied argument is not
a valid MySQL result resource in <file> on line <x>"?
</strong></dt>
<dd class="answer">
<p class="para">
You are trying to use a result identifier that is 0. The 0 indicates
that your query failed for some reason. You need to check for errors
after submitting a query and before you attempt to use the returned
result identifier. The proper way to do this is with code similar
to the following:
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br /><br />$result </span><span style="color: #007700">= </span><span style="color: #0000BB">mysql_query</span><span style="color: #007700">(</span><span style="color: #DD0000">"SELECT * FROM tables_priv"</span><span style="color: #007700">);<br />if (!</span><span style="color: #0000BB">$result</span><span style="color: #007700">) {<br /> echo </span><span style="color: #0000BB">mysql_error</span><span style="color: #007700">();<br /> exit;<br />}<br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
or
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br /><br />$result </span><span style="color: #007700">= </span><span style="color: #0000BB">mysql_query</span><span style="color: #007700">(</span><span style="color: #DD0000">"SELECT * FROM tables_priv"</span><span style="color: #007700">)<br /> or die(</span><span style="color: #DD0000">"Bad query: " </span><span style="color: #007700">. </span><span style="color: #0000BB">mysql_error</span><span style="color: #007700">());<br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
</p>
</dd>
</dl>
</div>
<hr /><div class="manualnavbar" style="text-align: center;">
<div class="prev" style="text-align: left; float: left;"><a href="faq.obtaining.html">Obtaining PHP</a></div>
<div class="next" style="text-align: right; float: right;"><a href="faq.installation.html">Installation</a></div>
<div class="up"><a href="faq.html">FAQ</a></div>
<div class="home"><a href="index.html">PHP Manual</a></div>
</div></body></html>
