Data from 2 tables, for multiple vehicles but limited by a another field

[BCAV_WEB]

Hey,

 

I'm wanting to have our site so cars and colours come from a backend database, so we have two tables one: cars, two: colours. My issue is that I have a two cars the Ford Ka and Ford Fiesta, but the KA colours are coming up on the fiesta and like wise.

 

Can anyone see the problem in my coding??

 

<?php include "sections/phparea.php";?>
<?php include "sections/header.php";?>
<?php include "sections/left.php";?>
<!-- start content -->
	<div id="content">
		<h2>Test Area</h2>
            <?php



				include "connections/dbconnect.php";
				$manfactures = "Ford";
				$car_query = "SELECT * FROM cars WHERE make = '$manfactures'";						
				$car_result = mysql_query($car_query) or die ("Error in query: $car_query. ".mysql_error()); 

				setlocale(LC_MONETARY, 'en_GB');
				$fmt = '%i';
				if (mysql_num_rows($car_result) > 0) 
				{



					while ($car_row = @ mysql_fetch_array($car_result)) 
					{


						print "
								<table class='details'>
									<tr>
										<td rowspan='2'>
											<img src=\"". $car_row["image"] ."\" alt='" . $car_row["image_alt"] . "' />
										</td>
										<td colspan='2'>
											<a href='" . $car_row["what_link"] . "'>
												" . $car_row["model"]." ".$car_row["model_details"] . " 
											</a>
									</tr>
									<tr>
										<td>
											<p class='info'>
												RRP:<br/>
												What Price:<br/> 	
												Our Price:<br/> 
												Savings of:<br/> 
												Delivery Time: 

											</p>
										</td>
										<td>
											<p class='info1'>
										";
												echo money_format($fmt, $car_row["rrp"] );
								  print "<br/>";

												echo money_format($fmt, $car_row["what_price"] );
								  print "<br/>";

												echo money_format($fmt, $car_row["our_price"] );

								  $savings = $car_row["rrp"] - $car_row["our_price"];
								  print " 		<br/>
												<font color=\"red\">";
													echo money_format($fmt, $savings );
								  print " 		</font><br/> 
												" . $car_row["delivery_time"] . "
											</p>
										</td>
									</tr>
									<tr>
										<td>
										";
											$test = "KA";
											$query_cols = "SELECT DISTINCT colours.id, colours.colour_img, colours.colour, colours.colour_img_alt, colours.price, colours.colour_type FROM colours,cars  WHERE colours.model = cars.model ";
											//$query_cols = "SELECT DISTINCT model FROM colours";
											$cols_result = mysql_query($query_cols) or die ("Error in query: $query_cols. ".mysql_error());
											while ($cols_row = @ mysql_fetch_array($cols_result)) 
											{
									?>
                                                

															<a href='#' onmouseout='hideTooltip()' onmouseover='showTooltip(event,"<?php print "" . $cols_row["colour"] . " " . $cols_row["colour_type"] . "<br/>(£" . $cols_row["price"] . ")"; ?>");return false'>
                                      <?php
													print "																
															<img src=\"". $cols_row["colour_img"] ."\" alt='" . $cols_row["colour_img_alt"] . "' /></a>
														  ";
											}
							  print "
										</td>
										<td colspan='2'>
											<div id='CollapsiblePanel" . $car_row["id"] . "' class='CollapsiblePanel'>
											  <div class='CollapsiblePanelTab' tabindex='0'><img class='drop' src='images/drop_down.jpg' alt='Go' /></div>
											  <div class='CollapsiblePanelContent'>
											  	" . $car_row["id"] . "
											  </div>
										</div>
										<script type='text/javascript'>
										<!--
											var CollapsiblePanel" . $car_row["id"] . " = new Spry.Widget.CollapsiblePanel('CollapsiblePanel" . $car_row["id"] . "', {contentIsOpen:false});
										//-->
										</script>
										</td>
									</tr>
									";	
					}									
				}


				else
				{
					echo "Aids!";
				}
				print "</table>";							


								?>
                                    
                                    
        </div>
	<!-- end content -->
