6 {{ upvoteCount | shortNum }}
6 {{ upvoteCount | shortNum }}

PHP Question

by solidsoul
5 replies
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?
#php #question
  • Profile picture of the author john_kennedy
    Probably need to use a join statement.

    MySQL Tutorial - Join
    {{ DiscussionBoard.errors[5906491].message }}
    • Profile picture of the author CoursesWeb
      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
      {{ DiscussionBoard.errors[5906805].message }}
  • Profile picture of the author Earnie Boyd
    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.
    Signature
    Earnie Boyd
    {{ DiscussionBoard.errors[5907532].message }}
  • Profile picture of the author solidsoul
    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?
    Signature
    {{ DiscussionBoard.errors[5916348].message }}
  • Profile picture of the author Earnie Boyd
    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.
    Signature
    Earnie Boyd
    {{ DiscussionBoard.errors[5916522].message }}

Trending Topics