Page 1 of 1

PHP/MySQL

Posted: Sun Jul 24, 2011 2:48 pm
by dhtseany
Hi all, new here...

Alright, I'm creating a database for my IT business for tracking customers, machines, and jobs.

I have 3 tables: cust (customer info), mach (machine info), and jobs (specific job information).

Each table has a unique ID, auto inc. (cust = custID), (mach = machID), (jobs = jobID) (all IDs are numbers)

What I'd like to do is use a mysql_fetch_array to capture the results I need (differs per page based on what i'm doing)

the cust.php isn't an issue because that page only fetches the results from the cust table. No biggie, working fine.

mach.php is basically the same code as cust.php. The difference is mach.php, when queried, currently returns results from the mach table correctly, including the custID (a 5 digit number based on the cust table). So in the mach table, the custID is not an auto inc but rather a varchar field with the approriate custID (based on cust) for who owns the PC.

What I need to do on mach.php is to A) query the mach table, return all results in a nice table then B) compare the custID fields from mach (currently returned at the 5 digit number), compare it against the cust table, then map that number to the actual customer name (from the cust table) so instead of seeing "10001" for the custID field, it would show the actual customer name "John Doe".

I hope I described that good enough, if not I can try better :P.

Peace
Sean

Re: PHP/MySQL

Posted: Tue Jul 26, 2011 4:04 am
by Benjamin
I think you want to use a join query. It's fairly basic although they can grow complex quickly.

Code: Select all

SELECT
  m.machine_name,
  u.first_name,
  u.last_name
FROM
  machines m,
  users u
WHERE
  u.id = m.owner_id
ORDER BY
  m.machine_name ASC
You may of course want to return machine names for machines that do not have owners. In that case you would want to use a LEFT JOIN.

Also, you should not store the owner id in a varchar. It should be an unsigned int as big as the primary key in the users table.