44 Fungsi PHP yang berhubungan dengan MySQL
1. mysql_affected_rows
mysql_affected_rows — Get number of affected rows in previous MySQL operation
Description
int mysql_affected_rows ([ resource $link_identifier ] )
Get the number of affected rows by the last INSERT, UPDATE, REPLACE or DELETE query associated with link_identifier.
Parameters
link_identifier
The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect() is assumed. If no such link is found, it will try to create one as if mysql_connect() was called with no arguments. If no connection is found or established, an E_WARNING level error is generated. Return Values
Returns the number of affected rows on success, and -1 if the last query failed.
If the last query was a DELETE query with no WHERE clause, all of the records will have been deleted from the table but this function will return zero with MySQL versions prior to 4.1.2.
When using UPDATE, MySQL will not update columns where the new value is the same as the old value. This creates the possibility thatmysql_affected_rows() may not actually equal the number of rows matched, only the number of rows that were literally affected by the query.
The REPLACE statement first deletes the record with the same primary key and then inserts the new record. This function returns the number of deleted records plus the number of inserted records.
Examples
Example #1 mysql_affected_rows() example
<?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());
?>
The above example will output something similar to:
Records deleted: 10
Records deleted: 0
Example #2 mysql_affected_rows() example using transactions
<?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");
?>
The above example will output something similar to:
Updated Records: 10
Notes
Note: Transactions
If you are using transactions, you need to call mysql_affected_rows() after your INSERT, UPDATE, or DELETE query, not after the COMMIT.
Note: SELECT Statements
To retrieve the number of rows returned by a SELECT, it is possible to use mysql_num_rows(). Note: Cascaded Foreign Keys
mysql_affected_rows() does not count rows affected implicitly through the use of ON DELETE CASCADE and/or ON UPDATE CASCADE in foreign key constraints.
2. mysql_close()
Untuk memutuskan hubungan dokumen dengan database MySQL.
syntax dasar
mysql_close(connection);
contoh:
<?
$con = mysql_connect("localhost","root","12345");
if(!$con)
{
die('Database tidak dapat terhubung: '.mysql_error());
}
// dan seterusnya
mysql_close($con);
3. mysql_connect()
Untuk menghubungkan dokumen dengan database MySQL.
syntax dasar
mysql_connect(servername,username,password);
contoh:
<?
$con = mysql_connect("localhost","root","12345");
if(!$con)
{
die('Database tidak dapat terhubung: '.mysql_error());
}
// dan seterusnya
?>
4. mysql_create_db
mysql_create_db — Create a MySQL database
Description
bool mysql_create_db ( string $database_name [, resource $link_identifier ] )
mysql_create_db() attempts to create a new database on the server associated with the specified link identifier.
Parameters
database_name
The name of the database being created.
The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect() is assumed. If no such link is found, it will try to create one as if mysql_connect() was called with no arguments. If no connection is found or established, an E_WARNING level error is generated. Return Values
Returns TRUE on success or FALSE on failure.
Examples
Example #1 mysql_create_db() alternative example
The function mysql_create_db() is deprecated. It is preferable to use mysql_query() to issue an sql CREATE DATABASE statement instead. <?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";
}
?>
The above example will output something similar to:
Database my_db created successfully
Notes
Note:
For backward compatibility, the following deprecated alias may be used: mysql_createdb()
5. mysql_data_seek
mysql_data_seek — Move internal result pointer
Description
bool mysql_data_seek ( resource $result , int $row_number )
mysql_data_seek() moves the internal row pointer of the MySQL result associated with the specified result identifier to point to the specified row number. The next call to a MySQL fetch function, such as mysql_fetch_assoc(), would return that row. row_number starts at 0. The row_number should be a value in the range from 0 to mysql_num_rows() - 1. However if the result set is empty (mysql_num_rows() == 0), a seek to 0 will fail with a E_WARNING and mysql_data_seek() will return FALSE. Parameters
The desired row number of the new result pointer.
Return Values
Returns TRUE on success or FALSE on failure.
Examples
Example #1 mysql_data_seek() example
<?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);
?>
Notes
Note:
See Also
Example (Find Last Record):
<?php
$query="SELECT * FROM `team` ORDER BY `team`.`id` ASC";
$result=mysql_query($query);
$last_row = mysql_num_rows($result) - 1;
if (mysql_data_seek($result, $last_row)) {
$row = mysql_fetch_row($result);
$id = $row[0] + 1;
} else {
echo "Cannot seek to row $last_row: " . mysql_error() . "\n";
}
?>
** Note:
1. The above example code relates to a MYSQL table named 'team' similar to this:
+-ID-+ TEAM NAME-+
| 49 | Team 49 |
| 33 | Team 33 |
| 84 | Team 84 |
| 07 | Team 07 |
+----+-----------+
** Records shown in the order inserted (i.e. TABLE NOT INDEXED)
2. In the above example code the field named ID is NOT set to AUTO-INCREMENT (i.e. field value set programmatically).
3. Wanting to insert a new team with a team number higher that the highest team number used, simply using the following code snippet
<?php
$query="SELECT * FROM `team`";
?
resulted in an ERROR when using the mysql_data_seek() function to select the record with the highest ID value.
4. Use of the ORDER statement in the query corrected the error/problem.
Here is a simple function to "peek" at the position of the internal pointer in a query result:
<?php
function mysql_pointer_position($result_set) {
$num_rows = mysql_num_rows($result_set);
$i = 0;
while($result = mysql_fetch_array($result_set)) {
$i++;
}
$pointer_position = $num_rows - $i;
if($pointer_position <= $num_rows - 1) {
mysql_data_seek($result_set, $pointer_position);
}
return $pointer_position;
}
?>
