PHP/MySQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
dhtseany
Forum Newbie
Posts: 1
Joined: Sun Jul 24, 2011 2:03 pm

PHP/MySQL

Post 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
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: PHP/MySQL

Post 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.
Post Reply