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 ) )