How to select all the values in the fiend using WHERE in the MySql Query

PHP
1

NEED:

I need to select all the value in the field of table using MySql query when the condition is true.

Code:

<?php 
include "db.php";
$type1=$_POST['type'];
if($type1=='all')
{
   $type='*';
}
else
{
$type=$type1;
}
$selecttype=mysqli_query($con, "SELECT * FROM product where type='$type'");
?>

But when i use the above code the query is not success. What is the solution of this?

2

That code is wide open to SQL Injection attack as it lets uder-submitted data near the database without sanitizing it. All user submitted should be sanitized and when using it in a query you should be using prepared statements which eliminate the risk of SQL Injection attack

3

then I guess there is no type *; print the SQL query and test it in a mysql client (such as phpMyAdmin or MySQL Workbench) directly.

1 Like
4

I know the * is not correct. I the need correct code to select all the values in the field when the condition is true.

5

if you want all types then don’t add a WHERE condition.

6

The WHERE Condition is necessary when the else part is executed.

7

that is correct. but I don’t see why you can’t omit it in the if part.

8 
$type1=$_POST['type'];
if($type1=='all')
{
   $type='*';
}
else
{
$type=$type1;
}
$selecttype=mysqli_query($con, "SELECT * FROM product where type='$type'");

=>

$type1 = $_POST['type'] /// SANITIZE THIS LINE.
$type = ($type1 == 'all') ? "" : " WHERE type = '".$type1."'";
$select = mysqli_query($con,"SELECT * FROM product".$type);
9 
$stmt = mysqli_prepare($mysqli, "SELECT * FROM product WHERE type = ?");


$stmt->bind_param( "s", $type); // If you were searching for more than one the there would be multiple "s" and variables, "s" means strings, i = integer, and so forth

$stmt->execute();

$stmt->bind_result($col1, $col2); // Depends on how many columns you want to fetch, better if you pulling specific column(s):
// then fetch and close the statement

BTW I don’t know if this will work for it’s untested and I normally use PDO

10

with that there is still the problem that a lookup for all types should be done without the WHERE condition.

11

Might it be better to put together a $where only when needed? eg.

$where = "";
if (...){
$where = "WHERE field = '" . $value . "'";
}

else maybe have 2 query statements in an if - else

Personally I prefer the first approach because it lets be do things like
$where .= " AND another_field = '" . $another_value . "'";

12

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.