[SOLVED] Array with PHP and Mysql

[eagleweb]

I have 2 tables in a db. I have a table 'floortype' with 'id' (int) and 'type' (varchar). It has id 1 = carpet, id 2 = linoleum, id 3 = tile, id 4 = wood.  I have a table 'listings' with 'id' (int) and 'floortype' (varchar). It has id 1 = 1, 3, 4 and id 2 = 2, 3.

The form has checkboxes for each floortype and a client can 'checkmark' all floor types that apply. This implodes to an array (comma and space seperated) into listings['floortype'] as integers, not text. I am trying this to save db space.

When I want to show what is in that listings['floortype'], I have to explode the array (no problem) and then I have an array of integers. How do I show the floortype['type'] instead of the integers? Here is some of my code.

<?php

mysql_select_db($database_mysql_connect, $mysql_connect);

$query = "SELECT * FROM listings WHERE id = '".$_GET['ID']."'";

$results = mysql_query($query, $mysql_connect) or die(mysql_error());

$row = mysql_fetch_assoc($results);

$floorchoices = explode(", ",$row['floortype']);

$Count = count($floorchoices);

                          ?>

<?php

mysql_select_db($database_mysql_connect, $mysql_connect);

$query2 = "SELECT * FROM floortype";

$results2 = mysql_query($query2, $mysql_connect) or die(mysql_error());

$row2 = mysql_fetch_assoc($results2);

?>

I am trying this, but I obviously only get the integers:

// Create a Loop to Output the Information.

for ($i=0; $i < $Count; $i++) {

// Output the Information.

echo "Here are the Floor Types: $floorchoices[$i]<BR>";

  }

 

Maybe I am trying too hard. It seems that it should be easy.

Please help.

 

Thanks in advance

[hitman6003]

Since you didn't do a many-to-many relationship, and therefor can't do a simple join to get your data, you will have to retrieve the data from your table and store it in a temporary array, then reference it for each floor type.  Fortunately, your non-normalization normalization has a very small dataset....

 

<?php
mysql_select_db($database_mysql_connect, $mysql_connect);

$query = "SELECT * FROM listings WHERE id = '".$_GET['ID']."'";
$results = mysql_query($query, $mysql_connect) or die(mysql_error());
$row = mysql_fetch_assoc($results);
$floorchoices = explode(", ",$row['floortype']);

$query2 = "SELECT * FROM floortype";
$results2 = mysql_query($query2, $mysql_connect) or die(mysql_error());

while ($row = mysql_fetch_assoc($results2)) {
$floor_type[$row['id']] = $row['floortype'];
}

echo "This listing has the following floor types:";

foreach ($floorchoices as $f) {
echo $floor_type[$f] . ", ";
}

[eagleweb]

This gave me an output of 1,2,3,,,

I changed the:

while ($row = mysql_fetch_assoc($results2)) {

$floor_type[$row['id']] = $row['floortype'];

to:

while ($row2 = mysql_fetch_assoc($results2)) {

$floor_type[$row2['id']] = $row['floortype'];

addding the '2' after $row on both lines, thinking this might be the problem, but now I get an output of 1, 3, 4, 1, 3, 4, 1, 3, 4, for the particular one I am using for testing purposes. The numbers are right; 1, 3, 4.

 

 

[eagleweb]

I ended up writing this and it works fine, with the exception it puts a comma after the last floor type also.

 

$query2 = "SELECT * FROM floortype";

$results2 = mysql_query($query2, $mysql_connect) or die(mysql_error());

$row2 = mysql_fetch_assoc($results2);

 

The Floor Types are:

<?php do { ?>

<?php if (in_array($row2['id'], $floorchoices)) {echo "".$row2['type'].", ";} ?>

<?php

} while ($row2 = mysql_fetch_assoc($floor));

?>

 

This is the output: The Floor Types are: Carpet, Tile, Wood,

 

I tried removing the last comma using rtrim but it did not works for some reason. Here is what I used:

 

<?php if (in_array($row_floor['id'], $floorchoices)) {

  $floor_types = "".$row_floor['type'].", ";

  echo rtrim($floor_types, ",");} ?>

 

Got any ideas on how to remove that comma, which is actually a comma and a space?

[eagleweb]

Sorry - I had changed a few things around to keep from mixing up my code. Here is the actual code I tried for rtrim:

 

<?php if (in_array($row2['id'], $floorchoices)) {

  $floor_types = "".$row2['type'].", ";

  echo rtrim($floor_types, ",");} ?>