Gathering info from SQL but without same field name
Moderator: General Moderators
-
psychotomus
- Forum Contributor
- Posts: 487
- Joined: Fri Jul 11, 2003 1:59 am
Gathering info from SQL but without same field name
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?
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?
-
psychotomus
- Forum Contributor
- Posts: 487
- Joined: Fri Jul 11, 2003 1:59 am
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?
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;
}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: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
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
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
-
psychotomus
- Forum Contributor
- Posts: 487
- Joined: Fri Jul 11, 2003 1:59 am
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact: