A quick way to extract a mysql query result into variables, without using
an array:

$result = mysql_query("select model,color,price from cars where
id=$id");

extract(mysql_fetch_array($result));

Now you have the variables $model, $color, $price. You can ask extract()
to prefix it for you, with like
extract(stuff,EXTR_PREFIX_ALL,"r"), and right there you have
$r_model, $r_color...

If you have to pull multiple rows from the query, e.g. using
"while", you have to pull 'em to an array, but still can use
extract() to extract them :)

while ($r = mysql_fetch_array($result)) {
   extract($r);
   ...
}

That was it, maybe it'll help someone..

The easy way to surpress MySQL error messages is to put an "@" in
front of the mysql command.  For example, @mysql_db_query(whatever).

This also works for any other PHP functions. (ed.)

Something to Note:

MySQL uses Unix time functions and has no problems with dates until 2069;
all 2-digit years are regarded to be in the range 1970 to 2069, which
means that if you store 01 in a year column, MySQL treats it as 2001.

A good place to get beginner's info and some good pointers on using MySQL
is: 

Just a note, when using MySQL to output files (ie: a database backup
script) if the directory permissions are not set right, it will not create
the files AND WILL NOT return an error code.  You must have the
permissions set right.

Hope it Helps,

Matthew Walkup
morningstarstudios.net

For one solution to problems with MySQL and special Nordic characters,
please see

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.

The mysql_fetch_row function returns an array with numeric indices.

For example, a row from the query:

SELECT bookid,title,author FROM books
 
would look like:

$record[0]=3
$record[1]="A Farewell To Arms"
$record[2]="Ernest Hemingway"

The mysql_fetch_assoc function returns an array with indices that are the
field names, like such:

$record["bookid"]=3
$record["title"]="A Farewell To Arms"
$record["author"]="Ernest Hemingway"

The mysql_fetch_array function returns an array with both kinds of
indices, so this array looks like:

$record[0]=3
$record["bookid"]=3
$record[1]="A Farewell To Arms"
$record["title"]="A Farewell To Arms"
$record[2]="Ernest Hemingway"
$record["author"]="Ernest Hemingway"

This is the reason for the "doubled" field output from the
example.

Note that the mysql_fetch_array function has an optional parameter where
you can specify the indices.

So replacing the call:

mysql_fetch_array($result)

with:

mysql_fetch_array($result,MYSQL_NUM)

would be an identical fix to:

mysql_fetch_row($result)

Hope this helps!

It looks like the reason for the double vision is so that when you want to
use the sql results, you can call the array elements via either indices
(e.g. $line[0], $line[1], etc.) or by their database field names (e.g.
$line["uid"], $line["pw"], $line["gid"],
etc.).

You can see this by changing the example to print $col_name as well as
$col_value. Each repeated val shows up with a different key (of course)
each time.

Hi,

The problem with
extract(mysql_fetch_array($result)); 
is that it actually fetches two arrays, a number index array ($arr[0],
$arr[1], $arr[2], ...) and an associative (hash) array,
($arr["field1"], $arr["field2"],
$arr["field3"], ...), thus $arr actually contains two sets of
data (as you have observed).

If you then try to push out all the data from $arr without explicitly
naming those field ids, then you WILL get your data appearing twice.

The full definition for mysql_fetch_array is as follows:
mysql_fetch_array ($result_id, [MYSQL_NUM|MYSQL_ASSOC])
Since MYSQL_NUM is equivalent to mysql_fetch_row, you have found that
switching to mysql_fetch_row works fine.
In fact, the only real reason you may want to use mysql_fetch_array is
with the MYSQL_ASSOC option, as this allows you to reference the fields in
the associative array returned.

If you're new to this then you should learn/focus on plain old SQL, as PHP
!= SQL.  
Here's are a few good basic tutorials:

  
  
  

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.

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

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

