Gathering info from SQL but without same field name

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
psychotomus
Forum Contributor
Posts: 487
Joined: Fri Jul 11, 2003 1:59 am

Gathering info from SQL but without same field name

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

GROUP BY...
psychotomus
Forum Contributor
Posts: 487
Joined: Fri Jul 11, 2003 1:59 am

Post 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;

}
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post 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 ...
psychotomus
Forum Contributor
Posts: 487
Joined: Fri Jul 11, 2003 1:59 am

Post by psychotomus »

Or statements wont work. i need the state associated with all other values. There has to be some way to do this.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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.
psychotomus
Forum Contributor
Posts: 487
Joined: Fri Jul 11, 2003 1:59 am

Post by psychotomus »

I allready tried explaining the best I can =(
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
Post Reply