본문 바로가기
PHP
2012.03.28 16:22

MySQL 주요 PHP 함수 (추천)

조회 수 4065 추천 수 0 댓글 0
?

단축키

Prev이전 문서

Next다음 문서

+ - Up Down Comment Print
?

단축키

Prev이전 문서

Next다음 문서

+ - Up Down Comment Print

1. mysql_connect — Open a connection to a MySQL Server

resource mysql_connect ([ string $server = ini_get("mysql.default_host") [, string $username = ini_get("mysql.default_user") [, string $password = ini_get("mysql.default_password") [, bool $new_link = false [, int $client_flags = 0 ]]]]] )

<?php

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

--- Note ---
mysql_error — Returns the text of the error message from previous MySQL operation
string mysql_error ([ resource $link_identifier = NULL ] )
------------

2. mysql_select_db — Select a MySQL database

bool mysql_select_db ( string $database_name [, resource $link_identifier = NULL ] )

<?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());
}
?>


3. mysql_query — Send a MySQL query

resource mysql_query ( string $query [, resource $link_identifier = NULL ] )

<?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 script
mysql_free_result($result);
?>

--- Note ---
mysql_real_escape_string — Escapes special characters in a string for use in an SQL statement
string mysql_real_escape_string ( string $unescaped_string [, resource $link_identifier = NULL ] )

mysql_real_escape_string() calls MySQL's library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a.

This function must always (with few exceptions) be used to make data safe before sending a query to MySQL.
------------


4-1. mysql_fetch_assoc — Fetch a result row as an associative array

array mysql_fetch_assoc ( resource $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 $userstatus
while ($row = mysql_fetch_assoc($result)) {
    echo $row["userid"];
    echo $row["fullname"];
    echo $row["userstatus"];
}

mysql_free_result($result);

?>

--- Note ---
mysql_num_rows — Get number of rows in result
int mysql_num_rows ( resource $result )
------------


4-2. mysql_fetch_array — Fetch a result row as an associative array, a numeric array, or both

array mysql_fetch_array ( resource $result [, int $result_type = MYSQL_BOTH ] )

Example #2 mysql_fetch_array() with MYSQL_NUM

<?php
mysql_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);
?>


Example #3 mysql_fetch_array() with MYSQL_ASSOC

<?php
mysql_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);
?>

Example #4 mysql_fetch_array() with MYSQL_BOTH

<?php
mysql_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);
?>


4-3. mysql_fetch_row — Get a result row as an enumerated array

array mysql_fetch_row ( resource $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_row($result);

echo $row[0]; // 42
echo $row[1]; // the email value
?>


5. (Optional) mysql_free_result — Free result memory

bool mysql_free_result ( resource $result )

mysql_free_result() will free all memory associated with the result identifier result.

mysql_free_result() only needs to be called if you are concerned about how much memory is being used for queries that return large result sets. All associated result memory is automatically freed at the end of the script's execution.

<?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'];
?>


6. (Optional) mysql_close — Close MySQL connection

bool mysql_close ([ resource $link_identifier = NULL ] )

mysql_close() closes the non-persistent connection to the MySQL server that's associated with the specified link identifier. If link_identifier isn't specified, the last opened link is used.

Using mysql_close() isn't usually necessary, as non-persistent open links are automatically closed at the end of the script's execution.

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


Title
List of Articles
번호 분류 제목 글쓴이 날짜 조회 수
36 PHP What does PHP keyword 'var' do? Hojung 2013.12.04 3440
35 MySQL How to reset MySQL’s root password on Mac OS X Hojung 2013.04.24 4805
34 PHP easy way would it be to use str_replace() on the whole file (파일내용변경) Hojung 2012.12.12 5111
33 MySQL Mysql SELECT inside UPDATE Hojung 2012.03.28 4941
32 PHP PHP-MySQLi-Wrapper Hojung 2012.03.28 6276
31 PHP MySQL 연결에서 결과값 얻는 PHP 예 (추천) Hojung 2012.03.28 4426
» PHP MySQL 주요 PHP 함수 (추천) Hojung 2012.03.28 4065
29 PHP Send email using SMTP server's settings Hojung 2012.03.13 4159
28 PHP Send email with mail() Hojung 2012.03.13 3772
27 PHP PHP Upload Progress Bar (APC) Hojung 2012.03.13 4518
26 PHP Rename if exists Hojung 2012.03.12 3898
25 PHP Program execution Functions (exec, system, shell_exec) Hojung 2012.03.11 5596
24 PHP email address validation Hojung 2012.03.11 3637
23 PHP file put contents Hojung 2012.03.01 4180
22 PHP The MVC Design Pattern for PHP Hojung 2012.02.14 5481
21 PHP PHP XML 파서 Hojung 2012.02.02 6509
20 MySQL 모든 호스트의 MySQL 액세스 Hojung 2012.01.27 4694
19 MySQL How Do I Enable Remote Access To MySQL Database Server? Hojung 2012.01.27 5354
18 MySQL 특정게시판의 메일리스트 정보 업데이트 Hojung 2011.12.15 5699
17 MySQL Xpressengine 정회원 메일주소만 Hojung 2011.12.15 5902
Board Pagination ‹ Prev 1 2 Next ›
/ 2

Designed by sketchbooks.co.kr / sketchbook5 board skin

나눔글꼴 설치 안내


이 PC에는 나눔글꼴이 설치되어 있지 않습니다.

이 사이트를 나눔글꼴로 보기 위해서는
나눔글꼴을 설치해야 합니다.

설치 취소

Sketchbook5, 스케치북5

Sketchbook5, 스케치북5

Sketchbook5, 스케치북5

Sketchbook5, 스케치북5