Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 15 of 15
  1. #1
    Regular Coder
    Join Date
    Sep 2004
    Posts
    713
    Thanks
    6
    Thanked 2 Times in 2 Posts

    questions about good php database design

    Code:
    CREATE TABLE pa_photo (
    id INT(15) NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    albumid INT(10) NOT NULL,
    userid INT(25) NOT NULL,
    date INT(10) DEFAULT '0',
    size INT(5) DEFAULT '0',
    PRIMARY KEY(id)
    )TYPE=MYISAM;
    
    CREATE TABLE pa_user (
    userid INT(25) NOT NULL AUTO_INCREMENT,
    name VARCHAR(14) NOT NULL,
    password VARCHAR(32) NOT NULL,
    email VARCHAR(20) NOT NULL,
    intro TEXT,
    PRIMARY KEY(userid)
    )TYPE=MYISAM;
    note that i only put userid in pa_photo but the problem is when i need to get username i need to query pa_user to get.

    it will cause too many queries when i want to display the uploaders for photos in one album for example, 100 photos, it means i need to query database 100 times to get uploaders name displayed.

    You may wonder why i dont just put the username instead of userid in pa_photo, the problem is i afraid when user change their name, i need to update all the name in pa_photo table.

    so what is your suggestion? user username to replace userid in pa_photo? or is there any other methods i mean mysql_query that can combine two tables together, i know there is something like "INNER JOIN"etc, so,

    $sql=mysql_query("SELECT * FROM pa_photo WHERE albumid='$id'");
    while($r=mysql_fetch_array($sql)){
    //displaypicture
    //then display usrname
    }

    how to write??
    flying dagger

  • #2
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That seems ok. You're better off using userid than username in pa_photo, because you'll want to put an index on it to speed up your queries, and indexes on integers are more efficient than on strings (and, like you said, you won't have to worry about changing pa_photo if someone changes their username).

    To get all of a user's photos with the username, you can use a query like this:

    Code:
    SELECT
     p.name
    ,p.date
    ,p.etc.
    ,u.username
    FROM
     pa_photo AS p
    INNER JOIN
     pa_user AS u
    ON
     p.userid = u.userid
    As a side note, I would make pa_photo's date column a DATE, DATETIME or TIMESTAMP type instead of an INT -- you'll have more flexibility that way.

  • #3
    Senior Coder
    Join Date
    Jun 2002
    Location
    UK
    Posts
    1,137
    Thanks
    0
    Thanked 0 Times in 0 Posts
    if one was going to look at your structure, imagine the over all view. On a gallery i use, they have

    Main Gallery>Event A> Pictures by Person A
    Main Gallery>Event A> Pictures by Person B
    Main Gallery>Event B> Pictures by Person A
    Main Gallery>Event B> Pictures by Person B

    So each uploader as there own sub gallery in the gallery about something.

    If you did it that way you would only need to query once per a username and show it at the top of the page.

    scroots
    Spammers next time you spam me consider the implications:
    (1) that you will be persuaded by me(in a legitimate mannor)
    (2)It is worthless to you, when i have finished

  • #4
    4xz
    4xz is offline
    Regular Coder
    Join Date
    Aug 2004
    Location
    localhost
    Posts
    163
    Thanks
    0
    Thanked 1 Time in 1 Post
    The following query would also select all data @ once. I will exclude all photos where the uploader has been deleted.

    SELECT a.*, b.* FROM pa_photo a,pa_user b WHERE a.albumid='$id'" AND b.userid = a.userid

  • #5
    Regular Coder
    Join Date
    Sep 2004
    Posts
    713
    Thanks
    6
    Thanked 2 Times in 2 Posts
    well, thanks for your guys help in the first place, the gallery is actually design in a way, that every user can upload in different album, thus
    Code:
    SELECT
     p.name
    ,p.date
    ,p.etc.
    ,u.username
    FROM
     pa_photo AS p
    INNER JOIN
     pa_user AS u
    ON
     p.userid = u.userid
    did not serve the purpose of selecting photos below to same albumid,
    and for scroots, the structure is actually

    main gallery>category>album>different photos by user

    and for 4xz
    Code:
    SELECT a.*, b.* FROM pa_photo a,pa_user b WHERE a.albumid='$id'" AND b.userid = a.userid
    also did not help to my structure.
    flying dagger

  • #6
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    How about you give us a bit of sample data and an example of what kind of result set you want out of it, because I'm really not sure why something like 4xz's query won't work for you (which, if you're curious, is another syntax for an inner join).

  • #7
    Regular Coder
    Join Date
    Sep 2004
    Posts
    713
    Thanks
    6
    Thanked 2 Times in 2 Posts
    for 4xz
    Code:
    SELECT a.*, b.* FROM pa_photo a,pa_user b WHERE a.albumid='$id'" AND b.userid = a.userid
    because function of my gallery is album>all users photo belong to that album, so i dont need b.userid=a.userid in the query, thus you see in pa_photo,pa_user there will not have a common field, albumid only happens in pa_photo, no in pa_user
    flying dagger

  • #8
    Senior Coder
    Join Date
    Feb 2003
    Location
    Ontario, Canada
    Posts
    1,223
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This is basic normalization. You store your users in one table, and you store all properties of a user in their own table. In this case, photos.

    Photos join to users by the users.id field. So you'd have users.id and photos.user_id. This way you can join tables in a query and select every photo and their owners' username. Whether you want to select the photos WHERE username = 'username' or whether you just want to select all photos, the table structure being proposed by the other posters is the best way to do it. You save space and you get all the info you need in one query, not 100. Why do you think they're wrong?

    And if you have an `albums` table, you'd just need two joins to select the albums from which there are photos owned by a given username. Is this what you mean?
    Last edited by AaronW; 06-11-2005 at 04:05 AM.

  • #9
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The two userids are there to match the user's username from pa_user with their photos from pa_photo. It has nothing to do with the album; that's what the albumid's for -- since it's not being used to join the tables, it doesn't need a match in pa_user. Take this simplified example:

    Code:
    TABLE user
    userid | username
      1    |  foo101
      2    |  bar360
    
    TABLE photo
    userid   | albumid  |  photoname
       1     |     1    |    flowers
       1     |     2    |    puppies
       2     |     1    |    bakeries
       2     |     2    |    grumpy people
    Now, say you want the username and picturename for all photos in album 1, which is what I think you're asking for.

    My original query (rewritten for this example):
    Code:
    SELECT
     u.username
    ,p.photoname
    FROM
     photo AS p
    INNER JOIN
     user AS u
    ON
     p.userid = u.userid
    will join your two tables, giving you all usernames matched with their photonames.

    Code:
    username | photoname
      foo101 |  flowers
      foo101 |  puppies
      bar360 |  bakeries
      bar360 |  grumpy people
    But since I left out the albumid, it returns the photos from both albums. So, like 4xz posted, we add an albumid conditional:

    Code:
    SELECT
     u.username
    ,p.photoname
    FROM
     user AS u
    INNER JOIN
     photo AS p
    ON
     u.userid = p.userid
    WHERE
     p.albumid = 1
    Now we get the joined results from the first query, but only for album 1:

    Code:
    username | photoname
      foo101 |  flowers
      bar360 |  bakeries
    Note the final query I posted and 4xz's are equivalent; both syntaxes are accepted inner joins.

  • #10
    Regular Coder
    Join Date
    Sep 2004
    Posts
    713
    Thanks
    6
    Thanked 2 Times in 2 Posts
    Thanks i try again
    flying dagger

  • #11
    Regular Coder
    Join Date
    Sep 2004
    Posts
    713
    Thanks
    6
    Thanked 2 Times in 2 Posts
    Final query
    Code:
    $sql=$db->query("SELECT a.id,a.name,a.albumid,a.date,a.size,b.username FROM pa_photo AS a INNER JOIN pa_user AS b ON a.userid=b.userid WHERE a.albumid='$albumid'");
    the query seems ok, but no result is selected when i use
    Code:
    while($r=$db->fetch_array($sql)){
    echo $r['name'];
    }
    nothing display

    when i do
    Code:
    $num=$db->num_rows($sql);
    result equal to 0
    flying dagger

  • #12
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Try putting your query into a variable and echo'ing it to make sure $albumid is being set correctly.

  • #13
    Regular Coder
    Join Date
    Sep 2004
    Posts
    713
    Thanks
    6
    Thanked 2 Times in 2 Posts
    already done, the albumid is correct

    my original query

    Code:
    $sql=$db->query("SELECT * FROM pa_photo WHERE albumid='$albumid' ORDER BY '$offset','$picnumperpage'");
    able to work
    flying dagger

  • #14
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Is the username column in pa_user called name or username? In your original structure, it says name -- if you're looking for username when the column is name, you'll get a syntax error in your query. Although a failed query should give you an error message when you try to run the results through a mysql_fetch function or mysql_num_rows() instead of an empty set. I'd check that db class you're using to see if it's suppressing those errors and giving you misleading information.

  • #15
    Regular Coder
    Join Date
    Sep 2004
    Posts
    713
    Thanks
    6
    Thanked 2 Times in 2 Posts
    thanks Kid Charming , i discovered another error in my script, now working fine
    flying dagger


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •