php - Mysql subquery return error when more than one record is fetched from db -


i using following code generate merit list of students' performance in different subjects display students according performances.

 $qry = "select * (  select (select percentage exams_db subjecttype = 'english') english,            (select percentage exams_db subjecttype = 'biology') biology,            (select percentage exams_db subjecttype = 'maths') maths,            (select percentage exams_db subjecttype = 'science') science,            (select percentage exams_db subjecttype = 'socialstudies') socialstudies,            (select total_marks exams_db order total_marks desc limit 1) total_marks,            (select exam_date exams_db order id desc limit 1) exam_date      exams_db group maths     union     select (select grade exams_db subjecttype = 'english') english,            (select grade exams_db subjecttype = 'biology' ) biology,            (select grade exams_db subjecttype = 'maths') maths,            (select grade exams_db subjecttype = 'science') science,            (select grade exams_db subjecttype = 'socialstudies') socialstudies,             (select total_marks exams_db  order total_marks desc limit 1) total_marks,            (select exam_date exams_db order id desc limit 1) exam_date      exams_db group maths      ) t ";

however, error subquery returns more 1 row

however, when edit code below, works 1 user.

$qry = "select * (  select (select percentage exams_db  subjecttype = 'english' , user = '$userid') english,            (select percentage exams_db  subjecttype = 'biology' , user = '$userid') biology,            (select percentage exams_db  subjecttype = 'maths' , user = '$userid') maths,            (select percentage exams_db  subjecttype = 'science' , user = '$userid') science,            (select percentage exams_db  subjecttype = 'socialstudies' , user = '$userid') socialstudies,            (select total_marks exams_db  user = '$userid' order total_marks desc limit 1) total_marks,            (select exam_date exams_db  user = '$userid' order id desc limit 1) exam_date      exams_db  group maths     union     select (select grade exams_db  subjecttype = 'english' , user = '$userid') english,            (select grade exams_db  subjecttype = 'biology' , user = '$userid') biology,            (select grade exams_db  subjecttype = 'maths' , user = '$userid') maths,            (select grade exams_db  subjecttype = 'science' , user = '$userid') science,            (select grade exams_db  subjecttype = 'socialstudies' , user = '$userid') socialstudies,             (select total_marks exams_db user = '$userid' order total_marks desc limit 1) total_marks,            (select exam_date exams_db  user = '$userid' order id desc limit 1) exam_date      exams_db  group maths      ) t ";

but, want display users. so, how can edit first code enable me display users because as second code helping me, allowing me display 1 user.

my database structure follows

user    subjecttype  percentage  grade   109283  english       40%         b   345245  biology       80%           832904  science       50%         c

i not 100% sure trying output here.

however assuming want list of users , percentages, , users grades, along last exam date , highest total marks user:-

select exam_date.user, english.percentage, biology.percentage, maths.percentage, science.percentage, socialstudies.percentage, exam_date.examdate, total_marks.totalmarks (select user, max(exam_date) examdate exams_db  user = '$userid' group user) exam_date left outer join (select user, percentage exams_db  subjecttype = 'english' group user) english on exam_date.user = english.user left outer join (select user, percentage exams_db  subjecttype = 'biology' group user) biology on exam_date.user = biology.user left outer join (select user, percentage exams_db  subjecttype = 'maths' group user) maths on exam_date.user = maths.user left outer join (select user, percentage exams_db  subjecttype = 'science' group user) science on exam_date.user = science.user left outer join (select user, percentage exams_db  subjecttype = 'socialstudies' group user) socialstudies on exam_date.user = socialstudies.user left outer join (select user, max(total_marks) totalmarks exams_db  user = '$userid' group user) total_marks on exam_date.user = total_marks.user union select exam_date.user, english.percentage, biology.percentage, maths.percentage, science.percentage, socialstudies.percentage, exam_date.examdate, total_marks.totalmarks (select user, max(exam_date) examdate exams_db  user = '$userid' group user) exam_date left outer join (select user, grade exams_db  subjecttype = 'english' group user) english on exam_date.user = english.user left outer join (select user, grade exams_db  subjecttype = 'biology' group user) biology on exam_date.user = biology.user left outer join (select user, grade exams_db  subjecttype = 'maths' group user) maths on exam_date.user = maths.user left outer join (select user, grade exams_db  subjecttype = 'science' group user) science on exam_date.user = science.user left outer join (select user, grade exams_db  subjecttype = 'socialstudies' group user) socialstudies on exam_date.user = socialstudies.user left outer join (select user, max(total_marks) totalmarks exams_db  user = '$userid' group user) total_marks on exam_date.user = total_marks.user

