22.9.2.5. MySQL Functions

<?php$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');if (!$link) {    die('Could not connect: ' . mysql_error());}mysql_select_db('mydb');/* this should return the correct numbers of deleted records */mysql_query('DELETE FROM mytable WHERE id < 10');printf("Records deleted: %d\n", mysql_affected_rows());/* with a where clause that is never true, it should return 0 */mysql_query('DELETE FROM mytable WHERE 0');printf("Records deleted: %d\n", mysql_affected_rows());?>    

Вышеупомянутый пример выведет что-то подобное:

Records deleted: 10Records deleted: 0

<?php$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');if (!$link) {    die('Could not connect: ' . mysql_error());}mysql_select_db('mydb');/* Update records */mysql_query("UPDATE mytable SET used=1 WHERE id < 10");printf ("Updated records: %d\n", mysql_affected_rows());mysql_query("COMMIT");?>    

Вышеупомянутый пример выведет что-то подобное:

Updated Records: 10

<?php$link    = mysql_connect('localhost', 'mysql_user', 'mysql_password');$charset = mysql_client_encoding($link);echo "The current character set is: $charset\n";?>    

Вышеупомянутый пример выведет что-то подобное:

The current character set is: latin1

<?php$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');if (!$link) {    die('Could not connect: ' . mysql_error());}echo 'Connected successfully';mysql_close($link);?>    

Вышеупомянутый пример выведет:

Connected successfully

<?php$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');if (!$link) {    die('Could not connect: ' . mysql_error());}echo 'Connected successfully';mysql_close($link);?>

<?php// we connect to example.com and port 3307$link = mysql_connect('example.com:3307', 'mysql_user', 'mysql_password');if (!$link) {    die('Could not connect: ' . mysql_error());}echo 'Connected successfully';mysql_close($link);// we connect to localhost at port 3307$link = mysql_connect('127.0.0.1:3307', 'mysql_user', 'mysql_password');if (!$link) {    die('Could not connect: ' . mysql_error());}echo 'Connected successfully';mysql_close($link);?>

<?php// we connect to localhost and socket e.g. /tmp/mysql.sock// variant 1: omit localhost$link = mysql_connect(':/tmp/mysql', 'mysql_user', 'mysql_password');if (!$link) {    die('Could not connect: ' . mysql_error());}echo 'Connected successfully';mysql_close($link);// variant 2: with localhost$link = mysql_connect('localhost:/tmp/mysql.sock', 'mysql_user', 'mysql_password');if (!$link) {    die('Could not connect: ' . mysql_error());}echo 'Connected successfully';mysql_close($link);?>

Функция mysql_create_db осуждается. Предпочтительно использовать mysql_query выпустить sql CREATE DATABASE оператор вместо этого.

<?php$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');if (!$link) {    die('Could not connect: ' . mysql_error());}$sql = 'CREATE DATABASE my_db';if (mysql_query($sql, $link)) {    echo "Database my_db created successfully\n";} else {    echo 'Error creating database: ' . mysql_error() . "\n";}?>    

Вышеупомянутый пример выведет что-то подобное:

Database my_db created successfully

<?php$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');if (!$link) {    die('Could not connect: ' . mysql_error());}$db_selected = mysql_select_db('sample_db');if (!$db_selected) {    die('Could not select database: ' . mysql_error());}$query = 'SELECT last_name, first_name FROM friends';$result = mysql_query($query);if (!$result) {    die('Query failed: ' . mysql_error());}/* fetch rows in reverse order */for ($i = mysql_num_rows($result) - 1; $i >= 0; $i--) {    if (!mysql_data_seek($result, $i)) {        echo "Cannot seek to row $i: " . mysql_error() . "\n";        continue;    }    if (!($row = mysql_fetch_assoc($result))) {        continue;    }    echo $row['last_name'] . ' ' . $row['first_name'] . "<br />\n";}mysql_free_result($result);?>

<?phperror_reporting(E_ALL);$link = mysql_connect('dbhost', 'username', 'password');$db_list = mysql_list_dbs($link);$i = 0;$cnt = mysql_num_rows($db_list);while ($i < $cnt) {    echo mysql_db_name($db_list, $i) . "\n";    $i++;}?>

