CODEWITHSUNDEEP
phpmysqlsorting
user2897131
user2897131

Reputation: 11

Distinct sorting for distinct types in php

My table description is as follows

**entry_table**

 - serial(int)
 - s_name(varchar)
 - user_id(int)
 - id(int)

**Students_details**

 - id(int)
 - user_id(int)
 - student_name(varchar)
 - adress(varchar)

**User_login**

 - user_id(int)
 - user_name(varchar)
 - password(varchar)
 - alotment(bool)

Scenario is that the students apply for multiple scholarships. Their selections are stored in the entry_table's s_name, user_id and id fields.

My next step is to build a sorted list of all the students who applied for a particular scholarship eg:"scholarship1".

This list should also show the student's name(student_name field of the students_details table)

The lists are to be sorted according to two types of scholarships that the system offers(merit and need). Applicants of the merit scholarship are required to be sorted in descending order using the ratio(obtained marks/ total marks). However, the need scholarship is to be shorted in ascending order as it uses the ratio(family income/no. of non-earning family members)

I tried to join my tables using 

  $query = "SELECT *FROM entry_table, students_details 
WHERE entry_table.id=students_details.id
    group by entry.s_id,entry.student_id";

Please help in the sorting as per type problem. Also the above query helps joining the tables but doesnt achieve the purpose.

thanking you in advance

Upvotes: 0

Views: 94

Answers (2)

Pablo Ezequiel Leone
Pablo Ezequiel Leone

Reputation: 1387

First thing you should connect tables properly.

$query = "SELECT sd.student_name, et.s_name FROM Student_details AS sd LEFT JOIN 
entry_table AS et ON et.user_id = sd.user_id LEFT JOIN 
user_login AS ul ON ul.user_id = sd.user_id 
WHERE et.s_name = 'merit'
ORDER BY et.s_name DESC 
UNION ALL 
SELECT sd.student_name, et.s_name FROM Student_details AS sd LEFT JOIN 
entry_table AS et ON et.user_id = sd.user_id LEFT JOIN 
user_login AS ul ON ul.user_id = sd.user_id 
WHERE et.s_name = 'need'
ORDER BY et.s_name ASC"

If you need a list you don't need to group, as far as you don't need a count number on any field or unique rows. This is what I have in the top of my head, maybe do the trick I didn't test it, but maybe give you an idea. I can't see the ratio field on your tables.

Use Join instead where to connect tables, makes more sense and improve query performance. Next time, put SQL table code, is more useful to give a better response.

Sorry for my english!!!

Upvotes: 0

Arka Bhattacharjee
Arka Bhattacharjee

Reputation: 341

select * from entry_table inner join students_details on entry_table.id=students_details.id order by entry.student_id asc;

Upvotes: 1

Related Questions