MySql query on different input fields where some of them are NULL

Question

Hello I have 3 fields on input form which are set via POST method to external php

 $id=$_POST['id']; 
 $nombre=$_POST['nombre']; 
 $cedula=$_POST['cedula'];

where I would like to make a search option depending on which field have data inside it or if a user put data in all 3 or in only 2 fields to search from the input fields which are not NULL fields in the same table where there is a result.

my sql query is something like that $sql = "SELECT * FROM users WHERE userID = $id AND nombre = $nombre AND cedula = $cedula) ";

obviosly which is not working, what should I do to make it work. Do I need to change only the query or I need to put something before it to check first what is not NULL. Thanks

what you are storing in `$pass` ?? where you have declared it? — Sumit Bijvani, Oct 05 '13 at 18:26
Really, you should stop for a moment, take a few steps back, and start learning to use a modern MySQL API which supports parameterized queries, such as PDO or MySQLi. Assuming this is bound for `mysql_query()`, it is vulernable to SQL injection and the API has been deprecated. — Michael Berkowski, Oct 05 '13 at 18:26
When bound as parameters, even if null it becomes a non-query-breaking scenario (whether or not you get the intended results). — Michael Berkowski, Oct 05 '13 at 18:27
Start here: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Michael Berkowski, Oct 05 '13 at 18:27
all that code is inside members area which is protected by server password. regarding the sql injection — John Siniger, Oct 05 '13 at 18:49

score 0 · Answer 1 · answered Oct 05 '13 at 18:26

0

Try to add quote:

$sql = "SELECT * FROM users WHERE userID = ".$id." AND nombre = ".$nombre." AND pass = '".$pass."' ";

answered Oct 05 '13 at 18:26

Adam

1,371
2
11
12

Pupil · Accepted Answer · 2013-10-05T18:40:15.627

0

Firstly, your SQL statement should be updated to have enclosed ' (commas) around string values.

So, modify it to:

$sql = "SELECT * FROM users WHERE userID = '$id' AND nombre = '$nombre' AND pass = '$pass'";
// ----------------------------------------^---^--------------^-------^------------^-----^

Second thing is that you should search a field only when it has a value otherwise, it of no use.

So, your modified PHP code should be:

$sql = "SELECT * FROM users WHERE 1 ";

if (!empty($id)) {
  $sql .= " AND userID = '$id' ";
}
if (!empty($nombre)) {
  $sql .= " AND nombre= '$nombre' ";
}
if (!empty($pass)) {
  $sql .= " AND pass= '$pass' ";
}

And your Database will be searched for the fields only if they have data filled in the form.

edited Oct 05 '13 at 18:40

answered Oct 05 '13 at 18:27

Pupil

23,834
6
44
66

hi one question what is the logic in this WHERE 1 = what is 1 ? – John Siniger Oct 05 '13 at 18:43
We are adding conditions only when there is some value posted. Say, if you did not post any value, then we need to select all the records from the DB table. "WHERE 1" does not make any error and it same as "SELECT * FROM tbl". – Pupil Oct 05 '13 at 18:46

score 0 · Answer 3 · answered Oct 05 '13 at 18:29

0

Yes, you will need to put a check before which will ignore the fields which are null.

Also, you would need to put the $variable inside single quotes ' if they are VARCHAR or CHAR types.

answered Oct 05 '13 at 18:29

neeagl

348
1
13

MySql query on different input fields where some of them are NULL

3 Answers3