PHP pick up available MySQL set values

[tiggsy]

I suspect there isn't a way to do this, but in case there is:

Does anyone know how to pick up all the possible set values for a particular field from php? The reason I'm asking is that it means adding a new set value will not break all the programs that depend on it! In general, I try and write stuff that doesn't require an update to be made in several different places, if at all possible.

[dietdew12z]

I may not be understanding the question.

Are you wanting to produce a list of all possible values that a field could be set to?

Like field_name int(8)?

That would be quite a list for just about any field except maybe a bool field.

Perhaps you're looking for

SELECT DISTINCT fieldname FROM table

[tiggsy]

I have a field, promo, which is currently set to

set('--None--','On Sale','On Sale, Just One Left','Just One Left','Just In, but Only One Left')

and I've set up a manual select in the form, with each of the field values listed. But I'd prefer to be able to just pick up the possible values so that I don't have to change the program manually if I add a promo type.

I know what the field names are. I currently know what the possible values of promo are. But if someone adds a promo type, I'd like the program to pick them up without needing amendment.

[dietdew12z]

ardav had it right. select distinct will work for that, only thing to watch out for is that it will give you the list of values currently in the database so you will have to make sure each of those values is on at least one record.

[smantscheff]

Maybe you want SHOW CREATE TABLE which retrieves the table definition. From this you can extract all your enum (set) fields.

Personally, I'd keep it simple.

I'd have a table just for promo:

promo_id (PK/tinyint/2)   | promo (varchar/30)
1                         - --None--  
2                         - On Sale
3                         - On Sale, Just One Left
4                         - Just One Left
5                         - Just in, but Only One Left

The in your products table or whatever table you have at the moment:

product_id | promo_id | description (etc)
(PK/int) (tinyint/2) (varchar or text)

So you build a dropdown in your form for your products like this:

$result = mysql_query("SELECT promo_id, promo FROM promos");
$opt = '<select id="promos" name="promos">';
while($opts = mysql_fetch_array($result)){
 $opt .= "<option value=\"{$opts['promo_id']}\">{$opts['promo']}</option>"; 
}
$opt .= "</select>";
....
echo $opt;

This now means that you can add new promos to the promos table without worrying about messing with the values already stored. I assume that's what you were after.

[tiggsy]

Yeah, this would work. It means they need to amend both the main database and the promo types database, but it's doable.

Thanks. I already do something similar for "product type"

Having the tables related or joined in this manner is the best way IMO. Storing repetitive long text descriptions like "On Sale, Just One Left" is a recipe for disaster.

[tiggsy]

I already have 3 tables joined together, and it gets really complex when you select. But i've decided to store the promotions in the main database by id, and have a lookup table to discover what those are. Maybe I will join them, but my select atm is:

SELECT * FROM (SELECT * FROM newproducts ORDER BY updated DESC) AS latest LEFT JOIN (suppliers, newsrchGrps) ON (suppliers.supplierName = latest.supplierName AND newsrchGrps.groupCode = latest.groupCode) GROUP BY latest.groupCode LIMIT 6

for the front page. It's going to take some more headscratching to add in a lookup for promo types :{

When you say main DB, are you saying that you've got tables across more than one DB? That's awkward. Well, no, I suppose it's not too bad if they're on the same host.

There's no substitute for normalizing data. The sql you've shown does a look a bit inflated. Your use of * will ensure it's a bit slower than it ought to be as well. Try stipulating fields instead. Are you sure you need to nest selects? Can't you bypass this with a JOIN?

Some SQL GUIs for query builders: http://sourceforge.net/search/?q=mysql+query+builder

[tiggsy]

Oh, sorry. I always mix up the terms "database" and "table" - reflects my years using dBase III and its terminology, I guess. No. I only generally have one database per site.

The reason for the nested selects is that is the only way I've found to get a single item from each group, and still get them in date order. You can't use order by with group by for some reason, unless you nest it like this. Anyway, with all those joins...

I've built tight tables, and actually use everything in them, which I know is unusual. The id might be redundant in some cases, but I don't think I'll save a lot of overhead by omitting it, whereas if I add fields and forget to update the select statement, it could cause quite serious problems.

OK, I'm not sure that I follow you. Are we solved?

[tiggsy]

I thought I'd leave it open for a bit to see if anyone came up with another idea, but I guess it's pretty unlikely...

OK, you can always mark this as 'unsolved' - no pressure :)