There are a number of considerations here, I'll try to lay them out one at a time for ease of following:
What must the site do with the data?
Oftentimes, we ask web sites to do things on our behalf when we are not actually visiting them. For example, I may want crypto.SE to email me when there are responses to this post. The site could not do that without access to my email address, preferences, and the list of which posts I've subscribed to.
Either the site does not ever need to access a user's data (or at least not when the user is not logged in, if we want to do some session management as part of our scheme)...or the benefit of encrypting each user's data with a different key is negated by the fact that the keys need to be stored near the data.
What do you mean exactly by "encrypt the data with the password"?
When an infosec professional says "encrypt foo with bar", we literally mean that bar is being used as the key for an encryption algorithm that will turn foo into encrypted-foo. When it comes to user-supplied passwords, this is universally a bad idea. Most users choose terrible passwords. Even a good password policy might get you a passphrase of 20 characters or so with a digit or uppercase thrown in as a gimmee...at best. That isn't very much entropy in the grand scheme of things (even less when 42 of your users pick their favorite bands' names or family pets).
OTOH, what one might do to combat the lack of entropy is to use the password to encrypt a decent, randomly-generated encryption key, which is in turn used on the data. That's much less terrible. Not perfect (what encryption is perfect?), but markedly less terrible.
As a free bonus, the use-password-to-encrypt-key method helps with the fact that yes, stupid users will recycle passwords, and their accounts on various sites where they have recycled passwords will inevitably have some duplicate content that can be matched up should that ever be relevant to an attacker.
Why are we doing this again?
You ask "Would this work?", but you never actually defined what the problem was that you were trying to solve. This is one of the first things I advise you un-learn on your path to getting good at crypto and/or infosec in general. ALWAYS define a goal, a use case, or some sane description of what you mean to accomplish in the first place. Failing to correctly and adequately define the problem before solving it is how a massively large fraction of screwups happen, in infosec and elsewhere.
"Would this work?" as a proxy for "could I actually code and run something like this?" -> Yes, you could. I wouldn't advise it, but it would work.
"Would this work?" as a proxy for "Would this make my database's data less likely to be stolen if my database fell off of a truck?" -> Probably not. Granted, most DBs sit unencrypted on disk, but they do that for a reason: they're always unencrypted, or the keys stored nearby, when in use. Also, for your option of directly encrypting data with user-supplied passwords as the key, the likely weakness of the passwords makes offline attacks on most of that data trivial.
"Would this work?" as a proxy for "Would it be a good idea to implement this in a production system?" -> Absolutely not. The argument that this scheme provides added protection is weak, but the complexity it would introduce into the web application software is significant. Even if the encryption scheme were somehow useful (which I'm not buying so far), the added complexity is almost certain to result in vulnerabilities of a magnitude greater than the increase from the encryption.
I may be off base, of course...
I did presume some things from your very light specification... I presumed that you are talking about encrypting data at rest in a publicly accessible web application's database.
I'm not trying to kill all of your aspirations here, just to introduce the harsh reality. The good news is that you are kinda-sorta dancing around something that IS a viable concept:
In something that's not a webapp, where the service provider doesn't need access to users' data (e.g. a backup service), it IS wise for users to store their data with keys that they (the users) control. Furthermore, those keys should not be shared with the provider, who's just acting as a warehouse at that point. This is, in fact, how every responsible cloud backup provider behaves.