Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Location
    Virginia, USA
    Posts
    124

    NULL value, query syntax question

    Ok here is the situation, I have a table that I am inserting info into by a PHP form. However, every column in the table is not always used. For example, this is how the data might look in the table:
    --------------------------------
    quest_4: BP
    quest_5: Yes
    quest_6: 9999
    quest_7: 32 Hill Dr.
    quest_8: None
    quest_9: Bob
    quest_10: NULL
    quest_11: NULL
    quest_12:
    quest_13: NULL
    quest_14: NULL
    quest_15:
    quest_16: NULL
    --------------------------------

    I need a select statement that can tell the difference between a NULL value and just an empty field. Is there a way to do this? Thanks for the help.

  2. #2
    Join Date
    Mar 2004
    Location
    Virginia, USA
    Posts
    124
    I seemed to have just answered my own question. The proper syntax is, 'IS NULL'. For example:

    SELECT customer_num FROM customer_questions WHERE quest_66 IS NULL


  3. #3
    Join Date
    May 2002
    Posts
    6,333
    Another useful method to remember is the isset and isempty functions after doing a select (in case you cant use IS NULL).

    i.e. if (isset($myrow["quest_10"]))

  4. #4
    Join Date
    Mar 2004
    Location
    Virginia, USA
    Posts
    124
    Ok, I'm not sure I understand what this is that you suggested. Mainly because I can't get it to work. Let me ask this another way. How would one go about checking to see if a column in a table existed without generating errors? My problem is that my columns are dynamic, meaning that they will forever be growing(up to a point). But I need to check to see if there is data in all of them, if they exist or not.


  5. #5
    Join Date
    May 2004
    Location
    Colorado
    Posts
    948
    well to check and see if a column exists (if that is what your asking right) in a mysql database, you could just use

    PHP Code:
    $query "SELECT * FROM tablename";
    $query mysql_query($query$link);
    $colexists mysql_result($query0"colname");
    if(!
    $colexists){
        echo 
    'Column does not exist';
    }else{
        
    //col exists
        
    free_result($query);

    that would echo Column doesnt exist if it doesnt get a result from the column that you spceify in the myslq result query.

    hope that helps you


    Absolute Power, its the only way to roll!

  6. #6
    Join Date
    Mar 2004
    Location
    Virginia, USA
    Posts
    124
    the problem that I have is that when I have an if statement like you are suggesting, if the column does not exist I get an error...

    Warning: mysql_result(): supplied argument is not a valid MySQL result resource in C:\myscript.php on line 65

    or if the select statement returns no rows I also get an error.

  7. #7
    Join Date
    May 2002
    Posts
    6,333
    Are you saying that quest_4 is the primary key of your table?

    In the table definition is it possible for this primary key to be NULL? i.e. did you define the key of the table to be 'not null'?

    If so you should be able to perform a select on the key without an error and then use the isset or isempty functions to check whether the subsequent fields (quest_10, quest_13) are null.

    Why dont you post your table definition, what search you want to do, and what you want to do with the results so we can more accurately tell you how you can do it?

  8. #8
    Join Date
    Mar 2004
    Location
    Virginia, USA
    Posts
    124
    No, the columns in question are not the key. The issue is that my php code may be requesting data from a column that does not exist. The reason is that the columns are created dynamically by another script due to the fact that they will be storing more and more data over time.

    I guess I could just go ahead and create all of the possible future columns in case they were to be used. Only problem with this is that I may end up with alot of extra fields that will never be used.

    Im sure all of this is confusing. Here is my table:
    (i have chopped off the end of it because there are many fields, just pretend that the quest_#s go on and on through 70)

    +--------------+------------------+------+-----+---------+----------------+
    | Field | Type | Null | Key | Default | Extra |
    +--------------+------------------+------+-----+---------+----------------+
    | autoid | int(10) unsigned | | PRI | NULL | auto_increment |
    | customer_num | int(10) unsigned | YES | | NULL | |
    | quest_3 | varchar(255) | YES | | NULL | |
    | quest_4 | varchar(255) | YES | | NULL | |
    | quest_5 | varchar(255) | YES | | NULL | |
    | quest_6 | varchar(255) | YES | | NULL | |
    | quest_7 | varchar(255) | YES | | NULL | |
    | quest_8 | varchar(255) | YES | | NULL | |
    | quest_9 | varchar(255) | YES | | NULL | |
    | quest_10 | varchar(255) | YES | | NULL | |
    | quest_11 | varchar(255) | YES | | NULL | |
    | quest_12 | varchar(255) | YES | | NULL | |
    | quest_13 | varchar(255) | YES | | NULL | |
    | quest_14 | varchar(255) | YES | | NULL | |
    | quest_15 | varchar(255) | YES | | NULL | |
    | quest_16 | varchar(255) | YES | | NULL | |
    | quest_17 | varchar(255) | YES | | NULL | |

    This is a system that asks the user a bunch of questions, however since an admin of the system can both create questions and deactivate them, the number of fields needed will change over time. Thats why I need to check for data that may not exist, in a column that may not exist.

    I need to either be able to tell if a column exist with out generating errors in my scripts, or like I said before, go ahead and create all of the columns, and just deal with the fact that some of them will never be used.

  9. #9
    Join Date
    Mar 2004
    Location
    Virginia, USA
    Posts
    124
    Ok, after walking away from this problem, and getting some fresh caffine in me, I realized what a monkey I have been.

    PHP Code:
    if (!empty($result) and mysql_num_rows($result) > 0
    This fixed the whole thing. Thanks for all of the suggestions guys!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •