0

I'm trying to deal with wpdb but unfortunetelly things that I'm creating are not even close to reaching what I need. I have data:

seller ID (post_author = user_id) - $seller_id

and

(post_parent = transaction_id) - $ordernum

I have 3 tables:

wp_dopbsp_calendars

id
user_id
post_id name
max_year
hours_enabled
hours_interval_enabled
price_min
price_max 
rating 
address address_en
address_alt
address_alt_en
coordinates

wp_dopbsp_reservations

id
calendar_id
language
currency
currency_code
check_in
check_out
start_hour
end_hour
no_items
price
price_total
refund
extras
extras_price
discount
discount_price
coupon
coupon_price
fees
fees_price
deposit
deposit_price
days_hours_history
form
address_billing
address_shipping
email
status
payment_method
payment_status
transaction_id
token
date_created

and wp_posts

ID
post_author
post_date
post_date_gmt
post_content
post_title
post_excerpt
post_status
comment_status
ping_status
post_password
post_name
to_ping
pinged
post_modified
post_modified_gmt
post_content_filtered
post_parent
guid
menu_order
post_type
post_mime_type
comment_count

I need to create table and get its only record:

by seller ID (post_author = user_id) get calendar ID ("id" table wp_dopbsp_calendars) and post_parent ("post_parent" from wp_posts)

And with these two look for rows in table wp_dopbsp_reservations, and get from there only the ones where transaction_id is post_parent (from wp_posts) and calendar_id is id (from wp_dopbsp_calendars)

The columns that I need from this table are transaction_id, calendar_id, check_in, check_out, start_hour, end_hour and price_total.

I couldn't go any further than this:

Select
  $wpdb->posts.post_author,
  $wpdb->dopbsp_calendars.user_id,
  $wpdb->dopbsp_reservations.price_total,
 $wpdb->dopbsp_reservations.check_in,
  $wpdb->dopbsp_reservations.check_out,
  $wpdb->dopbsp_reservations.start_hour,
  $wpdb->dopbsp_reservations.end_hour,
  $wpdb->dopbsp_calendars.id,
  $wpdb->posts.post_title,
  $wpdb->posts.ID As ID1,
  $wpdb->posts.post_parent,
  $wpdb->dopbsp_reservations.transaction_id
From
  wp_posts
  INNER JOIN $wpdb->posts.post_author ON $wpdb->dopbsp_calendars.user_id
  $wpdb->dopbsp_calendars
    On wp_posts.post_author = $wpdb->dopbsp_calendars.user_id,
  $wpdb->dopbsp_reservations
  • What where your attempts? – Max Yudin Oct 27 '16 at 12:31
  • @MaxYudin I added my attempt, which I realize is completely wrong – Rafal Oct 27 '16 at 15:23
0

You don't want to ask wpdb for each field. This is the wrapper for SQL queries

Like so (not tested):

<?php
$my_results = $wpdb->get_results(
    SELECT
        posts.post_author, dopbsp_calendars.user_id, dopbsp_reservations.price_total, dopbsp_reservations.check_in, dopbsp_reservations.check_out, dopbsp_reservations.start_hour, dopbsp_reservations.end_hour, dopbsp_calendars.id, posts.post_title, posts.ID As ID1, posts.post_parent, dopbsp_reservations.transaction_id
    FROM wp_posts
    INNER JOIN
        posts.post_author
    ON
        dopbsp_calendars.user_id
        dopbsp_calendars
    ON
        wp_posts.post_author = dopbsp_calendars.user_id,
        dopbsp_reservations
);

Then see what you get in this object:

<?php
echo '<pre>';
print_r($my_results);
echo '</pre>';

Warning: Methods in the wpdb() class should not be called directly. Use the global $wpdb object instead! Class Reference/wpdb

So, it's better to make $wpdb global:

global $wpdb;
$my_results = $wpdb->get_results(..................);

Notice: I didn't checked the query itself.

  • Unfortunately I'm getting error #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'dopbsp_calendars ON wp_posts.post_author = dopbsp_calendars.user_i' at line 8 – Rafal Oct 27 '16 at 23:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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