Correct SQL query returning empty array?
Development and Programming
I’ve got an installation of version 1.5.2 built 20070404. I made a plugin to return some very specific results from the database (it has nested SQL calls which isn’t the smartest way to do it, but it was a quick and dirty thing).
I am printing out the SQL query before calling it, and if I take the SQL query and run it directly on the database it returns the correct results. If I run it from within the plugin it returns an empty set (no results). Here’s the SQL query itself:
SELECT DISTINCT (
d.field_id_5
) AS company
FROM exp_weblog_data AS d
JOIN exp_weblog_titles AS t ON t.entry_id = d.entry_id
WHERE t.status = 'open'
AND t.expiration_date > '1214850706'
AND (
d.field_id_10 > '1214850706'
OR d.field_id_10 = '0'
)
AND d.field_id_101 = 'Retail/Wholesale'
ORDER BY d.field_id_5Is this something that might be fixed up upgrading? It really isn’t an option to upgrade the whole site at this point (at least not without charging the client which I don’t want to do) - is it possible to grab a newer version of ‘db.mysql.com’ or something similar (if it isn’t against the license that is…
Let me know if you’d like to see the php itself. Thanks for any help!
Moved to Plugins: Technical Assistance by Moderator
Here’s the plug-in function (doubt the query above helps you much now that I think about it):
function Vbsr_member_jobs($str = '')
{
global $TMPL, $DB;
$now = time();
$type = ( ! $TMPL->fetch_param('type')) ? '' : $TMPL->fetch_param('type');
$category = ( ! $TMPL->fetch_param('job_category')) ? '' : $TMPL->fetch_param('job_category');
$county = ( ! $TMPL->fetch_param('county')) ? '' : $TMPL->fetch_param('county');
if($type) {
$type = " AND d.field_id_11 = '" . $type . "'";
}
if($category) {
$category = " AND d.field_id_101 = '" . $category . "'";
}
if($county) {
$county = " AND d.field_id_14 = '" . $county . "'";
}
$refinements = "t.status = 'open' AND t.expiration_date > '$now' AND (d.field_id_10 > '$now' OR d.field_id_10 = '0')";
// First SQL query gets unique company names with current job listings
$sql = "SELECT distinct(d.field_id_5) as company from exp_weblog_data AS d JOIN exp_weblog_titles AS t ON t.entry_id = d.entry_id where $refinements $type $category $county ORDER BY d.field_id_5";
$companies = $DB->query($sql);
if($companies->num_rows > 0 ) {
foreach($companies->result as $company) { // loop through each company
// Second SQL query gets unique city names that correspond to the company and that are current jobs (users can enter different job locations)
$sql = "SELECT distinct(d.field_id_12) as city, d.field_id_13 as state, d.field_id_14 as county from exp_weblog_data AS d JOIN exp_weblog_titles AS t ON t.entry_id = d.entry_id WHERE $refinements $type $category $county AND d.field_id_5 = '" . $company['company'] . "'ORDER BY d.field_id_12";
$locations = $DB->query($sql);
$i = 0;
foreach($locations->result as $location) { // Loop through each location for each company
//Start HTML output
$i++;
if($i == 1) {
$this->return_data .= "<div class=\"job_entry\">\n";
$this->return_data .= "<h1 class=\"job_link\"><a >" . ucwords(strtolower($company['company'])) . "</a></h1>\n";
}
$this->return_data .= "<h4 class=\"job_location\">";
if($location['state'] == "Vermont") {
$this->return_data .= " " . $location['county'] . " County • ";
}
$this->return_data .= $location['city'] . ", " . $location['state'] . "</h4>\n";
$this->return_data .= "<div class=\"location job_listing hidden\">\n";
$this->return_data .= "<h4 class=\"location_header\">";
if($location['state'] == "Vermont") {
$this->return_data .= " " . $location['county'] . " County • ";
}
$this->return_data .= $location['city'] . ", " . $location['state'] . "</h4>\n";
// END HTML output
// Third SQL query gets actual jobs for each location, corresponding to each company, and that are current
$sql = "SELECT t.title as title, t.url_title as url, t.entry_date as posted, d.field_id_101 as category, d.field_id_11 as type, d.field_id_10 as deadline from exp_weblog_data AS d JOIN exp_weblog_titles AS t ON t.entry_id = d.entry_id WHERE d.field_id_5 = '" . $company['company'] . "' AND d.field_id_12 = '" . $location['city'] . "' AND $refinements $type $category $county ORDER BY t.title";
$jobs = $DB->query($sql);
foreach($jobs->result as $job) { // Loop through each job
$this->return_data .= "<h4 class=\"job\"><a >" . $job['title'] . "</a></h4>\n";
$this->return_data .= "<h5 class=\"job\">" . $job['type'] . " • " . $job['job_category'] . " <span>POSTED:</strong> " . date('M j, Y', $job['posted']) . " <strong>DEADLINE:</strong> ";
if($job['deadline'] == 0) {
$this->return_data .= "Open until filled";
} else {
$this->return_data .= date('M j, Y', $job['deadline']);
}
$this->return_data .= "</span></h5>\n";
}
$this->return_data .= "</div>\n"; // END location div
}
$this->return_data .= "</div>\n"; // END job_entry DIV
}
} else { // NO RESULTS
$this->return_data .="<h1 class=\"error\">No Results</h1>\nYour search returned no results. Please broaden your search criteria.";
}It’s definitely the plug-in somehow - this is a remake of a jobs page, and the old page returns the correct results.
So I guess moving the thread means I can’t get “official” support for this issue? :(
Greetings,
Looking at the code block below:
$type = ( ! $TMPL->fetch_param('type')) ? '' : $TMPL->fetch_param('type');
$category = ( ! $TMPL->fetch_param('job_category')) ? '' : $TMPL->fetch_param('job_category');
$county = ( ! $TMPL->fetch_param('county')) ? '' : $TMPL->fetch_param('county');
if($type) {
$type = " AND d.field_id_11 = '" . $type . "'";
}
if($category) {
$category = " AND d.field_id_101 = '" . $category . "'";
}
if($county) {
$county = " AND d.field_id_14 = '" . $county . "'";
}I can see a potential problem with the way the $type, $category, $county are being handled. They will always evaluate to true, even if they are not specified. Your query would then be looking for a blank field, and that is probably why you are experiencing blank results.
An example of what I mean by ‘always evaluate to true’:
$type = ( ! $TMPL->fetch_param('type')) ? '' : $TMPL->fetch_param('type');
if($type) {
$type = " AND d.field_id_11 = '" . $type . "'";
}If you were to execute that, and not specify a $type. The result would still be true, and $type would equal ” AND d.field_id_11 = ” “. which would be asking the query to look for a blank field_id_11. This is what I believe is causing the blank results. If you were to manually enter this into the command line, you would probably specify something manually for each field, which is why it would work on the command line.
One quick troubleshooting solution would be to print out each one of those variables just before the query is executed. This should give you an idea as to if the query is looking for a blank field, instead of nothing at all.