Need help writing an SQL query for a select statement

debugcn Published at Dev

Johnny97

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
date_default_timezone_set("Europe/Berlin");
$timestamp = date("Y-m-d H:i:s");
//Database Query
$abfrage = "SELECT * FROM test_related_orders WHERE user_id = '$user_id' AND order_unlock_time <= '$timestamp'";

I think I can use an INNER JOIN and I've tried it here:

INNER JOIN wp_posts ON '$user_id' = post_author WHERE....

but it's to complicated for me as a beginner.

Here are my database structures:

The test_related_orders structure: The order_number looks like: DE-1016-835 and the user_id as a normal number/id like 1 in this example.

The wp_posts database structure:

The last picture is an example entry from the wp_posts. The problem is that I have to grab the order number from post_title and for this I have to remove the first word "Lieferschein" from this title to get the order_number and there's no extra field for the number...

Thank you for your help! I hope I explained it completely.

Dave P

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'

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-07-21

Comments

0 comments

From Dev

Related Related

Article

Need help writing an SQL query for a select statement

Need help writing an SQL query for a select statement

Need help on writing sql select statement

Need help for writing a complex SQL query

I need help in a select statement Query

Need Help in Writing a Query

Need help on SQL statement

Need help writing recursive query

Need help writing nested SQL query in C#

Need help in writing sql xquery

Need help on a SQL query

Need help for SQL query?

SQL query writing help - PostgreSQL

Need help writing a query (LEFT JOIN)

Need help writing compound linux query

Need help in properly writing an XML LINQ query

Need help writing remove() for global query scope

Need help in understanding this SQL query

Need help in making SQL query

Need help building sql query

Need Help In Sql like query

Need SQL Query Help Please

Need help to understand SQL query

Need help in understanding this SQL query

Need Help Optimizing SQL query

SQL need help building a query

SQL - need help creating query

Need help for Oracle SQL query

need help to generate SQL query

Need help writing an SQL query to count non duplicate rows (not a distinct count)

sql query select statement