<?phpif (!$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')) {    echo 'Could not connect to mysql';    exit;}if (!mysql_select_db('mysql_dbname', $link)) {    echo 'Could not select database';    exit;}$sql    = 'SELECT foo FROM bar WHERE id = 42';$result = mysql_query($sql, $link);if (!$result) {    echo "DB Error, could not query the database\n";    echo 'MySQL Error: ' . mysql_error();    exit;}while ($row = mysql_fetch_assoc($result)) {    echo $row['foo'];}mysql_free_result($result);?>

<?php$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');if (!$link) {    die('Could not connect: ' . mysql_error());}$sql = 'DROP DATABASE my_db';if (mysql_query($sql, $link)) {    echo "Database my_db was successfully dropped\n";} else {    echo 'Error dropping database: ' . mysql_error() . "\n";}?>

<?php$link = mysql_connect("localhost", "mysql_user", "mysql_password");if (!mysql_select_db("nonexistentdb", $link)) {    echo mysql_errno($link) . ": " . mysql_error($link). "\n";}mysql_select_db("kossu", $link);if (!mysql_query("SELECT * FROM nonexistenttable", $link)) {    echo mysql_errno($link) . ": " . mysql_error($link) . "\n";}?>    

Вышеупомянутый пример выведет что-то подобное:

1049: Unknown database 'nonexistentdb'1146: Table 'kossu.nonexistenttable' doesn't exist

<?php$link = mysql_connect("localhost", "mysql_user", "mysql_password");mysql_select_db("nonexistentdb", $link);echo mysql_errno($link) . ": " . mysql_error($link). "\n";mysql_select_db("kossu", $link);mysql_query("SELECT * FROM nonexistenttable", $link);echo mysql_errno($link) . ": " . mysql_error($link) . "\n";?>    

Вышеупомянутый пример выведет что-то подобное:

1049: Unknown database 'nonexistentdb'1146: Table 'kossu.nonexistenttable' doesn't exist

<?php$item = "Zak's Laptop";$escaped_item = mysql_escape_string($item);printf("Escaped string: %s\n", $escaped_item);?>    

Вышеупомянутый пример выведет:

Escaped string: Zak\'s Laptop

SELECT table1.field AS foo, table2.field AS bar FROM table1, table2

<?phpmysql_connect("localhost", "mysql_user", "mysql_password") or    die("Could not connect: " . mysql_error());mysql_select_db("mydb");$result = mysql_query("SELECT id, name FROM mytable");while ($row = mysql_fetch_array($result, MYSQL_NUM)) {    printf("ID: %s  Name: %s", $row[0], $row[1]);  }mysql_free_result($result);?>

<?phpmysql_connect("localhost", "mysql_user", "mysql_password") or    die("Could not connect: " . mysql_error());mysql_select_db("mydb");$result = mysql_query("SELECT id, name FROM mytable");while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {    printf("ID: %s  Name: %s", $row["id"], $row["name"]);}mysql_free_result($result);?>

<?phpmysql_connect("localhost", "mysql_user", "mysql_password") or    die("Could not connect: " . mysql_error());mysql_select_db("mydb");$result = mysql_query("SELECT id, name FROM mytable");while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {    printf ("ID: %s  Name: %s", $row[0], $row["name"]);}mysql_free_result($result);?>

<?php$conn = mysql_connect("localhost", "mysql_user", "mysql_password");if (!$conn) {    echo "Unable to connect to DB: " . mysql_error();    exit;}if (!mysql_select_db("mydbname")) {    echo "Unable to select mydbname: " . mysql_error();    exit;}$sql = "SELECT id as userid, fullname, userstatus        FROM   sometable        WHERE  userstatus = 1";$result = mysql_query($sql);if (!$result) {    echo "Could not successfully run query ($sql) from DB: " . mysql_error();    exit;}if (mysql_num_rows($result) == 0) {    echo "No rows found, nothing to print so am exiting";    exit;}// While a row of data exists, put that row in $row as an associative array// Note: If you're expecting just one row, no need to use a loop// Note: If you put extract($row); inside the following loop, you'll//       then create $userid, $fullname, and $userstatuswhile ($row = mysql_fetch_assoc($result)) {    echo $row["userid"];    echo $row["fullname"];    echo $row["userstatus"];}mysql_free_result($result);?>

<?php$conn = mysql_connect('localhost', 'mysql_user', 'mysql_password');if (!$conn) {    die('Could not connect: ' . mysql_error());}mysql_select_db('database');$result = mysql_query('select * from table');if (!$result) {    die('Query failed: ' . mysql_error());}/* get column metadata */$i = 0;while ($i < mysql_num_fields($result)) {    echo "Information for column $i:<br />\n";    $meta = mysql_fetch_field($result, $i);    if (!$meta) {        echo "No information available<br />\n";    }    echo "<pre>blob:         $meta->blobmax_length:   $meta->max_lengthmultiple_key: $meta->multiple_keyname:         $meta->namenot_null:     $meta->not_nullnumeric:      $meta->numericprimary_key:  $meta->primary_keytable:        $meta->tabletype:         $meta->typeunique_key:   $meta->unique_keyunsigned:     $meta->unsignedzerofill:     $meta->zerofill</pre>";    $i++;}mysql_free_result($result);?>

<?php$result = mysql_query("SELECT id,email FROM people WHERE id = '42'");if (!$result) {    echo 'Could not run query: ' . mysql_error();    exit;}$row     = mysql_fetch_assoc($result);$lengths = mysql_fetch_lengths($result);print_r($row);print_r($lengths);?>    

Вышеупомянутый пример выведет что-то подобное:

Array(    [id] => 42    [email] => user@example.com)Array(    [0] => 2    [1] => 16)

<?phpmysql_connect("hostname", "user", "password");mysql_select_db("mydb");$result = mysql_query("select * from mytable");while ($row = mysql_fetch_object($result)) {    echo $row->user_id;    echo $row->fullname;}mysql_free_result($result);?>

<?phpclass foo {    public $name;}mysql_connect("hostname", "user", "password");mysql_select_db("mydb");$result = mysql_query("select name from mytable limit 1");$obj = mysql_fetch_object($result, 'foo');var_dump($obj);?>

<?php$result = mysql_query("SELECT id,email FROM people WHERE id = '42'");if (!$result) {    echo 'Could not run query: ' . mysql_error();    exit;}$row = mysql_fetch_row($result);echo $row[0]; // 42echo $row[1]; // the email value?>

<?php$result = mysql_query("SELECT id,email FROM people WHERE id = '42'");if (!$result) {    echo 'Could not run query: ' . mysql_error();    exit;}$flags = mysql_field_flags($result, 0);echo $flags;print_r(explode(' ', $flags));?>    

Вышеупомянутый пример выведет что-то подобное:

not_null primary_key auto_incrementArray(    [0] => not_null    [1] => primary_key    [2] => auto_increment)

<?php$result = mysql_query("SELECT id,email FROM people WHERE id = '42'");if (!$result) {    echo 'Could not run query: ' . mysql_error();    exit;}// Will get the length of the id field as specified in the database// schema. $length = mysql_field_len($result, 0);echo $length;?>

<?php/* The users table consists of three fields: *   user_id *   username *   password. */$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');if (!$link) {    die('Could not connect to MySQL server: ' . mysql_error());}$dbname = 'mydb';$db_selected = mysql_select_db($dbname, $link);if (!$db_selected) {    die("Could not set $dbname: " . mysql_error());}$res = mysql_query('select * from users', $link);echo mysql_field_name($res, 0) . "\n";echo mysql_field_name($res, 2);?>    

Вышеупомянутый пример выведет:

user_idpassword

<?php$query = "SELECT account.*, country.* FROM account, country WHERE country.name = 'Portugal' AND account.country_id = country.id";// get the result from the DB$result = mysql_query($query);// Lists the table name and then the field namefor ($i = 0; $i < mysql_num_fields($result); ++$i) {    $table = mysql_field_table($result, $i);    $field = mysql_field_name($result, $i);    echo  "$table: $field\n";}?>

<?phpmysql_connect("localhost", "mysql_username", "mysql_password");mysql_select_db("mysql");$result = mysql_query("SELECT * FROM func");$fields = mysql_num_fields($result);$rows   = mysql_num_rows($result);$table  = mysql_field_table($result, 0);echo "Your '" . $table . "' table has " . $fields . " fields and " . $rows . " record(s)\n";echo "The table has the following fields:\n";for ($i=0; $i < $fields; $i++) {    $type  = mysql_field_type($result, $i);    $name  = mysql_field_name($result, $i);    $len   = mysql_field_len($result, $i);    $flags = mysql_field_flags($result, $i);    echo $type . " " . $name . " " . $len . " " . $flags . "\n";}mysql_free_result($result);mysql_close();?>    

Вышеупомянутый пример выведет что-то подобное:

Your 'func' table has 4 fields and 1 record(s)The table has the following fields:string name 64 not_null primary_key binaryint ret 1 not_nullstring dl 128 not_nullstring type 9 not_null enum

<?php$result = mysql_query("SELECT id,email FROM people WHERE id = '42'");if (!$result) {    echo 'Could not run query: ' . mysql_error();    exit;}/* Use the result, assuming we're done with it afterwards */$row = mysql_fetch_assoc($result);/* Now we free up the result and continue on with our script */mysql_free_result($result);echo $row['id'];echo $row['email'];?>

<?phpprintf("MySQL client info: %s\n", mysql_get_client_info());?>    

Вышеупомянутый пример выведет что-то подобное:

MySQL client info: 3.23.39

<?php$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');if (!$link) {    die('Could not connect: ' . mysql_error());}printf("MySQL host info: %s\n", mysql_get_host_info());?>    

Вышеупомянутый пример выведет что-то подобное:

MySQL host info: Localhost via UNIX socket

<?php$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');if (!$link) {    die('Could not connect: ' . mysql_error());}printf("MySQL protocol version: %s\n", mysql_get_proto_info());?>    

Вышеупомянутый пример выведет что-то подобное:

MySQL protocol version: 10

<?php$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');if (!$link) {    die('Could not connect: ' . mysql_error());}printf("MySQL server version: %s\n", mysql_get_server_info());?>    

Вышеупомянутый пример выведет что-то подобное:

MySQL server version: 4.0.1-alpha

Операторы та строка возврата значения. Числа только для того, чтобы иллюстрировать цель; их значения будут соответствовать запросу.

INSERT INTO ... SELECT ...String format: Records: 23 Duplicates: 0 Warnings: 0 INSERT INTO ... VALUES (...),(...),(...)...String format: Records: 37 Duplicates: 0 Warnings: 0 LOAD DATA INFILE ...String format: Records: 42 Deleted: 0 Skipped: 0 Warnings: 0 ALTER TABLEString format: Records: 60 Duplicates: 0 Warnings: 0 UPDATEString format: Rows matched: 65 Changed: 65 Warnings: 0

<?php$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');if (!$link) {    die('Could not connect: ' . mysql_error());}mysql_select_db('mydb');mysql_query("INSERT INTO mytable (product) values ('kossu')");printf("Last inserted record has id %d\n", mysql_insert_id());?>

<?php// Usage without mysql_list_dbs()$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');$res = mysql_query("SHOW DATABASES");while ($row = mysql_fetch_assoc($res)) {    echo $row['Database'] . "\n";}// Deprecated as of PHP 5.4.0$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');$db_list = mysql_list_dbs($link);while ($row = mysql_fetch_object($db_list)) {     echo $row->Database . "\n";}?>    

Вышеупомянутый пример выведет что-то подобное:

database1database2database3

<?php$result = mysql_query("SHOW COLUMNS FROM sometable");if (!$result) {    echo 'Could not run query: ' . mysql_error();    exit;}if (mysql_num_rows($result) > 0) {    while ($row = mysql_fetch_assoc($result)) {        print_r($row);    }}?>    

Вышеупомянутый пример выведет что-то подобное:

Array(    [Field] => id    [Type] => int(7)    [Null] =>      [Key] => PRI    [Default] =>    [Extra] => auto_increment)Array(    [Field] => email    [Type] => varchar(100)    [Null] =>    [Key] =>    [Default] =>    [Extra] =>)

<?php$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');$result = mysql_list_processes($link);while ($row = mysql_fetch_assoc($result)){    printf("%s %s %s %s %s\n", $row["Id"], $row["Host"], $row["db"],        $row["Command"], $row["Time"]);}mysql_free_result($result);?>    

Вышеупомянутый пример выведет что-то подобное:

1 localhost test Processlist 04 localhost mysql sleep 5

<?php$dbname = 'mysql_dbname';if (!mysql_connect('mysql_host', 'mysql_user', 'mysql_password')) {    echo 'Could not connect to mysql';    exit;}$sql = "SHOW TABLES FROM $dbname";$result = mysql_query($sql);if (!$result) {    echo "DB Error, could not list tables\n";    echo 'MySQL Error: ' . mysql_error();    exit;}while ($row = mysql_fetch_row($result)) {    echo "Table: {$row[0]}\n";}mysql_free_result($result);?>

<?php$result = mysql_query("SELECT id,email FROM people WHERE id = '42'");if (!$result) {    echo 'Could not run query: ' . mysql_error();    exit;}/* returns 2 because id,email === two fields */echo mysql_num_fields($result);?>

<?php$link = mysql_connect("localhost", "mysql_user", "mysql_password");mysql_select_db("database", $link);$result = mysql_query("SELECT * FROM table1", $link);$num_rows = mysql_num_rows($result);echo "$num_rows Rows\n";?>

<?phpset_time_limit(0);$conn = mysql_connect('localhost', 'mysqluser', 'mypass');$db   = mysql_select_db('mydb');/* Assuming this query will take a long time */$result = mysql_query($sql);if (!$result) {    echo 'Query #1 failed, exiting.';    exit;}/* Make sure the connection is still alive, if not, try to reconnect */if (!mysql_ping($conn)) {    echo 'Lost connection, exiting after query #1';    exit;}mysql_free_result($result);/* So the connection is still alive, let's run another query */$result2 = mysql_query($sql2);?>

Следующий запрос синтаксически недопустим, таким образом, mysql_query сбои и возвраты FALSE .

<?php$result = mysql_query('SELECT * WHERE 1=1');if (!$result) {    die('Invalid query: ' . mysql_error());}?>

Следующий запрос допустим, таким образом, mysql_query возвращает ресурс.

<?php// This could be supplied by a user, for example$firstname = 'fred';$lastname  = 'fox';// Formulate Query// This is the best way to perform an SQL query// For more examples, see mysql_real_escape_string()$query = sprintf("SELECT firstname, lastname, address, age FROM friends     WHERE firstname='%s' AND lastname='%s'",    mysql_real_escape_string($firstname),    mysql_real_escape_string($lastname));// Perform Query$result = mysql_query($query);// Check result// This shows the actual query sent to MySQL, and the error. Useful for debugging.if (!$result) {    $message  = 'Invalid query: ' . mysql_error() . "\n";    $message .= 'Whole query: ' . $query;    die($message);}// Use result// Attempting to print $result won't allow access to information in the resource// One of the mysql result functions must be used// See also mysql_result(), mysql_fetch_array(), mysql_fetch_row(), etc.while ($row = mysql_fetch_assoc($result)) {    echo $row['firstname'];    echo $row['lastname'];    echo $row['address'];    echo $row['age'];}// Free the resources associated with the result set// This is done automatically at the end of the scriptmysql_free_result($result);?>

<?php// Connect$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')    OR die(mysql_error());// Query$query = sprintf("SELECT * FROM users WHERE user='%s' AND password='%s'",            mysql_real_escape_string($user),            mysql_real_escape_string($password));?>

<?php// We didn't check $_POST['password'], it could be anything the user wanted! For example:$_POST['username'] = 'aidan';$_POST['password'] = "' OR ''='";// Query database to check if there are any matching users$query = "SELECT * FROM users WHERE user='{$_POST['username']}' AND password='{$_POST['password']}'";mysql_query($query);// This means the query sent to MySQL would be:echo $query;?>    

Запрос, отправленный MySQL:

SELECT * FROM users WHERE user='aidan' AND password='' OR ''=''    

Это позволило бы любому входить в систему без допустимого пароля.

<?php$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');if (!$link) {    die('Could not connect: ' . mysql_error());}if (!mysql_select_db('database_name')) {    die('Could not select database: ' . mysql_error());}$result = mysql_query('SELECT name FROM work.employee');if (!$result) {    die('Could not query:' . mysql_error());}echo mysql_result($result, 2); // outputs third employee's namemysql_close($link);?>

<?php$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');if (!$link) {    die('Not connected : ' . mysql_error());}// make foo the current db$db_selected = mysql_select_db('foo', $link);if (!$db_selected) {    die ('Can\'t use foo : ' . mysql_error());}?>

