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.

Improve this question
2
  • 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, 2016 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, 2016 at 23:24

1 Answer 1

Reset to default
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);

Improve this answer
7
  • Still returning 0 rows when I add the percent.
    – tim92109
    Oct 11, 2016 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, 2016 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, 2016 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, 2016 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, 2016 at 2:20

Your Answer

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

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