SQL: Select * from

Question

i hava a query of the following form

select * from tablename where id= $var

now sometimes i need to select a recod with a particular id ,and sometimes i need to select recore with any id

so i intent to something of follwoing form

$v = $_GET['id'];
if($v== -1)
   $var = '*' //here i want to select all recods
else
   $var = $v  //select record recod with specifinc id

//execure the SQL query now

select * from tablename where id= $var

but SQL dont support it when $var = *

How can this problem be solved by using the above technique NOTE: Actial scenirio is very different and complex so i have just give a simple eg for others to get to know my requiremnt I know that the above thing can be handled in many diffret appooaches But i want to solve it in the above descibed approach

Answer 1

or just don't use any condition, e.g.:

select * from tablename

Answer 2

$v = $_GET['id];
if($v== -1)
   $var = '' //here i want to select all recods
else
   #var = 'WHERE id= ' + $v  //select record recod with specifinc id

//execure the SQL query now

select * from tablename $var

Answer 3

You could always set the variable to NULL.

SELECT * FROM table WHERE (ID = @Var or @Var IS NULL)

FYI, in SQL don't compare something to null using x=NULL since Null has no value, it cannot equal anything.

Answer 4

You need to construct your sql string depending on the value of V.

If V = *

select * from table

if V != *

select * fronm table WHERE id = $v

Answer 5

$v = $_GET['id];
if($v== -1)
   $var = 'id' //here i want to select all recods
else
   #var = $v  //select record recod with specifinc id

//execure the SQL query now
select * from tablename where id= $var

The query would look like:

SELECT * FROM tablename WHERE id = id

Answer 6

You can't do this the way you are trying to approach it. The asterisk is a wildcard, but it doesn't apply in comparisons in where clauses. You should use one of the approaches listed here.

Answer 7

select * from tablename where (id= $var) or ($var='*')

($var='*') evaluates to true for every row, thus it would select all rows. Or it will evaluate to false, in which case only those records are selected where (id = $var).

For those who don't get it, I'm not comparing to * but to a string constant. I could have used any value instead of '*' but this just looks more clear. I could also use:

select * from tablename where (id= $var) or ($var=-1)

Which basically has the same effect. Either the first or the second condition evaluates to true. If you want all records, just make sure the second validation is true by assigning '*' or -1 to $var, depending on the piece of code you use...

And since some people still don't understand why this works, here's what the query translates to when $var gets the value '*':

select * from tablename where (id= '*') or ('*'='*')

Because the second comparison translates to true, all rows result in true for this where clause.

And if $var has the value 20, it translates to:

select * from tablename where (id= '20') or ('20'='*')

And in this case, the latter comparison translates to false, but the first one will be true for all records where id equals 20.

Answer 8

You could use

if(isset($_GET['id'])){
   $v = $_GET['id'];
   $sql_statement = "select * from tablename where id=".mysql_real_escape_string($v);
} else {
   $sql_statement = "select * from tablename";
}
// then do your sql business here

Answer 9

The * should never be used when writing SQL. Make certain you list out all column names that are needed. Even if you need 50 column names list them out.

Using SELECT * is bad practice imho. Don't be lazy write clean code!

Answer 10

It seems to me the easiest solution would be:

$v = $_GET['id'];
$query = 'select * from tablename';

if( is_numeric($v) && ($v>0) )
   $query .= ' where id=' . $v;

//execute the query

Just add the WHERE clause when necessary.

If the input is not expected to be numeric you will need to escape it or - even better - use a prepared statement (mysqli or pdo).