Multi Table Select & Loop

Hi everyone,
I’m just wondering if anyone would be able to help me.

I have two tables;

Info
	[B]scheme_id 	scheme_title[/B]
 	1 	        Snow Drops
	2 	        New Scheme Monkey

Colours
[B]colour_id 	scheme_id 	hex_value[/B]
        1 	1 	        #8d9cdd
 	2 	1 	        #bac2d9
 	3 	1 	        #e8e7ec
 	4 	1 	        #90a680
 	5 	1 	        #89a65d
 	6 	2 	        #111
 	7 	2 	        #333
 	8 	2 	        #444
	9 	2 	        #666
 	10 	2 	        #EEE
 	11 	2 	        #FFF 

Now what I’m trying to do is make a loop that displays like this

only the number of colours would be all with a matching scheme_id obviously.
the images are simply a transparent png with the hex_value being set as the background colour inline style. I also hope to remove the need to include the # in the db itself.

The code from the image was from this post http://www.sitepoint.com/forums/showthread.php?t=694702

Any help would be appreciated,
Thanks
Steph

Now that makes a lot more sense to me :D;
I’m pretty sure that you are right in that it’s time to leave this until tomorrow, but sometimes you just have to get that last thing working you know what I mean?

I will definitely have a look into that piece of code, but for right now i’m off to sleep

Thanks very much,
Steph

Wow, you’d better go to bed and continue tomorrow… :smiley:

but if you would like to explain it in a little more detail maybe I’d understand better?

Of course :slight_smile:

Working on the code, I noticed that you want to display the color codes and images horizontally. The easiest way to achieve this is, I think, storing the data in arrays first.

Take a look at this code:


$query = "
  SELECT 
      scheme_title
    , hex_value
  FROM info
  INNER JOIN colours
  ON info.scheme_id = colours.scheme_id
";
$result = mysql_query($query) or die("mysql error " . mysql_error() . "in query: $query");

// load the data in an array
$scheme_array = array();

while ($row = mysql_fetch_assoc($result)) {
  $scheme_array[$row['scheme_title']][$row['hex_value']] = $row['hex_value'];
}

// now you can loop through the array and display the data
foreach ($scheme_array as $scheme => $value) {
  echo "<h3>" . $scheme . "</h3>";
  echo "<p>";
  foreach ($value as $colour) {
    echo "$colour ";
  }
  foreach ($value as $colour) {
    echo "<img src='images/colour_box.png' style='background-color:$colour' />";

  }
}
}

I didn’t want to manually track the scheme, as it could have any number of schemes… perhaps I wasn’t quite understanding. It’s fairly late here (1:30am) so my brain is not fully functioning, but if you would like to explain it in a little more detail maybe I’d understand better?

Any reason why you abbandoned the join, and ignore my post about keeping track of the current scheme?

Ok, this ended up not being how I went with it, but this messy code works the way I like it.

Anyway to perhaps neaten it up?


	$sql_result_1 = mysql_query("SELECT scheme_id, scheme_title FROM info");
	
	
	while($row = mysql_fetch_array($sql_result_1)){
		
		echo "<h3>" . $row['scheme_title'] . "</h3>";
		echo "<p>";
		$hex_values = mysql_query("SELECT hex_value FROM colours WHERE scheme_id=" . $row['scheme_id']);
		while ($colour = mysql_fetch_array($hex_values)){
			echo $colour['hex_value']." ";
		}
		echo "</p>";
		$values = mysql_query("SELECT hex_value FROM colours WHERE scheme_id=" . $row['scheme_id']);
		while ($colours = mysql_fetch_array($values)){
			echo "<img src='images/colour_box.png' style='background-color: ".$colours['hex_value']."' />";
		}
		
	}

You still have to loop through the rows, but you’ll have to keep track of the scheme you’re currently displaying, so in case of a change of schemes, you can display the scheme header info.


// set the current scheme variable
$current_scheme = '';
while ($row = mysql_fetch_assoc($result)) {
  // check if a new scheme has come up
  if ($current_scheme != $row['scheme_title']) {
    // if so, display the scheme info
    ...
    // and update the current scheme variable
    $current_scheme = $row['scheme_title'];
  }
  // display the hex info
  ... 
} 

Well, I used


SELECT scheme_title, hex_value
FROM info
JOIN colours
USING ( scheme_id )

Result


[B]
scheme_title 	        hex_value[/B]
Snow Drops 	        #8d9cdd
Snow Drops 	        #bac2d9
Snow Drops 	        #e8e7ec
Snow Drops 	        #90a680
Snow Drops 	        #89a65d
New Scheme Monkey 	#111
New Scheme Monkey 	#333
New Scheme Monkey 	#444
New Scheme Monkey 	#666
New Scheme Monkey 	#EEE
New Scheme Monkey 	#FFF 

But it would now register each row as a scheme if i were to loop it as rows.

Good :slight_smile:

but was unsure on how to use php to display the result.

Try it. Then if you can’t get it to work, post your code here and I’m sure we’ll be able to help you.

I figured out the joining part but was unsure on how to use php to display the result.

You must join the two tables.

I’m actually just getting stuck on how i’d go about it, I mean I know what I want to achieve and can do it when all information is in a single table as shown in the other post.

My issue is drawing just the hex_values that correspond to the scheme_title and then looping the whole process i could do it using “WHERE scheme_id=1” but then how would i set that 1 to become the next value? and what if the next value is 3 not 2 because a row had been deleted.

Do you already have some code?