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
That would be a very simple JOIN query. :)

http://dev.mysql.com/doc/refman/5.0/en/join.html

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

,

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

,

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.