Jesse Pfieffer Jesse Pfieffer - 2 months ago 8
MySQL Question

Is there a symbol in SQL to return "All"

I am trying to create a search code for my program I m writing in PHP and I am trying to figure out a good way to make my code more compact. I learned PHP from watching YouTube so give me a break ;). Here is what i started with:

if (empty($_POST['nick-cc'])) {
$sql = "SELECT * FROM pcr WHERE student = $user->id";
} else {
$sql = "SELECT * FROM pcr WHERE student = $user->id AND cc = '%$nccn%'";
}


The "if" statement would have to be repeated for each column in my database. So I got to thinking, "why can't I use the "*" from the beginning of the SQL in the field to show that if there is no search typed in that it will just return everything instead. That way if someone comes to the page or just doesn't type anything in and clicks "search" that it will still search but it will search for * (all) but if they do type something in then it will search for that instead. So this is what I came up with. But needless to say is not working for me. Is this a wild goose chase? Is there a better way?

if (empty($_POST['nick-cc'])) {
$nccn = '*';
} else {
$nccn = ''.$_POST['nick-cc'].'';
}
$sql = "SELECT * FROM pcr WHERE student = $user->id AND cc = '%$nccn%'";

Answer

I don't really see any reason to make your search more complicated. I usually just append the LIKE clause like so:

$sql = "SELECT * FROM pcr WHERE student = $user->id";

if($_POST['nick-cc'] !== ''){
    $sql .= " AND cc = '%$nccn%'";
}

More importantly you are vulnerable to sql injection using this method. I would advise you to research parameterized queries and prepared statements. Below is a simple code example using the built in Mysqli class.

$sql = 'SELECT * FROM pcr WHERE student = ?';
$mysqli = new Mysqli($host, $username, $password, $database);
if($_POST['nick-cc'] === ''){
    $mysqli->prepare($sql);
    $mysqli->bind_param('i', $user->id);
}else{
    $sql .= ' AND cc = ?';
    $mysqli->prepare($sql);
    $mysqli->bind_param('is', $user->id, '%' . $_POST['nick-cc'] . '%');
}

$mysqli->execute(); // returns true or false
$mysqli->fetch_array(); // return the results