WordPress全域資料庫物件$wpdb – 資料查詢
$wpdb是WordPress中用來操作所有資料表格的全域資料庫物件。
function myFunction() {
global $wpdb;
//資料庫操作程式碼…
}
方法：
- get_var
- get_row
- get_col
- get_results
get_var
從一個query中返回一個值// 取得WordPress中一個特定使用者(id=1)的email
function get_user_email(){
global $wpdb;
$email = $wpdb->get_var("SELECT user_email from ".$wpdb->prefix."users WHERE ID = 1");
return $email;
}
Another example
<?php
global $wpdb; //已經連結WordPress資料庫物件
$email = $wpdb->get_var("SELECT user_email from ".$wpdb->prefix."users WHERE ID = 1");
?>
<h1><?php echo $email; ?></h1>
get_row
從一個Query中取得一個資料列 語法：function myFunction(){
global $wpdb;
$wpdb->get_row("Query here", Format_Parameter);
// Rest code here
}
Format_Parameter：
- OBJECT (預設) – 預設將回傳的資料以物件的形式返回。
- ARRAY_A – 返回的資料為一個關聯陣列。
- ARRAY_N – 返回的資料為一個數字索引陣列。
// 取得WordPress中一個特定使用者(id=1)所有的資料
function get_user_data(){
global $wpdb;
$userdata = $wpdb->get_row("SELECT * from ".$wpdb->prefix."users WHERE ID = 1");
//$userdata = $wpdb->get_row("SELECT * from ".$wpdb->prefix."users WHERE ID = 1", ARRAY_A);
//$userdata = $wpdb->get_row("SELECT * from ".$wpdb->prefix."users WHERE ID = 1", ARRAY_N);
return $userdata;
}
Another Example
<?php
global $wpdb; //已經連結WordPress資料庫物件
$userdata = $wpdb->get_row("SELECT * from ".$wpdb->prefix."users WHERE ID = 1");
?>
<h1><?php echo $userdata->display_name; ?></h1>
取得的資料依第2個參數分別為：
OBJECT
stdClass Object ( [ID] => 1 [user_login] => admin [user_pass] => $P$BWsoQcNmbugCw7dQOdzkCz9pkH5ctY/ [user_nicename] => admin [user_email] => sample@test.com [user_url] => Something URL you have [user_registered] => 2020-07-25 06:22:13 [user_activation_key] => [user_status] => 0 [display_name] => admin )
ARRAY_A
Array ( [ID] => 1 [user_login] => admin [user_pass] => $P$BWsoQcNmbugCw7dQOdzkCz9pkH5ctY/ [user_nicename] => admin [user_email] => sample@test.com [user_url] => something url you have [user_registered] => 2020-07-25 06:22:13 [user_activation_key] => [user_status] => 0 [display_name] => admin )
ARRAY_N
Array ( [0] => 1 [1] => admin [2] => $P$BWsoQcNmbugCw7dQOdzkCz9pkH5ctY/ [3] => admin [4] => sample@test.com [5] => http://localhost/woocommerce-session [6] => 2020-07-25 06:22:13 [7] => [8] => 0 [9] => admin )
get_col
從一個Query取得一個特定欄位的資料，返回的資料型為陣列。// 取得所有文章的標題
function get_posts_title(){
global $wpdb;
$posts_title = $wpdb->get_col("SELECT post_title from ".$wpdb->prefix."posts");
return $posts_title;
}
取得的資料例：
Array
(
[0] => Hello world!
[1] => Sample Page
[2] => Privacy Policy
[3] => Auto Draft
[4] => Library Tabs
)
使用foreach將陣列的值一一迭代出來。
// 取得所有文章的標題
function get_posts_title(){
global $wpdb;
$posts_title = $wpdb->get_col("SELECT post_title from ".$wpdb->prefix."posts");
return $posts_title;
}
$posts_title = get_posts_title();
foreach ($posts_title as $pt) {
echo $pt.'<BR>';
}
Another Example
<?php
global $wpdb;
$posts_title = $wpdb->get_col("SELECT post_title from ".$wpdb->prefix."posts");
?>
<ol>
<?php
foreach ($posts_title as $pt) {
echo '<li style="font-size : 20px;">'.$pt.'</li>';
}
?>
</ol>
get_results
從一個Query取得一般性的結果，通常是多個列多個行的資料型式。 語法function myFunction(){
global $wpdb;
$wpdb->get_results("Query here", Format_Parameter);
// 資料操作程式碼…
}
Format_Parameter：
- OBJECT (預設) – 預設將回傳的資料以物件的形式返回。
- ARRAY_A – 返回的資料為一個關聯陣列。
- ARRAY_N – 返回的資料為一個數字索引陣列。
// 取得2筆文章(posts)
function my_get_posts(){
global $wpdb;
$all_posts = $wpdb->get_results("SELECT post_title, post_excerpt, post_status, post_type, post_name from ".$wpdb->prefix."posts limit 2");
return $all_posts;
}
Another example
<?php
global $wpdb;
$all_posts = $wpdb->get_results("SELECT post_title, post_name from ".$wpdb->prefix."posts limit 2");?>
<ol>
<?php
foreach ( $all_posts as $pt) {
echo '<li style="font-size : 20px;">'.$pt->post_title.','.$pt->post_name.'</li>';
}
?>
</ol>
依據上面的SELECT字串，該Query將posts這個資料表格取得post_title, post_excerpt, post_status, post_type, post_name這5個欄位的資料，限定2筆。
取出的資料依型式分別是：
預設(Object)
得到的資料形式是：Array
(
[0] => stdClass Object
(
[post_title] => Hello world!
[post_excerpt] =>
[post_status] => publish
[post_type] => post
[post_name] => hello-world
)
[1] => stdClass Object
(
[post_title] => Sample Page
[post_excerpt] =>
[post_status] => publish
[post_type] => page
[post_name] => sample-page
)
)
ARRAY_A
如果Query是用： $wpdb->get_results(“SELECT post_title, post_excerpt, post_status, post_type, post_name from “.$wpdb->prefix.”posts limit 2”, ARRAY_A);那麼得到的資料形式將是：
Array
(
[0] => Array
(
[post_title] => Hello world!
[post_excerpt] =>
[post_status] => publish
[post_type] => post
[post_name] => hello-world
)
[1] => Array
(
[post_title] => Sample Page
[post_excerpt] =>
[post_status] => publish
[post_type] => page
[post_name] => sample-page
)
)
ARRAY_N
$wpdb->get_results(“SELECT post_title, post_excerpt, post_status, post_type, post_name from “.$wpdb->prefix.”posts limit 2”, ARRAY_N);Array
(
[0] => Array
(
[0] => Hello world!
[1] =>
[2] => publish
[3] => post
[4] => hello-world
)
[1] => Array
(
[0] => Sample Page
[1] =>
[2] => publish
[3] => page
[4] => sample-page
)
)