SQL statement not working

pinehead18

Ok, i got a little help rigging up this conditional sql statement. However when i echo the $sql; it will not echo anythign and nothing actually works. I would appriciate any help that can be provided.

   $con = mysql_connect("localhost","pinehead","") or die(mysql_error());
        $db = mysql_select_db("pinehead",$con) or die(mysql_error());
       $sql = "SELECT * FROM users WHERE user='$uname'";


    if($agemin == "") {
     $sql .= "AND age > 18";
    }
    if($agemax == "") {
    $sql .= "AND age < 80";
    }
    if(isset($_POST['uname'])) {
     $sql .= "AND user LIKE '%$uname%'";  
    }
    if(isset($_POST['location'])) {
    $sql .= "AND location LIKE '%$location%";
    }
    if($sex == "any") {
    $sql .= "AND (sex='male' OR big='female')";


$sql .= "ORDER BY create_date";

    echo $sql;
    print $sql;
    $result = mysql_query($sql,$con);
    while ($row = mysql_fetch_array($result)) {
stuff i don't need to post here
}

Thank you
- Anthony

paulnaj

See how you get on with this ...

//$con = mysql_connect("localhost", "pinehead", "") or die(mysql_error()); 
//$db = mysql_select_db("pinehead", $con) or die(mysql_error());

if(isset($_POST['uname'])) {
  $sql = "SELECT * FROM users WHERE user='".$_POST['uname']."'"; 

  if(!isset($_POST['agemin'])) { 
    $sql .= " AND age > 18"; 
  } 
  if(!isset($_POST['agemax'])) { 
    $sql .= " AND age < 80"; 
  } 
  if(isset($_POST['location'])) { 
    $sql .= " AND location LIKE '%.".$_POST['location']."%'"; 
  } 
  if(!isset($_POST['sex']) || $_POST['sex'] == "any") { 
    $sql .= " AND (sex='male' OR sex='female')"; 
  }
  $sql .= " ORDER BY create_date"; 

} else {

$sql = "Error: uname must be set!";

}

echo $sql; 

//$result = mysql_query($sql,$con); 
//while ($row = mysql_fetch_array($result)) { 
// ... other stuff here
//}

1) I assumed that the whole things depends on $_POST['uname'] being passed to the page ... everything else optional.

2) Newer versions of PHP don't allow using $uname for $_POST['uname'] unless you've specifically configured that way. It's much easier to debug, anyway.

3) Never embed PHP variables in strings ...
Don't do: "My name is $name";
Do: "My name is ".$name;
... it's faster and easier to debug.

Come back if you need more help with the logic.

pinehead18

Pretty sure it is working now, Thank you

pinehead18

Hey, i have a slight problem. When i don't enter any data into the form. So that the search query posts everythign in the db. The uname and location still show up in the sql statement. Even though they are not set.

Any ideas?

paulnaj

That's odd!

Apart from the bit of code we've got here ... has $uname or $location already been used?

Because if I test it myself, I get the else-error statement.

Show me the string you get when you don't pass any variables.

pinehead18

SELECT * FROM users WHERE (id > 0) AND user LIKE '%%' AND age > AND age < AND location LIKE '%%' ORDER BY create_date

that is my sql statement when i pass no vars.

paulnaj

Hmmm ... that is odd ... it might be to do with the way isset() works.

Notice that in the first main if, I check just isset(), but in all the others I use !isset() (not isset).

Try this instead ...

if(!isset($_POST['uname'])) { 

  $sql = "Error: uname must be set!"; 

} else { 

  $sql = "SELECT * FROM users WHERE user='".$_POST['uname']."'";  

  if(!isset($_POST['agemin'])) {  

    $sql .= " AND age > 18";  

  }  

  if(!isset($_POST['agemax'])) {  

    $sql .= " AND age < 80";  

  }  

  if(isset($_POST['location'])) {  

    $sql .= " AND location LIKE '%.".$_POST['location']."%'";  

  }  

  if(!isset($_POST['sex']) || $_POST['sex'] == "any") {  

    $sql .= " AND (sex='male' OR sex='female')";  

  } 
  $sql .= " ORDER BY create_date";      

} 

echo $sql;

pinehead18

Doesn't that still leave the location part with an isset problem?

This is my edited code.. IF you want to take a look at it

      $sql = "SELECT * FROM users WHERE (id > 0)";

