Here in this tutorials, we have explained you about how to write the custom query in osclass.In osclass classified script, it is easy to write a custom query to fetch some result from the database or insert or update some fields into the database table.
We can use some predefined functions to print some fields from osclass database. But the predefined functions in osclass are limited, so we can't get all field's data from osclass database by the predefined functions. Another one important thing in osclass , there is no way to update or insert any fields using any predefined functions .
For that we need to write a custom query and execute it in osclass.
This post will be very much usefull to the osclass users those who customising the functionality to their needs in osclass.
If we want to use the custom queries in osclass , we need to have it as a seperate function inside theme funtions.php file. The main reason for keeping it as a seperate function is, to avoid the repetation of the code.
Example custom query for selecting the post count for a user in osclass is given below. Paste this function at the bottom of the functions.php file inside the theme folder.Make sure , not to leave any space after the function.
if( !function_exists('logged_user_post_count') ){
function logged_user_post_count($userId) {
$conn = getConnection();
$item_count = $conn->osc_dbFetchResults("SELECT count(*) FROM %st_item WHERE fk_i_user_id='%d' AND b_active='1'", DB_TABLE_PREFIX, $userId);
return $item_count[0]['count(*)'];
}
}
function logged_user_post_count($userId) {
$conn = getConnection();
$item_count = $conn->osc_dbFetchResults("SELECT count(*) FROM %st_item WHERE fk_i_user_id='%d' AND b_active='1'", DB_TABLE_PREFIX, $userId);
return $item_count[0]['count(*)'];
}
}
You can call the fucntion by passing user id to the function as parameter. Here $conn = getConnection();
is the connections string to establish the connection to the osclass database to execute the query.
$conn = getConnection();
This function is only used to select the fields from database using custom Query in osclass.
$conn->osc_dbFetchResults();
Example custom query for Inserting the a value to the database in osclass.Paste this function at the bottom of the functions.php file inside the theme folder.Make sure , not to leave any space after the function.
For Insert
$conn = getConnection();
$conn->osc_dbExec("INSERT INTO %st_pages (s_internal_name, b_indelible, dt_pub_date) VALUES ('subscription_email', 1,'%s' )", DB_TABLE_PREFIX, date('Y-m-d H:i:s'));
$conn->osc_dbExec();$conn->osc_dbExec("INSERT INTO %st_pages (s_internal_name, b_indelible, dt_pub_date) VALUES ('subscription_email', 1,'%s' )", DB_TABLE_PREFIX, date('Y-m-d H:i:s'));
This function is used to execute the custom query in osclass for Insert,update,delete .The table names which are used in the above examples are osclass table names to understand better for the users. This is the way to write custom query to select , insert, update, delete operations in osclass.
