Dan Whiteside Dan Whiteside - 6 months ago 25
SQL Question

Passing query strings to a PDO sql query using PHP

I am making a custom endpoint for returning JSON based on different SQL queries - the queries are created based on multiple query strings I am passing from my App. What I've done is shabby and would like to know if there is a better way of achieving the same results as what the below returns:

The below is just a small selection of all the IF/Else statements checking for different query strings

if(isset($_GET['pt']) && !isset($_GET['c']) && !isset($_GET['sc']) && isset($_GET['size'])) {

$sql = "SELECT *
FROM product
INNER JOIN product_sizes
ON product.product_sku = product_sizes.affiliate_p_id
WHERE product_sizes.product_type IN (".$_GET['pt'].")
AND product_sizes.product_sizes IN (".$_GET['size'].")
GROUP BY product_sizes.affiliate_p_id
ORDER BY product.last_updated ASC
LIMIT 100";


} else {

$sql = "SELECT *
FROM product
INNER JOIN product_sizes
ON product.product_sku = product_sizes.affiliate_p_id
GROUP BY product_sizes.affiliate_p_id
ORDER BY product.last_updated ASC
LIMIT 100";

}


$result = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
$return = array();
foreach ($result as $row) {
$return[] = array(
'affiliate_p_id' => $row['affiliate_p_id'],
'affiliate_id' => $row['affiliate_id'],
'product_colours' => $row['product_colours'],
'product_sub_category' => $row['product_sub_category'],
'merchant_name' => $row['merchant_name'],
'product_type' => $row['product_type'],
'product_sizes' => $row['product_sizes'],
'product_name' => $row['product_name'],
'sale_price' => $row['sale_price'],
'rrp_price' => $row['rrp_price'],
'product_image' => $row['product_image'],
'product_slug' => $row['product_slug']
);
}
$dbh = null;

header('Content-type: application/json');
echo json_encode($return);

Answer

You can break it into smaller parts like this

$sql = 'SELECT *
    FROM product
    INNER JOIN product_sizes
    ON product.product_sku = product_sizes.affiliate_p_id ';
if(isset($_GET['pt']) && !isset($_GET['c']) && !isset($_GET['sc']) && isset($_GET['size'])) {
$sql .= 
    " WHERE product_sizes.product_type IN :pt AND product_sizes.product_sizes IN  = :size ';
}
$sql .= " GROUP BY product_sizes.affiliate_p_id
    ORDER BY product.last_updated ASC
    LIMIT 100";
$sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
if(isset($_GET['pt']) && !isset($_GET['c']) && !isset($_GET['sc']) && isset($_GET['size'])) {
$sth->execute(array(':pt' => $_GET['pt'], ':size' => $_GET['size']));
}
$result = $sth->fetchAll();