Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

For one of my client's WP site, I'm making a plugin, where I'm using the db Query. I've two custom tables dedicated to the plugin:

  • user_req — stores user chosen posts along with some custom fields' data.
  • user_req_admin — stores some very basic things, with a special foreign key user_req_id.

Table: user_req
user_req view

Table: user_req_admin
user_req_admin view

My plan is to Join 5 tables with a single query:

  1. user_req
  2. user_req_admin
  3. posts
  4. postmeta
  5. users

Joining will be done by:

  • {$wpdb->user_req_admin}.user_req_id connects by {$wpdb->user_req}.id (Connects both the db with User Request ID)
  • {$wpdb->user_req}.user_id connects by {$wpdb->users}.ID (Fetch the User details using user_id)
  • {$wpdb->user_req}.post_id connects by {$wpdb->posts}.ID (Fetch the Posts details using the post_ids in an array)
  • {$wpdb->user_req}.post_id connects by {$wpdb->postmeta}.post_id (Fetch the Custom Fields using the post_ids in an array)

But for a startup, I tried joining the first two tables (#1 and #2), and eventually Failed!

Here's how I tried:

global $wpdb;
    $wpdb->print_error();
    $inbox_query = "SELECT
                          {$wpdb->user_req}.id,
                          {$wpdb->user_req}.dt_usr_sent,
                          {$wpdb->user_req}.dt_usr_rcvd,
                          {$wpdb->user_req}.user_id,
                          {$wpdb->user_req}.post_id,
                          {$wpdb->user_req}.qty,
                          {$wpdb->user_req}.msg_user,
                          {$wpdb->user_req}.msg_admin,
                          {$wpdb->user_req}.price,
                          {$wpdb->user_req_admin}.user_req_id
                        FROM
                          {$wpdb->user_req}
                        LEFT JOIN
                          {$wpdb->user_req_admin}
                        ON
                          {$wpdb->user_req}.id = {$wpdb->user_req_admin}.user_req_id";
    $inbox_rows = $wpdb->get_results( $inbox_query, OBJECT );

RESULT
<?php var_dump( $inbox_rows ); ?> shows an:

array
   empty

» WHAT'S THE PROBLEM WITH MY QUERY?

References

share|improve this question
    
What appear if you var_dump( $inbox_query )? –  G. M. Oct 8 '13 at 16:49
1  
The first basic step in debugging a custom query is to make sure it has no syntax errors. After you performed get_results() you'll have the query string in $wpdb->last_query. You can output this and copy it to an SQL shell (phpMyAdmin or whatever you use) to try the query there. –  s1lv3r Oct 8 '13 at 16:50
    
@G.M. I mentioned that already in the "Result" section - empty array. –  Mayeenul Islam Oct 8 '13 at 17:25
1  
@MayeenulIslam I said var dump the query not the result... that's the same thing said by s1lv3r –  G. M. Oct 8 '13 at 17:28
    
I already tried echo $inbox_query (same as @s1lv3r said: $wpdb->last_query, and same as G.M. said var_dump( $inbox_query )) - all shows a same result: SELECT .id, .dt_usr_sent, .dt_usr_rcvd, .user_id, .post_id, .qty, .msg_user, .msg_admin, .price, .user_req_id FROM LEFT JOIN ON .id = .user_req_id - all the tables' names are EMPTY! - why? –  Mayeenul Islam Oct 8 '13 at 17:33

1 Answer 1

up vote 2 down vote accepted

I don't see where you define your custom table names.

Please try:

$wpdb->user_req = $wpdb->prefix . 'user_req';

and

$wpdb->user_req_admin = $wpdb->prefix . 'user_req_admin';
share|improve this answer
    
if I put the prefix hard coded like: $wpdb->wp_user_req, isn't that same to $wpdb->prefix . 'user_req'? I actually skipped the prefix part here in this question, but I mentioned them hard coded. –  Mayeenul Islam Oct 8 '13 at 17:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.