MySQL Value Replace
Moderator: General Moderators
-
WanamakerStudios
- Forum Commoner
- Posts: 65
- Joined: Thu Nov 30, 2006 7:35 am
MySQL Value Replace
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
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
Last edited by WanamakerStudios on Thu Dec 07, 2006 8:19 am, edited 2 times in total.
-
WanamakerStudios
- Forum Commoner
- Posts: 65
- Joined: Thu Nov 30, 2006 7:35 am
Ok. So I've put
But how would I pull over the description to replace AK?
Code: Select all
JOIN `vehiclecodetable` ON `vehiclecodetable`.code = `vehicledatatable`.make-
WanamakerStudios
- Forum Commoner
- Posts: 65
- Joined: Thu Nov 30, 2006 7:35 am
feyd | Please use
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:
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
,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'] ."
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'] ."
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
,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]