开发者

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'));
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