PHP Variable Passing not working in database call.

Question

This works.

function get_distinct_size_for_bracelets() {
    $sql = "SELECT DISTINCT size FROM mytable WHERE id = 27 AND type='plastic' ORDER BY size";
}

This does not work and stops php dead with no error reporting.

 function get_distinct_size_for_bracelets($myvalue) {
    $sql = "SELECT DISTINCT size FROM mytable WHERE id = 27 AND type=".$myvalue." ORDER BY size";
}

I have tried a number of configurations and nothing is working.

Answer 1

function get_distinct_size_for_bracelets($myvalue) {
    $sql = "SELECT DISTINCT size FROM mytable WHERE id = 27 AND type='".$myvalue."' ORDER BY size";
}

You still need the single quotes in the SQL query.

Answer 2

Remember to quote the passed value:

function get_distinct_size_for_bracelets($myvalue) 
{ 
$sql = "SELECT DISTINCT size FROM mytable WHERE id = 27 AND type=".$myvalue." ORDER BY size";
}

Should be:

function get_distinct_size_for_bracelets($myvalue) 
{ 
$sql = "SELECT DISTINCT size FROM mytable WHERE id = 27 AND type='".$myvalue."' ORDER BY size";
}

Note the added single quotes at type.

Answer 3

You need single quotes around it still. So

$sql = "SELECT DISTINCT size FROM mytable WHERE id = 27 AND type='".$myvalue."' ORDER BY size";

Answer 4

try

function get_distinct_size_for_bracelets($myvalue) {
    $sql = "SELECT DISTINCT size FROM mytable WHERE id = 27 AND type='".$myvalue."' ORDER BY size";
}

Answer 5

You're not escaping your value and you're forgetting your single quotes, that'd be my guess. Try:

function get_distinct_size_for_bracelets($myvalue) { 
    $query = sprintf("SELECT DISTINCT size FROM mytable WHERE id = 27 AND type='%s'  ORDER BY size",
        mysql_real_escape_string($myvalue));
}

That lets you pass an escaped value into the string, as opposed to using concatenation.

Answer 6

MySQL has different data types too. And strings need to be enclosed in quotes too:

$sql = "SELECT DISTINCT size FROM mytable WHERE id = 27 AND type='".$myvalue."' ORDER BY size";

Or better with additional use of the mysql_real_escape_string function:

$sql = "SELECT DISTINCT size FROM mytable WHERE id = 27 AND type='".mysql_real_escape_string($myvalue)."' ORDER BY size";