How to abbreviate MySQL queries in PHP?
I have a page that needs to make six nearly identical queries with the only difference between them being the value of "category":
$query = "SELECT * FROM table_name WHERE category="CATEGORY1";
Instead of writing out six different $query variables ($query1, $query2, etc.) for each different category, I'd like to abbreviate so I can pass in category as a variable each time I instantiate $query, like so:
$query = "SELECT * FROM table_name WHERE category='$cat'";
$results= mysql_query($query,'CATEGORY1');
There must be some way to do this, but I simply can't find the syntax anywhere for passing a variable to a MySQL query in this way (I checked php.net, this site, various other php resources on Google). Can anyone help me out?
M开发者_如何学Cuch appreciated, thanks!
Use PDO and prepared statements
$categories = array(
'CATEGORY1',
'CATEGORY2',
'CATEGORY3',
'CATEGORY4',
'CATEGORY5',
'CATEGORY6'
);
$db = new PDO(...);
$stmt = $db->prepare('SELECT * FROM table_name WHERE category = :category');
$stmt->bindParam('category', $category);
foreach ($categories as $category) {
$stmt->execute();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
// do stuff with $row
}
}
You can't use a single variable in SQL to represent a comma separated list, like I'd use for situations like these as values for an IN clause. You need to use dynamic SQL, which means constructing the query as a string before submitting it to the database.
$array = array('category1', 'category2', 'category3');
$comma_separated = implode("','", $array);
$query = "SELECT *
FROM table_name
WHERE category IN ('$comma_separated')";
$results = mysql_query($query);
An alternative would be to use MySQL's Prepared Statement syntax, which is MySQL's dynamic SQL syntax...
You can use sprintf
for that:
$query = "SELECT * FROM table_name WHERE category='%s'";
$results= mysql_query(sprintf($query,'CATEGORY1'));
精彩评论