Simple query not working

[eluser]mabright[/eluser]
I know this is a simple problem but I haven't coded in awhile and I know I'm missing something simple. It's driving me crazy.

If I do the following, I get no error but no result returned.

Code:

function get_region($country,$state)
    {
        $country = strtoupper(trim($country));
        $state   = strtoupper(trim($state));
        
        $sql = "SELECT `region` FROM `my_region_table`
                       WHERE UPPER(`country`) = '".$country."'
                       AND   UPPER(`description`) = '".$state."'";
                
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0)
        {
            $row = $query->row();
            return $row->region;
        }
    }

However, when I hard code the values, all works fine.

Code:

function get_region($country,$state)
    {
        $country = strtoupper(trim($country));
        $state   = strtoupper(trim($state));
        
        $sql = "SELECT `region` FROM `my_region_table`
                       WHERE UPPER(`country`) = 'US'
                       AND   UPPER(`description`) = 'GEORGIA'";
                
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0)
        {
            $row = $query->row();
            return $row->region;
        }
    }

[eluser]mi6crazyheart[/eluser]
Hmmm, i suggestion just echo that $sql variable in both cases & watch out for any difference between the SQL queries generated in both cases...

[eluser]mabright[/eluser]
I did that, both versions of the SQL string look fine and when I cut and paste the sql into my DB, I get results. It's real weird and driving me nuts.

When hard code or using variables both SQL strings look like below.
SELECT `region` FROM `my_region_table` WHERE UPPER(`country`) = 'US' AND UPPER(`description`) = 'GEORGIA'

However, the version that uses the variables doe not satisfy

Code:

if ($query->num_rows() > 0){...

[eluser]LuckyFella73[/eluser]
I'm sure you can write it a bit shorter for you
only get one result anyway but try if this code
gives you a result:

Code:

if ($query->num_rows() > 0)
   {
        foreach ($query->result() as $row)
           {
               return $row->region;
           }
   {

[eluser]mabright[/eluser]
Tried and failed. As I said, this is weird, it should be working just fine. Below is the all the code from my model and the call from the controller. Maybe I'm missing something.

Code:

class Venue_model extends Model {
    
    function Venue_model()
    {
        parent::Model();
    }
    
    public function get_region($country,$state)
    {
        $country = strtoupper(trim($country));
        $state   = strtoupper(trim($state));
        
        $sql = "SELECT `region` FROM `my_region_table`
                       WHERE UPPER(`country`) = ?
                       AND   UPPER(`description`) = ?";

        $query = $this->db->query($sql,array($country,$state));
        if ($query->num_rows() > 0)
        {
            foreach ($query->result() as $row)
           {
               return $row->region;
           }
        }
    }
    
}

Code:

public function get_user_location()
    {                
        $location  = file_get_contents('http://ipinfodb.com/ip_query.php?ip='.$this->input->ip_address().'&timezone=false', 'r');
        $loc_array = explode(' ',$location);
        $region = $this->Venue_model->get_region(trim($loc_array[8]),trim($loc_array[15]));
        $addr_str = trim($loc_array[17]).', '.$region;
        return $addr_str;        
    }

[eluser]LuckyFella73[/eluser]
Did you try to hardcode the model/function call?

Code:

$region = $this->Venue_model->get_region(trim('US'),trim('GEORGIA'));

Another question:
You changed the $sql code - I'm no sql pro, did you tested with
the old version too?

[eluser]mabright[/eluser]
I just changed the code back from the binding version, sorry for the confusion. i was testing different scenarios.

I have been meaning to test it by changing the values in the controller but I just didn' think that was the issue as I could see the values being passed.

However, your suggestion worked. I hard coded the values in the controller and it works. So now I will need to determine why.

Thanks.

[eluser]mabright[/eluser]
I found the issue but I still have to resolve it.

If I echo the query and look at the page source code it is as follows;

Code:

SELECT `region` FROM `papp_region_fk`
WHERE UPPER(`country`) = '<COUNTRYCODE>US</COUNTRYCODE>'
AND   UPPER(`description`) = '<REGIONNAME>GEORGIA</REGIONNAME>'

I have no idea why this would not show up on the actual page but in the page source code.

The results from my call to the third party API returns XML and I need to properly parse it somehow.

[eluser]LuckyFella73[/eluser]
Before setting up your query just replace the wanted parts
of your string comming from your xml file like this:

Code:

<?php
// $country = "<COUNTRYCODE>US</COUNTRYCODE>"; example
$searchfor = array("<COUNTRYCODE>","</COUNTRYCODE>");
$country = str_replace($searchfor, "", $country);

// $region = "<REGIONNAME>GEORGIA</REGIONNAME>"; example
$searchfor = array("<REGIONNAME>","</REGIONNAME>");
$region = str_replace($searchfor, "", $region);

?&gt;

This way you get the "pure" data ..

[eluser]kent.elchuk[/eluser]
I think you may want complete control to choose pure php or OOP? I remember searching for days trying to solve this one and I could not until I decided to add the database connection from my view file. You can use a view just like CodeIgniter does not exist. But, you must add a require, include or mysql connect in the view file, or, that will not return. I found that view files will select without connecting to database, but inserting or update into will not work without doing another database connect, even if the controller or autoload database is enabled.