<?php$link   = mysql_connect('localhost', 'mysql_user', 'mysql_password');$status = explode('  ', mysql_stat($link));print_r($status);?>    

Вышеупомянутый пример выведет что-то подобное:

Array(    [0] => Uptime: 5380    [1] => Threads: 2    [2] => Questions: 1321299    [3] => Slow queries: 0    [4] => Opens: 26    [5] => Flush tables: 1    [6] => Open tables: 17    [7] => Queries per second avg: 245.595)

<?php$link   = mysql_connect('localhost', 'mysql_user', 'mysql_password');$result = mysql_query('SHOW STATUS', $link);while ($row = mysql_fetch_assoc($result)) {    echo $row['Variable_name'] . ' = ' . $row['Value'] . "\n";}?>    

Вышеупомянутый пример выведет что-то подобное:

back_log = 50basedir = /usr/local/bdb_cache_size = 8388600bdb_log_buffer_size = 32768bdb_home = /var/db/mysql/bdb_max_lock = 10000bdb_logdir =bdb_shared_data = OFFbdb_tmpdir = /var/tmp/...

<?phpmysql_connect("localhost", "mysql_user", "mysql_password");$result = mysql_list_tables("mydb");$num_rows = mysql_num_rows($result);for ($i = 0; $i < $num_rows; $i++) {    echo "Table: ", mysql_tablename($result, $i), "\n";}mysql_free_result($result);?>

<?php$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');$thread_id = mysql_thread_id($link);if ($thread_id){    printf("current thread id is %d\n", $thread_id);}?>    

Вышеупомянутый пример выведет что-то подобное:

current thread id is 73