Forum Moderators: coopster

Message Too Old, No Replies

get and display sql join for two table columns

         

bkeep

7:02 am on Feb 28, 2008 (gmt 0)

10+ Year Member



What I have is a website where users can add there own feature options
option1 option2 option3 ...
These options are stored in a MySQL database

one of the tables is features
with columns of id, features, default
default is an smallint col with 1 or 0 and once a user enters in the options they can edit those options and set each option as a default option or 1.

the second part of the process is the user can then go to a page that allows them to enter a vehicle listing which pulls the data from the features into a select box that allows multiple selections features[]
I submit this via post and grab it in my script.

Once the features[] array is grabbed I run it through serialize and dump the features id contents into the database. (14,12,4,34...)
That table is listings
with columns of features data ...Other ones left out for brevity.

So at this point I have id numbers in the listings features column and a table with the id, features, default in the features table. All is good. I can retrieve the data from the listings table features column using unserialize without issue. So that is where I will start my question once I have gotten my unserialzed data and it is in an array.

So now that I have bored the death out of you with the background information.

My question is, how do I get the full list with all of the information to merge with the short list in the listings table so when the information gets displayed the options already selected as defaults get overridden with the listings features selections as defaults?

Upfront I will tell you I am not so smart ;) What I think needs to happen is I need to get the features list first from the features table then somehow check it against the features list

$sql = "SELECT features FROM listings";
$sql = "SELECT * FROM features";

Any help would be appreciated as I am just kinda stumped even at how to think through this problem. Maybe it's my ADD kicking in ;)
Brandon

bkeep

7:21 am on Feb 28, 2008 (gmt 0)

10+ Year Member



well Maybe writing it out helped me see the solution and here it is Since I am using Smarty thats where it was.


<p><label for="features">Features:</label>
<select id="features" name="features[]" size="4" multiple="multiple">
{foreach item="features" from=$featurelist}
<option value="{$features.id}"{foreach item="data" from=$records.features}{if $features.id eq $data} selected="selected"{/if}{/foreach}>{$features.features}</option>
{/foreach}
</select>
</p>

I had to add in another foreach loop inside the existing foreach I would assume it is basically the same way within php pages? I do have a lot of nested foreach loops and It makes me wonder if that slows it down and could be better handled before being sent to the template.

Any ideas/thoughts on that?
Best Regards,
Brandon