if(isset($_POST['uname'])) {
  $sql .= " AND user LIKE '%".$_POST['uname']."%'";

  if(!isset($_POST['agemin'])) {
    $sql .= " AND age > 18";
  }
  if(!isset($_POST['agemax'])) { 
    $sql .= " AND age < 80";
  }
  if (isset($_POST['agemin'])) {
    $sql .= " AND age > $agemin";
 }
  if (isset($_POST['agemax'])) {
    $sql .= " AND age < $agemax";
 }
  if(isset($_POST['location'])) {
    $sql .= " AND location LIKE '%".$_POST['location']."%'";
  }
  if(!isset($_POST['sex']) || $_POST['sex'] == "any") {
    $sql .= " AND (sex='male' OR sex='female')";
  }
  $sql .= " ORDER BY create_date";

} else {

$sql = "Error: uname must be set!";

}

echo $sql;

paulnaj

Hang on a minute .. that is really odd!

Look at the SQL statement you gave:

SELECT * FROM users WHERE (id > 0) AND user LIKE '%%' AND age > AND age < AND location LIKE '%%' ORDER BY create_date

Where did that 'id' in the WHERE clause come from?

pinehead18

Well i dont' want the where colum to entail uname becuase if the person chooses not to enter a username then it will not work correctly right? So for the where statement i just said where id > 1 and the id field in the db is where i got id.

paulnaj

Ok ... we're crossing over here.

Now I know where the id came from.

The thing is ... if you make changes to the script and don't tell us, then when you report errors back, we'll have no idea ahat's going on.

As I understand it ... correct me if I'm wrong ... uname is now optional (this changed!) and a search will ALWAYS be returned(this changed!).
Also you've added conditions to the age bit as well!!!

I'm gonna have to look at this later I'm afraid.

pinehead18

Yes that is correct. I apologize i didn't think those changes where to big to notify you about. I should have.

Take your time. I would appriciate any help you can give me on this.

Thank you
Anthony

paulnaj

Hi Anthony,

Ok below is my go ... seems to work fine for me.

It seems like you are really having major trouble understanding how to break this problem into logical steps. Is that the case? If so, I would have a look at some tutorials on program flow and conditional statements. It's unlikely you'll be able to adapt scripts without this under your belt.

Also, look at the lines you added for $agemax and $agemin ...

2) Newer versions of PHP don't allow using $uname for $_POST['uname'] unless you've specifically configured that way. It's much easier to debug, anyway.

3) Never embed PHP variables in strings ...
Don't do: "My name is $name";
Do: "My name is ".$name;
... it's faster and easier to debug.

$sql = "SELECT * FROM users WHERE (id > 0)"; 


if(isset($_POST['uname'])){
  $sql .= " AND user LIKE '%".$_POST['uname']."%'";
}

if(isset($_POST['agemin'])){
    $sql .= " AND age > ".$_POST['agemin'];
} else {
    $sql .= " AND age > 18";
}

if(isset($_POST['agemax'])){
	$sql .= " AND age < ".$_POST['agemax'];
} else {
	$sql .= " AND age < 80";
}

if(isset($_POST['location'])){
	$sql .= " AND location LIKE '%".$_POST['location']."%'";
}

if(isset($_POST['sex'])){
	if($_POST['sex'] == "any"){
		$sql .= " AND (sex='male' OR sex='female')";
	} else {
		$sql .= " AND sex='".$_POST['sex']."'";
	}
} else {
	$sql .= " AND (sex='male' OR sex='female')";
}

$sql .= " ORDER BY create_date";

echo $sql;

pinehead18

Thank you for all your help, However, with your new code i seem to still be getting the following sql statement

SELECT * FROM users WHERE (id > 0) AND user LIKE '%%' AND age > AND age < AND location LIKE '%%' AND sex='' ORDER BY create_date

Its adding the user and location and sex and age even though i left them blank.

I havn't changed the code from what you posted. Used exactly the same thing. Maby i missed something?

pinehead18

BTW where would i find that tutorial on what you were reffering to.. Would never hert to read up some more. I hate being a pest 🙁

paulnaj

Tell you what ... why don't you show me the form you are using to send the variables to the page.

Also, try to find out what version of PHP you're using and let me know.

I'll have a look for those tuts for you.

* EDIT **

You are not being a pest at all!