if isn't want explain not in code. eg, have students table?

edit - list of users (who have done exam) think following might it:-

select exam_date.user,      english.percentage,      biology.percentage,      maths.percentage,      science.percentage,      socialstudies.percentage,      english.grade,      biology.grade,      maths.grade,      science.grade,      socialstudies.grade,      exam_date.examdate,      total_marks.totalmarks (select user, max(exam_date) examdate exams_db group user) exam_date left outer join (select user, percentage, grade exams_db subjecttype = 'english' group user) english on exam_date.user = english.user left outer join (select user, percentage, grade exams_db subjecttype = 'biology' group user) biology on exam_date.user = biology.user left outer join (select user, percentage, grade exams_db subjecttype = 'maths' group user) maths on exam_date.user = maths.user left outer join (select user, percentage, grade exams_db subjecttype = 'science' group user) science on exam_date.user = science.user left outer join (select user, percentage, grade exams_db subjecttype = 'socialstudies' group user) socialstudies on exam_date.user = socialstudies.user left outer join (select user, max(total_marks) totalmarks exams_db group user) total_marks on exam_date.user = total_marks.user

note bit dubious total_marks field (your original query getting highest total marks).

possibly more efficient (but untested) be:-

select users.user,      exam_dates.examdate,      total_marks.totalmarks,      max(if(sub1.subjecttype='english', exams_db.percentage, 0)) englishpercentage,     max(if(sub1.subjecttype='english', exams_db.grade, 0)) englishgrade,     max(if(sub1.subjecttype='biology', exams_db.percentage, 0)) biologypercentage,     max(if(sub1.subjecttype='biology', exams_db.grade, 0)) biologygrade,     max(if(sub1.subjecttype='maths', exams_db.percentage, 0)) mathspercentage,     max(if(sub1.subjecttype='maths', exams_db.grade, 0)) mathsgrade,     max(if(sub1.subjecttype='science', exams_db.percentage, 0)) sciencepercentage,     max(if(sub1.subjecttype='science', exams_db.grade, 0)) sciencegrade,     max(if(sub1.subjecttype='socialstudies', exams_db.percentage, 0)) socialstudiespercentage,     max(if(sub1.subjecttype='socialstudies', exams_db.grade, 0)) socialstudiesgrade users left outer join (     select user, subjecttype, max(exam_date) latestsubjectexam      exams_db      subjecttype in ('english', 'biology', 'maths', 'science', 'socialstudies')      group user, subjecttype ) sub1 on users.user = sub1.user left outer join exams_db on sub1.user = exams_db.user , sub1.subjecttype = exams_db.subjecttype , sub1.latestsubjectexam = exams_db.exam_date left outer join (select user, max(total_marks) totalmarks exams_db group user) total_marks on users.user = total_marks.user left outer join (select user, max(exam_date) examdate exams_db group user) exam_dates on users.user = exam_dates.user group users.user, exam_dates.examdate, total_marks.totalmarks

edit again

to try , cope duplicate users / exams in 2nd style query

