I need help with an SQL query.
I want to show lines from my test_related_orders table where the current user id equals the user_id in my test_related_orders table and where order_unlock_time (from my table) is <= acutal timestamp. Until here all works fine. But now it gets complicated.
I also need to check/restrict in my wp_posts table if there is an post_author equals to my test_related_orders user_id who has an existing order_number same as from my test_related_orders order_number.
If this statement is true the associated lines from my test_related_orders will be shown. If this statement is false there should be no associated lines.
(So when I delete an order in the woocommerce frontend the statement should'nt return a string for this entry.)
Here is my existing code:
$user_id = get_current_user_id();
//Timestamp for current system time
$timestamp = date("Y-m-d H:i:s");
$abfrage = "SELECT * FROM test_related_orders WHERE user_id = '$user_id' AND order_unlock_time <= '$timestamp'";
INNER JOIN wp_posts ON '$user_id' = post_author WHERE....
First problem to solve is getting the order number from the post_title so that you can compare the values in your inner join. A primitive way of doing this, assuming the order number is always in form AA-DDDD-DDD would be
SELECT right(post_title, 11) as posts_order_number
As you've already discovered, an inner join is what you need, as it'll only return results where the tables match. Note you usually join tables based on their columns, rather than directly comparing to your '$user_id' string (and you've already filtered for this in your where clause).
You can join on more than one field at a time, to match on order number as well as user_id.
Your query now becomes:
SELECT * FROM test_related_orders tro INNER JOIN wp_posts p ON tro.user_id = p.post_author AND tro.order_number = right(p.post_title, 11) WHERE tro.user_id = '$user_id' AND order_unlock_time <= '$timestamp'