Page 1 of 1

MySQL query part of string (and other questions)

Posted: Thu Feb 11, 2010 7:54 pm
by amh1010
I have a table strictly for managing data on real estate properties. One of the fields (named "misc") contains a string of numbers separated by commas (i.e. "1,1,3,2,4,0"). The 3rd number in the string (the 3) represents the number of bedrooms. I want the user to be able to type the number bedrooms they want, and the page will display all the properties with their desired number of bedrooms. Usually when I access the data in the "misc" field, I just use the explode function to build an array.

How can I query the table so that it only looks at 1 value in that string, ignoring the rest?

Re: MySQL query part of string

Posted: Fri Feb 12, 2010 5:18 am
by xjake88x
You can't really do that. You'd be better off making number of bedrooms as its own field.
Is there a fixed number of fields in the comma delimited list? If so, a cleaner approach would be to create a field associated with each one of those variables.

Re: MySQL query part of string

Posted: Fri Feb 12, 2010 5:45 am
by JakeJ
I agree with the other Jake. Listen to the two Jakes. LOL.

Always practice good data normalization. I'm guessing that you probably have normalization issues beyond just this one field.

Re: MySQL query part of string

Posted: Fri Feb 12, 2010 8:50 pm
by xjake88x
I've seen this Jake posting around, and I gotta say, I like this guy. His head is in the right place. He's got a good name too! :]

Re: MySQL query part of string

Posted: Sun Feb 14, 2010 12:17 pm
by amh1010
Noob question: What is data normalization?

Re: MySQL query part of string

Posted: Sun Feb 14, 2010 2:06 pm
by JakeJ
Data normalization is the process of atomizing your data which is breaking down your tables in to the smallest logical groups possible.

For example, you don't store data for a customers order in the same table as you store the customer information such as name, address and phone number.

For that matter, if a customer has more than one phone number or more than one address, you store those in a separate table but you store the customer id in that table along with a unique id for that record.

How your data is normalized largely depends on your needs. No one really follows all 7 normal forms. If you search for "data normalization" you'll find plenty of info on it.

Re: MySQL query part of string

Posted: Sun Feb 14, 2010 4:21 pm
by amh1010
Here is how my database is currently laid out:

1st table contains user information. The fields are:
  • ID#
    username
    password
    name (what is displayed on their profile page)
    description
    email
    phone
    address
2nd table contains property information:
  • ID# (property's unique ID)
    userID# (the ID# for the user who uploaded the property)
    address
    city
    state
    zip
    rent
    lease (a string of the starting and ending dates, which is later exploded into an array)
    image (URL for an image of the property)
    description
    latitude
    longitude
    then miscellaneous fields for beds, baths, etc.
Is there a better way to organize my database?

Re: MySQL query part of string

Posted: Sun Feb 14, 2010 9:30 pm
by JakeJ
Not a bad start but....

User names and passwords should be in a different table mostly because when you assign permissions, the people accessing the user table should not necessarily (probably) have access to changing passwords. Only an administrator and a password changing script should have access to those.

Even so, user names and passwords are different functions than general user information and should be stored separately from general user information.

In table 2, general property information should be in one table, a schedule of rents in another table and who is renting them in yet another table. Lease dates absolutely should be in separate fields.

But really, you're off to a good start and some here might disagree with my assessment or break it up even further.

I'm also assuming you are going to store information about payments, late charges etc. That stuff should also be segregated.

That's it for now! Good luck!

Re: MySQL query part of string

Posted: Sun Feb 14, 2010 11:21 pm
by amh1010
Nearly all fields are queried for properties at the same time, almost every time, on my site. For instance, when the property page is populated, it displays all data for the property. Does this change your assessment?

Also, a little off-topic, but since I know almost nothing about security for my site, is it still safe to have an image uploading feature? Users uploading content have me really worried. [EDIT:] Would it be a good idea to pay $20/year for Photobucket pro, and allow users to upload images to my Photobucket account via my website?

Another side note: is there some sort of list of basic security features I should code into my site? All I have right now is script preventing users from "hacking" the URL to let them edit other users' information (by comparing the user# in their cookie with the user# in the URL).