Easy Oracle-Connection with my Class for Oracle/mySQL/MSSQL!
my cDBC-Klass is free and with Documentation and sources.

visit my Site: 

I find it the least problematic to simply use the list() construct to
assign variables from an array returned from the mysql_fetch_row() &
others.

$data = mysql_query("SELECT name, data FROM table");
while(list($t_name, $t_data) = mysql_fetch_row($data)) {
   echo "Name: $t_name    Data: $t_data\n";
}

It's not too difficult to maintain something like this, since the list()
variables mirror the fields being queried.  It gets messy when you have
more than 10 fields you are working with.

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.

in dem obigen beispiel muss das passwort in die php datei reingeschrieben
werden. ist das nicht eine sicherheitsl�cke? was passiert wenn das php
irgendwie versagt und die datei vom server als text datei ausgegeben wird?
dann ist es doch m�glich das passwort einfach rauszulesen?!

I had some difficulties installing PHP with MySQL support on RedHat-7.1,
but it works at last. :)  Here are a few of the unexpected things I had to
do to get it working:
After installing apache, mysql, and php4, I found a php-mysql rpm, since
the php4 rpm lacks MySQL support.  (I also had to find a few more rpms
before this one would install.) Then, I had to make sure and export the
path of the library libgcc_s.so (export LD_LIBRARY_PATH=/usr/local/lib)
before running the Apache httpd.  Otherwise PHP fails to load the MySQL
library, which apparently depends on libgcc_s.so, but doesn't give much
useful feedback except that the MySQL-specific commands you're using are
unrecognized.  I hope this saves someone else from going through the hours
of hassle it took me to figure it out!

just a little FYI, when installing PHP from an RPM.  In order to get MySql
support you need to download the php-mysql*.rpm.  If you don't install the
php-mysql*.rpm you'll just get the common error: Call to undefined
function: mysql_connect().

To protect your mysql server from long running query which hangs the
database 

This PHP code basically kills the long running sql process . 

I kept this PHP file in cron to run every 15 minues and believe me 
it had dramatic performance on my website and my mysql database NEVER
HANGED 

//################ //

kill_long_query.php
//################ //

set_time_limit(30000); 

$result=mysql_query("show processlist"); 

while ($row=mysql_fetch_array($result)) 
{ 
$process_id=$row["Id"]; 
if (($row["Time"] > 100 ) ||
($row["Command"]=="Sleep") ) 
{ 
print $row["Id"]; 
$sql="kill $process_id"; 
mysql_query($sql); 
} 

} 
//###################//

//###################//

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: 

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

This may be useful to some people - it creates an update query using the
form data passed to it.  

$link = mysql_connect("localhost", "username",
"password");
	mysql_select_db("database");
	$fields = mysql_list_fields("database", "tablename",
$link);
	$columns = mysql_num_fields($fields);

$SQL = "UPDATE tablename SET ";
	foreach ($_POST as $key => $value)
	{
		if ($value != "")
		{
			for ($i = 0; $i < $columns; $i++)
			{
				if(mysql_field_name($fields, $i) == $key)
				{
					if ($i == 0)
					{
						if (is_numeric($value))
						{
							$SQL = $SQL . "$key = $value";
						}
						else
						{
							$SQL = $SQL . "$key = '$value'";
						}
					}
					else
					{
						if (is_numeric($value))
						{
							$SQL = $SQL . ",$key = $value";
						}
						else
						{
							$SQL = $SQL . ",$key = '$value'";
						}
					}
				}
			}
		}
	}
	$result = mysql_query($SQL);

The shortest way to execute a MySQL query in PHP is by using the mysql
function. 

Example:

<?php
$conn=mysql_connect("$dbhost","$dbuser","$dbpass");
$result=mysql("$dbname","$dbquery");
mysql_close($conn);
?>

This way you can work directly with multiple databases.

Hello! I've got a question: How to fetch auto_increment field after INSERT
was executed i order to obtain the new record's ID.