WEB Basic PHP Query w/ MySQL Help

Discussion in 'OT Technology' started by Fase, Feb 8, 2010.

  1. Fase

    Fase Your Face, In A Pickle Jar.

    Joined:
    Apr 6, 2004
    Messages:
    29,540
    Likes Received:
    0
    Location:
    Windsor, Ont, Canada.
    I'm trying to run:

    Code:
    USE testdb; INSERT INTO Users (username, userpassword, email, country) VALUES ('Fase', 'q', [email protected]', 'CA');
    
    When I run this via PHP I get:

    Code:
    Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO Users (username, userpassword, email, country) VALUES ('Fase', 'q', ' at line 1
    
    When I copy and paste the query at the top into myPHPAdmin page, it works fine.
     
  2. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,647
    Likes Received:
    15
    Location:
    Atlanta
    try doing the USE and INSERT as two separate commands...

    I.E. -

    mysql_query('USE db_name');
    mysql_query('insert ....');
     
  3. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,647
    Likes Received:
    15
    Location:
    Atlanta
    also, your passwords should always be MD5'd. Never have a straight text to text password system.
     
  4. Fase

    Fase Your Face, In A Pickle Jar.

    Joined:
    Apr 6, 2004
    Messages:
    29,540
    Likes Received:
    0
    Location:
    Windsor, Ont, Canada.
    This is just testing for learning purposes. I would of course MD5 the password and secure everything in real world.
     
  5. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,647
    Likes Received:
    15
    Location:
    Atlanta
    infact all those tables are wrong. You should have something like this:

    USERS
    *userID
    *userName

    PASSWORDS
    *userID
    *password

    EMAILS
    *userID
    *emailAddress

    COUNTRY
    *userID
    *country


    Then just use inner joins to pull information. Normalization is your friend
     
  6. Fase

    Fase Your Face, In A Pickle Jar.

    Joined:
    Apr 6, 2004
    Messages:
    29,540
    Likes Received:
    0
    Location:
    Windsor, Ont, Canada.
    Works!

    So I can't run multiple queries together?
     
  7. Fase

    Fase Your Face, In A Pickle Jar.

    Joined:
    Apr 6, 2004
    Messages:
    29,540
    Likes Received:
    0
    Location:
    Windsor, Ont, Canada.
    This seems like a fantastic way to complicate things. I was copying my table structures from vbulletin.
     
  8. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,647
    Likes Received:
    15
    Location:
    Atlanta
    It looks harder now, but it'll help so much later on once you start HAVING to mix tables. In your example, it makes sense to put everything into one table and just make it all mandatory. However, when you start having tables where not everything has to be filled out, normalization is a must.
     
  9. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,647
    Likes Received:
    15
    Location:
    Atlanta
    not unless you make a function to do it.
     
  10. Fase

    Fase Your Face, In A Pickle Jar.

    Joined:
    Apr 6, 2004
    Messages:
    29,540
    Likes Received:
    0
    Location:
    Windsor, Ont, Canada.
    My table has more columns than that. I simply used NOT NULL default '' or 0 for most everything not required to ensure values are not NULL. Is this not good practice?
     
  11. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,647
    Likes Received:
    15
    Location:
    Atlanta
    not at all. Everything should be broken down as much as possible to reduce null's (or 0's in your case) and to make other searches easier.

    For example, in your example, you have country as a field. If you wanted to count the number of countries your users have entered, you'd have to make mysql sort through all the other bullshit (name, password, email). If you normalize your db and put countries into it's own table, you reduce the amount of time spent searching. That's the most basic example of how normalization helps.
     
  12. Fase

    Fase Your Face, In A Pickle Jar.

    Joined:
    Apr 6, 2004
    Messages:
    29,540
    Likes Received:
    0
    Location:
    Windsor, Ont, Canada.
    I see. So if I did like SELECT COUNT(country) WHERE country = 'CA' or whatever my query speeds would be much faster with your normalized layout than mine all in the same table?

    This is only for learning purposes so this is good to know. I was copying vbullitins sql setup script.
     
  13. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,647
    Likes Received:
    15
    Location:
    Atlanta
    Much faster yes, but still probably not even noticeable to humans until your records got into the hundreds of thousands... but still - If you're gonna do something, do it right.
     
  14. dazmanultra

    dazmanultra New Member

    Joined:
    Jun 17, 2002
    Messages:
    34,795
    Likes Received:
    0
    Location:
    English Countryside
    Fresh out of school or a database design class?

    Too much normalization just increases the overhead of your application. Doing joins every time you need to get an individual's record is massively expensive.
     
  15. CyberBullets

    CyberBullets I reach to the sky, and call out your name. If I c

    Joined:
    Nov 13, 2001
    Messages:
    11,865
    Likes Received:
    0
    Location:
    BC, Canada/Stockholm, Sweden
    There is a balance.

    If a user has to have an ID, Username, Password, Email it does make sense to store them all in a similar table. But once you start getting into optional fields, countries, IM contacts, website, etc it's better to put them in their own table and reference the user.

    But in the end, it comes down to many factors. Size, scalability, data sharding, hardware, development (is there a ORM?). Majority of sites won't have issues if it's one giant table, they just dont see the traffic.

    CustomerTable
    -id (int auto inc) *PK
    -username (nvarchar(50))
    -password (nvarchar(50))
    -email (nvarchar(255))

    CustomerOptionsTable
    -id (int auto inc) *PK
    -customerID (int) *FK
    -optionID (int) *FK
    -text (nvarchar(255)

    OptionTable
    -id (int auto inc) *PK
    -description (nvarchar(255))

    I'm a DBA for a large DWH (poker) and we de-normalize a lot of our data, but our servers can handle it.
     
  16. drpepper

    drpepper Active Member

    Joined:
    Nov 13, 2006
    Messages:
    38,076
    Likes Received:
    2
    Location:
    San Antonio
    shouldnt you declare the db you're using in
    Code:
    mysqli_connect()
    ?
     
  17. dazmanultra

    dazmanultra New Member

    Joined:
    Jun 17, 2002
    Messages:
    34,795
    Likes Received:
    0
    Location:
    English Countryside
    Definitely true - it all comes down to the application. Was mainly just pointing out that you can't just assume normalization = good when it comes to real world database applications. :o
     
  18. Fase

    Fase Your Face, In A Pickle Jar.

    Joined:
    Apr 6, 2004
    Messages:
    29,540
    Likes Received:
    0
    Location:
    Windsor, Ont, Canada.
    Fuck forgot about that.
     
  19. CyberBullets

    CyberBullets I reach to the sky, and call out your name. If I c

    Joined:
    Nov 13, 2001
    Messages:
    11,865
    Likes Received:
    0
    Location:
    BC, Canada/Stockholm, Sweden
    Very true. When I was in uni, they were all about BCNF and completely didn't take other factors into consideration. I guess "perfect world" scenarios right?
     
  20. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,647
    Likes Received:
    15
    Location:
    Atlanta
    subtle "king" bragging :mamoru:
     
  21. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,647
    Likes Received:
    15
    Location:
    Atlanta
    Agreed, but how often will you have to do a 4 table inner join if you simply set a session with the userID? If you set a session variable, you can simple do something like:

    SELECT email FROM EMAILS WHERE userID = " . $_SESSION['userID'] . ";";
     
  22. CyberBullets

    CyberBullets I reach to the sky, and call out your name. If I c

    Joined:
    Nov 13, 2001
    Messages:
    11,865
    Likes Received:
    0
    Location:
    BC, Canada/Stockholm, Sweden
    Dangerous and easy exploit if you store the user id in session variables without hashing and salting it.

    If you did it like your examples, simple enough to view my cookies, change the value and and see what I can exploit. The code you pasted could easily destroy your DB because you're not escaping it, thus I could inject some SQL to stop the query and drop your DB.

    I was using User as a simple example. For a CRM you could have page type, content type, content, ratings, comments, friendly url, keywords, parent page, meta info, just for a single article....
     
  23. o2

    o2 Witty Title Here OT Supporter

    Joined:
    Oct 4, 2005
    Messages:
    16,099
    Likes Received:
    12
    Location:
    Toronto
    Or you can store all the user's data in an array, inside the session (provided you give it a hashed + salted key). That way, you dont have to query the DB every time you use it. Just fetch it from the session. Or better yet, throw it in memcache.

    I got a fully dynamic site that runs with 0 SELECT mysql queries for 99% of all page views and actions.
     
  24. Phasm

    Phasm OT Supporter

    Joined:
    Sep 20, 2005
    Messages:
    9,680
    Likes Received:
    0
    Location:
    Michigan
    where the fuck is my phone
     
  25. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,647
    Likes Received:
    15
    Location:
    Atlanta
    [​IMG]
     

Share This Page