0

I want to retrieve only the latest course of the student which is BSCS in my semstudent table, so I sorted it out by using orderBY sy and sem. Below is my code:

enter image description here

$query = db_select('student', 't1',array('target' => 'import'));
    $query->join('studinfo', 't2', 't1.studid = t2.studid');
    $query->join('semstudent', 't3', 't1.studid = t3.studid');
    $query->join('program', 't4', 't4.progcode = t3.studmajor');

$result = $query
    ->fields('t1', array('studid','lastname', 'firstname', 'middlename', 'extname'))         
    ->fields('t2', array('guardian','guardian_phone')) 
    ->fields('t3', array('sy','sem'))  
    ->fields('t4', array('progcode', 'college'))  
    ->orderBy('sy', 'DESC')
    ->orderBy('sem', 'DESC')
    ->condition('t1.studid', trim($form_state['input']['idnumber']) ,'=')
    ->distinct()           
    ->execute();

The result of the code above $record->progcode is BSGE and not BSCS. Where am I missing? It should be BSCS.

Update: Result of dsm((string) $query); before executing the query.

enter image description here

  • You have a varchar column and varchar column sort by character and not numbers. So, I think you need a different table or column that contains the course date too and then you can sort list by course date. – Sumit Madan Jun 15 '15 at 5:42
  • @SumitMadan -->Course Date is not available. The 'sy' and 'sem' fields are the only available fields for me to sort it out. I tried sorting it out using OrderBy directly in to the database and it works alright. SELECT * FROM semstudent WHERE studid = '091-03004' ORDER BY sy DESC, sem DESC; – ABC Jun 15 '15 at 5:55
  • Can you please try to print the query like dsm((string) $query); before ->execute() it. – Sumit Madan Jun 15 '15 at 6:06
  • @SumitMadan --> Updated my post. – ABC Jun 15 '15 at 6:15
  • Try to run this query in your mysql server. I think the issue with distinct. – Sumit Madan Jun 15 '15 at 6:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.