a little sql problem

[faust]

so, I need a query that does this: get everything except null and empty values from a field and values which start certain way. Example of the values: everything NOT NULL and != '' and values which are like this 'test A 111', 'test B 222', 'test A 321'. I need the test A XXX, test B XXX values to get out from the query looking like this test A, test B (only one time). What I have so far:

$sql = "SELECT field";
$sql .= " FROM table";
$sql .= " WHERE field IS NOT NULL ";
$sql .= " AND field != '' ";
$sql .= " AND (src LIKE 'test A%' ";
$sql .= " OR src LIKE 'test B%') ";

But this will return only test A XXX and test B XXX values. I hope I explained it clear... Please help

[egami]

SELECT DISTINCT(fieldname) FROM table WHERE fieldname LIKE 'test%'"

[faust]

nope, this won't work. I have other values as well. field may contain val1, john, fire, etc... doesn't matter. The question is to grab all these distinct values and the ones that aren't empty/null and test A% and test B%

[egami]

I skipped over the everything part.. my bad.


$query = "SELECT DISTINCT(fieldname) FROM table WHERE fieldname != '' AND fieldname != NULL ";
// But really, you don't need fieldname != NULL.. as '' already does that. //


This will get EVERYTHING NOT NULL OR EMPTY for that specific field. If you want the rest of the row's pertinent data, you'll need to specify the fields individually. Just keep in mind, that it'll only bring up ONE row for each distinct fieldname, and not all of them. You can do all of them, but you'll need PHP to parse and store.

Since this is really what you asked for.. unless you have more specific terms...


But now that I smell the air a little better.. sounds like school work..




$query = "SELECT * FROM table WHERE fieldname != '' ";
$result = mysql_query($query);
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
$array[$row['fieldname']][] = $row;
}
}

echo '<pre>'; print_r($array); echo '</pre>';


You'll find that this creates an array key (category of the fieldname as distinct)
and then stores a new row of data underneath the category, therefore, having all of the data that partains to each row, nicely tucked away into an array that you can work with.



donate a couple of bucks to your local animal shelter if this helps you..

[faust]

thanks for the help. It's not school work and I know what this loop does. I was just wondering if there's a pure sql way to do it I guess this is the best way then, thanks again