Page 1 of 1
MySQL Value Replace
Posted: Wed Dec 06, 2006 3:07 pm
by WanamakerStudios
How would I go about temporarily changing the value of a field based on another table and field for display? For ex:
Vehicle Code Table:
| Code | Description|
--------------------------
| AK | Dodge |
| AJ | Chrysler |
Vehicle Data Table:
| VIN | Make | Model |
---------------------------
| 123 | AK | Caliber |
How could I do an inline replace of AK in the Vehicle Data Table so that when I need to display the results, Dodge shows instead of AK
Posted: Wed Dec 06, 2006 3:13 pm
by feyd
Posted: Wed Dec 06, 2006 3:23 pm
by WanamakerStudios
Ok. So I've put
Code: Select all
JOIN `vehiclecodetable` ON `vehiclecodetable`.code = `vehicledatatable`.make
But how would I pull over the description to replace AK?
Posted: Wed Dec 06, 2006 3:27 pm
by feyd
in your selection field list, replace the reference to vehicledatatable.make with vehiclecodetable.description
Posted: Wed Dec 06, 2006 3:34 pm
by WanamakerStudios
Perfect! Thanks!
Posted: Thu Dec 07, 2006 8:25 am
by WanamakerStudios
feyd | Please use Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
I spoke a little too soon ... I am current using the following code to produce a list of all the vehicles within a certain radius of a zip code
[syntax="sql"]
SELECT DISTINCT CVIN, CYEAR, CDESCRIPTION, CMODEL, CCOLOR, CMILEAGE, CPRICE, CIDLOTD
FROM `vehicles-temp`
RIGHT JOIN `carparts` ON (`carparts`.CIDCARP = `vehicles-temp`.CMAKE AND `carparts`.CCATEGORY = 'MAKE')
WHERE CIDLOTD IN (". $dealers .")
AND CPRICE <> 0
ORDER BY ". $_GET['sort'] ." ". $_GET['order'] ."
The next part of what I need to do is determine what dealers are considered 'Premium' dealers based on another table as follows:
Premium Dealers Table
LOTID | STATUS |
---------------------
ABCD | 3 |
So now I change up my code as follows:
Code: Select all
SELECT DISTINCT CVIN, CYEAR, CDESCRIPTION, CMODEL, CCOLOR, CMILEAGE, CPRICE, CIDLOTD, CIDLOTD AS STATUS
FROM `vehicles-temp`
RIGHT JOIN `carparts` ON (`carparts`.CIDCARP = `vehicles-temp`.CMAKE AND `carparts`.CCATEGORY = 'MAKE')
RIGHT JOIN `premiumdealers` ON `premiumdealers`.LOTID = `vehicles-temp`.STATUS
WHERE CIDLOTD IN (". $dealers .")
AND CPRICE <> 0
ORDER BY ". $_GET['sort'] ." ". $_GET['order'] ."
As soon as I do that ...
1) I only get dealers that are in the Premium Dealers table; all others are left out
2) The page load time goes from 17 seconds to 150 seconds
Any ideas?
feyd | Please use[/syntax]Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Posted: Thu Dec 07, 2006 12:26 pm
by feyd
You may want INNER or LEFT JOINs depending on how your tables are built.