select exam_date.user,      english.percentage,      biology.percentage,      maths.percentage,      science.percentage,      socialstudies.percentage,      english.grade,      biology.grade,      maths.grade,      science.grade,      socialstudies.grade,      exam_date.examdate,      total_marks.totalmarks (select user, max(exam_date) examdate exams_db group user) exam_date left outer join (     select user, subjecttype, max(exam_date) latestsubjectexam      exams_db      subjecttype in ('english', 'biology', 'maths', 'science', 'socialstudies')      group user, subjecttype ) sub1 on exam_date.user = sub1.user left outer join (select user, exam_date, percentage, grade exams_db subjecttype = 'english' group user, exam_date) english on exam_date.user = english.user , sub1.subjecttype = 'english' , sub1.latestsubjectexam = english.exam_date left outer join (select user, exam_date, percentage, grade exams_db subjecttype = 'biology' group user, exam_date) biology on exam_date.user = biology.user , sub1.subjecttype = 'biology' , sub1.latestsubjectexam = biology.exam_date left outer join (select user, exam_date, percentage, grade exams_db subjecttype = 'maths' group user, exam_date) maths on exam_date.user = maths.user , sub1.subjecttype = 'maths' , sub1.latestsubjectexam = maths.exam_date left outer join (select user, exam_date, percentage, grade exams_db subjecttype = 'science' group user, exam_date) science on exam_date.user = science.user , sub1.subjecttype = 'science' , sub1.latestsubjectexam = science.exam_date left outer join (select user, exam_date, percentage, grade exams_db subjecttype = 'socialstudies' group user, exam_date) socialstudies on exam_date.user = socialstudies.user , sub1.subjecttype = 'socialstudies' , sub1.latestsubjectexam = socialstudies.exam_date left outer join (select user, max(total_marks) totalmarks exams_db group user) total_marks on exam_date.user = total_marks.user

Comments

Post a Comment

Popular posts from this blog

jquery - How can I dynamically add a browser tab? -

since using iframes house external pages are, accounts "not preferred method" , disallowed sites (flickr, one), want dynamically generate browser tabs. e.g., when user submits form, want dynamically generate tab on browser in response submitted. e.g., might want add new tab url like: http://www.bigsurgarrapata.com/contact how in jquery? you want form target attribute action attribute. http://www.w3schools.com/tags/att_form_target.asp browsers have ultimate control on result of target attribute open new tab value of _blank. <form action="contact_submit.php" target="_blank" method="post">
Read more

keyboard - C++ GetAsyncKeyState alternative -

i using windows.h library's getasynckeystate function define key pushed on keyboard . here program returns int value of pushed key example. #include <iostream> #include <windows.h> using namespace std; int main (){ char i; for(i=8;i<190;i++){ if(getasynckeystate(i)== -32767){ cout<<int (i)<<endl; } } return 0; } but trying make opengl simple game , function appeared slow . there function not need cycle 180 times , if statement . thanks you're trying make opengl game, you're going use glfw. if that's case can away using glfwgetkey() such: if (glfwgetkey(glfw_key_up) == glfw_press) {...} if (glfwgetkey(glfw_key_down) == glfw_press) {...} that method nicely explained in tutorial 6 opengl-tutorial.org . should start first tutorial of beginners tutorials , later intermediate tutorials . if you're going make win32 window can listen wm_keydown or wm_keyup messages , virtua...
Read more

android - java.net.UnknownHostException(Unable to resolve host “URL”: No address associated with hostname) -

i developing app testing asp.net webservice in android.for simple webservice adding 2 numbers used.where numbers passed parameters.the result in dialogue.the webserver works in local. when application runs following result exception.i provided internet permission in manifest. java.net.unknownhostexception(unable resolve host “ http://url.com/ ”: no address associated hostname) my code given below main activity public static string rslt=""; /** called when activity first created. */ @override public void oncreate(bundle savedinstancestate) { super.oncreate(savedinstancestate); setcontentview(r.layout.activity_main); button b1=(button)findviewbyid(r.id.button1); final alertdialog ad=new alertdialog.builder(this).create(); b1.setonclicklistener(new onclicklistener() { @override public void onclick(view arg0) { // todo auto-generated method stub try { ...
Read more