Collecting 1 name from 2 columns and diplaying results

[blasterstudios]

ok. i need to setup a query that pulls all jobs for a specific user. the only thing is the user's name can appear in 2 different columns. 1 for when they created a job, and the other when someone created a job for them. I need it to check both columns and pull the ones where they are involved. the query i have just repeats only the first one for however many they have. for instance if there are 4 jobs that involve id "2", it lists the first job 4 times. here's what i got:

$query_getuserjobs = "SELECT
	fox_clients.code AS code,
	fox_jobs.jobid AS jobid,
	fox_clients.name AS client,
	fox_users_employee.firstname AS employee,
	fox_users_originator.firstname AS originator,
	fox_jobs.duedate,
	fox_status.status AS status,
	fox_priorities.prioritylevel AS priority,
	fox_types.type AS jobtype
FROM
	fox_jobs,
	fox_clients,
	fox_users AS fox_users_employee,
	fox_users AS fox_users_originator,
	fox_status,
	fox_priorities,
	fox_types
WHERE
	fox_clients.clientid = fox_jobs.client AND
	fox_users_employee.userid = fox_jobs.employee AND
	fox_users_originator.userid = fox_jobs.originator AND
	(fox_jobs.employee = " . $_GET['user'] . " OR fox_jobs.originator = " . $_GET['user'] . ") AND
	fox_status.statusid = fox_jobs.status AND
	fox_priorities.priorityid = fox_jobs.priority AND
	fox_types.typeid = fox_jobs.jobtype AND
	fox_jobs.status <= 2
ORDER BY fox_jobs.duedate ASC, fox_jobs.priority DESC";

 

[r937]

i've had a look at your query and it looks okay

here it is again, rewritten with JOIN syntax for clarity:

select fox_clients.code 
     , fox_jobs.jobid 
     , fox_clients.name AS client
     , fox_users_employee.firstname AS employee
     , fox_users_originator.firstname AS originator
     , fox_jobs.duedate
     , fox_status.status 
     , fox_priorities.prioritylevel AS priority
     , fox_types.type AS jobtype
  from fox_jobs
inner
  join fox_clients
    on fox_jobs.client
     = fox_clients.clientid
inner
  join fox_status
    on fox_jobs.status
     = fox_status.statusid 
inner
  join fox_priorities
    on fox_jobs.priority
     = fox_priorities.priorityid 
inner
  join fox_types
    on fox_jobs.jobtype
     = fox_types.typeid 
inner
  join fox_users AS fox_users_employee
    on fox_jobs.employee
     = fox_users_employee.userid
inner
  join fox_users AS fox_users_originator
    on fox_jobs.originator
     = fox_users_originator.userid 
 where (
       fox_jobs.employee = " . $_GET['user'] . " 
    or fox_jobs.originator = " . $_GET['user'] . "
       )
   and fox_jobs.status <= 2
order 
    by fox_jobs.duedate asc
     , fox_jobs.priority desc

as far as i can tell, this should return exactly what you want

r937.com | rudy.ca

 

[blasterstudios]

this pulls the same results as before. the part that i added that may not be right is:

 WHERE (fox_jobs.employee = " . $_GET['user'] . "
    OR fox_jobs.originator = " . $_GET['user'] . "
    )

 

[r937]

okay, the mysql part of that looks fine, so ...

r937.com | rudy.ca

 

[blasterstudios]

well, crap, my fault again. i changed the name of the query, but didn't change the last part that said:

<?php } while ($row_getuserjobs = mysql_fetch_assoc($getuserjobs)); ?>

It works now. Thanks a lot man!