generic;11020701 wrote:The bulk of it comes down to the edit section of my code where I'm trying to grab the prod_cats field (ie, "1,12,7") and check against those numbers on every iteration so it can add a selected="selected" to the select field.
There is the first problem I see - a non-normalized DB schema. If you had a normalized schema (e.g. no multiple-valued column but instead a separate relation table), you could execute a simple SQL query that would return all categories and a true/false value indicating whether they should be selected.
Fortunately (or unfortunately, depending upon how you look at it), MySQL is an extremely forgiving DBMS and has built-in functionality to let you work around having a poorly designed DB schema. Namely, it has functions like FIND_IN_SET(). Thus, you could execute a query like:
SELECT
c.cat_id,
c.cat_name,
IF( FIND_IN_SET( c.cat_id, p.prod_cats ), 1, 0 ) selected
FROM
categories c
LEFT JOIN products p ON p.prod_id = 123
(untested) that will give you a result set containing all categories as well as a true/false indicator (the "selected" pseudo-column) you can use in your PHP code to determine whether or not to output the selected="selected" HTML markup.
Note that you could simplify your code a bit in a couple of ways:
Fetch the list of categories first and store them in an array. No reason to deal with multiple MySQL query/result resources in cryptically named variables.
For that last bit... even if there was such a need... you might want to do yourself (and us) a favor and choose more helpful/descriptive variable names. (Result #2 and result #5 don't really tell you which one is the categories vs. the product data.)
Based on an assumption drawn from the name of the prod_id column ("id" = "identifier"?), I'm guessing that your products query should never return more than one row, correct? In other words, no two rows in your products table will share the same prod_id value.
If that's true, why do you have a while() loop to process the results of that query? A loop that executes at most one time isn't really much of a loop at all.
Finally, while the FIND_IN_SET() may work for a well-formed data set, note that you'd probably be better off removing the prod_cats column from the products table and instead creating a new product_categories cross-reference table that has two columns - prod_id and cat_id. For product ID #3, as an example, you'd INSERT two rows into such a table, the tuples being (3, 1) and (3, 3). (In other words, you'd be "normalizing" your DB schema to at least 1NF - First Normal Form.)
generic;11020701 wrote:I though of using checkboxes but it gets unwieldy if they end up with a few dozen categories.
But a multiple <select> entity that you have to sit and scroll through isn't unwieldy? :p
