1

The following query will not return any results...

if (empty($from_date)) {
    $from_date = 2000-01-01;
}
if (empty($to_date)) {
    $to_date = 3000-01-01;
}
if (empty($_POST['supervisor'])) {
    $_POST['supervisor'] = "%";
}
$query
    ->select(array('id', 'job_date', 'emp_name', 'super', 'qty_jobs', 'job_id', 'job_name', 'qty_measure', 'qty_install', 'qty_shop', 'qty_misc'))
    ->from($db->quoteName('admin_service_reports'))
    ->where(($db->quoteName('job_date') . ' >= ' . $db->quote($from_date).' AND '.$db->quoteName('job_date') . ' <= ' . $db->quote($to_date)).' AND '.$db->quoteName('emp_name').' LIKE "'.$_POST['employee'].'"')
    ->order('id DESC');

But if I remove the employee comparison in the where part like...

$query
    ->select(array('id', 'job_date', 'emp_name', 'super', 'qty_jobs', 'job_id', 'job_name', 'qty_measure', 'qty_install', 'qty_shop', 'qty_misc'))
    ->from($db->quoteName('admin_service_reports'))
    ->where(($db->quoteName('job_date') . ' >= ' . $db->quote($from_date).' AND '.$db->quoteName('job_date') . ' <= ' . $db->quote($to_date)).'')
    ->order('id DESC');

It works fine.

I've tried using parenthesis and dozens of other things and it just will not return any rows. Just wondering if anyone sees anything that I haven't in the past 5 hours. I don't know if this is enough info to troubleshoot even. I'm on the fence as far as scrapping the JDatabase code here and going with standard PHP/MySQL.

  • Try using the same query directly on your database, you can get the query using echo $query; die; if think there is no matching results. – Pratyush Oct 11 '16 at 19:47
  • Don't see any issues when I echo it. I actually did that previously by purposely putting an error in near the end of the statement (and viewing the code up to the purposely placed error). – tim92109 Oct 11 '16 at 23:24
0

The like clausule is incorrect. Intead of

LIKE "'.$_POST['employee'].'"

It should be

LIKE "%'.$_POST['employee'].'%"

Note the % sorrounding your text.

By the way, it is not a good practice to inject globals like $_POST or $_GET into queries because this produces an SQL injection vulnerable code. You better get post data through JInputclass and, as you did before, quote and escape this data with $db->quote($posted_data, true);

  • Still returning 0 rows when I add the percent. – tim92109 Oct 11 '16 at 23:26
  • Oh, thanks also for the info on security. I was thinking about changing the code to be more secure once it's finished and working. But not too concerned as it's somewhat protected by Joomla since you have to belong to the 'employee' usergroup to access. So the only people with access to try to inject would be construction employees. But it would be good probably in case someone outside the business gets ahold of an employee's credentials – tim92109 Oct 11 '16 at 23:28
  • My issue was actually an issue with the datepicker that was selecting the value for the dates. I have no idea why it worked with the dates clausule only (would have thought it would be the opposite). But I found your answer never the less. – tim92109 Oct 12 '16 at 16:34
  • A second mistake in the code shown: $from_date = 2000-01-01; better use $from_date = "2000-01-01" (the same for $to_date) Otherwise php may try to do the operation. When in trouble with queries use a echo (string)$query to see what is doing. – Pep Lainez Oct 12 '16 at 16:40
  • Also good info [thanks!]. One thing I should mention is that when I did echoing the query and pasting it directly into phpMyAdmin, I noticed that the column 'super' was being interpreted as something else (was bright bold orange). So I changed that to 'super_name'. – tim92109 Oct 13 '16 at 2:20

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.