<?php include "sections/right.php";?>
<?php include "sections/footer.php";?>

[fenway]

The first problem was that you didn't use CODE tags -- but I've fixed that for you.

 

The second problem is that I don't understand the problem.

[BCAV_WEB]

What are the coding tags, as I never seem to get them to wrok properly arent they [ ]??

 

Okay I have a website that has makes of cars such as ford, and they have models such as KA and Fiesta and then they have colours some which are unique to each car but some that are universal. So I have created two backend database one; car which holds all the car info, image, name, model etc.... and another called colours which has the car model, colour, colour type (metallic, solid etc...), colour image.

 

The way I thought this coding should work is by selecting the colour for each model of car where the model of table colours equals that of cars. Eg. if Fiesta (In colours) = Fiesta (In cars) then show the colour Green with Green.jpg etc...

 

But this isn't the case because the KA colours are also coming out on the Fiesta.

 

Does that make sense??

[fenway]

I don't see why you simply don't like the colours to the models.

[BCAV_WEB]

can you explain please

[jdavidbakr]

Put a field in your colors table that is a foreign key to the car table - then you can look up just the colors for the car by filtering the color table by the car's ID, and you can do joins between the two tables.

[BCAV_WEB]

but on php  my admin, how do you add foreign keys??

[jdavidbakr]

if your tables are MyISAM there is no internal foreign key mechanism - but you can still use the concept, there's just not a way for the database to validate them.  if your tables are InnoDB then, in the "Structure" view there is a link for "Relation View" which is where you set up the foreign keys.

[BCAV_WEB]

I'm using MyISAM, so how do I go about it?

 

If you go onto the link, you'll see what I'm trying to do.

 

http://www.bikescarsandvans.co.uk/test.php

Colours Table

 

id model colour colour_type colour_img colour_img_alt price

1 KA Crystal White   images/colours/white_28x28.jpg Crystal White 0.00

2 KA Midnight Black – Metallic images/colours/plain_black_28x28.jpg Midnight Black 425.00

3 KA Sunrise   images/colours/rimini_red_28x28.jpg Sunrise 0.00

4 KA Blush – Pearlescent images/colours/red_28x28.jpg Blush 625.00

5 KA Moonlight – Metallic images/colours/star_silver_28x28.jpg Moonlight 425.00

6 KA Piste – Pearlescent images/colours/sparkling_silver_28x28.jpg Piste 625.00

7 KA Scuba – Metallic images/colours/sucba_28x28.jpg Scuba 425.00

8 KA Dive   images/colours/dive_28x28.jpg Dive 0.00

9 KA Strobe – Metallic images/colours/iorn_grey_28x28.jpg Strobe 425.00

10 Fiesta test test images/colours/lime_green_28x28.jpg test 0.00

11 Focus ST Focus TDCi 136 Zetec Focus TDCi 136 Titan... Panther Black – Metallic images/colours/plain_black_28x28.jpg Panther Black

470.00

12 Focus ST Preformance Blue – Metallic images/colours/normal_blue_28x28.jpg Preformance Blue 470.00

13 Focus ST Electric White – Pearlescent images/colours/white_28x28.jpg Electric White 745.00

 

Cars Table

 

id make model model_details search rrp what_price what_link our_price delivery_time image image_alt

 

Audi A3 Sportback TDI Quattro 170 S–Line 5Dr 2.0 Litre Diesel Audi A3 Sportback TDI Quattro 170 S–Line 5Dr... 26450.00 23320.00 http://www.whatcar.com/car-reviews/audi/a3-sportba... 22745.00 12 &ndash 16 Weeks images/cars/audi/audi_a3_sportback_tdi_170_sline_5... A3 Sportback TDI Quattro 170 S-Line

 

If thats any help to you

[jdavidbakr]

Just add a column in your colors table that is car_id.

 

