How to convert a mysqli query to wpdb

Resolved

(@starapple)

Hello, I’d appreciate some help in converting this mysqli query to using wpdb:

    $ret = array( 0.0, 0.0, 0.0, 0.0, 0.0, 0.0 );
    $q = "SELECT id,user,status,completed,track,coursecost,testcost," .
         "gst,pst,discount FROM exams WHERE student=$id AND status=" .  
         $theExam->STATUS_COMPLETED . " ORDER BY completed";

    if (!( $result = mysqli_query($Connection, $q) ) ) {
        if ($show ) {
            makeInstruction(
                4, "Database Error: Could not retrieve " .
                "student's exams: $q" 
            );
        }

        return( $ret );
    }

From my reading of the developer reference it should look something like:

global $wpdb;
    $q = $wpdb->get_results( "SELECT id,user,status,completed,track,coursecost,testcost," .
         "gst,pst,discount FROM {$wpdb->prefix}exams WHERE student=$id AND status=" .  
         $theClass->STATUS_COMPLETED . " ORDER BY completed");

How do I handle the error if the foregoing is the correct approach?

    if (!( $result = mysqli_query($Connection, $q) ) ) {
        if ($show ) {
            makeInstruction(
                4, "Database Error: Could not retrieve " .
                "student's exams: $q" 
            );
        }

        return( $ret );
    }

Thanks.

Viewing 4 replies - 1 through 4 (of 4 total)

Dion
(@diondesigns)

1 year, 8 months ago
If you add this to the top of your code:
```
global $wpdb;
$Connection = $wpdb->__get('dbh');
```
you can use the rest of your code as-is. This assumes the variables $id and $show exist, the class/object theExam exists, the table exams is in the WordPress database, and you actually do something with the result set as opposed to returning the array of zeros you initially create.
Thread Starter starapple
(@starapple)

1 year, 8 months ago
Thanks for your response @diondesigns. Just to get this clear. At present in my header file I have the below that takes its values from a definitions file:
```
$Connection = mysqli_connect($DB_SERVER, $DB_USER, $DB_PWD, $DB_NAME);
if (!$Connection) {
die("Database connection failed: " . mysqli_connect_error());
```
If I substitute what you’ve shown me, will my header then look like?:
```
global $wpdb;
$Connection = $wpdb->__get($DB_NAME);
```
Or, from searching wordpress.org (https://developer.wordpress.org/reference/classes/wpdb/__get/), should that be $Connection = $wpdb->__get('table_name'); ?

The former would certainly make life easier for me and even better just to use your example as is if I’m to go by this: https://docs.w3cub.com/wordpress/classes/wpdb/_do_query/.

Thanks.
- This reply was modified 1 year, 8 months ago by starapple.
- This reply was modified 1 year, 8 months ago by starapple.
Dion
(@diondesigns)

1 year, 8 months ago

Are you attempting to connect to an external database, or is the exams table in your WordPress database? The code I provided assumes the latter and should be used as-is with no changes. The code will also work if the WordPress DB user has access privileges to the external database, though you’ll need to change the queries to include the database name when referencing tables in the external database.

Otherwise, just use your code as-is and ignore $wpdb. It is not needed to connect to external databases and in fact will add significant overhead.
Thread Starter starapple
(@starapple)

1 year, 8 months ago
The plan is to convert an existing PHP app to a WP plugin and add its tables to the WP database. What you say now gives me hope that it will not be as tedious as I had imagined. It might come down to incorporating the admin portions into WP options/settings pages and adding the sql on activation. Thanks a lot @diondesigns
- This reply was modified 1 year, 8 months ago by starapple.

Viewing 4 replies - 1 through 4 (of 4 total)

The topic ‘How to convert a mysqli query to wpdb’ is closed to new replies.

Views