0

I am trying to run a simple query in Joomla 4 to retrieve order data to display in a module.

Testing the query with phpMyAdmin works fine, but despite generating no errors (set to Maximum) the below code returns no rows.

$db = Factory::getDbo();
$query = $db->getQuery(true);

// Prepare the query
$query = "SELECT *
          FROM #__orders
          WHERE year(order_date)=" . $orderyear . "
                and month(order_date)=" . $ordermonth . "
                and DAY(order_date)>=" . $beginning . "
                and DAY(order_date)<=" . $enddate . "
          order by order_id desc";
$db->setQuery($query);
// Load the results
$orders = $db->loadObjectList();

Am I doing anything wrong?

5
  • Phil, what I do not understand in your sql query is that you separately querying year, month and day, on order_date field, however you just want back results from the table based on a Date BETWEEN interval which could be selected with one WHERE line, if I understood your code well: stackoverflow.com/questions/3822648/…. So, is there a specific reason why you do not use the simpler approach and why do you need those extra variables for your query on this?
    – Zollie
    Commented Apr 16, 2023 at 4:53
  • So your sql query is working without a problem if the correct values are applied in the query and there are corresponding records in the table: You can check here too: sqlfiddle.com/#!9/37d61a/9
    – Zollie
    Commented Apr 16, 2023 at 6:33
  • 2
    But I would use this query on your records: sqlfiddle.com/#!9/37d61a/10 and then I would convert this to Joomla query as Peter did...
    – Zollie
    Commented Apr 16, 2023 at 6:34
  • And as I suspect the reason you get an empty array from this query is because you use four PHP variables in the query and one or more of them is null or has a wrong value. You should check them more. That is the only thing what I see as the real issue in this short post, but we cannot check them from here.
    – Zollie
    Commented Apr 16, 2023 at 13:10
  • 1
    For volunteers to reproduce the failure, we will need to see a sql fiddle with sample data, and the values that you are using for $orderyear, $ordermonth, $beginning, and $enddate. I am noticing that those variables are not quoted, so if they are anything other than integers or float values, your SQL logic/syntax will be compromised. Commented Apr 17, 2023 at 20:41

2 Answers 2

1

Never put variables directly in an SQL query. That's asking for (SQL injection) problems. Instead use Joomla's query object and escape the variables. Or more advanced: Since Joomla 4 you can use prepared statements.

I would write that code like this (it's without prepared statements)

use Joomla\CMS\Factory;

$db = Factory::getContainer()->get('DatabaseDriver');
$query = $db->getQuery(true)
    ->select('*')
    ->from($db->quoteName('#__user_profiles'))
    ->where('year(order_date) = ' . (int) $orderyear)
    ->where('month(order_date) = ' . (int) $ordermonth)
    ->where('DAY(order_date) >= ' . (int) $beginning)
    ->where('DAY(order_date) <= ' . (int) $enddate)
    ->order('order_id desc');
// The following line is only to display the SQL that you can test in phpMyAdmin
echo $query->dump();

$db->setQuery($query);
$orders = $db->loadObjectList();

In the query I made the variables safe by casting them to an integer using (int). Strings can be sanitized with $db->quote($some_string)

More info see: https://docs.joomla.org/Selecting_data_using_JDatabase

2
  • Hello Peter and Zollie,, thank you for highlighting my variables mistake...Was not really aware of this as I'm more on the dotnet side than php and would do things a bit differently there. As far as Peter's test, it unfortunately does not return any data and I really wonder if this is not a mysql issue. I am using a 5.7 database from my provider. Will try to get it to work with a mySQL 8 version to see if it gets better....
    – Phil91
    Commented Apr 15, 2023 at 20:11
  • 1
    This SQL logic (transferred from the asked question) may not be reliable when the date range crosses over to another month/year. Please consider refactoring the SQL as Zollie has recommended with BETWEEN and two Y-m-d date strings. Commented Apr 17, 2023 at 1:56
0

The code which works is:

$query = $db->getQuery(true)
    ->select('*')
    ->from($db->quoteName('#__orders'))
    ->where('YEAR(order_date) = ' . (int) $orderyear)
    ->where('MONTH(order_date) = ' . (int) $ordermonth)
    ->where ('DAY(order_date) BETWEEN ' . (int) $beginning. ' AND ' . (int) $enddate)
    ->order('order_id DESC');

I deliberately want to breakout the date so that I can search by year, month or days.

1
  • The code which works is: $query = $db->getQuery(true) ->select('*') ->from($db->quoteName('#__orders')) ->where('year(order_date) = ' . (int) $orderyear) ->where('month(order_date) = ' . (int) $ordermonth) ->where ('DAY(order_date) BETWEEN ' . (int) $beginning. ' and ' . $enddate) ->order('order_id desc'); Ivoluntarily want to breakout the date so that I can search by year, month or days.
    – Phil91
    Commented Apr 19, 2023 at 11:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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