Query Help

[CFROG]

This seemed like a good idea to me but it's not working out the way that I want ... I'm probably over-complicating it 8-) I'm trying to allow users to search for other users in their area and part of the search criteria gives you 8 different options to choose from. They can choose 1 or all 8 if they want to. Here is how I set it up.

$looking_for = " ".$_GET['lf1']." ".$_GET['lf2']." ".$_GET['lf3']." ".$_GET['lf4']." ".$_GET['lf5']." ".$_GET['lf6']." ".$_GET['lf7']." ".$_GET['lf8']."";

if($looking_for =='Any'){
	$pag="SELECT * FROM users WHERE zip IN ({$zcodes}) AND  gender='$gender' AND age BETWEEN '$min_age' and '$max_age'";} else{

$pag="SELECT u.*, up.* FROM users AS u INNER JOIN user_profile AS up ON u.id = up.user_id WHERE u.zip IN ({$zcodes}) AND  u.gender='$gender' AND u.age BETWEEN '$min_age' and '$max_age' AND up.lf1 OR up.lf2 OR up.lf3 OR up.lf4 OR up.lf5 OR up.lf6 OR up.lf7 IN ({$looking_for}) ;}

When I echo $looking_for and even though it's value is 'Any' it still skips my if statement and gives me an unknown column in my where clause which would be ({$looking_for}) It seemed like it should work. Am I trying to make it do something that is just never gonna happen?

[CFROG]

if($_GET['lf1']=='Any'){ $looking_for = 'Any';} else {
$lk_for = array(" ".$_GET['lf2']." ".$_GET['lf3']." ".$_GET['lf4']." ".$_GET['lf5']." ".$_GET['lf6']." ".$_GET['lf7']." ".$_GET['lf8']."");
$looking_for = implode(",", $lk_for);}

That took care of it skipping the if statement, but no matter how many different ways I write the query it still sees ({$looking_for}) as a field when it is an array. I'm overlooking something simple here, I know it.

[fwdean]

I think you have a few other problems and the query isn't going to scale well, however the code below should fix your array() problems.

<?php
$terms = array();
for ($x = 1; $x < 9; $x++)
{
    $searchTerm = "lf" . $x;
    if (isset($_GET[$searchTerm]) && $_GET[$searchTerm] != '')
    {
        $terms[] = $_GET[$searchTerm];
    }
}
if (in_array("Any",$terms))
{
    $page="SELECT * FROM users WHERE zip IN ({$zcodes}) AND  gender='$gender' AND age BETWEEN '$min_age' and '$max_age'";
}
else
{
    $looking_for = implode(",", $terms);
    $page="SELECT u.*, up.* FROM users AS u INNER JOIN user_profile AS up ON u.id = up.user_id WHERE u.zip IN ({$zcodes}) AND  u.gender='$gender' AND u.age BETWEEN '$min_age' and '$max_age' AND up.lf1 OR up.lf2 OR up.lf3 OR up.lf4 OR up.lf5 OR up.lf6 OR up.lf7 IN ({$looking_for})";
}
?>

[CFROG]

A few other problems? LOL, boy let me tell ya' ! I've been learning a lot, I just think I get too far ahead of myself sometimes. What do you mean when say the query isn't going to scale well? I've been playing around and trying to add quotes to everything like the 101 $_GETS I have there so they are interpreted as a string. Heck, I dunno, If I did I wouldn't be having this issue! 8-)

[CFROG]

I tried your alternative for the array, it seemed a lot more reasonable than what I had going on, now it just tells me that the query was empty.

[fwdean]

Well, the real issue is the query. It is not going to do what you think it will.

$page="SELECT u.*, up.* FROM users AS u INNER JOIN user_profile AS up ON u.id = up.user_id WHERE u.zip IN ({$zcodes}) AND  u.gender='$gender' AND u.age BETWEEN '$min_age' and '$max_age' AND up.lf1 OR up.lf2 OR up.lf3 OR up.lf4 OR up.lf5 OR up.lf6 OR up.lf7 IN ({$looking_for})";

The above will NOT check if ANY or ALL of the lf(1-6) work for the array. It is only checking if those exist all all (evaluate to TRUE) OR up.lf7 IN (array)

You didn't mention what the values of any of the

$_GET['lf']

could be, but you'd probably be better off checking for the existance of each lf element and dynamically creating the mysql query like the following example.

$query="SELECT u.*, up.* FROM users AS u INNER JOIN user_profile AS up ON u.id = up.user_id WHERE u.zip IN ({$zcodes}) AND  u.gender='$gender' AND u.age BETWEEN '$min_age' and '$max_age'";
if (isset($_GET['lf2']) && $_GET['lf2'] != '')
{
    $value = $_GET['lf2']; // other validation for what value you want goes here
    $query .= " AND up.lf2 = " . $value;
}
if (isset($_GET['lf3']) && $_GET['lf3'] != '')
{    $value = $_GET['lf3']; // other validation for what value you want goes here
    $query .= " AND up.lf3 = " . $value;
}
echo $query . "\n";

[CFROG]

Welcome to my experience with php, it never does what I think it will and i try not look surprised when it does, lol. I've only used IN() a few times and never really thought it may only be checking for up.lf7 in the array. Makes sense now that you mention it. The dynamic query is really cool (doesn't take much to amuse me), I was wondering if it was possible but never really looked into it because I was already having problems from biting off more than I could chew. One thing at a time you know.

