Database request

Started by dany, Oct 06, 2022, 01:32 AM

Previous topic - Next topic

danyTopic starter

Hello, could you guide me through the process of querying the phpmyadmin database by date?

Here is a common query: SELECT * FROM store__product WHERE verification='1' AND price='-1' ORDER BY date DESC
However, I have an additional table that contains the product_id index, the ID of the product in the first table, and a date in seconds. After sorting by date, I want to display the newest product first, and display all products from both databases in descending order of relevance (based on date).


To include all fields in a query, you can use a.*. However, this may not be visually clear when reviewing the code after a few months. If you can't recall what the selected fields are called, you will have to check the database or print out the information for clarification. Although this is a minor inconvenience, it is still unnecessary.

The following query can be used instead:

FROM store__product a
LEFT JOIN store__info b
WHERE a.verification=1 AND a.price=-1

This query sorts data by date and displays the relevant information clearly without the need for external references.


To query the phpMyAdmin database by date and display the newest products first, you can use a JOIN operation to combine both tables and sort the results accordingly. Here's an example query that achieves this:

FROM store__product AS p
JOIN additional_table AS a ON p.product_id = a.product_id
WHERE p.verification = '1' AND p.price = '-1'

In this query:

- The "store__product" table is aliased as "p," and the "additional_table" is aliased as "a."
- The JOIN operation connects the "product_id" column in both tables.
- The WHERE clause filters the products that have verification set to '1' and price set to '-1'.
- The ORDER BY clause sorts the results based on the date column in the "additional_table" in descending order first and then the date column in the "store__product" table.

By doing this, the newest product will be displayed first, and the rest of the products will follow in descending order of relevance based on the date.