Correct SQL query returning empty array?

Brian M.

Posted: 30 June 2008 01:41 PM

[ Ignore ]

Research Assistant

Total Posts: 739

Joined 07-18-2006

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_5

Is 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

Profile

Brian M.

Posted: 30 June 2008 01:45 PM

[ Ignore ] [ # 1 ]

Research Assistant

Total Posts: 739

Joined 07-18-2006

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 &bull; ";
                    }
                    $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 &bull; ";
                    }
                    $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'] . " &bull; " . $job['job_category'] . " <span class=\"subtitle\" <strong>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>\n<p>Your search returned no results. Please broaden your search criteria.</p>";    
        }

It’s definitely the plug-in somehow - this is a remake of a jobs page, and the old page returns the correct results.

Profile

Brian M.

Posted: 01 July 2008 08:12 AM

[ Ignore ] [ # 2 ]

Research Assistant

Total Posts: 739

Joined 07-18-2006

So I guess moving the thread means I can’t get “official” support for this issue? :(

Profile

Justin Hurlburt

Posted: 01 July 2008 01:15 PM

[ Ignore ] [ # 3 ]

Research Assistant

Total Posts: 582

Joined 04-29-2008

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.

Profile

‹‹ Plugin help Magpie and Podcast feeds ››

Username		Remember Me?
Password		forgot password?

Post Marker Legend
	New posts	Hot Topic with new posts	New Poll	Moved Topic	Sticky topic
	No new posts	Hot Topic with no new posts	Old Poll	Closed Topic	Announcements

Visitor Statistics
The most visitors ever was 1149, on July 16, 2007 10:33 AM
Total Registered Members:	61095	Total Logged-in Users:	29
Total Topics:	73931	Total Anonymous Users:	21
Total Replies:	398698	Total Guests:	480
Total Posts:	472629