[Solved] Relational Databases

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
josephman1988
Forum Newbie
Posts: 15
Joined: Mon Apr 21, 2008 8:29 am

[Solved] Relational Databases

Post by josephman1988 »

Hey guys, I've moved onto learning Relational Database Design now, and - at the mo', getting my head confuzzled. =[

i have 3 tables:
Image
Named accordingly.

I then have the client.php profile page - this being the current code:

Code: Select all

$ClientId = $_GET['ClientId'];
 
$sql = "SELECT * FROM Client WHERE ClientId = $ClientId";  
$result = mysql_query($sql);
 
 
while($ClientInfo = mysql_fetch_array($result))
{
echo "<strong>Name:</strong> {$ClientInfo['ClientName']} - <strong>Category:</strong> <br /><br />";
echo "<img src='./images/headshots/{$ClientInfo['ClientPic']}.jpg' name='{$ClientInfo['ClientName']}' /><br /><br />";
echo "<strong>Bio:<br /></strong> {$ClientInfo['ClientBio']}<br /><br />";
echo "Link Exchange: <br /><a href='{$ClientInfo['ClientExchange']}'>Click To Visit {$ClientInfo['ClientName']}'s Site</a><br /><br />";
echo "Demo:<br /> <a href='./demos/{$ClientInfo['ClientDemo']}.mp3'>Click Here For {$ClientInfo['ClientName']}'s Demo</a><br /><br />";
 
}
::Please excuse the messy DB design, and this that and the other, I know there are rules of thumb here - but im learning =]::

So yea, I want to have another page where all clients are listed in categories, so - how would i go about that?
But, saying that, the above code is 'Ok' when getting the clients information, but what about trying to retrieve the category that the client is in?

Its hurts my brain - =[

Thanks in advanced for the help
Last edited by josephman1988 on Fri Jun 27, 2008 11:51 am, edited 1 time in total.
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Re: Relational Databases

Post by Kieran Huggins »

your DB design looks sound, but the names might benefit from the following changes (just for clarity's sake)

Code: Select all

categories
----------------------
id (int)
name (varchar)
 
 
clients
----------------------
id (int)
name
bio
pic
demo
exchange
 
 
categories_clients (the join table, named for the tables is joins in alphabetical order - it helps you remember what you named it)
----------------------
category_id
client_id
Then to display clients by category, you could do something like: (*untested)

Code: Select all

SELECT cat.name AS category, cli.* 
FROM categories cat 
LEFT JOIN categories_clients cc ON cat.id = cc.category_id 
LEFT JOIN clients cli ON cli.id = cc.client_id 
ORDER BY cat.name, cli.name
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Relational Databases

Post by califdon »

There are a bunch of tutorials on relational database design and particularly on JOINs, which is what you need to learn. Here's one, for a starter: http://www.w3schools.com/Sql/sql_join.asp

Although this is a controversial subject, I would recommend not storing images in the database, for performance. Instead, store the images in a subdirectory and store just the path and filenames in the database.
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Re: Relational Databases

Post by Kieran Huggins »

don++

Though in his original schema image it looks like the image field is varchar (100), so I assume he meant path.

But definitely: files on the file system!

I'd suggest going a step further and naming them the md5 of themselves, and storing that value in the DB.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Relational Databases

Post by califdon »

Kieran Huggins wrote:Though in his original schema image it looks like the image field is varchar (100), so I assume he meant path.

But definitely: files on the file system!
Gosh, when I took another look at his code, he's doing it right. I must have been looking cross-eyed at his html. :oops:
I'd suggest going a step further and naming them the md5 of themselves, and storing that value in the DB.
Good idea, too.
josephman1988
Forum Newbie
Posts: 15
Joined: Mon Apr 21, 2008 8:29 am

Re: Relational Databases

Post by josephman1988 »

Thanks for your responses guys =]

Code: Select all

SELECT cat.name AS category, cli.*
FROM categories cat
LEFT JOIN categories_clients cc ON cat.id = cc.category_id
LEFT JOIN clients cli ON cli.id = cc.client_id
ORDER BY cat.name, cli.name
Could you explain this more please?

====
cat.name
category
cli.*
These arn't values in any table listed in the code you supplied?

====
cc
cat.id
cc.category?
What is the 'cc' all about? And whered you get cat.id from .. or are these reffering to the categories table.value?

Ditto with cli ..

Thanks again (il be checking that link out now califdon, thanks)
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Re: Relational Databases

Post by Kieran Huggins »

cat, cc and cli are aliases defined in the query. It just saves typing.

Whenever you see "LEFT JOIN table_name alias" or "FROM table_name alias" you're creating an alias called "alias" for the table named "table_name". It helps clean up the SQL for readability.

By the way, I was coding to the revised table and property names I suggested in the same post. The tables are essentially the same as yours, just with naming conventions that are easier to predict / remember, IMO.

JOINS in SQL are a powerful feature, but somewhat difficult to grasp at first. What the query does is this:

Code: Select all

SELECT cat.name AS category, cli.* /* selecting the category name and everything from the clients table for each row */
FROM categories cat  /* ...starting from the categories table. we're also defining an alias named "cat" to save typing. "cat" now means the same thing as "categories" */
LEFT JOIN categories_clients cc ON cat.id = cc.category_id  /* now we're JOINING the categories_clients join table (aliased as "cc"), which we'll need to join the categories table to the clients table */
LEFT JOIN clients cli ON cli.id = cc.client_id  /* and now we can join the categories_clients table to the clients table (aliased as "cli") */
ORDER BY cat.name, cli.name  /* and here we set the order we want our rows returned */
josephman1988
Forum Newbie
Posts: 15
Joined: Mon Apr 21, 2008 8:29 am

Re: Relational Databases

Post by josephman1988 »

it is much easier to read after changing my db - thanks =]

