...

View Full Version : anyone critics my query code



che_anj
08-31-2007, 06:35 PM
anyone critics my query code or what do u think is the advantage of this query code. tnx


SELECT DISTINCT e.sdate, e.edate, e.endtime, e.interviewdate, e.interviewtime, rc.employer_id, rct.contact_id, rj.vacancy_id, e.comments, e.action_type, rc.employer_name, rj.jobtitle, rct.confirstname, rct.conlastname, rcd.firstname, rcd.lastname, e.oras, rcd.firstname, rcd.lastname
FROM rec_candidates AS rcd, rec_client AS rc, rec_jobs AS rj, event_history, rec_contacts AS rct, eventss AS e
WHERE rcd.candidate_id = event_history.applicant_id
AND rc.employer_id = event_history.client_id
AND rj.vacancy_id = event_history.job_id
AND e.event_id = event_history.event_id
AND rct.contact_id = event_history.contact_id
AND (
event_history.applicant_id = '$candid'
AND e.event_id = '$events')
OR (event_history.client_id = '$empid' AND e.event_id = '$events')
OR (event_history.job_id = '$jobid' AND e.event_id = '$events'
)GROUP BY event_history.event_id

Fumigator
08-31-2007, 09:26 PM
Selecting DISTINCT with a GROUP BY on the same query? Wow. You probably should know that DISTINCT is probably useless, as it applies to the entire list of columns. And with the GROUP BY you can't depend on any column you select that isn't part of the GROUP BY or isn't a column function (sum(), avg(), count(), etc).

But hey if you're happy with it then more power to ya.

GJay
08-31-2007, 09:32 PM
does the query do what you want it to? Those ANDs and ORs look a little odd...

you don't seem to be escaping your variables, you probably want to:


event_history.applicant_id = '".mysql_real_escape_string($candid)."'

Bahamut
09-03-2007, 02:14 PM
try


SELECT
e.sdate, e.edate, e.endtime, e.interviewdate, e.interviewtime, e.comments, e.action_type, e.oras,
rc.employer_id, rc.employer_name,
rct.contact_id,
rj.vacancy_id, rj.jobtitle,
rct.confirstname, rct.conlastname,
rcd.firstname, rcd.lastname, rcd.firstname, rcd.lastname

FROM
rec_candidates AS rcd,
rec_client AS rc,
rec_jobs AS rj,
event_history,
rec_contacts AS rct,
eventss AS e

WHERE
rcd.candidate_id = event_history.applicant_id
AND rc.employer_id = event_history.client_id
AND rj.vacancy_id = event_history.job_id
AND e.event_id = event_history.event_id
AND rct.contact_id = event_history.contact_id
AND event_history.applicant_id = '$candid'
AND e.event_id = '$events'
AND (
(event_history.client_id = '$empid' AND e.event_id = '$events')
OR (event_history.job_id = '$jobid' AND e.event_id = '$events')
)
GROUP BY event_history.event_id

che_anj
09-03-2007, 03:19 PM
this query display the type, startdate, enddate and comments, but the problem is, it displays the same types which is wrong. what should i do so that the rows will not display the same data..


SELECT e.action_type, a.descriptions, e.sdate, e.comments,e.edate
FROM actiontypes AS a, eventss AS e
WHERE e.action_type = a.type_id AND e.sdate >= CURDATE( )
OR edate >= CURDATE( ) AND e.updatedby = '$id' GROUP BY comments

tnx

che_anj
09-03-2007, 03:40 PM
I query at actiontypes table because the description of eventcode can be found at actionptypes table.. thats why I the result will display all the list of descriptions with the same date, and comments.

Bahamut
09-03-2007, 03:46 PM
???

please give full query and place values instead of variables for me to better understand

ie
select * from table where field = '$data'

should be

select * from table where field = '0020'

che_anj
09-03-2007, 03:59 PM
ok...

in table action types has the following datas.

code description
1 1st Interview
2 2nd interview
3 CV Sent
4 Offer

table events has the following fields

code sdate edate comments updatedby
1 08/31/2007 09/04/2007 Mr. M has interview 1
2 09/01/2007 09/07/2007 Mr. M invited for 2nd 1
4 09/09/2007 09/12/2007 Mr M was offered 1


now I want to display this following datas in events table, but the result must be

code sdate edate comments updatedby
1st Interview 08/31/2007 09/04/2007 Mr. M has interview 1
2nd Interview 09/01/2007 09/07/2007 Mr. M invited for 2nd 1
Offer 09/09/2007 09/12/2007 Mr M was offered 1

SELECT e.code, e.sdate, e.comments,e.edate,a.descriptions
FROM eventss AS e, actiontypes as a
WHERE e.sdate >= CURDATE( )
OR edate >= CURDATE( ) AND e.updatedby = '1'

thats its..

tnx

Bahamut
09-03-2007, 04:38 PM
SELECT a.descriptions AS code, e.sdate, e.edate, e.comments, e.updatedby
FROM actiontypes a
LEFT JOIN eventss e ON e.action_type = a.type_id
WHERE e.updatedby = '2'
AND (
e.sdate >= '2007-08-15'
OR e.edate >= CURDATE()
)

che_anj
09-03-2007, 04:53 PM
brilliant.. thanks Bahamut..

vanjustin
09-04-2007, 01:13 PM
Che_anj next time u should ask harder question !! :thumbsup:



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum