Board index   FAQ   Search  
Register  Login
Board index php forum :: Database mySQL & php coding

a little sql problem

Codes here !

Moderators: macek, egami, gesf

a little sql problem

Postby faust » Wed Jun 20, 2012 3:34 am

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 :)
faust
New php-forum User
New php-forum User
 
Posts: 109
Joined: Thu May 03, 2012 7:22 am

Re: a little sql problem

Postby egami » Wed Jun 20, 2012 4:32 am

SELECT DISTINCT(fieldname) FROM table WHERE fieldname LIKE 'test%'"
User avatar
egami
php-forum GURU
php-forum GURU
 
Posts: 2197
Joined: Wed Oct 06, 2010 11:19 am
Location: Happy Valley, UT

Re: a little sql problem

Postby faust » Wed Jun 20, 2012 6:27 am

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%
faust
New php-forum User
New php-forum User
 
Posts: 109
Joined: Thu May 03, 2012 7:22 am

Re: a little sql problem

Postby egami » Wed Jun 20, 2012 8:35 am

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..
User avatar
egami
php-forum GURU
php-forum GURU
 
Posts: 2197
Joined: Wed Oct 06, 2010 11:19 am
Location: Happy Valley, UT

Re: a little sql problem

Postby faust » Thu Jun 21, 2012 12:04 am

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 :)
faust
New php-forum User
New php-forum User
 
Posts: 109
Joined: Thu May 03, 2012 7:22 am


Return to mySQL & php coding

Who is online

Users browsing this forum: No registered users and 2 guests

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.