|
|
 |
LXIII. MySQL FunktionenEinf�hrung
Diese Funktionen erlauben den Zugriff auf einen MySQL Datenbank-Server.
Weitere Information zu MySQL sind unter erh�ltlich.
Die Dokumentation zu MySQL ist unter zu finden.
Anforderungen
Um diese Funktionen nutzen zu k�nnen, m�ssen Sie PHP mit
MySQL-Unterst�tzung �bersetzen.
Installation
Durch die Angabe der Konfigurationsoption
--with-mysql[=DIR] beim Aufruf von configure
erm�glichen Sie den Zugriff auf MySQL �ber PHP.
Falls Sie den Pfad zu MySQL nicht angeben, verwendet PHP die
internen MySQL-Client Bibliotheken. Seit PHP 4 ist die Unterst�tzung f�r
MySQL standardm��ig immer aktiviert; falls Sie die Konfigurationsoption
weglassen, werden die internen MySQL-Bibliotheken genutzt. Benutzer die
MySQL auch in anderen Anwendungen nutzen m�chten (zum Beispiel: PHP 3 und
PHP 4 als simultane Apache-Module oder auth-mysql) sollten immer den Pfad
zu MySQL mit der Option
-- with-mysql=/pfad/zu/mysql
festlegen. Diese Angabe zwingt PHP die Client-Bibliotheken
zu nutzen, die von MySQL installiert wurden. Diese Vorgehensweise
vermeidet jegliche Konflikte.
The windows version of PHP
has built in support for this extension. You do not need to load any additional
extension in order to use these functions. | Warnung |
Abst�rze und Startprobleme von PHP k�nnen auftreten,
wenn Sie diese Extension zusammen mit der recode-Extension laden.
F�r weitere Informationen schauen Sie bitte bei der
recode Extension nach.
|
Laufzeit Konfiguration
Das Verhalten dieser Funktionen wird von Einstellungen
in der php.ini bestimmt.
Tabelle 1. MySQL Konfigurations-Optionen | Name | Standard | Ver�nderbar |
|---|
| mysql.allow_persistent | "On" | PHP_INI_SYSTEM | | mysql.max_persistent | "-1" | PHP_INI_SYSTEM | | mysql.max_links | "-1" | PHP_INI_SYSTEM | | mysql.default_port | NULL | PHP_INI_ALL | | mysql.default_socket | NULL | PHP_INI_ALL | | mysql.default_host | NULL | PHP_INI_ALL | | mysql.default_user | NULL | PHP_INI_ALL | | mysql.default_password | NULL | PHP_INI_ALL | | mysql.connect_timeout | "0" | PHP_INI_SYSTEM |
Weitere Details und die Definition der PHP_INI_* Konstanten sind unter
ini_set() zu finden.
Beschreibung der Konfigurations-Optionen
- mysql.allow_persistent
boolean
gibt an, ob
persistente
Verbindungen zu MySQL gestattet werden sollen.
- mysql.max_persistent
integer
Die maximale Anzahl von persistenten MySQL Verbindungen pro Prozess.
- mysql.max_links
integer
Die maximale Anzahl von MySQL Verbindungen, einschlie�lich
persistenter, pro Prozess.
- mysql.default_port
string
Gibt die Portnummer an, �ber den die Verbindung zum MySQL-Server
aufgebaut werden soll, wenn kein Port als Verbindungsparameter
�bergeben wurde.
Wird die Portnummer nicht angegeben, wird der Port �ber die
MYSQL_TCP_PORT Umgebungsvariable, dem
mysql-tcp Eintrag aus der Datei
/etc/services oder der Konstanten
MYSQL_PORT festgelegt, in dieser Reihenfolge.
Win32 Systeme verwenden nur die Konstante MYSQL_PORT.
- mysql.default_socket
string
Legt den Socketnamen fest, der bei der Verbindung zu einem lokalen
Datenbankserver bennutzt werden soll, wenn kein anderer Name angegeben
wurde.
- mysql.default_host
string
Legt den Datenbankserver fest, zu dem eine Verbindung aufgebaut
werden soll, wenn kein Server angegeben wurde. Diese Option
hat im safe mode keine
Auswirkungen.
- mysql.default_user
string
Legt den Benutzernamen fest, der bei einer Verbindung zu einer
Datenbank verwendet werden soll, wenn kein Benutzer angegeben wurde.
Diese Option hat im safe mode
keine Auswirkungen.
- mysql.default_password
string
Legt das Passwort fest, das bei einer Verbindung zu einer Datenbank
verwendet werden soll, wenn kein Passwort angegeben wurde.
Diese Option hat im safe mode
keine Auswirkungen.
- mysql.connect_timeout
integer
Verbindungstimeout in Sekunden. Auf Linuxsystemen wird dieser Timeout
auch f�r die erste Antwort des Servers benutzt.
Resource Typen
Innerhalb des MySQL-Moduls werden zwei Resourcetypen benutzt.
Der erste ist die Verbindungskennung f�r eine Verbindung zum
Datenbankserver, der zweite Typ ist eine Resource die auf das Ergebnis
einer Anfrage/Abfrage verweist.
Vordefinierte Konstanten
Folgende Konstanten werden von dieser Erweiterung definiert und
stehen nur zur Verf�gung, wenn die Erweiterung entweder statisch
in PHP kompiliert oder dynamisch zur Laufzeit geladen wurde.
Ab PHP 4.3.0 ist es m�glich zus�tzliche Client-Flags bei den Funktionen
mysql_connect() und mysql_pconnect()
anzugeben. Die folgenden Konstanten sind definiert:
Tabelle 2. MySQL-Client Konstanten | Konstante | Beschreibung |
|---|
| MYSQL_CLIENT_COMPRESS | Benutze das Kompression Protokoll | | MYSQL_CLIENT_IGNORE_SPACE | Erlaubt Leerzeichen nach Funktionsnamen | | MYSQL_CLIENT_INTERACTIVE | Erlaubt Inaktivit�t von interactive_timeout Sekunden
(anstatt wait_timeout) bevor die Verbindung geschlossen wird.
|
Die Funktion mysql_fetch_array() verwendet eine
Konstante f�r die verschiedenen Arten der Ergebnisarrays. Die folgenden
Konstanten sind definiert:
Tabelle 3. MySQL fetch Konstanten | Konstante | Beschreibung |
|---|
| MYSQL_ASSOC |
Spalten werden in einem assoziativen Array zur�ckgegeben.
| | MYSQL_BOTH |
Spalten werden sowohl als assoziatives als auch als numerisches
Array zur�ckgegeben.
| | MYSQL_NUM |
Spalten werden als numerisches Array zur�ckgegeben. Der Index beginnt
bei 0, dieser entspricht dem ersten Feld des Ergebnisses.
|
Begriffserl�uterung
In diesem Dokument wird eine Reihe von Begriffen benutzt, die
hier kurz erl�utert werden sollen.
- Anfrageergebnis, Ergebnis
Die R�ckgabe auf eine Anfrage an den Datenbankserver. Anfragen
k�nnen sowohl Datenbankanfragen sein, die Teile des
Datenbankinhalts umfassen oder Informationen �ber eine
Datenbank oder den Datenbankserver liefern. Ist das Ergebnis
eine Datenbankanfrage kann man es sich als Tabelle vorstellen,
in der die Zeilen die Datens�tze sind und die Felder den
Spalten ensprechen. Ein Ergebnis besteht aus 0 oder beliebig
vielen Datens�tzen.
- Ergebnis-Kennung
Eine Referenz auf ein Ergebnis. �ber diese Kennung erfolgt
grunds�tzlich der Zugriff auf das Ergebnis.
- Datensatz
Eine Zeile aus einem Anfrageergebnis bestehend aus den Werten
der einzelnen Felder.
- Datensatzzeiger
Ein interner Zeiger auf einen Datensatz in einem
Anfrageergebnis. Dieser Zeiger bestimmt auf welchen Datensatz
zugegriffen wird. Der Zeiger kann sowohl implizit als auch
explizit ver�ndert werden.
- Feld
Entspricht einer Spalte im Anfrageergebnis.
- Feldzeiger
Wie Datensatzzeiger bei Zugriffen auf Felder eines
Anfrageergebnisses.
- Verbindungs-Kennung
Eine Referenz auf eine Verbindung zum Datenbank-Server. Mit
dieser Verbindung ist beispielsweise die Datenbank, der
Benutzer, der Rechnername auf dem die Datenbank l�uft und
weiteres verkn�pft. Jeder Zugriff auf den Server nutzt diese
Kennung. Wenn die Kennung beim Aufruf einer Funktion nicht
angegeben wird, so wird die aktuelle Verbindungs-Kennung
verwendet, die intern von PHP verwaltet wird.
Beispiele
Folgendes einfache Beispiel zeigt Ihnen, wie Sie sich mit einer MySQL-
Datenbank verbinden, eine Anfrage ausf�hren, die Ergebnisse ausgeben
und die Verbindung wieder trennen.
Beispiel 1. MySQL Erweiterung: ein �berblicks Beispiel <?php
/* Verbindung aufbauen, ausw�hlen einer Datenbank */
$link = mysql_connect("mysql_host", "mysql_user", "mysql_password")
or die("Keine Verbindung m�glich!");
print "Verbindung zum Datenbankserver erfolgreich";
mysql_select_db("Meine_Datenbank") or die("Auswahl der Datenbank fehlgeschlagen");
// ausf�hren einer SQL Anfrage
$query = "SELECT * FROM Meine_Tabelle";
$result = mysql_query($query) or die("Anfrage fehlgeschlagen");
// Ausgabe der Ergebnisse in HTML
print "<table>\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
print "\t<tr>\n";
foreach ($line as $col_value) {
print "\t\t<td>$col_value</td>\n";
}
print "\t</tr>\n";
}
print "</table>\n";
// Freigeben des Resultsets
mysql_free_result($result);
// schliessen der Verbinung
mysql_close($link);
?> |
|
User Contributed Notes
MySQL Funktionen |
 |
