how-to logic - not one row, but are fields populated in row

[ATS16805]

how might i form an IF statement to determine how many, and which, if any fields are populated in any given row of my db?

specifically-- i have 10 fields (9 + 1 primary key, auto_inc). in addition to the primary key, one of these fields has been completely populated w/ a unique identifier representing all widgets which need describing.

PURPOSE of APP:
Ensure a thorough description exists for a list of Widgets.

PROCESS OF APP (as i see it, and at its current state of development):

• Select a Widget from the entire list of Widgets, and ensure that each (or at least some) of its characteristics have been defined. Described Widgets will have their respective fields populated with data.

• If Widgets have not been yet described, then show form for providing their missing description.

• If a description already exists, then ask if description should be shown for verification (and updated), or to go back and select another widget from the Unique Identifier field (which currently populates a dropdown for the selection process via simple WHILE loop).

• If existing Widget description needs updating, then provide appropriate form.

some of the widgets have already been described along with their unique ids during the database creation process, however, most have not.

This is the extent of my current knowledge for performing the logic test:

$widgetrow = mysql_num_rows($queryresult)
if ($widgetrow == 0) { // SHOW FORM 

MY IMPASS:
the php i've shown doesn't help me at all because EVERY row already exists for my query against my Widget Identifier. I need to query instead against the empty (or defined) fields which represent the Widget's 8 possible characteristics.

how do i create the IF statement / MySQL query to test for populated fields instead of populated rows? is this a normalization issue in some way?

should this not be done using an IF statement, then what choices might i have? do you have a recommended reading for me? a reference to the manual maybe-- for a function that i don't know yet?

thanks!!!

[ATS16805]

HOLD THE PRESS!
this TOTALLY is a normalization issue, isn't it!?!?

okay, if it is-- and i need to have a table only of my widget ID's-- how should I best handle the relationship between the widget-only table, and the new, blank table?

(sorry! if i seem really confused, it's because i am. i know i've done more complex stuff than this-- but it's the PRE-Populated data that's really messing me up for some reason!)

[Roger_Ramjet]

Pretty straightforward really, and not really a data normalisation issue. Yes, you could have 2 tables (or more, you could end up with a seperate table for each field - would still be normalised data), but if the intention is that every widget MUST have a full description then it is not neccessary.

1) Make things easy by defining the default for the columns as NULL.

2) Personally I'd design the edit page diferently - I'd basically just show all fields empty or not, and let users click an edit button. You see, it's all an update - whether the fields are all empty, partially filled, or fully poulated - cos the records already exists and you want people to be able to edit the existing data.

The determining factor in cases like this is what is going on OUTSIDE the database, ie how the organisation and the users are going to do the work. If someone is going to sit down and go through the widgets methodically then you want to ask them which ones they want to do first - those with no data, those with partial data, or just in some numeric order? And the answer to that will probably be dictated by where they are going to get the data to input from. Will it be in random order, or at random intervals? Questions like that are called 'Domain Knowledge' and are really the most important questions to answer when designing the application.

That said, to select the data you just use 'WHERE col1 IS NULL' or 'WHERE col1 IS NOT NULL' etc. Breaking things out into seperate tables and using LEFT JOINS is not going to help determine if all or only some of the fields have data - you'll still need the IS NULL test so just go with it from the off.

[ATS16805]

Roger Ramjet wrote:

to select the data you just use 'WHERE col1 IS NULL' or 'WHERE col1 IS NOT NULL' etc. Breaking things out into seperate tables and using LEFT JOINS is not going to help determine if all or only some of the fields have data - you'll still need the IS NULL test so just go with it from the off.

THanks, RR! okay, so i use WHERE col1 is NULL at the end of my UPDATE statement-- but, and this is not at all to say that you didn't give sufficient effort in your explanation, but i'm still feeling like i'm at a loss here-- i think it's because i'm having difficulty thinking about how i'm going to actually put that query to use since i've never done this type of query before -- how do i handle the result?

in other words-- am i going use something like this:

$checkDataQuery = mysql_query("SELECT * from widgets_chars WHERE char1 IS NULL") or die(mysql_error());
$widgetsArray = mysql_fetch_array($checkDataQuery);

while ($showVals = $widgetsArray) {
$widgetChar1 = $showVals[char1];
$widgetChar2 = $showVals[char2];
$widgetChar3 = $showVals[char3];
$widgetChar4 = $showVals[char4];   // etc..

if ($widgetChar1 == "NULL") {

??????

}

see, i'm just not sure what i'm supposed to do with it-- if you can think of a tutorial, or my best reference URL -- or even just an idea of how i should modify the above code, i'd really appreciate it. i'll lookup IS NULL at MySQL.com, but anything you can do to lead me in the right direction, please let me know-- i don't expect you to write a book on it of course, as i all too often do in my posts
😉
but anything that might come to mind, if it's not too much trouble, it'll probably really help me.

does it look like i'm even on the right track at all above, or am i already going down the wrong path by the looks of it?

BTW-- i've never used a "LEFT JOIN", so i'll have to look that up too to see what you had on your mind about what i was doing over here.

thanks again!!!

[ATS16805]

ATS16805 wrote:

it's the PRE-Populated data that's really messing me up for some reason

Roger Ramjet wrote:

just show all fields empty or not, and let users click an edit button. You see, it's all an update - whether the fields are all empty, partially filled, or fully poulated - cos the records already exists and you want people to be able to edit the existing data.

actually -- now that i think about it-- THIS is what i WANT to do-- but again, i don't know how to make it happen. i suppose that's the inherent problem here-- my lack of knowledge of php scripts to do these various types of view/ insert/ update functions-- i guess i really only know how to view or insert-- but not show all and allow for update at the same time. i need to learn a script for that.

Roger Ramjet wrote:

The determining factor in cases like this is what is going on OUTSIDE the database, ie how the organisation and the users are going to do the work.

this whole thing is actually something i decided to do to help me build the backend of a larger app (an app for comparing these widgets) while allowing for possible collaboration w/ the ultimate owner of this app-- so that he and i can go through this data and ensure that it is complete and appropriate for powering what is going to go on after it IS complete-- and that is to be able to use the data for comparison operations between a pair (or more) of Widgets. in other words-- so we can ultimately look and say "okay-- this one is ready for the comparison app. how about the next one", until we arrive at a full, complete set of Widgets for later comparison against each other. does that make sense? it's the only way i can think of to "guarantee" that i've assembled the best possible data, so that the "larger app" will be as efficient as possible for its own purpose. also, leaving this part to be updated at anytime on the backend will ensure that my "larger app"-- the Widgets for comparison, will always be up-to-date.

[ATS16805]

so, why when i search the entire MySQL.com site for "is not null" do i receive no matches?

suggestions, please?

edit: nevermind-- i found it