如何将sql和php代码分开
我有一个课程可以帮助我处理用户。 例如：
$user = new User("login","passw");
$name = $user->getName();
$surname = $user->getSurname();
$table = $user->showStats();
所有这些方法都内部有 SQL 查询。有些操作只需要一个 sql 查询，有些则需要多个 sql 查询。如果数据库结构发生变化 - 将很难更改所有查询（类很长）。所以我想让 SQL 查询远离这个类。但如何做到这一点呢？
阅读这个问题后，我了解了存储过程。这是否意味着现在一项操作只需要一个 SQL 查询（存储过程的调用）？但是如何组织分离sql和php呢？我应该将 sql 查询保留在数组中吗？或者它应该是一个 sql 查询类。如果是的话，如何组织这堂课（也许我应该学习什么模式）
I have a class that helps me to handle users.
For example:
$user = new User("login","passw");
$name = $user->getName();
$surname = $user->getSurname();
$table = $user->showStats();
All these methods have SQL queries inside. Some actions require only one sql queries, some - more than one. If database structure changes - it will be difficult to change all queries (class is long). So I thought to keep SQL queries away from this class. But how to do this?
After reading this question I've known about Stored Procedures. Does it mean, that now one action requires only one SQL query (call of Stored Procedure)? But how to organize separation sql from php? Should i keep sql-queries in an array? Or may be it should be an sql-queries class. If yes, how to organise this class (maybe what pattern I should learn)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
这是一个令人惊讶的大主题，但我有一些建议可以帮助您：
您应该研究对象关系映射，其中对象自动生成 SQL 查询。查看对象关系映射和Active Record 文章了解概述。这将使您的数据库代码保持最少，并且在表结构发生变化时变得更容易。
但这里没有灵丹妙药。如果您的架构发生更改，您将必须更改查询以匹配。有些人更喜欢通过将查询逻辑封装在数据库视图和存储过程中来处理这个问题。如果您保持一致，这也是一个好方法，但请记住，一旦您开始编写存储过程，它们将与您正在使用的特定数据库密切相关。使用它们并没有什么问题，但是它们将使您以后切换数据库变得更加困难 - 通常不是问题，而是需要记住的一个重要方面。
无论如何，无论您选择什么方法，我建议您将数据库逻辑存储在几个“模型”类中。看起来你已经在做类似的事情了。基本思想是每个模型都封装数据库特定区域的逻辑。传统上，每个模型都会映射到数据库中的单个表 - 这就是 Ruby on Rails 活动记录类的工作原理。这是一个很好的策略，因为它将数据库逻辑分解为简单的小“块”。如果将所有数据库查询逻辑保存在一个文件中，它可能会很快失去控制并成为维护噩梦 - 相信我，我曾经经历过这样的情况！
为了更好地理解“大局”，我建议您花一些时间阅读 Web 模型-视图-控制器 (MVC) 架构。您还需要查看已建立的 PHP MVC 框架，例如 CodeIgniter、Kohaha、CakePHP 等。即使您不使用一——虽然我推荐你do - 了解这些框架如何组织您的代码将会很有帮助。
This is a surprisingly large topic, but I have a few suggestions to help you on your way:
You should to look into object-relational mapping, in which an object automatically generates SQL queries. Have a look at the Object-Relational Mapping and Active Record articles for an overview. This will keep your database code minimal and make it easier if your table structure changes.
But there is no silver bullet here. If your schema changes you will have to change your queries to match. Some people prefer to deal with this by encapsulating their query logic within database views and stored procedures. This is also a good approach if you are consistent, but keep in mind that once you start writing stored procedures, they are going to be tied heavily to the particular database you are using. There is nothing wrong with using them, but they are going to make it much more difficult for you to switch databases down the road - usually not an issue, but an important aspect to keep in mind.
Anyway, whatever method you choose, I recommend that you store your database logic within several "Model" classes. It looks like you are doing something similar to this already. The basic idea is that each model encapsulates logic for a particular area of the database. Traditionally, each model would map to a single table in the DB - this is how the Ruby on Rails active record class works. It is a good strategy as it breaks down your database logic into simple little "chunks". If you keep all of the database query logic within a single file it can quickly grow out of control and become a maintenance nightmare - trust me, I've been there!
To get a better understanding of the "big picture", I recommend you spend some time reading up on web Model-View-Controller (MVC) architecture. You will also want to look at the established PHP MVC frameworks, such as CodeIgniter, Kohaha, CakePHP, etc. Even if you do not use one - although I recommend you do - it would be helpful to see how these frameworks organize your code.
我想说你应该考虑在代码中实现“存储库”设计模式。
对于如何实现这一点的一个很好的答案对于这个空间来说太长了，所以我将发布一些面向 PHP 的参考文献：
travis swicegood -- PHP 中的存储库模式
Jon Lebensold——PHP 中的存储库模式
I would say you should look into implementing the "repository" design pattern in your code.
A good answer to how to implement this would be too long for this space, so I'll post a couple of PHP-oriented references:
travis swicegood -- Repository Pattern in PHP
Jon Lebensold -- A Repository Pattern in PHP
如果您使用关注点分离将业务逻辑与数据访问分开，那么您的思路是正确的逻辑上你会处于一个更好的位置。
You are on the right lines if you use separation of concerns to separate your business logic from your data access logic you will be in a better place.
从你的“已经有 2K 行代码”的声明来看，你要么正在维护某些东西，要么正在开发某些东西。
Faust 和 Justin Ethier 都提出了很好的建议 - “我应该如何将数据库访问与应用程序代码分开”是 Web 开发中最古老且回答最多的问题之一。
就我个人而言，我喜欢 MVC - 它几乎是 Web 开发的默认范例，它平衡了可维护性和生产力，并且有大量框架可以在您执行此操作时为您提供支持。
当然，您可能会认为从头开始重写应用程序太过费力——在这种情况下，存储库模式是一个很好的中途之家。
无论哪种方式，你都需要阅读有关重构的内容——从你现在的位置到你想要的位置将是很棘手的。我推荐福勒的书作为入门。
您能否详细解释一下为什么您的数据库架构可能会发生变化？这通常是前方有麻烦的征兆......
Judging by your "there are already 2K lines of code" statement, you're either maintaining something, or midway through developing something.
Both Faust and Justin Ethier make good recommendations - "how should I separate my database access from my application code" is one of the oldest, and most-answered, questions in web development.
Personally, I like MVC - it's pretty much the default paradigm for web development, it balances maintainability with productivity, and there are a load of frameworks to support you while you're doing it.
You may, of course, decide that re-writing your app from scratch is too much effort - in which case the repository pattern is a good halfway house.
Either way, you need to read up on refactoring - getting from where you are to where you want to be is going to be tricky. I recommend the book by Fowler, as a starter.
Could you explain more about why your database schema may change? That's usually a sign of trouble ahead.....