...

View Full Version : questions about good php database design



xiaodao
06-10-2005, 05:59 PM
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??

Kid Charming
06-10-2005, 06:46 PM
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:



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.

scroots
06-10-2005, 06:47 PM
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

4xz
06-10-2005, 08:01 PM
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

xiaodao
06-11-2005, 02:19 AM
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


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


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.

Kid Charming
06-11-2005, 02:28 AM
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).

xiaodao
06-11-2005, 02:49 AM
for 4xz


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

AaronW
06-11-2005, 03:03 AM
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?

Kid Charming
06-11-2005, 03:05 AM
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:



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):


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.



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:



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:



username | photoname
foo101 | flowers
bar360 | bakeries


Note the final query I posted and 4xz's are equivalent; both syntaxes are accepted inner joins.

xiaodao
06-11-2005, 09:26 AM
Thanks i try again

xiaodao
06-11-2005, 09:41 AM
Final query


$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


while($r=$db->fetch_array($sql)){
echo $r['name'];
}


nothing display

when i do


$num=$db->num_rows($sql);

result equal to 0

Kid Charming
06-11-2005, 10:28 AM
Try putting your query into a variable and echo'ing it to make sure $albumid is being set correctly.

xiaodao
06-11-2005, 01:54 PM
already done, the albumid is correct

my original query



$sql=$db->query("SELECT * FROM pa_photo WHERE albumid='$albumid' ORDER BY '$offset','$picnumperpage'");


able to work

Kid Charming
06-11-2005, 06:17 PM
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.

xiaodao
06-12-2005, 01:05 AM
thanks Kid Charming , i discovered another error in my script, now working fine



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum