PHP SQL query for local DB other than $wpdb

  • Resolved rlohmj

    (@rlohmj)


    3 years, 1 month ago

    Hi Peter,

    I love this plugin and Code Manager for being able to easily show dynamic content on my pages.

    However, I need to utilize one of my existing database table that contains dynamic content for my wp pages across different wp installations.

    The current solution I am working on in to ‘replicate’ this database table in a local database (not the wordpress db) and add the wordpress db user to this local db. (I did not want to use remote db connection for security reasons.)

    I know that $wpdb have “esc_like()” and “prepare()” functions for safe execution of SQL query and am currently using it.
    Does $wpdadb have the same or similar functions for safe execution of SQL query?

    Here is a sample snippet of my current sql query using $wpdb that needs converting to $wpdadb:

    
$param1 = $wpdb->esc_like($wpda_shortcode_args['param1']);
    $param2 = '%0' . $wpdb->esc_like($param2) . '0%';
    $result = $wpdb->get_results(
        $wpdb->prepare(
            "SELECT * FROM my_table WHERE my_col2 LIKE %s AND my_col3 LIKE %s ORDER BY my_col2 ASC",
            $param1,
            $param2
        )
    );
Viewing 4 replies - 1 through 4 (of 4 total)
  • Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    Hi @rlohmj,

    >>> I know that $wpdb have “esc_like()” and “prepare()” functions for safe execution of SQL query and am currently using it. Does $wpdadb have the same or similar functions for safe execution of SQL query?

    Every method available in the wpdb class is also available in the wpdadb class. In fact wpdadb is just a wrapper class of wpdb. If you are calling a wpdadb method, you are in fact calling a wpdb method. So wpdadb has the same safe execution as wpdb.

    You can use wpdadb to connect to a remote database, but you can also use it to connect to a local database. Here is an example of a remote connection:
    https://code-manager.com/code/?wpda_search_column_code_name=Execute%20query%20from%20PHP%20shortcode

    To connect to a local database you just need to change the database name:
    $wpdadb = WPDataAccess\Connection\WPDADB::get_db_connection( 'your-local-db-name' );

    Hope this helps. Let me know if you need further assistance.

    Good luck ??
    Peter

    Thread Starter rlohmj

    (@rlohmj)

    Hi Peter,

    Just to clarify any remaining doubts I have …

    That means I need to call
    $wpdadb = WPDataAccess\Connection\WPDADB::get_db_connection( 'your-local-db-name' );
    rather than the usual
    global $wpdb;
    before this set of revised (replaced $wpdb with $wpdadb) codes

    $param1 = $wpdadb->esc_like($wpda_shortcode_args['param1']);
$param2 = '%0' . $wpdadb->esc_like($wpda_shortcode_args['param2']) . '0%';
$my_result = $wpdadb->get_results(
    $wpdadb->prepare(
        "SELECT * FROM my_table WHERE my_col2 LIKE %s AND my_col3 LIKE %s ORDER BY my_col2 ASC",
        $param1,
        $param2
    )
);

    and “your-local-db-name” is what is seen in the “Database” text field in your plugin screenshot here: WP Data Access plugin sample screenshot on wp.

    Because my db is local rather than remote, it would be something like “dfg_d68fd” rather than “rdb:dfg_d68fd”.

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    Hi @rlohmj,

    You are correct! ??

    If you want to be sure the plugin has access to your local database, start the Data Explorer and check if your database shows up in the list of available databases (see image below).

    https://ibb.co/GCT80Tt

    The image shows I have access to database max for example, so I could replace:
    your-local-db-name
    With:
    max

    Please check your credentials if you local database is not in the list. This is usually the result of missing privileges.

    Hope this helps,
    Peter

    Thread Starter rlohmj

    (@rlohmj)

    Hi Peter,

    Thanks for the clarification.

    I’ve tested my updated code and it works great!

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘PHP SQL query for local DB other than $wpdb’ is closed to new replies.