Wordpress: Custom Queries

gistfile1.txt

//*** Article from http://wp.tutsplus.com/tutorials/creative-coding/writing-custom-queries-in-wordpress/

//The query function needs a string containing the custom query. The returnng value is an integer corresponding //to the number of rows affected/selected, and false when there is an error.

$query = "SELECT COUNT(apple) FROM fruits";
$wpdb->query($query);

//This function gets multiple rows when executing a query. By default the result of the function is an array.
$query = " 
    SELECT * 
    FROM wp_terms wt 
    INNER JOIN wp_term_taxonomy wtt ON wt.term_id = wtt.term_id 
    WHERE wtt.taxonomy = 'post_tag' AND wtt.count = 0";  
  
$wpdb->get_results($query);

//This will return one variable from the database, but the complete result of the query is cached for later use. Returns NULL if no result is found.
$query = "SELECT COUNT(*) FROM users";  
$wpdb->get_var($query);

//A complete row will be returned as a result of the function, which can be an object, an associative array, or a numerically indexed array. NULL is the result when no matching data is found. result_type can be OBJECT, ARRAY_A or ARRAY_N (object, associative array or numbered array). Offset is an integer with a default of 0.
$query = " 
SELECT * FROM wp_posts 
WHERE post_type = 'post'";  
  
$wpdb->get_row($query, ARRAY_A, 3);

//For getting a column, use this function. Output will be a dimensional array. An empty array will be returned if no result is found. The second parameter is the column offset.
$query = " 
SELECT * FROM wp_posts 
WHERE post_type = 'post'";  
  
$wpdb->get_col($query, 3); 

//You can protect SQL queries against SQL injection attacks. In short data in queries must be SQL-escaped before the query is executed to prevent injection attacks. This can be easily done with the prepare method. In the following example, the values ’10′, ‘monkey’ and ‘apple’ will be escaped when used in this method.
$wpdb->query( $wpdb->prepare(  
    "INSERT INTO test_table (post_id, animal, food) VALUES ( %d, %s, %s )",  
    array(  
        10,  
        'monkey',  
        'apple'  
    )  
)); 

//Error msg
$wpdb->show_errors();  
$wpdb->hide_errors();  

//Cache control
$wpdb->flush();  


//Insert Data
$wpdb->insert($table, $data, $format);  
  
$wpdb->insert(  
    'foods',  
    array(  
        'fruit' => 'apple',  
        'year' => 2012  
    ),  
    array(  
        '%s',  
        '%d'  
    )  
);

//Update Data
$wpdb->update(  
    'foods',  
    array(  
        'fruit' => 'apple',  // string  
        'year' =>  'value2'  // integer (number)  
    ),  
    array( 'ID' => 1 ),  
    array(  
        '%s',   // value1  
        '%d'    // value2  
    ),  
    array( '%d' )  
);  

//You can get information about the columns of the most recent result with this function. When a function has returned an OBJECT and there are properties you don’t know much about, this can be useful.
$wpdb->get_col_info('type', offset);