MySQL Value Replace

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
WanamakerStudios
Forum Commoner
Posts: 65
Joined: Thu Nov 30, 2006 7:35 am

MySQL Value Replace

Post 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
Last edited by WanamakerStudios on Thu Dec 07, 2006 8:19 am, edited 2 times in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

That would be a very simple JOIN query. :)

http://dev.mysql.com/doc/refman/5.0/en/join.html
WanamakerStudios
Forum Commoner
Posts: 65
Joined: Thu Nov 30, 2006 7:35 am

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

in your selection field list, replace the reference to vehicledatatable.make with vehiclecodetable.description
WanamakerStudios
Forum Commoner
Posts: 65
Joined: Thu Nov 30, 2006 7:35 am

Post by WanamakerStudios »

Perfect! Thanks!
WanamakerStudios
Forum Commoner
Posts: 65
Joined: Thu Nov 30, 2006 7:35 am

Post 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]
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

You may want INNER or LEFT JOINs depending on how your tables are built.
Post Reply