Page 1 of 1

[Solved] Relational Databases

Posted: Mon Jun 23, 2008 12:44 pm
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

Re: Relational Databases

Posted: Mon Jun 23, 2008 2:35 pm
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

Re: Relational Databases

Posted: Mon Jun 23, 2008 5:50 pm
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.

Re: Relational Databases

Posted: Mon Jun 23, 2008 8:57 pm
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.

Re: Relational Databases

Posted: Mon Jun 23, 2008 10:43 pm
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.

Re: Relational Databases

Posted: Tue Jun 24, 2008 4:25 am
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)

Re: Relational Databases

Posted: Tue Jun 24, 2008 8:57 am
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 */

Re: Relational Databases

Posted: Tue Jun 24, 2008 12:07 pm
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?

Re: Relational Databases

Posted: Tue Jun 24, 2008 12:27 pm
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());

Re: Relational Databases

Posted: Wed Jun 25, 2008 2:54 am
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).

Re: Relational Databases

Posted: Wed Jun 25, 2008 8:36 am
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.