PHP Availability"[]\ Query with mySQL

Question

This is my first PHP application I've ever made; I started learning PHP this week, so don't kill me, ok? ;)

I'm trying to use this script to find which DJs in our database are available. For some reason, when I manually enter the region like:

 'regions' LIKE \'tn_tricities\'

it returns results, but when I enter it like:

 $region = 'tn_tricities'
 ...
 `regions` LIKE \'$region\'

it doesn't return any results. Any idea how to fix this? or if I'm totally going in the wrong direction and this is not the best way to check availability, PLEASE let me know!

The full code:

 $region = $_GET['region'];
 $date = $_GET['date'];

 require "connect.php";
 echo $region;
 $sql = 'SELECT `vendorName`, `vendorBio`, `vendorType`, `regions` FROM
 `vendors` WHERE `regions` LIKE \'$region\' AND `datesBooked` NOT LIKE
 \'$date\'  AND `datesUnavailable` NOT LIKE \'$date\' ';

Answer 1

Try this one,

$region = $_GET['region'];
$date = $_GET['date'];

require "connect.php";
echo $region;

$sql = "SELECT `vendorName`, `vendorBio`, `vendorType`, `regions` FROM `vendors` WHERE `regions` LIKE '$region' AND `datesBooked` NOT LIKE '$date'  AND `datesUnavailable` NOT LIKE '$date' ";
$result = mysql_query($sql);

Hope this works.

Answer 2

You are using single quotes ('). Variables will not be parsed. Either bring your variables outside of your quotes, or use double quotes (").

$sql = 'SELECT `vendorName`, `vendorBio`, `vendorType`, `regions` FROM
 `vendors` WHERE `regions` LIKE \'' . $region . '\' AND `datesBooked` NOT LIKE
 \'' . $date . '\'  AND `datesUnavailable` NOT LIKE \'' . $date . '\' ';
// OR
$sql = "SELECT `vendorName`, `vendorBio`, `vendorType`, `regions` FROM
 `vendors` WHERE `regions` LIKE '$region' AND `datesBooked` NOT LIKE
 \'$date\'  AND `datesUnavailable` NOT LIKE '$date' ";

Also beware of SQL injections.

Answer 3

single quotation does not allow you to use variables inside. instead use double quotes:

$sql = "SELECT `vendorName`, `vendorBio`, `vendorType`, `regions` FROM
 `vendors` WHERE `regions` LIKE \'$region\' AND `datesBooked` NOT LIKE
 \'$date\'  AND `datesUnavailable` NOT LIKE \'$date\' ";

Answer 4

That's because of the difference between Single Quotes and Double Quotes.

Single Quotes do not "read" variables inside them and just print what you write. Double Quotes instead read the variable value and replace the string you've wrote with them.

Little example:

$var = "Hello";

echo "$var";
//prints Hello
echo '$var';
// prints $var

So simply enclose your query in double quotes and everything should work fine:

$sql = "SELECT `vendorName`, `vendorBio`, `vendorType`, `regions` FROM `vendors` WHERE
`regions` LIKE \'$region\' AND `datesBooked` NOT LIKE \'$date\'  AND `datesUnavailable`
NOT LIKE \'$date\'";

Answer 5

Please do not use direct variables for mysql queries. Someone can submit ; DELETE * FROM TABLE; or any arbitrary mysql code as the date or region and can do very evil things to your server. Please use an escape function such as mysqli_real_escape_string.

I would recommend:

$sql = 'SELECT `vendorName`, `vendorBio`, `vendorType`, `regions` FROM
 `vendors` WHERE `regions` LIKE \'' . $region . '\' AND `datesBooked` NOT LIKE
 \'' . mysqli_real_escape_string($date) . '\'  AND `datesUnavailable` NOT LIKE \'' . real_escape_string($date) . '\' ';

Please read up more about sql injection - http://en.wikipedia.org/wiki/SQL_injection.