complex query write

[itisnot_me]

ok so i was starting to make a query and thought that there could be a better way of making it.

what i am trying to do it only print entries that does not have there value as null. ex: phone IS NOT NULL and street IS NOT NULL

i was starting to write it out like that but i know there has to be a better way.

also as a double whammy question if i do make it a better query how can i have a or statement for one thing. ex: lets say that there are two fields that are high school gpa and college gpa. (there only going to fill out one) and if hs gpa is null check to see if college is not null.

i hope that i am making sense. thanks in advance

[OS_dev]

ok so i was starting to make a query and thought that there could be a better way of making it.

what i am trying to do it only print entries that does not have there value as null. ex: phone IS NOT NULL and street IS NOT NULL

i was starting to write it out like that but i know there has to be a better way.

also as a double whammy question if i do make it a better query how can i have a or statement for one thing. ex: lets say that there are two fields that are high school gpa and college gpa. (there only going to fill out one) and if hs gpa is null check to see if college is not null.

i hope that i am making sense. thanks in advance

"phone IS NOT NULL and street IS NOT NULL", this is the only way I know how to do it.

"and if hs gpa is null check to see if college is not null.", I would look into some type of case statement.
I would think it would be some like this:
EDIT: (keep in mind, this will probably work fine but I haven't tested it.)

select name, address, phone, street, 
case 
when hs_gpa is null then college
when college is null then 'n/a'
end as gpa
from applicant where phone IS NOT NULL and street IS NOT NULL

[damnpoet]

ok so i was starting to make a query and thought that there could be a better way of making it.

what i am trying to do it only print entries that does not have there value as null. ex: phone IS NOT NULL and street IS NOT NULL

i was starting to write it out like that but i know there has to be a better way.

also as a double whammy question if i do make it a better query how can i have a or statement for one thing. ex: lets say that there are two fields that are high school gpa and college gpa. (there only going to fill out one) and if hs gpa is null check to see if college is not null.

i hope that i am making sense. thanks in advance

i'm not completely sure what you mean, but for your first question do you need a SQL statement:

SELECT * FROM table WHERE phone IS NOT NULL AND street IS NOT NULL

or you need a php script to print not null values like this:

include("dbconnect.php");
$query = mysql_query("SELECT * FROM table");
while($data = mysql_fetch_array($query)){
   if(!is_null($data['phone']) && !is_null($data['street'])){
       var_dump($data);
   }
}

let me know if that's what you mean...

[OS_dev]

Im sorry, that case statement was wacked.

select name, address, phone, street, 
case 
when hs_gpa is null then college
when college is null then 'n/a'
end as gpa
from applicant where phone IS NOT NULL and street IS NOT NULL

instead, I would try this

select name, address, phone, street, 
case 
when hs_gpa is null and college is null then 'n/a'
when hs_gpa is null then college
else hs_gpa
end as gpa
from applicant where phone IS NOT NULL and street IS NOT NULL

[itisnot_me]

so i just do it how i thought i had to

SELECT * FROM applicant 
WHERE 
phone IS NOT NULL and 
street IS NOT NULL and
(hs_gpa IS NOT NULL or college_gpa IS NOT NULL)

[FlashCreations]

There is no shorter way to ensure that several fields aren't blank in SQL, but you could write a PHP function to make it slightly easier:

function selectWhereNotNull($fields, $table, $where) {
    //Init Query
    $query = "SELECT ";
    //Append fields to Select
    if(is_array($fields)) {
        $query .= implode(", ", $fields);
    } else {
        $query .= $fields;
    }
    //Append table to select data from
    $query .= " FROM {$table} WHERE ";
    //Append IS NOT NULL for each statement
    $statements = array();
    foreach($where as $field) {
        $statements[] = "{$field} IS NOT NULL";
    }
    $query .= implode(" AND ", $statements);
    return(mysql_query($query));
}

Usage:

$query = selectWhereNotNull(array("fields", "to", "select"), "table", array("fields", "that", "are", "not", "null"));