Could you check why this Codeigniter order by isn't recognised when filling a combobox ?

Eduardo Fuerte
Eduardo Fuerte used Ask the Experts™
on
Hi Experts

Could you check why this Codeigniter order by isn't recognised when filling a combobox ?

img001

jquery code
<script type="text/javascript">
var baseurl = "http://www.codeagro.sp.gov.br/";

$( document ).ready(function(){
    $('#programa').on('change', function()
	{
                var idprograma = $(this).val();
                //alert(idprograma);
 		$.ajax({
 			type: 'post',
            //url: baseurl + "edital/get_orgao_items",
            url: baseurl + "acesso_usuarios/get_orgao_items",
 			
            data: {idprograma: idprograma},
            
 			dataType: 'json',
			success: function (result)
            {
	           $('#orgao').children().remove();
	           $.each(result, function(i, item)
	           {
		          $('#orgao').append("<option value='" + i + "'>" + item + "</option>");
	           });
            },
			error: function (result)
			{
				alert('Ocorreu algum erro.');
			}
		});
	}); 
 });
</script>

Open in new window


Controler Code

  //EF 2015 Dez01 atualização do combo
    public function get_orgao_items(){
        $idprograma = $this->input->post('idprograma');
        $orgao_items = $this->edital_model->get_orgao($idprograma);
        echo json_encode($orgao_items); 
    }

Open in new window





Model code
  function get_orgao($idprograma = 0)   
    { 
        
        $this->db->select('idorgao');
        $this->db->select('nome');
        $this->db->from('tb_orgao');
        $this->db->where('idprograma', $idprograma);
 
      // ------ Relevant line -------------------------
      $this->db->order_by('nome ');
     //----------------------------------------------------


       $query = $this->db->get();
        $result = $query->result();

//        $sql = "SELECT idorgao, nome FROM tb_orgao WHERE idprograma =".$idprograma. " ORDER BY nome";
//        $result = $this->db->query($sql);


        //array to store idorgao & nome
        $idorgao = array('-SELECT-');
        $nome = array('-SELECT-');


     for ($i = 0; $i < count($result); $i++)
        {
            array_push($idorgao, $result[$i]->idorgao);
            array_push($nome, $result[$i]->nome);
        }
        return $department_result = array_combine($idorgao, $nome);

    }
 

Open in new window


Thanks in advance!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Can you modify like this and check?  Include "ASC"
//        $sql = "SELECT idorgao, nome FROM tb_orgao WHERE idprograma =".$idprograma. " ORDER BY nome ASC";

Open in new window

Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
$this->db->order_by('nome ');
                         ^   

Open in new window

There is a space in your order by field Try this
$this->db->order_by('nome');

Open in new window

Eduardo FuerteDeveloper and Analyst

Author

Commented:
Hello

I'm going to check. By the way  how to use this
$sql = "SELECT idorgao, nome FROM tb_orgao WHERE idprograma =".$idprograma. " ORDER BY nome ASC";

// It doesn't return an array
 $result = $this->db->query($sql);

Open in new window


Having an array as result ?
(since an array is needed to the subsequent the code)
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
What does it return?

What do you get if you run that query directly against the database?

i.e. at line 2
echo $sql 

Open in new window

Copy result and paste into SQL query tool - do you get more than one result?
Marco GasiFreelancer
Top Expert 2010

Commented:
For the last question:
$sql = "SELECT idorgao, nome FROM tb_orgao WHERE idprograma =".$idprograma. " ORDER BY nome ASC";
// It doesn't return an array
$result = $this->db->query($sql);
return $result->result_array(); //this is the array

Open in new window


