Page 1 of 1

Gathering info from SQL but without same field name

Posted: Wed Aug 22, 2007 9:15 pm
by psychotomus
I need to search a sql database. it has 3 fields

fid, uid, value

I am searching for fid and value entrys, but when it matches, it returns more then 1 result with uid.

like it will return 3 values with the same uid. i only want to return all results but with only 1 record for each uid it returns.

how do I do this?

Posted: Wed Aug 22, 2007 9:19 pm
by feyd
GROUP BY...

Posted: Thu Aug 23, 2007 4:44 pm
by psychotomus
i'm still having some troubles. seems to be a bit more complicated

Theres 3 fields in the table

fid, uid, value

I need to search for instances of each matching fid and value. i believe i am doing it the wrong way as my sql_string is something like this:

SELECT * FROM profile_values WHERE fid='9' AND value='NY' AND fid='16' AND value='1' AND fid='17' AND value='1' AND fid='21' AND value='1' AND fid='19' AND value='1' AND fid='16' AND value='1' AND fid='18' AND value='1' AND fid='20' AND value='1' AND fid='4' AND value='' GROUP BY uid


so im sure since its looking for multiple values with different values, it returns 0 results. how can i go about making this search work?

Code: Select all

$state = mysql_real_escape_string($_POST['state']);
$wearhat = mysql_real_escape_string($_POST['wearhat']);
$temptattoo = mysql_real_escape_string($_POST['temptattoo']);
$bumperstickers = mysql_real_escape_string($_POST['bumperstickers']);
$flyers = mysql_real_escape_string($_POST['flyers']);
$wearshirt = mysql_real_escape_string($_POST['wearshirt']);
$permtattoo = mysql_real_escape_string($_POST['permtattoo']);
$carmagnets = mysql_real_escape_string($_POST['carmagnets']);
$searchtype = mysql_real_escape_string($_POST['searchtype']);

$SQL_STRING = "SELECT * FROM profile_values WHERE ";

//if state is selected
if(!empty($state))
	$SQL_STRING = $SQL_STRING . "fid='9' AND value='$state'";

//if wear hat
if (strpos($SQL_STRING, "AND") === FALSE)
{
	if(!empty($wearhat))
		$SQL_STRING = $SQL_STRING . "fid='16' AND value='1'";
}
else
{
	if(!empty($wearhat))
		$SQL_STRING = $SQL_STRING . " AND fid='16' AND value='1'";
}

//if temptattoo
if (strpos($SQL_STRING, "AND") === FALSE)
{
	if(!empty($temptattoo))
		$SQL_STRING = $SQL_STRING . "fid='17' AND value='1'";
}
else
{
	if(!empty($temptattoo))
		$SQL_STRING = $SQL_STRING . " AND fid='17' AND value='1'";
}

//if bumperstickers
if (strpos($SQL_STRING, "AND") === FALSE)
{
	if(!empty($bumperstickers))
		$SQL_STRING = $SQL_STRING . "fid='21' AND value='1'";
}
else
{
	if(!empty($bumperstickers))
		$SQL_STRING = $SQL_STRING . " AND fid='21' AND value='1'";
}

//if flyers
if (strpos($SQL_STRING, "AND") === FALSE)
{
	if(!empty($flyers))
		$SQL_STRING = $SQL_STRING . "fid='19' AND value='1'";
}
else
{
	if(!empty($flyers))
		$SQL_STRING = $SQL_STRING . " AND fid='19' AND value='1'";
}

//if wearshirt
if (strpos($SQL_STRING, "AND") === FALSE)
{
	if(!empty($wearshirt))
		$SQL_STRING = $SQL_STRING . "fid='16' AND value='1'";
}
else
{
	if(!empty($wearshirt))
		$SQL_STRING = $SQL_STRING . " AND fid='16' AND value='1'";
}

//if permtattoo
if (strpos($SQL_STRING, "AND") === FALSE)
{
	if(!empty($permtattoo))
		$SQL_STRING = $SQL_STRING . "fid='18' AND value='1'";
}
else
{
	if(!empty($permtattoo))
		$SQL_STRING = $SQL_STRING . " AND fid='18' AND value='1'";
}

//if carmagnets
if (strpos($SQL_STRING, "AND") === FALSE)
{
	if(!empty($carmagnets))
		$SQL_STRING = $SQL_STRING . "fid='20' AND value='1'";
}
else
{
	if(!empty($carmagnets))
		$SQL_STRING = $SQL_STRING . " AND fid='20' AND value='1'";
}

//search type
if (strpos($SQL_STRING, "AND") === FALSE)
{
	if($searchtype == "business")
	{
		$SQL_STRING = $SQL_STRING . "fid='4' AND value<>''";
	}
	else
	{
		$SQL_STRING = $SQL_STRING . "fid='4' AND value=''";
	}
	
}
else
{
	if($searchtype == "business")
	{
		$SQL_STRING = $SQL_STRING . " AND fid='4' AND value<>''";
	}
	else
	{
		$SQL_STRING = $SQL_STRING . " AND fid='4' AND value=''";
	}
}

$SQL_STRING = $SQL_STRING . " GROUP BY uid";
echo $SQL_STRING;
$result = mysql_query($SQL_STRING) or die(mysql_error());
while($results = mysql_fetch_object())
{
	$new_result = mysql_query("SELECT * FROM users WHERE uid='" . $results->uid . "'") or die(mysql_error());
	$new_result = mysql_fetch_object($new_result);
	echo '1' . $new_result->name;

}

Posted: Thu Aug 23, 2007 7:21 pm
by califdon
psychotomus wrote:SELECT * FROM profile_values WHERE fid='9' AND value='NY' AND fid='16' AND value='1' AND fid='17' AND value='1' AND fid='21' AND value='1' AND fid='19' AND value='1' AND fid='16' AND value='1' AND fid='18' AND value='1' AND fid='20' AND value='1' AND fid='4' AND value='' GROUP BY uid
What this says is "send me all records that have ALL THESE VALUES as stated. That cannot work, because no single record can have the fid='9' AND the fid='16', etc. You want something more like this:

Code: Select all

SELECT * FROM profile_values WHERE (fid='9' AND value='NY') OR (fid='17' AND value='1') OR ...

Posted: Thu Aug 23, 2007 7:37 pm
by psychotomus
Or statements wont work. i need the state associated with all other values. There has to be some way to do this.

Posted: Thu Aug 23, 2007 10:51 pm
by John Cartwright
I'm not exactly sure what you mean..

Can you explain in plain english what you want your query to accomplish?

Moved to Databases.

Posted: Thu Aug 23, 2007 11:18 pm
by psychotomus
I allready tried explaining the best I can =(

Posted: Fri Aug 24, 2007 9:39 am
by John Cartwright
psychotomus wrote:I allready tried explaining the best I can =(
then I've helped you the best I can.. why not try explaining again?

Posted: Fri Aug 24, 2007 10:02 am
by feyd
Proper grouping with parentheses and the AND pairs with OR connecting those groups will work more than likely.. unless your grouping is different than it appears.. in which case IN(...) may be more appropriate.