[SOLVED] normalization

[ngreenwood6]

I am new to php and mysql and had a few questions for someone that is willing to help.  I have been looking into the normalization of a database. Say I had a structure like this:

DB: Normal
Table: Users
  Row: Id
  Row: Username
  Row: Password
Table: Work
  Row: Request
  Row: User_id

 

1.) Wouldn't that be normalizing as I am using the user_id as the id from the users table?

2.) If I wanted it to show the username instead of the user_id when I display it how would I do it?

 

I may have some more questions but thanks in advance for any help.

[rhodesa]

first, Row is deceiving, as it is actually a column (or field).

 

In your work table, will any given user EVER have more then the Work entry?

 

to JOIN the tables, it would be:

SELECT * FROM Work w LEFT JOIN Users u ON w.User_id = u.Id

[ngreenwood6]

Thanks for the reply rhodesa. Yeah they could have more than the work entry. Is what I showed you normalization?  Also, how would I display this on the page can you give me a small example.

[rhodesa]

The Work table should have it's own Unique ID then...like Work_id

[.josh]

Database normalization is a technique to reduce the amount of duplicate data in a database.  Making a relational database (a database structure) is an end result of normalizing a database.  In other words, one way to normalize a database is to make a relational database.  That is pretty much what you did there. You identified that users can have more than one request, so instead of repeating the same user info in a single table over and over again, where only the request changes, you made two different tables, with a common user id link. 

 

As rhodesa pointed out, you can use various JOIN queries to return linked table data.  His query will pretty much just dump all info out from both tables.  If you are just wanting to show username:request, it would look something like this:

 

select Username, Request from Users, Work, where Users.Id = Work.User_id

 

 

 

[ngreenwood6]

Thank you crayon that helped. I think you misunderstood the least question though. I know how to select the data to be displayed but I want to know how to display it on the page.

 

Say i have this query

SELECT * FROM Work w LEFT JOIN Users u ON w.User_id = u.Id

 

I want to display the username. would this work:

 

$query = "SELECT * FROM Work w LEFT JOIN Users u ON w.User_id = u.Id";

$result = mysql_query($query)

while($row = mysql_query)
{
echo $row['username'];
}

 

Does that display the username from the first table? Also what happens if I have 2 table with a common field like date?

 

Thanks for any help.

[.josh]

If you have two tables with a common field like 'date' you would refer to them like so:

 

select tablename.columnname ....

 

You can also assign an alias to it like so:

 

select tablename.columnname as something....

 

As far as your code is concerned, other than your missing ; for mysql_query, yes, that should work.  Also you probably need to capitalize username in your $row['username'], since it's capitalized in the db...

 

edit: however, since you are using rhodesa's join, the results will look something like this:

 

Request User_id Id Username Password

blah1      1      1    joe        123

blah2      1      1    joe        123

blah3      1      1    joe        123

etc...

 

If you're just wanting it to return this:

 

Username Request

joe          blah1

joe          blah2

joe          blah3

 

Use something like my query

[ngreenwood6]

yeah sorry that was supposed to look like

while($row = mysql_fetch_array($results)
{
echo $row['Username'];
}

 

Thanks for the help

[premiso]

<?php

$query = "SELECT u.username, w.request FROM Work w LEFT JOIN Users u ON w.User_id = u.Id ORDER BY u.username";

$result = mysql_query($query)

while($row = mysql_fetch_assoc($result))
{
      if ($row['username'] != $lastUsername) {
           echo $row['username'] . " has<br />";
      }
      $lastUsername = $row['username'];

      echo "a Request of " . $row['request'] . "<br />";
}

?>

 

Should display it so that if you grabbed multiple usernames, it would show it for each username.

[rhodesa]

yeah sorry that was supposed to look like

while($row = mysql_fetch_array($results)
{
echo $row['Username'];
}

 

Thanks for the help

 

couple tips to save your sanity...

-don't name a field 'date', as it is a Reserved Word: http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

-keep everything lowercase. all table names and field names

-use and underscore as a logical separator (aka date_added)...you are already kind of doing this

-use a prefix on your field names so you can tell them apart. then you will never have collisions with common fields like 'id' or 'title'.

 

if it was me, my tables would look like:

DB: normal
Table: users
  Row: user_id
  Row: user_username
  Row: user_password
Table: work
  Row: work_id
  Row: user_id
  Row: work_request

 

...those are my personal opinions i've gathered from my experiences. i hope this doesn't start a standards/opinions war....

[.josh]

...those are my personal opinions i've gathered from my experiences. i hope this doesn't start a standards/opinions war....

 

Everybody knows the standard is to always capitalize variable letters that correspond to your favorite Playmate's last name.  You noob.

[rhodesa]

...those are my personal opinions i've gathered from my experiences. i hope this doesn't start a standards/opinions war....

 

Everybody knows the standard is to always capitalize variable letters that correspond to your favorite Playmate's last name.  You noob.

 

i thought i saw that on wikipedia somewhere, so it must be true. i just couldn't find it right now to reference it