PHP Question

by 5 replies
6
Alright so I'm working on a form that spits out our inventory (cars) make model year kms and cost I have it working fine except that I noticed we store the cost in a seperate place

IE: pulling all other information from SELECT * from cars WHERE model ='$model'

cost is store in bills using an ID per vehicle - so my question is when using while loops to echo out the results how to get the cost associated with each unit using the ID??

if I just use a seperate statement and enter the $row['cost'] into it i'm assuming it wouldn't pick up the ID and associate to the right vehicle?

does this make sense the way I explain?
#programming #php #question
  • Probably need to use a join statement.

    MySQL Tutorial - Join
    • [1] reply
    • Hi,
      If you want to select 'model' from a table, and 'cost from table2, associated by ID, you can use a SQL query like this:
      SELECT model, cost FROM cars, table2 WHERE model ='$model' AND cars.id=table2.id
  • John_Kennedy is right

    SELECT * FROM cars JOIN cost ON cost.id = cars.id WHERE model = '$model';

    should do what you want if there is a 1 to 1 relationship between the two tables. For speedier scripting you might want to change * to specific columns as CourseWeb suggested.
  • Alright so here is the statement that I'm using that is given an error

    SELECT * FROM 'cars' JOIN 'bills' ON cars.id=bills.cars_id WHERE cars.make='$userchoice' ORDER BY cars.year DESC

    Given Error

    "Dunno Mate Something Didn't Click HereYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''cars' JOIN 'bills' ON cars.id=bills.car_id WHERE cars.make='Toyota' ORDER BY ca' at line 1"

    Any ideas?
  • MySQL :: MySQL 5.0 Reference Manual :: 12.2.8.2 JOIN Syntax
    MySQL - LEFT JOIN and RIGHT JOIN, INNER JOIN and OUTER JOIN

    Maybe these will help you? However, if you change
    SELECT * FROM 'cars' JOIN 'bills' ON cars.id=bills.cars_id WHERE cars.make='$userchoice' ORDER BY cars.year DESC
    to
    SELECT * FROM `cars` JOIN `bills` ON cars.id=bills.cars_id WHERE cars.make='$userchoice' ORDER BY cars.year DESC

    Then you should have better luck. Note the change is from ' to ` on the table names.
Join the discussion

Next Topics on Trending Feed

  • 6

    PHP Question

    Alright so I'm working on a form that spits out our inventory (cars) make model year kms and cost I have it working fine except that I noticed we store the cost in a seperate place IE: pulling all other information from SELECT * from cars WHERE model ='$model'