Or, if you need to have more than one color per car, have a table that is car_to_color with car_id and color_id as the two columns.

 

Then, to get the list of colors for car_id 1 you would either:

 

select * from colors where car_id = 1

 

or, if you use the second method,

 

select colors.* from colors join car_to_color on (color.id = car_to_color.color_id) where car_id = 1

[BCAV_WEB]

I can't seem to figure it out  :'(

 

<?php include "sections/phparea.php";?>
<?php include "sections/header.php";?>
<?php include "sections/left.php";?>
   <!-- start content -->
      <div id="content">
         <h2>Test Area</h2>
            
            <?php


               
               print "
                     <form target='_self' method='POST'>
                     <table class=''> 
                        <tr> 
                           <td>
                              <input name='vehicle' type='text' id='search_name' size='16'>
                              <input type='image' src='images/search.gif' alt='Search' name='submit' id='search' value='Search'/>
                           </td>
                        </tr>
                        <tr>
                           <td>
                              <select name='filter' id='filter'>
                                 <option value='make' selected='selected'>Filter By</option>
                                 <option value='make'>Vehicle Manufacture</option>
                                 <option value='model'>Vehicle Model</option>
                                 <option value='our_price'>Price</option>
                                 <option value='delivery_time'>Delivery Time</option>
                              </select>
                           </td>
                        </tr>
                        <tr>
                           <td>
                              <input type='radio' name='direction' value='ASC' checked/>Ascending 
                              <input type='radio' name='direction' value='DESC' />Descending
                              
                           </td>
                        </tr>   
                      </form>
                     ";
               include "connections/dbconnect.php";
               
               if(isset($_POST['submit'])) 
               {   
                  $vehicle = $_POST['vehicle'];
                  $filter = $_POST['filter']; 
                  $direction = $_POST['direction']; 
               }
               else
               {
                  $filter = "make"; 
                  $direction = "ASC"; 
               }  
               //$manfactures = "Ford";
               $car_query = " SELECT * FROM cars WHERE model LIKE '%$vehicle%' OR make LIKE '%$vehicle%' OR model_details LIKE '%$vehicle%' OR search LIKE '%$vehicle%' ORDER BY $filter $direction ";                  
               $car_result = mysql_query($car_query) or die ("Error in query: $car_query. ".mysql_error()); 
               setlocale(LC_MONETARY, 'en_GB');
               $fmt = '%i';
               if (mysql_num_rows($car_result) > 0) 
               {
                  
                  
                  
                  while ($car_row = @ mysql_fetch_array($car_result)) 
                  {
                     
                     
                     print "
                           <table class='details'>
                              <tr>
                                 <td rowspan='2'>
                                    <img src=\"". $car_row["image"] ."\" alt='" . $car_row["image_alt"] . "' />
                                 </td>
                                 <td colspan='2'>
                                    <a href='" . $car_row["what_link"] . "'>
                                       " . $car_row["model"]." ".$car_row["model_details"] . " 
                                    </a>
                              </tr>
                              <tr>
                                 <td>
                                    <p class='info'>
                                       RRP:<br/>
                                       What Price:<br/>    
                                       Our Price:<br/> 
                                       Savings of:<br/> 
                                       Delivery Time: 
                                       
                                    </p>
                                 </td>
                                 <td>
                                    <p class='info1'>
                                 ";
                                       echo money_format($fmt, $car_row["rrp"] );
                             print "<br/>";
                           
                                       echo money_format($fmt, $car_row["what_price"] );
                             print "<br/>";
                           
                                       echo money_format($fmt, $car_row["our_price"] );
                            
                             $savings = $car_row["rrp"] - $car_row["our_price"];
                             print "       <br/>
                                       <font color=\"red\">";
                                          echo money_format($fmt, $savings );
                             print "       </font><br/> 
                                       " . $car_row["delivery_time"] . "
                                    </p>
                                 </td>
                              </tr>
                              <tr>
                                 <td>
                                 ";
                                    
                                    $query_cols = "SELECT * FROM colours JOIN car_to_color ON (colours.id = car_to_color.colour_id) WHERE car_id = 1";
                                    //$query_cols = "SELECT DISTINCT colours.id, colours.colour_img, colours.colour, colours.colour_img_alt, colours.price, colours.colour_type FROM colours,cars  WHERE colours.model LIKE '%$vehicle%' AND cars.model LIKE '%$vehicle%'";
                                    //$query_cols = "SELECT DISTINCT model FROM colours";
                                    $cols_result = mysql_query($query_cols) or die ("Error in query: $query_cols. ".mysql_error());                           if (mysql_num_rows($cols_result) > 0) 
                                    {
                                       while ($cols_row = @ mysql_fetch_array($cols_result)) 
                                       {
                                
                                 ?> 
                                                <a href='#' onmouseout='hideTooltip()' onmouseover='showTooltip(event,"<?php print "" . $cols_row["colour"] . " " . $cols_row["colour_type"] . "<br/>(£" . $cols_row["price"] . ")"; ?>");return false'>
                                      <?php
                                          print "                                                
                                                <img src=\"". $cols_row["colour_img"] ."\" alt='" . $cols_row["colour_img_alt"] . "' /></a>
                                               ";
                                       }
                                    }
                          print "
                                 </td>
                                 <td colspan='2'>
                                    <div id='CollapsiblePanel" . $car_row["id"] . "' class='CollapsiblePanel'>
                                      <div class='CollapsiblePanelTab' tabindex='0'><img class='drop' src='images/drop_down.jpg' alt='Go' /></div>
                                      <div class='CollapsiblePanelContent'>
                                         " . $car_row["id"] . "
                                      </div>
                                 </div>
                                 <script type='text/javascript'>
                                 <!--
                                    var CollapsiblePanel" . $car_row["id"] . " = new Spry.Widget.CollapsiblePanel('CollapsiblePanel" . $car_row["id"] . "', {contentIsOpen:false});
                                 //-->
                                 </script>
                                 </td>
                              </tr>
                              ";   
                  }                           
               }
               
   
               else
               {
                  print "
                           <table class='details'>
                              <tr>
                                 <td>Sorry, but we don't seem to have that vehicle available to us.</td>
                              </tr>
                       ";
               }
               print "</table>";                     

                        
                           ?>
                                    
                                    
        </div>
      <!-- end content -->
<?php include "sections/right.php";?>
<?php include "sections/footer.php";?>


            

[jdavidbakr]

Let's look just at your query logic and ignore the PHP formatting and strip away all the columns that aren't necessary for the logic.

 

Here's a basic situation: two cars, three colors, colors 1 and 2 go to car 1 and colors 2 and 3 go to car 2.  Your tables, without all the extra info, would look something like this:

 

Car table:

car_id   car_name
1          Car 1
2          Car 2

 

Color table:

color_id    color_name
1              Red
2              Blue
3              Green

 

Car_to_color table:

car_id     color_id
1            1
1            2
2            2
2            3

 

So, basically, Car 1 can be either red or blue, and car 2 can be either blue or green.  The primary key for the car_to_color table should be car_id and color_id together.

 

Now, you're looping through the cars:

 

select * from car

 

Each row you are using $car_row with mysql_fetch_array(), so to get the colors for the car whose row you're on:

 

select color.* from color join car_to_color on (car_to_color.color_id = color.color_id) where car_to_color.car_id = '{$car_row['car_id']}'

 

Be sure you understand the logic of how it should work before you try to put it into your code.  You might try making a simple page that just lists the car id and its color id's just to simplify the code and give you a chance to make sure you know exactly how it's supposed to work.

[BCAV_WEB]

You're my hero!  :D :D :D :D :D :D :D

 

First I forgot to put colour_id and car_id to have any data and secondly you've explained the logic and iv'e managed to get it to fit into my code and it works fine now!!