aeroswat Posted December 15, 2009 Share Posted December 15, 2009 My delimma... I have a field in my database that contains eye information as it pertains to being blind or not. The records can have values like so: CF - Counting Fingers NIL - Totally blind etc stored as the acronym only or the field can have a visual acuity like such 20/200 or 20/20 etc... I want two sql statements one to be able to pull up the records on people who have any letter acronym or greater than or equal to a 20/200 visual acuity and another to pull up the records of people who have less than a 20/200 visual acuity Would it be better to search them all and filter them with a php function or is there a way I can do this with a SQL statement? Link to comment https://forums.phpfreaks.com/topic/185245-would-this-be-easier-to-do-with-a-php-function/ Share on other sites More sharing options...
nafetski Posted December 16, 2009 Share Posted December 16, 2009 Well, my first suggestion would be house the data in two seperate fields. I'd have the acryonym set as an ENUM field, and the other set as a varchar. Having said that, given your problem...there is a way around it If your acronyms are typically 3 characters, you could use some of MySQL's string functions. Ex: SELECT * from blind_ass_patients_table where LENGTH(howBlind) > 3 ORDER BY howBlind ASC This way your return result from MySQL isn't going to be as large, and it does some initial sorting for you. Not a perfect solution, but might work Link to comment https://forums.phpfreaks.com/topic/185245-would-this-be-easier-to-do-with-a-php-function/#findComment-978321 Share on other sites More sharing options...
JonnoTheDev Posted December 16, 2009 Share Posted December 16, 2009 Well, my first suggestion would be house the data in two seperate fields. I'd have the acryonym set as an ENUM field, and the other set as a varchar. Having said that, given your problem...there is a way around it If your acronyms are typically 3 characters, you could use some of MySQL's string functions. No, you are better creating a relational table i.e people ===== id name eyeSightScoreId eyeSightScore ============= eyeSightScoreId title So in the eyeSightScore table I have the following records 1 20/20 2 20/200 3 Counting Fingers 4 Totally Blind In the people table I have the following records 1 Joe Bloggs 3 2 John Doe 4 3 Sue Smith 4 4 Paul Smith 2 The tables are related on the field eyeSightScoreId So If I want to find all people who are totally blind I run the following query SELECT name FROM people WHERE eyeSightScoreId=4 If I want people who are less than 20/20 vision I can run the follwing query SELECT name FROM people HAVING eyeSightScoreId IN(2,3,4) Link to comment https://forums.phpfreaks.com/topic/185245-would-this-be-easier-to-do-with-a-php-function/#findComment-978370 Share on other sites More sharing options...
aeroswat Posted December 21, 2009 Author Share Posted December 21, 2009 I like both ideas. Thanks guys for the help! Link to comment https://forums.phpfreaks.com/topic/185245-would-this-be-easier-to-do-with-a-php-function/#findComment-981586 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.