0

new to php and am enrolled on a course, so can ask tutor tomorrow if this is more complicated than i think it might be!

I have an sql query, and it works fine. But I am trying to add and 'and' in the select statement.

This is what I have at the minute

$query = "SELECT * from table1 where table1.age <= " . $_POST['min_age'] ;

I have a 'region' input on my linked html page and want results to be returned only if the min_age and region values match those inputted by the user.

I have tried adding an 'and where' but it doesn't work and I am not sure if it is because of the multiple "'s or if what I am trying to do needs a different method?

Thanks

4
  • it should not be "and where". Can you post the current query that you have (with multiple wheres or something like that ) ? Commented Oct 23, 2013 at 18:38
  • WHERE column=stuff adn someothercolumn=stuff Commented Oct 23, 2013 at 18:38
  • You only need to use 'where' once in a query. WHERE x=y AND x>4 Commented Oct 23, 2013 at 18:39
  • ok thanks but I tried that too - so would I need to start with " again after [min_age] and before the and? Commented Oct 23, 2013 at 19:11

1 Answer 1

2

If you need multiple conditions, just separate them with AND:

... WHERE table1.age <= ? AND table1.region = ?

No need to use WHERE again. Just like you wouldn't need to use if() more than once if you were writing a complex condition in PHP.


PS: This isn't directly related to your question, but you should get into the habit of not putting $_POST or $_GET variables directly into your SQL queries. It's a good way to get hacked! Ask your tutor about "SQL injection," or read my presentation SQL Injection Myths and Fallacies.

I know you're just starting out, but if you were training to be an electrician, you would place a high priority on learning how to avoid being electrocuted or how to avoid causing a fire.

Here's how I would write your query using mysqli. One advantage of using query parameters is you never need to worry about where you start and end your quotes.

$query = "SELECT * from table1 where table1.age <= ? AND table1.region = ?";
$stmt = $mysqli->prepare($query) or trigger_error($mysqli->error, E_USER_ERROR);

$stmt->bind_param("is", $_POST["min_age"], $_POST["region"]);
$stmt->execute() or trigger_error($stmt->error, E_USER_ERROR);

The other good habit I'm showing here is to always report if prepare() or execute() return an error.


If you must interpolate variables into your SQL, first make sure you protect the variables either by coercing the value to an integer, or else by using a proper escaping function like mysqli_real_escape_string(). Don't put $_POST variables directly into the string. Also you don't have to stop and restart the quotes if you use PHP's syntax for embedding variables directly in double-quoted strings:

$age = (int) $_POST["min_age"];
$region = $mysqli->real_escape_string($_POST["region"]);
$query = "SELECT * from table1 where table1.age <= {$age} 
    AND table1.region = '{$region}'";
Sign up to request clarification or add additional context in comments.

2 Comments

ok thanks but I tried that too - so would I need to start with " again after [min_age] and before the and?
ok brilliant thanks i will ask about that! For now though (as this is only a test iam doing, it wont go live) how would i add the and without "'s interfering with each other?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.