For the main question; I had the same problem some day ago: the problem is not in sql query or in php but in the way jquery manages the data returned by php script. When you iterate using
$.each(result, function(i, item)
{

Open in new window


jquery re-sorts result on i basis so the order set by 'order_by' clause is missing. I solved the problem changing the way I returned the array from php and then adjusting the jquery code:
    public function get_orgao_items(){
        $idprograma = $this->input->post('idprograma');
        $orgao_items = $this->edital_model->get_orgao($idprograma);
				foreach ($orgao_items[0] as $k=>$v)
				{
					$result[$k] = $v;
				}
        echo json_encode($result); 
    }

Open in new window


jquery:
	$(document).ready(function () {
		$('#programa').on('change', function ()
		{
			var idprograma = $(this).val();
			//alert(idprograma);
			$.ajax({
				type: 'post',
				//url: baseurl + "edital/get_orgao_items",
				url: baseurl + "acesso_usuarios/get_orgao_items",
				data: {idprograma: idprograma},
				dataType: 'json',
				success: function (result)
				{
					$('#orgao').children().remove();
					var $orgao = [];
					$.each(result, function (i, item)
					{
						$orgao.push({ nome: item[0],id: item[1] });
					});
					$.each($orgao, function (i, item) {
						$('#orgao').append("<option value='" + item.id + "'>" + item.nome + "</option>");
					});
				},
				error: function (result)
				{
					alert('Ocorreu algum erro.');
				}
			});
		});
	});

Open in new window

Probably, it wont work immediately because I tried to convert my code integrating it in CI, but perhaps this give you a base to build on a working code
Eduardo FuerteDeveloper and Analyst

Author

Commented:
Thank you for the replies!

I'm out of office now and will check asap.
Most Valuable Expert 2018
Distinguished Expert 2018
Commented:
jQuery doesn't maintain the order of a list of objects - they have no inherent order, which is why you get that issue. In your model, retrieve an array, prepend the SELECT item. json_encode() that and pass it back to your AJAX success handler:

// Model
function get_orgao($idprograma = 0) {
	// Select your records
	$this->db
		->select('idorgao, nome')
		->from('tb_orgao')
		->order_by('nome')
		->where('idprograma', $idprograma);
		
	// Get the results 
	$results = $this->db->get()->result_array();

	// Add the SELECT item to the start of the array
	array_unshift($results, array('id' => '0', 'name' => 'SELECT'));

	return $results;
}

Open in new window


// View
$('#programa').on('change', function () {
	var idprograma = $(this).val();
	
	$.ajax({
		type: 'post',
		url: baseurl + 'acesso_usuarios/get_orgao_items',
		data: { idprograma: idprograma },
		dataType: 'json',
		success: function (result)
		{
			$('#orgao').children().remove();
			
			$(result).each(function(i, item) {
				$('#orgao').append("<option value='" + item.idorgao + "'>" + item.nome + "</option>");
			});
		},
		error: function (result)
		{
			alert('Ocorreu algum erro.');
		}
	});
});

Open in new window

Eduardo FuerteDeveloper and Analyst

Author

Commented:
Exactly!

Thank you very much!

(I have just another doubt to be posted in another question.)
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
Good news.

Just post a link to your new question here and I'll try and take a look :)
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
@Chris,

Trying to replicate the problem where you say
jQuery doesn't maintain the order of a list of objects - they have no inherent order
I cannot replicate this - when I query results via AJAX they are always represented in the order they are sent - and are displayed as such.

Did I misunderstand your post or is there something I am missing? Interested to know more on this.
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
Hey @Julian

Poor choice of words on my part :)

Probably better to have said 'jQuery doesn't maintain the order of an objects properties' - I'd been working on C# lists all day !!

In Eduardo's code, he pulled an ID and Name from a database, sorted by Name, and then built his data something like this:

{
   "-SELECT-" : "-SELECT-",
   "3" : "A String",
   "1" : "B String",
   "2" : "C String"
}

When he looped through in jQuery, he was obviously expecting:

-SELECT-
A String
B String
C String

What he got was:

B String
C String
A String
-SELECT-

That's the point I was trying to make - poorly I guess :)
Marco GasiFreelancer
Top Expert 2010

Commented:
I am interested me too :-)
I don't know if my posted code would work in the Eduardo's implementation: I tried to convert to CI framework from a simple app I'm deploying in html5, jquery, mysql to use with Cordova.
In my code I was querying mysql this way:
$sql = "SELECT * FROM categories ORDER BY sort_order ASC";
$result = $db->query( $sql );

Open in new window

When I returned the result array to jquery to print out categories; i got the correct order using console.log but after using each loop the order was broken and the only way to preserve it has been to rearrange result array in php:
while ( $row = $result->fetch_assoc( ) )
{
	$cats[$row[ 'sort_order' ]] = array($row[ 'category_id' ],$row[ 'category_name' ]);
}

Open in new window

And then modifying jquery script this way:
		success: function (result)
		{
			console.log('success');
			var prodItem = [];
			$.each(result, function (order, values) {
				prodItem.push({id: values[0], name: values[1]});
			});
			$.each(prodItem, function (i, item) {
				$('.sidebar-nav').append('<li><a class="cat-menu-item" data-cat="' + item.id + '" href="#">' + item.name + '</a></li>');
			});
		},

Open in new window

This is what I tried to adapt to the CI context where the Eduardo's code is running and I confess I didn't test it and I'm not sure if my code was correct. But I'm wondering if there be a simpler way to get the correct result.
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
Marco - In CodeIgniter, retrieve your DB records as a result_array and JSON encode that. This will automatically create an array that will maintain the order it was pulled from your DB:

$result = $db->query( $sql );
echo json_encode( $result->result_array() );

This will create an indexed array:

array(
   [0] => array( ['id'] => 30 ['name'] => 'A String' )
   [1] => array( ['id'] => 10 ['name'] => 'B String' )
   [2] => array( ['id'] => 20 ['name'] => 'C String' )
   [3] => array( ['id'] => 40 ['name'] => 'D String' )
)

The order that Jquery will loop through is now defined by the auto-generated array key 0-3, so you order will stay like that.
Marco GasiFreelancer
Top Expert 2010

Commented:
Thank you Chris.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial