有没有更好、更“标准”的方法在PHP中执行SQL查询而不使用框架?

For the longest time, I've been using the following basic formatting for SQL queries within my PHP:

很长一段时间以来，我一直在我的PHP中对SQL查询使用以下基本格式:

$sql = "SELECT * FROM `user-data` WHERE `id` = '".$id."' LIMIT 1;";
$fn = mysql_fetch_assoc(mysql_query($sql));

While this works flawlessly, it can get really messy for longer bits of code, and something deep inside of my conscience cringes at the string concatenation every time I do it. Still, it works and I use it almost everywhere without major issues. (That's a tame example, I'm not dense enough to pass user data directly into an SQL string without escaping it first, etc etc.)

虽然它可以完美地工作，但对于更长的代码位来说，它可能会变得非常混乱，而且每次我这样做时，我内心深处的某些东西会在字符串连接中感到畏缩。尽管如此，它仍然有效，我几乎在任何地方都使用它，没有什么大问题。(这是一个平淡的例子，我没有足够的密度将用户数据直接传递到一个SQL字符串中，而不需要先转义，等等)

What I'd like to do is something a bit more object oriented, but I'm not sure what the best approach would be. It'd be nice to just be able to sql->insert($values [, $where, $extra]); or something similar, using PHP's natural Associative Array types to pass in the query strings in a more simplified manner. Less flexible? Yes. More readable? Heck yes, and harder to make "silent" syntax errors at that.

我想做的是一些更面向对象的东西，但是我不确定最好的方法是什么。能够使用sql->插入($values， $ $， $ $);或者类似的东西，使用PHP的自然关联数组类型以更简化的方式传入查询字符串。更灵活?是的。更可读吗?是的，而且很难在这个问题上犯“沉默”的语法错误。

What are the community's takes on this? What approaches have you seen to this problem that were the most effective for projects you were working on?

社区对此有何看法?对于这个问题，您看到过哪些方法是您正在进行的项目中最有效的?

Not that it matters, but I personally don't do much more complicated than SELECTs, INSERTs, and UPDATEs, with occasional nesting of subqueries, but that's mostly because my SQL flavor doesn't do stored procedures.

这并不重要，但我个人并不会做比选择、插入和更新更复杂的事情，偶尔会嵌套子查询，但这主要是因为我的SQL风格不支持存储过程。

8 个解决方案

#1

PDO is a good, solid, secure solution that many frameworks build off of. If you're going to start from the bottom, PDO is a solid foundation.

PDO是一个很好的、可靠的、安全的解决方案，许多框架都是基于它构建的。如果你从底部开始，PDO是一个坚实的基础。

#2

Maybe it would make you a little happier at least to use PHP's string variable substitution:

也许至少使用PHP的字符串变量替换会让你更高兴一些:

$sql = "SELECT * FROM `user-data` WHERE `id` = '$id' LIMIT 1;";

#3

There is MDB_QueryTool I never tried.

我从未尝试过MDB_QueryTool。

IMHO Zend_DB is really cool, the zend framework allow you to use only the part you are interested in so you might want to take it a look event if you don't want the full framework.

IMHO Zend_DB真的很酷，zend框架允许您只使用感兴趣的部分，所以如果您不想要完整的框架，您可能想让它成为一个外观事件。

what I like in Zend_DB is the table select syntax

我喜欢Zend_DB中的表选择语法

$userRowset = $user->fetchAll( $user->select()
 ->where('name LIKE ?', $name . '%')
 ->order('id ASC')
 ->limit(10) 
);

You can easily see all the criterias and table involved so I find better then doing plain SQL. Just one warning Zend_DB doesn't handle all the SQL, so time to time you would have to write plain SQL but that's really rare.

您可以很容易地看到所涉及的所有标准和表，因此我发现更好地使用纯SQL。只有一个警告Zend_DB不能处理所有的SQL，所以有时您必须编写纯SQL，但这是非常罕见的。

#4

Doctrine is an ORM wrapped around PDO.

教条是一种缠绕在PDO上的ORM。

#5

Another vote for doctrine. Don't waste your time with PDO. I can't emphasize this enough. Go with an orm. Forget about wasting time writing CRUD methods, custom caching logic, and worrying about premature optimization such as "overhead" resulting from a library. The overhead incurred by spattering statements like "select * from app_users" and their associated ugly heredocs isn't worth it.

另一个投票原则。不要把时间浪费在PDO上。我再怎么强调也不为过。一个orm。忘记浪费时间编写CRUD方法、自定义缓存逻辑，以及担心库中产生的“开销”等过早优化问题。诸如“从app_users中选择*”之类的语句及其相关的丑陋的heredocs所产生的开销是不值得的。

If you need to fall back to sql, you can. The other 90% of the time you're in a state of bliss.

如果需要使用sql，可以这样做。另外90%的时间你处于幸福的状态。

http://www.doctrine-project.org/

#6

You can use mysqli to write little place holders in you SQL and then fill them in. It should be less susceptible to SQL injection attacks than string concatenation.

您可以使用mysqli在您的SQL中编写小的位置，然后填充它们。它应该比字符串连接更不容易受到SQL注入攻击的影响。

$conn = new mysqli($server, $username, $password, $database);
$stmt = $conn->prepare('SELECT * FROM people WHERE age = ? AND name != ?');
$stmt->bind_param('is', 20, "Austin");

#7

Try:

试一试:

$stat2 = <<<SQL
SELECT *  from YOUR.DET_TABLE
WHERE ID = ?
ORDER BY ID, EFF_DT
SQL;
  $d_cur = $conn->prepare($stat2);
  $status   = $d_cur->execute(array($selected));

#8

I've been wondering why I am always seeing the more complicated form of string building like this: "literal string " . $a . " more literal", rather than "literal string $a more literal", or in your case:

我一直在想，为什么我总是看到更复杂的字符串构建形式:“文字字符串”。一个美元。“更字面的”，而不是“更字面的字符串$a更多字面的”，或者在你的情况下:

"SELECT * FROM `user-data` WHERE `id` = '".$id."' LIMIT 1;";

instead of this:

而不是:

"SELECT * FROM `user-data` WHERE `id` = '$id' LIMIT 1;";

For more complicated expressions, I like to use sprintf (but I was a c programmer for a long time):

对于更复杂的表达式，我喜欢使用sprintf(但我长期以来都是c程序员):

$sql = sprintf("SELECT * FROM `user-data` WHERE `id` = '%s' LIMIT 1", $id);

This can also be written in this format:

这也可以写成这种格式:

$sql = sprintf("
    SELECT * 
       FROM `user-data` 
    WHERE `id` = '%s' 
        LIMIT 1", 
    $id);

In this case, it doesn't buy much, but when there are several variables embedded in the string, it makes it easier to manage.

在这种情况下，它不会买很多东西，但是当字符串中嵌入了几个变量时，它就更容易管理。

#1