Retrive data from multiple table

This is how my database structure looks like.

The c_color1, c_color2,......& c_color6 (int) are all integer field. I want to fetch cp_fullname of cp table on to c_color1, c_color2,......& c_color6 individually.
is this possible.
it would be better if i could alias the c_color1 = color1<-which would be the cp_fullname (which is fetched by the id of c_color1 = cp_id)

 

Can you tall me what the functionality of this is. I'm having a hard time visualizing exactly what you are needing to accomplish. Mainly the association with of cp_fullname -> c_color*. Can cp_fullname be any of the values of those c_color fields? If this is the case, this is probably not that way you want to organize this.

Also, if possible, you should associate the categoryid and not the categoryname with c_category, as numerical associations are almost always quicker for the DB to process than character based ones.

Also, this is MySQL, correct?

 

you can always use a join

 

With your structure you'd have to join back to cp.color_id from every colordb.c_colorX column (1,2,3...). It would look something like this (you'll have to add all the joins for cp3 thru cp6).

select cc.categoryname, cdb.c_notes, cp1.cp_fullname, cp2.cp_fullname
from c_category cc, colordb cdb, cp cp1, cp cp2
where cc.category_id = cdb.category_id
and cdb.c_color1 = cp1.color_id
and cdb.c_color2 = cp2.color_id

Code (markup):

 

i tried all of the above suggestion, but I couldn't join them to get the values of the ID number so i ended up adding this long code to get the result

Please suggest me if you have better idea than this temporary solution.

$sumit1 = $row_color['c_color1'];
$sumit2 = $row_color['c_color2'];
$sumit3 = $row_color['c_color3'];
$sumit4 = $row_color['c_color4'];
$sumit5 = $row_color['c_color5'];
$sumit6 = $row_color['c_color6'];

if($sumit1){
	$query1 = "SELECT cp_fullname FROM cp WHERE cp_id=$sumit1";
	$result1 = mysql_query($query1) or die('Query Failed: ' . mysql_error());
	$row1 = mysql_fetch_row($result1);
	
	}
if($sumit2){
	$query2 = "SELECT cp_fullname FROM cp WHERE cp_id=$sumit2";
	$result2 = mysql_query($query2) or die('Query Failed: ' . mysql_error());
	$row2 = mysql_fetch_row($result2);	
	}
if($sumit3){
	$query3 = "SELECT cp_fullname FROM cp WHERE cp_id=$sumit3";
	$result3 = mysql_query($query3) or die('Query Failed: ' . mysql_error());
	$row3 = mysql_fetch_row($result3);	
	}
if($sumit4){
	$query4 = "SELECT cp_fullname FROM cp WHERE cp_id=$sumit4";
	$result4 = mysql_query($query4) or die('Query Failed: ' . mysql_error());
	$row4 = mysql_fetch_row($result4);	
	}
if($sumit5){
	$query5 = "SELECT cp_fullname FROM cp WHERE cp_id=$sumit5";
	$result5 = mysql_query($query5) or die('Query Failed: ' . mysql_error());
	$row5 = mysql_fetch_row($result5);	
	}
if($sumit6){
	$query6 = "SELECT cp_fullname FROM cp WHERE cp_id=$sumit6";
	$result6 = mysql_query($query6) or die('Query Failed: ' . mysql_error());
	$row6 = mysql_fetch_row($result6);	
	}

<th><?php echo $row1['0']; ?></th>
    <td><?php echo $row_color['c_color1_qty']; ?></td>
  </tr>
  <tr>
    <th><?php echo $row2['0']; ?></th>
    <td><?php echo $row_color['c_color2_qty']; ?></td>
  </tr>
  <tr>
    <th><?php echo $row3['0']; ?></th>
    <td><?php echo $row_color['c_color3_qty']; ?></td>
  </tr>
  <tr>
    <th><?php echo $row4['0']; ?></th>
    <td><?php echo $row_color['c_color4_qty']; ?></td>
  </tr>
  <tr>
    <th><?php echo $row5['0']; ?></th>
    <td><?php echo $row_color['c_color5_qty']; ?></td>
  </tr>
  <tr>
    <th><?php echo $row6['0']; ?></th>
    <td><?php echo $row_color['c_color6_qty']; ?></td>

PHP: