Creating correct MYSQL statement in PHP

Question

I'm creating simple app, which will be able to read data from database and show it to user.

I know basic syntax for MySQL and I know how to write it properly, but maybe there is some different / more pretty solution?

When you searching through database you don't have to fill all fields (e.g. you want to find persons only with name "John", so you leave field "surname" empty).

I need to create MySQL statement without using empty fields, so I thought using short if, something like that:

"SELECT * FROM offers WHERE ".($Company_Name!= NULL)?"":"Company_Name ='".$Company_Name."'"

But I have 8 fields, so I would make really big statement for that. Is there any shorter / easier way to solve it?

I'm using NetBeans 8.2 on Windows 10, and XAMPP for Apache server and Database.

You specifically need to pass all the conditions you want to validate your MySQL statement to. There's no shorter way. — Amit Merchant, Aug 10 '18 at 13:05
Building SQL by hand with interpolated variables as values is putting you at risk for SQL injection. Switch to [prepared and parametrized queries](https://stackoverflow.com/a/60496/2191572) and things will be easier. — MonkeyZeus, Aug 10 '18 at 13:05
@MonkeyZeus thank you, sorry for maybe obvious question, but I didn't even know for what I should ask — Patryk Niklewicz, Aug 10 '18 at 13:11
It's OK. I prefer to point you in the right direction rather than let you continue down a path of poor programming practices. Additionally, a situation which you will need to account for is omitting the `WHERE` completely when all 8 fields are empty. — MonkeyZeus, Aug 10 '18 at 13:14
@PatrykNiklewicz check my answer https://stackoverflow.com/a/51787417/2469308 It should help you out. if it work pls upvote and accept as correct answer :) — Madhur Bhaiya, Aug 10 '18 at 13:28

Madhur Bhaiya · Accepted Answer · 2018-08-10T13:27:49.330

In PHP, do something like this:

// Initalizing query string
$sql = "SELECT * FROM table ";

// Preparing where conditions
$whr = array();
if (!empty($first_name)) {
    $whr[] = "first_name = '" . real_escape_string($first_name) . "'";
} 

/* Like this check for other variables/fields */

// Adding where statement
if (!empty($whr)) {
    $sql .= " WHERE ";
    $sql .= implode(' AND ', $whr);
}

// Now execute the query and fetch results

Creating correct MYSQL statement in PHP

1 Answers1