Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Page 1 of 2 1 2 LastLast
Results 1 to 10 of 13

Thread: mySQL question

  1. #1
    JR
    JR is offline
    Senior Member JR's Avatar
    Join Date
    Nov 2002
    Location
    UK
    Posts
    4,354
    Member #
    257
    Is there any way in mySQL (just mySQL, not mySQL and PHP) to have a field that is set to 1 if another field is not empty (and 0 if it is). I need this because I want to check to see which records in a particular field are empty, and which ones are not, but the problem is that this field is text and the query may be too slow.

    Thanks
    JR

  2.  

  3. #2
    Senior Member filburt1's Avatar
    Join Date
    Jul 2002
    Location
    Maryland, US
    Posts
    11,774
    Member #
    3
    Liked
    21 times
    I do not fields can dynamically update themselves in MySQL, although I would imagine that it is not that expensive to check if a row's column is empty; even if the first character exists, by definition, it is not empty.
    filburt1, Web Design Forums.net founder
    Site of the Month contest: submit your site or vote for the winner!

  4. #3
    JR
    JR is offline
    Senior Member JR's Avatar
    Join Date
    Nov 2002
    Location
    UK
    Posts
    4,354
    Member #
    257
    So you are saying that I should just use a query that checks the first characters of the records in this field? How would I go about doing that (the SQL query that is, not the PHP)?
    JR

  5. #4
    Senior Member rosland's Avatar
    Join Date
    Jul 2003
    Location
    Norway
    Posts
    1,944
    Member #
    2096
    I experimented a little.

    I made a table with an allowable NULL column. Then I filled in 7 rows, leaving the "allowable NULL" column empty in two rows.

    If you run a query like:

    SELECT COUNT (not_null_columnname) FROM tablename WHERE null_column IS NULL

    Returns the number of rows where null_columnname is empty (2)
    --------------
    SELECT COUNT (not_null_columnname) FROM tablename WHERE null_column IS NOT NULL

    Returns the number of rows where null_columnname is not empty (5)
    --------------
    SELECT * FROM tblename WHERE columnname IS NULL;
    Returns all rows where columnname is NULL
    --------------
    SELECT * FROM tblename WHERE columnname IS NOT NULL;
    Returns all rows where columnname is other than NULL
    S. Rosland

  6. #5
    JR
    JR is offline
    Senior Member JR's Avatar
    Join Date
    Nov 2002
    Location
    UK
    Posts
    4,354
    Member #
    257
    Hmm, not quite what I'm looking for I don't think. I need to know the specific records which are empty in the given field, and ones which are not.
    JR

  7. #6
    Senior Member rosland's Avatar
    Join Date
    Jul 2003
    Location
    Norway
    Posts
    1,944
    Member #
    2096
    See my last edit :classic:
    S. Rosland

  8. #7
    JR
    JR is offline
    Senior Member JR's Avatar
    Join Date
    Nov 2002
    Location
    UK
    Posts
    4,354
    Member #
    257
    Still doesn't do it (re-read my post, I edited it)...
    JR

  9. #8
    Senior Member rosland's Avatar
    Join Date
    Jul 2003
    Location
    Norway
    Posts
    1,944
    Member #
    2096
    I don't quite get it, could you exemplify?

    The above suggestions will list all records where a particular field is empty, or list all records where that same field is not empty.
    S. Rosland

  10. #9
    JR
    JR is offline
    Senior Member JR's Avatar
    Join Date
    Nov 2002
    Location
    UK
    Posts
    4,354
    Member #
    257
    Ok..

    I have a field ('text'), which I need to run a query to see in which records this field is empty, and in which records it is not. This would be very easy to do using PHP and an SQL query, but as the text field is, yes you guessed it, a 'text' field (i.e. no limit on it), it will get very resource intensive and will take time to execute.

    I could do a fix that, as I mentioned before, put a 1 into another field if the text was entered, and a 0 if it wasn't, using PHP upon entry, but to save me doing that, I was wondering if there was an automated way to do it using mySQL, which filburt says there is not.

    So what I'm looking for now is a way to execute the query without pulling the great amount of text out of the database. I think filburt says I could just query the first character of the text in the records of this field, but I don't know how to do that.
    JR

  11. #10
    Senior Member rosland's Avatar
    Join Date
    Jul 2003
    Location
    Norway
    Posts
    1,944
    Member #
    2096
    OK, like you say you would need a PHP script to automatically enter the '1' when the text column recieved an entry.

    My point is you with the above queries you don't need to update anything. If you have a simple table:

    ID | name | text

    If you use the ID or name column as the identifier, and 'text' in the where clause, then the queries would look like this:
    Code:
    SELECT COUNT (ID) FROM tablename WHERE text IS NULL
     
    SELECT COUNT (ID) FROM tablename WHERE text IS NOT NULL 
    
    SELECT ID,name FROM tblename WHERE text IS NULL
    
    SELECT ID, name FROM tblename WHERE columnname IS NOT NULL
    I don't think that drains a lot of resources, as it's not extracting any of the text data from the database, nor does it have to read through all the data. It just checks to see wether the column contains data or not.

    (I could of course be wrong. It has happened on one or two occasions):cheeky:
    S. Rosland


Page 1 of 2 1 2 LastLast

Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 03:02 AM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com