$wpdb returns no results with SELECT query on custom post type, works on default post type

Question

EDIT: Adding more background info.

I've coded a plugin that adds a meta box to the post page. When the post is published, the plugin will generate a random ID for the post, and insert the random ID and the post guid to a custom table in the database. After publishing, or upon editing, the plugin queries the custom table to retrieve the random ID for the post based on the post guid, and prints out the random ID in the meta box.

Everything works great until I change the post type in the add_meta_box function from 'post' to 'quilt'. The plugin will generate the random ID and save it and the post guid to the table, but presents this error in the meta box:

Notice: Trying to get property of non-object in /home/qultshow/public_html/wp-content/plugins/randomID/randomID.php on line 126

instead of the random ID. If I use

var_dump( $query );
$wpdb->print_error();

to try to debug, I see:

NULL

WordPress database error: [] SELECT * FROM wp_customtable WHERE qrci_guid = "http://testsite.com/?post_type=quilt&p=213"

If I copy the query from the Wordpress database error directly and run it in phpmyadmin, a row is returned as expected. So, the query is coded correctly, and the plugin works on the default post type of "post" but not on the custom post type of "quilt". The only change in the plugin is in this code:

add_meta_box( 'randomid_sectionid', 'Random ID', 'cd_meta_box_cb', 'quilt', 'normal', 'high' );

where 'post' was changed to 'quilt'.

Is there something odd or different about querying the database from a custom post type that I need to know about to make this work?

Notes: This plugin is not for usage by anyone but me on one website where I need this weird functionality. Also, this is a multi-site installation of wordpress if that makes any difference. I can't use post metadata for this since I need to be able to query this data without querying every postmeta table on the network, so it has to be a custom table in the database.

Here's the pertinent code that is not returning a row on custom post types, but does return a row on default post types. Maybe it's not the best written code, but it does work on default post types:

global $wpdb;
$wpdb->show_errors();
$sql = 'SELECT * FROM ' . $wpdb->base_prefix . 'customtable WHERE qrci_guid = "'. $post->guid . '"';
$query = $wpdb->get_row($sql);
$_quilt_id = $query->quilt_id;
var_dump( $query );
$wpdb->print_error();

And again, here are the errors on running:

Notice: Trying to get property of non-object in /home/qultshow/public_html/wp-content/plugins/metaboxplugin/metaboxplugin.php on line 126 somewebsite/?post_type=quilt&p=49

NULL

WordPress database error: [] SELECT * FROM wp_customtable WHERE qrci_guid = "http://testsite.com/?post_type=quilt&p=213"

I initially thought that there was an issue with the ampersand in the guid that the query searches on, because it was getting inserted into the custom table as & instead of just an ampersand. I fixed that problem, and now it is inserted into the table correctly, as just an ampersand, so there shouldn't be any issue pulling a record out of the table. That being said, on default post types, the ampersand is not in the guid, and the code works great. Maybe that really is the issue, but I don't know how to fix it.

Can anyone help out with this?

Answer 1

I've solved the problem myself though it is probably not be the right way to do it. Note that I've had to type "website-address" instead of the actual URL because system here won't let me use more than one URL in a post without more reputation. That makes it quite difficult to convey what's actually going on of course, but there is no choice for that it seems. In the actual code and in the table, the actual URL is there.

The issue is that when trying to select a record from the table where the qrci_guid is a url like "http://testsite.com/?post_type=quilt&p=213" it seems to hang up on the ampersand. In the beginning, the $post->guid was being inserted into the custom table like this:

website-address/?post_type=quilt&p=213

but returns this error when trying to retrieve the data:

WordPress database error: [] SELECT * FROM wp_customtable WHERE qrci_guid = "website-address/?post_type=quilt&p=213"

The select statement is looking for & and the record contains &. I tried stripping the ampersand out of the guid before inserting into the table and stripping the ampersand out of the $post->guid before building the query, but that didn't work to get the record either. That resulted in this in the table:

website-address/?post_type=quiltp=239 

which was fine, because I don't really need the ampersand in there anyway for my purposes, but the error looked like this:

WordPress database error: [] SELECT * FROM wp_customtable WHERE qrci_guid = "website-address/?post_type=quiltamp;p=239"

so still no record returned.

In the end, I solved this by using this code before inserting the $post->guid into the table:

$clean_guid = str_replace("&", "amp;", $post->guid);

and inserting the $clean_guid instead of the $post->guid, and then using this code before querying the table:

$clean_guid = str_replace("&", "", $post->guid);

and then using this query to retrieve the data:

$sql = 'SELECT * FROM ' . $wpdb->base_prefix . 'customtable WHERE qrci_guid = "'. $clean_guid . '"';
$query = $wpdb->get_row($sql);

which returned the row as expected.

As I said, this is probably not the right way to do this; I know there are PHP functions like htmlentities() and urlencode() and other such functions, one of which might have been the right one to use in this situation, and I even used to know how to use all of those functions effectively three or four years ago when I was doing a lot of PHP coding. My coding experience is rusty obviously and I couldn't make any of those other functions work, but this solution worked for me. If there's a better way, I'd be glad to hear it.

Answer 2

If your query fails you will get the "property of non-object" error. You should be checking to see that the query returned a result before trying to use it.

$query = $wpdb->get_row($sql);
$_quilt_id = $query->quilt_id;
if (!empty($_quilt_id)) {
  // your code
}

Secondly, Your queries don't match. One ends with &p=49, the other with &>p=49. What you are doing should work if the queries are correct. I am sure that is the root of the problem.

I would try to avoid searching for complex strings like that though. Isn't there a simple value you can use, such as an ID?