I am being lazy atm - so i used your code -

Code: Select all

 
$sql = mysql_fetch_array("SELECT cat.name AS category, cli.*
FROM categories cat
LEFT JOIN categories_clients cc ON cat.id = cc.category_id
LEFT JOIN clients cli ON cli.id = cc.client_id
ORDER BY cat.name, cli.name");
 
$result = mysql_query($sql) or die(mysql_error());
 
Which simply prints:

Code: Select all

Query was empty
Maybe im having a stupiid moment lol?
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Re: Relational Databases

Post by Kieran Huggins »

try:

Code: Select all

$sql = "SELECT cat.name AS category, cli.*
FROM categories cat
LEFT JOIN categories_clients cc ON cat.id = cc.category_id
LEFT JOIN clients cli ON cli.id = cc.client_id
ORDER BY cat.name, cli.name";
 
$result = mysql_query($sql) or die(mysql_error());
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Re: Relational Databases

Post by CoderGoblin »

I'm another one who never stores images in the database. Instead I use a system where each image is associated with an Id, normally the Id of whatever it is associated with. The actual image is stored in a multileveled directory structure based on the reversed id. Given the id number 12345 if we set the directory level to 4 the file will be placed in images_dir/5/4/3/2/12345.jpg. This system works really well for large numbers of images. If we run into problems with file storage (number of files per directory we can simply increase the depth used (obviously moving existing files first).
josephman1988
Forum Newbie
Posts: 15
Joined: Mon Apr 21, 2008 8:29 am

Re: Relational Databases

Post by josephman1988 »

Thanks for all your replies.

How would I call the cargory name then?

Like i have a client.php template for the clients profiles, but i don't know how to print the associated Category.
Ditto with that list though, i want to organize the clients in categories, but instead of pulling everything from each table. I want to use static XHTML for each header, then pull the neccessary data using the neccessary query.

Thanks for all your help up 'til now, and in advanced.
Post Reply