The values of all the $_GET are thing like 'Friends', 'networking', etc.

[CFROG]

Okay, I really like with what you did with the dynamic query, I'll definitely use that again in the future. I implemented it and ironed out all the wrinkles (mine, not yours). I'm pretty much back to where I started from. If I choose 'Any' for the search criteria the code does exactly what it is supposed to. If I choose just one search term (besides 'Any') I get the familiar error of "unknown column 'example' in 'where clause'. If I choose multiple search criteria it gives me a syntax error for anything after the second statement. Example:

$query="SELECT u.*, up.* FROM users AS u INNER JOIN user_profile AS up ON u.id = up.user_id WHERE u.zip IN ({$zcodes}) AND  u.gender='$gender' AND u.age BETWEEN '$min_age' and '$max_age'";

if (isset($_GET['lf2']) && $_GET['lf2'] != '')
{
    $value = $_GET['lf2'];
    $query .= " AND up.lf1 = " . $value;
}
if (isset($_GET['lf3']) && $_GET['lf3'] != '')
{    $value = $_GET['lf3']; 
    $query .= " AND up.lf2 = " . $value;
}
if (isset($_GET['lf4']) && $_GET['lf4'] != '')
{
    $value = $_GET['lf4']; // <--- It errors out at this point
    $query .= " AND up.lf3 = " . $value;
}
if (isset($_GET['lf5']) && $_GET['lf5'] != '')
{    $value = $_GET['lf5']; 
    $query .= " AND up.lf4 = " . $value;
}
if (isset($_GET['lf6']) && $_GET['lf6'] != '')
{
    $value = $_GET['lf6']; 
    $query .= " AND up.lf5 = " . $value;
}
if (isset($_GET['lf7']) && $_GET['lf7'] != '')
{    $value = $_GET['lf7']; 
    $query .= " AND up.lf6 = " . $value;
}
if (isset($_GET['lf8']) && $_GET['lf8'] != '')
{    $value = $_GET['lf8']; 
    $query .= " AND up.lf7 = " . $value;

[CFROG]

I'm just throwing this out there ... if I take the following snippet of code:

if (isset($_GET['lf2']) && $_GET['lf2'] != '')
{
    $value = $_GET['lf2']; 
    $query .= " AND up.lf1 = " . $value;
}

and i put tick marks around up.lf1 or change up.lf1 to user_profile.lf1 both tell me it is an unknown column. Could it be that the code is not recognizing any of my columns in the user_profile table and just skipping over it and interpreting $value as the column name? If this is the case, is there something wrong with my JOIN?

[fwdean]

Are you saying that the field name in your table is not lf1? It is really up.friends and up.networking?

[CFROG]

No, lf1, lf2, lf3 etc. up to lf7 are the field names. Friends, Networking, etc. is the search criteria.

[fwdean]

if (isset($_GET['lf2']) && $_GET['lf2'] != '')
{
    $value = $_GET['lf2']; // other validation for what value you want goes here
    $query .= " AND up.lf2 = '" . addslashes($value) . "'";
}

add an echo and die() before the syntax error so we can see what $query contains. Please post that variable as well as the table schema for both tables.

[CFROG]

You want me to echo $query?

Buy the way, inserting the addslashes for all of the $values took care of the syntax errors. Besides whitespace there are no special characters in the criteria, or is whitespace enough to throw it off.

It's not giving me a result when it should be, I'll have to work that one out.

[fwdean]

Whitespace won't throw it off, but echoing the $query variable as well as having the table schema, we should be able to see if you are looking for AND or OR in your multiple search criteria.

[CFROG]

Here is the $sql given two search criteria entered (I echoed at the end of the query since it don't error out anymore):

SELECT u.*, up.* FROM users AS u INNER JOIN user_profile AS up ON u.id = up.user_id WHERE u.zip IN (18017,18020,18025,18016,18018,18015,18055) AND u.gender='Male' AND u.age BETWEEN '18' and '99' AND up.lf1 = 'Friends' AND up.lf2 ='Dating'

Looking for the schema now.

Everything seems to work, just need to figure out why I'm not getting a result.

[CFROG]

TABLE NAME: user_profile
TABLE_TYPE: BASE TABLE
ENGINE: MyISAM
VERSION: 10
ROW_FORMAT: Dynamic
TABLE_ROWS: 9
AVERAGE_ROW_LENGTH: 156
DATA_LENGTH: 1580
MAX_DATA_LENGTH: 281474976710655
INDEX_LENGTH: 8192
DATA_FREE: 176
AUTO_INCREMENT: 25
TABLE_COLLATION: latin1_swedish_ci

[CFROG]

Odd, I should most definitely be getting a result here. I get one when I use 'Any' because it executes a different query under that condition.

[CFROG]

okay, here is another question. With my result set I order by first_name ASC and use a paginate.class to limit my results per page by inserting $pages->limit in my query. Where do I put this now that I've broken the query down to multiple if statements?

[CFROG]

Brainfart, LOL ... I worked that part out.

if (isset($_GET['lf8']) && $_GET['lf8'] != '')
{    $value = $_GET['lf8'];
    $query .= " AND up.lf7 ='" .addslashes($value)."'";
}
$query.= " ORDER BY u.first_name ASC $pages->limit";
}

[CFROG]

Narrowed down the problem, just not sure why it's doing what it is. I'm posting the entire query below including the html output. When the first query executes there is no problem and the code runs through from top to bottom. When the second query executes the while loop does not execute, or the result set is empty and there is nothing to loop through. I can't echo anything withing the loop, not just the variables. If I echo the variables outside the loop they come up empty. Here is the whole code:

<?php 


$zips = $z->get_zips_in_range("".$my_zip."", "".$distance."", _ZIPS_SORT_BY_DISTANCE_ASC, true); 

$zipcodes = array_keys($zips); 
$zcodes = implode(",",$zipcodes);
  
  $terms = array();
for ($x = 1; $x < 9; $x++)
{
    $searchTerm = "lf" . $x;
    if (isset($_GET[$searchTerm]) && $_GET[$searchTerm] != '')
    {
        $terms[] = $_GET[$searchTerm];
    }
}
if (in_array("Any",$terms))
{
    $query="SELECT * FROM users WHERE zip IN ({$zcodes}) AND  gender='$gender' AND age BETWEEN '$min_age' and '$max_age' ORDER BY first_name ASC $pages->limit";
}
else
{	
   $query="SELECT u.*, up.* FROM users AS u INNER JOIN user_profile AS up ON u.id = up.user_id WHERE u.zip IN ({$zcodes}) AND  u.gender='$gender' AND u.age BETWEEN '$min_age' and '$max_age'";
if (isset($_GET['lf2']) && $_GET['lf2'] != '')
{
    $value = $_GET['lf2']; 
    $query .= " AND up.lf1 = '" .addslashes($value)."'";
}
if (isset($_GET['lf3']) && $_GET['lf3'] != '')
{    $value = $_GET['lf3'];
    $query .= " AND up.lf2 ='" .addslashes($value)."'";
}
if (isset($_GET['lf4']) && $_GET['lf4'] != '')
{
    $value = $_GET['lf4']; 
    $query .= " AND up.lf3 ='" .addslashes($value)."'";
}
if (isset($_GET['lf5']) && $_GET['lf5'] != '')
{    $value = $_GET['lf5']; 
    $query .= " AND up.lf4 = '" .addslashes($value)."'";
}
if (isset($_GET['lf6']) && $_GET['lf6'] != '')
{
    $value = $_GET['lf6']; 
    $query .= " AND up.lf5 = '" .addslashes($value)."'";
}
if (isset($_GET['lf7']) && $_GET['lf7'] != '')
{    $value = $_GET['lf7'];
    $query .= " AND up.lf6 = '" .addslashes($value)."'";
}
if (isset($_GET['lf8']) && $_GET['lf8'] != '')
{    $value = $_GET['lf8'];
    $query .= " AND up.lf7 ='" .addslashes($value)."'";
}
$query .= " ORDER BY u.first_name ASC $pages->limit";	
}

$res=mysql_query($query)or die(mysql_error()); 

$i = 0; 
$num_of_cols = 6; 
echo "<table cellspacing=\"10\" cellpadding=\"10\"><tr>"; 

while($row = mysql_fetch_array($res)) {  ///<---- The second query gives up at this point 
	$fname=ucwords($row['first_name']);
	$age=$row['age'];
	$id=$row['id']; 
	
	$sql1="SELECT prof_pic FROM user_profile WHERE user_id='$id'";
	$res1=mysql_query($sql1);
	$numr=mysql_num_rows($res1);
	while($r1=mysql_fetch_array($res1)){
		$pic=$r1['prof_pic'];
		if($pic == ''){ $pic = 'default.jpg';} 
		
		$on="SELECT user_id FROM users_online WHERE user_id='$id'";
		$ron=mysql_query($on);
		$rnum=mysql_num_rows($ron);
		if($rnum>0){ $online = "<font color=\"green\">Online now!</font>"; } else { $online = '<font color="red">Offline</font>';}
		
	if($numr == 0){ echo "<center><h3>Your search did not return any results</h3></center>";}
	
echo ($i!= 0 && $i%$num_of_cols == 0)?'</tr><tr>':''; 
echo "<td valign=\"bottom\" align=\"center\">";
echo "<a href=\"my_profile.php?id=".$id."\"><img src=\"../xpics/profiles/small/".$pic."\" border=\"none\"></a><br />";
echo "".$fname." <font color=\"blue\">".$age."</font><br />";
echo "".$online."";
echo"</td>"; 
$i++; }}
echo '</tr></table>';
?>

I originally thought it was a problem with the curly brackets but I've switched them all around to no avail.

[fwdean]

After Line 58, put

print_r($terms);
echo $query . "\n"
die();

Also, your $_GET variables are not lined up. I see you comparing lf1 to lf2 etc. Did you mean to do that?

Once you get the echo of $query, go to your database using the command-line client and enter that query, post the error message you get.

[CFROG]

Yes, I meant to do that. $_GET holds the value 'Any' and I don't store that value in the db.

Didn't get an error when I ran the query at the command line. 0 results. I'm looking at the table and I should be getting a result.

[CFROG]

Sorry, it actually did give me a result, the one that it should have. I don't usually use PHPMyAdmin and wasn't looking the right place.

[fwdean]

You said the second query gave up while reading the first row of the query. Did you run the script the second time and echo $query and run it from phpmyadmin?

[CFROG]

I don't know about it giving up at the 1st line, it just doesn't loop through the result set when I know there is a result there. Yes, I ran it the second time, echoed $query and ran that through phpmyadmin. I'll do it again just to be sure.

[CFROG]

Here is the query and phpmyadmin returns one result based on it.

SELECT u . * , up . * 
FROM users AS u
INNER JOIN user_profile AS up ON u.id = up.user_id
WHERE u.zip
IN ( 18017, 18020, 18025, 18016, 18018, 18015, 18055 ) 
AND u.gender =  'Male'
AND u.age
BETWEEN  '18'
AND  '99'
AND up.lf1 =  'Friends'
ORDER BY u.first_name ASC 
LIMIT 0 , 25

[fwdean]

If phpmyadmin showed one row, then what error message do you see when you execute the script. You said it stopped at

while ($row = mysql_fetch_array($res))

it should have at least run through that once. As a check you can put this around that loop

if (!mysql_num_rows($res))
{
    echo "Found no rows\n";
}
else
{
    while ...
}

[CFROG]

"Stopped" was a poor choice of words on my part. I'm sure it looped through at once, it just didn't return a result when it should have. There was no error.

I implemented the check and did not get a message that stated "Found no rows". I could understand if there was a result and it just isn't outputting the result correctly but why can I not echo anything whatsoever inside the loop?

[fwdean]

Well, if it didn't error, then you have at least one row of data in there that matches. Now before here

while($r1=mysql_fetch_array($res1))

echo $numr and $sql1 and then die(). Then run the $sql1 through phpmyadmin and make sure you get a row. $numr should tell you that you found at least one but it's good to check. After this, repeat the process just before the next mysql_fetch_assoc() to see which one of those looped queries is not executing correctly.

[CFROG]

There is no result for query

$sql1="SELECT prof_pic FROM user_profile WHERE user_id='$id'";

because it's looking for user_id # 16 and there is no users with that id. The id it should be looking for is 67. It's picking up the auto increment id from user_profile and not id from users where I thought it should be looking. Do I have something mixed up in my JOIN? Is it valid for me to specify my rows as such $row=['u.id']; ?