steer at projex dot hu
07-Mar-2000 06:53 |
|
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_assoc($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);
...
}
|
|
laura at freschinfo dot com
22-Mar-2000 09:38 |
|
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.)
|
|
craig at chiid dot ie
21-Dec-2000 05:17 |
|
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.
|
|
|
15-Feb-2001 07:51 |
|
A good place to get beginner's info and some good pointers on using MySQL
is:
|
|
mwalkup at morningstarstudios dot net
04-Apr-2001 09:04 |
|
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.
|
|
jcn at iki dot fi dot no_spam
01-May-2001 01:37 |
|
For one solution to problems with MySQL and special Nordic characters,
please see
|
|
mw-php at ender dot com
22-Jun-2001 04:11 |
|
The mysql_fetch_[row|object|array] functions return data as type string.
Owing to the very flexible nature of php variables, this is normally not
relevent, but if you happen to retrieve two integers from a database, then
try to compare with bitwise operators, you'll run into trouble, because
(19 & 2) == 2, but ("19" & "2") == 0. To
remedy this, if you use variables from a database with bitwise operators,
use the settype() function to explicitly cast your variables as integers
before comparing.
|
|
davey at shakedownwizards dot com
30-Jun-2001 06:24 |
|
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!
|
|
keith at keithtyler dot com
30-Jun-2001 06:41 |
|
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.
|
|
rupert at ditzed dot org
13-Jul-2001 08:23 |
|
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.
|
|
philip at cornado dot com
23-Jul-2001 07:24 |
|
If you're new to this, you really should learn basic SQL before moving on.
PHP != SQL. Here's are a few good basic SQL tutorials:
*
*
*
|
|
mbabcock-php at fibrespeed dot net
29-Jul-2001 02:41 |
|
Since there aren't functions to start and end/rollback transactions, you'll
have to use mysql_query("BEGIN"),
mysql_query("COMMIT") and mysql_query("ROLLBACK").
These will only work properly on tables that support transactions. You
may also wish to roll your own mysql_begin (etc) functions that run the
above queries for you.
|
|
skelley at diff dot nl
25-Sep-2001 09:11 |
|
Hi, here's a nice little trick to select records in random order from a
table in a MySQL database prior to version 3.23
SELECT *,
(ItemID/ItemID)*RAND() AS MyRandom FROM Items ORDER BY
MyRandom
[[[Editors note:
And just SELECT * FROM foo WHERE
bar = RAND() after 3.23
]]]
|
|
nospam at nospam dot nos
19-Nov-2001 04:17 |
|
ever wanted to know the date a table was last updated? use
this:
$info = mysql_fetch_array(mysql_query("show table
status from databasename like 'tablename'"));
echo
$info["Update_time"];
|
|
kekoa13 at yahoo dot com
18-Feb-2002 11:44 |
|
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.
|
|
jeyoung at priscimon dot com
25-Apr-2002 04:23 |
|
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.
|
|
pvenable at cs dot cmu dot edu
31-May-2002 12:43 |
|
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!
|
|
ajenks at discoverymining dot com
05-Jun-2002 04:04 |
|
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().
|
|
vinod at jobsure dot com
09-Jun-2002 09:22 |
|
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);
}
}
//###################//
//###################//
|
|
|
16-Jun-2002 07:38 |
|
Regarding transactions, you must use a recent MySQL version which supports
InnoDB tables. you should read the mysql manual (the part about Innodb
tables, section 7.5) and configure your server to use them.
Some
reading about how it works:
(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;
|
|
varga dot zoltan at freechat dot hu
02-Oct-2002 12:42 |
|
Here is the code to load a mysql dump file into mysql
database:
$odump=file($temp["db"]);
$dump="";
foreach
($odump as $line) {
if (trim($line)!='' &&
substr(trim($line),0,2)!='--')
{
$dump.=trim($line);
}
$tables=explode(';',$dump);
foreach
($tables as $sql) {
mysql_query($sql);
print mysql_error();
}
|
|
guillaume dot lapierre at iname dot com
21-Oct-2002 01:54 |
|
mw-php at ender dot com said this is not a problem that php fetch functions
return elements as strings.
That's not totaly true.
I use
setlocale() to use french dates and numbers.
If I have a float in a
table then the fetch functions will return an english float (ie
"1.23" instead of "1,23") that I have to convert with
str_replace(".",",",$float_from_db).
If I can
find a good workaround I will post it here.
|
|
phplist at gremlins dot com dot hk
03-Nov-2002 09:19 |
|
Can the example be modified so that instead of simply using
die('Query failed')
it uses:
die('Query failed: ' .
mysql_error());
This will save newbies a lot of grief.
|
|
opaf at yahoo dot com
13-Nov-2002 02:56 |
|
Two notes about PHP and MySQL:
1. Get used to the
syntax
$myquery = "<your query here>";
$result =
mysql_query($myquery);
instead of
$result =
mysql_query("<your query here>");
Some of the
benefits of the proposed syntax are:
- abilty to perform a global
search to your scripts nice and easy
- ability to easily output/debug
your queries
2. The second note has more to do with
MySQL:
Timestamp fields update automaticaly everytime you issue an
Update query. Read the documentation!!
I lost 3 days of my life
because of this
|
|
mijnpc at xs4all dot nl
20-Nov-2002 09:33 |
|
If you have a Windows machine running a webserver with PHP you don't need
to install MySQL server to locally test scripts, if you are granted to
establish a Secure Telnet connection (port 22) to the remote
webserver.
To do this you need a Secure Telnet client, which
supports port-forwarding.
Before you establish a connection, define the
port-forward.
Forward local port 3306 to [name or ip of remote
server]:3306
Make sure that local ports accept connections from other
hosts
Save this session
Connect to remote server with username
and password
Minimize the shell and that's it...
You can use the
same username (and password) as if you were working on the remote server
!
E.g. : $link = mysql_connect("localhost", "root",
"") or die("no way jose");
You may get a
shell-timeout after xx minutes depending on your remote server, just
reconnect or press enter in the shell once in a while...
An example
of a superb freeware Secure Telnet client is Putty : Putty :
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 (-:
|
|
|
14-Jan-2003 11:51 |
|
Just visit.
|
|
mijnpc at xs4all dot nl
09-Feb-2003 04:11 |
|
[Editor's Note: it is possible to use multi-dimensional arrays in SQL
queries (they are just a double quoted string) by using SELECT * FROM
{$whichtable[$whichdb[$which]]} WHERE id = $entry"; ]
I
found out that you cannot use multidimensional arrays in a SQL-query, e.g.
:
$sql = "SELECT * FROM $whichtable[$whichdb[$which]] WHERE id =
$entry";
this will work fine however :
$tempvar =
$whichtable[$whichdb[$which]];
$sql = "SELECT * FROM $tempvar
WHERE id = $entry";
If it already is a bug, is it in mySQL
or PHP ?
|
|
admin at programmers-unite dot com
15-Feb-2003 05:22 |
|
The reason it didn't work was because you just
used:
mysql_query("......
$var[$var2[$var3]]....");
and
not
mysql_query("......
".$var[$var2[$var3]]."....");
Let's say:
$var3 =
"example";
$var2["example"] =
"ex2";
$var["ex2"] =
"realoutput";
In the first example, the query sent to
mysql will be
".......
Array[Array[example]]...."
and in the second
one:
"....... realoutput...."
|
|
zeisss at web dot de
19-Feb-2003 04:12 |
|
Furthermore u can make sth like this:
mysql_query("...
{$array1[$array2[$key]]} .." );
which works with me also
fine, and is more readable, i think.
|
|
past at sbox dot tugraz dot at
21-Feb-2003 09:17 |
|
As MySQL docs say, RAND() is not very usefull for generation of randomized
result orders.
But this worked for me on Linux, however:
Somewhere before:
mt_srand((double)microtime()*1000000);
"SELECT *, "
RAND(".mt_rand(0,86622340).")*10000%100 AS randomvalue ORDER BY
randomvalue"
The upper value for mt_rand() has to be Quite Big
to see any effect on MySQL's RAND(). The exact number shouldn't be
significant. Note the multiplication and modulo; MySQL seems to count
steadily upwards when generating random numbers, so we take some numbers
from between.
|
|
soren at byu dot edu
14-Mar-2003 08:23 |
|
Let's say that you want to generate a MySQL password hash from a plain text
password. Normally, you would just submit the MySQL query "SELECT
PASSWORD('password')", but if for some reason you can't access to
MySQL database directly, then you can use the following function
(translated right out of the MySQL source code):
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.
|
|
andre dot hoogeland at zonnet dot nl
01-May-2003 11:13 |
|
To find the holes in a table like this:
id| myField
3 | 6
4 |
0
5 | 1
6 | 5
7 | 4
I came up with this
function:
function findhole( $table, $field ) {
$retval =
0;
$qrytxt = "SELECT $field FROM $table ORDER BY $field
ASC";
$query = mysql_query( $qrytxt );
$current =
mysql_fetch_array( $query, MYSQL_ASSOC );
if( $current[$field] != 0
|| mysql_num_rows( $query ) < 1 ) {
// no initial values: return
0
return $retval;
} else {
do {
$next =
mysql_fetch_array( $query, MYSQL_ASSOC );
// if successor doesn't
appear in the table
if( $next[$field] != $current[$field] + 1)
{
$retval = $current[$field] + 1;
break;
}
else {
$current = $next;
}
} while( true );
}
return $retval;
}
And... to _fill_ the holes:
$fh
= findhole( "myTable", "myField" ); // will return
2
mysql_query( "insert into myTable values( null, '$fh' )"
);
I hope this is useful!
Regards, Andr� Hoogeland
|
|
Sparc at brturbo dot com
02-May-2003 10:35 |
|
At 25-Apr-2002 10:23 jeyoung at priscimon dot com said:
"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. "
As a matter of fact,
you must call mysql_connect with the new_link parameter set to true to
make a new connection, otherwise, you'll get the same resource id as
returned by the first mysql_connect in the later
connections.
Regards
|
|
leader at k2wrpg dot org
03-May-2003 11:27 |
|
While doing lots of mysql work remotely I got tired of having to upload my
files over and over again to find problems in my sql queries. I was also
having a hard time checking the data in the DB itself (especially when
behind a firewall, ugh.) anyway I present to you a single file query
analyzer. So I made an easier way. It�s not persistent so some complex
statements using temp tables and such probably won't work, but for simple
Selects, Updates, Inserts and Deletes, it works just fine. I would say it
handles about 95% of the queries I want to run. Anyway I found it useful
so I'll share. Just save the following code into a file named
sqlquery.php, and put it on your server If you name it something else
you'll have to change the target of the form. Also its totally insecure so
if you�re worried about eavesdroppers don't use
it.
Enjoy!
rather than post the code, here is a
download:
|
|
Ronald
13-May-2003 09:45 |
|
Ask yourself why you want to use MySQL instead of other DB-engines. MySQL
has some bugs and does not have all features which are standard in e.g.
MSSQL or Oracle.
For more information about the bugs and missing
features:
Ok,
maybe this list is made by someone who is against MySQL, but I think it's
worth reading for everyone.
|
|
santo at gimpyleg dot com
13-May-2003 09:49 |
|
Roland, maybe you should ask yourself the cost of SQL Server and Oracle
versus the cost of MySQL.
|
|
|
|
| |
PHP Handbuch
Funktionsreferenz
Apache
msql