markifornia

my first guess or place to tinker with is by using INNER JOIN in your query by selecting the jobs table and using the WHERE clause specify which id you want.

i did some research on cacade, i found the advantages to be very interesting.

ok thanks bahaa, took me a while to understand as I am not using InnoDB. In any case, I will try completing my script and I will be back here with the results. thanks guys.

yes please

can you put your admin table query here too - thanks.

I am talking about your method not Hartleys. When you have created_by, modified_by fields on tables, is the admin_id related to those tables?

Ok thanks for clarifying Hartley's suggestion - I now understand what he was talking about. Here is how I understand Hartley's method below: mod table mod_id client_id (FK) location_id (FK) website_id (FK) image_id (FK) I would like to also understand your approach though. Explain your method - did you add at the admin user id to each table (the tables that contain the created by, modified by fields)?

thanks bahaa, i thought it was possible. though through hartley's example there are alternatives. bahaa - do you have the user admin id foreign keys in those tables? Are they related? This would put the nail in the coffin as far as my initial question(s) are concerned. If not, then I'll have to find a solution thanks, Mark

After some more thinking, I realized that when a table is updated by an admin user, upon update, I can create a query that can delete the existing user that initially created the record, and then insert the new admin user who has edited the record. In a way he will be the new owner of the record until someone else has updated it. I will have: added_by (this is created initially when the records is created) modified_by and last_modified (done through what i said above) Thoughts on this?

hi hartley, i get an idea of what you mean. I just wanted to get a visual of what how a client_mods table would relate to normal tables. For the sake of simplicity, one example is fine. If I understand correctly, see example below: admin table admin_id admin_firstname admin_lastname admin_username admin_email client table client_id admin_id client_company_name client_mod table client_id last_modified date_created location table location_id client_id location_street_address location_city location_state location_zip location_mod table location_id last_modified date_created

Sorry to drag this out, but I must say that most of my questions have been answered. I am not clear on what you mean by creating another table called client_mods. Which table(s) would this relate too? Wouldn't I have to add those fields(last_modified, date_added, created_by) to each and every table? admin admin_id admin_firstname admin_lastname admin_username admin_email

@hartley - thanks for clarifying the movies-actors table, as it was a very unique example in the book. It was not clear to me it was a many-to-many relationship. But if the movies-actors table is many-to-many as shown in the book, then the wouldn't you say the the database design here is incorrect? That it should be converted into one-to-many? The admin table is really just several select users that can edit ALL the tables. Lets say you, me and bahaa are managing this tool and want to edit the clients, location, website and image table. We would all be admins, and I would like to know who added or updated each table. I would think that an admin table is required for this, should it not? I will have multiple images, locations and websites for one client. But I do understand your point, that I can combine them given that there will be only one image, website and location per client. Hartley I do have a question still regarding how I can find out which table was modified, added or deleted by an admin user. I have explained why the admin table exists earlier. I hope you understand its purpose. Let me clarify that clients will not be touching this table nor its front end interface, but administrators will be constantly adding new records to these tables. I hope this is clear. So in order to find out who edited each table, I would need admin users created. Let's say I have an admin_id=1, bahhaa admin_2, you have admin_id=3. When any records are added to any table, I would like to know who worked on it???

CreationDate CreatedBy LastEdited LastEditedBy Should I add admin_id to the location, website, and image tables as well? How would I be able to know auditing information from these tables?

I like the auditing fields idea and will add. Is it correct to have the admin_id in the client table ?

Please see revised diagram above. Thanks you to you both, I have taken your advice into consideration during my revision. @Hartley - I should have been a lot clearer with what I am trying to construct. I am building a management tool to organize my clients. Lets say I have a client named "Blizzard Inc.". Blizzard would have multiple locations throughout the United States, multiple websites (starcraft II website, diablo website, and world of warcraft website are their popular games), and then some images to represent each client - perhaps a blizzard logo and some nice character art work. Also the cl, cw, and ci are just short for client_location_id, client_website_id, client_image_id. Those letters were confusing, I should have spelled it all out. In any case, the diagram is now revised. That being said, I was confused as to why on chapter 9 there is a movie-actors table inbetween the movie table and the actors table. That is why on my first chart, I have so many tables. @bahaa - Thank you for the additional comments to add to Hartley's suggestion as well, your advice is very similar. The cl_id in the clients table was an error and should not have been there. It looks like I'm getting close to getting this diagram to work before I start populating and testing queries. I did notice in both your comments there is something missing, maybe I missed this important piece of information: Why isn't the admin_id a foreign key to the clients table. I do have to mention though, that there will be multiple clients (blizzard inc, rockstar games, sony online entertainment, trion worlds etc). I would like to know which admin created and/or modified each table. Should the admin_id be a foriegn id to each and every table now? Thanks again. Mark

I am not sure if this would be better suited in another forum, but since I am working through this book and understanding the normalization process/ database design, I went ahead. I am working on a client management tool to organize some of my clients. The database flowchart below is a rough sketch, it may or may not fullfill the normalization process which is expected. I am still premature in this area (the normalization process), so bear with me. See below a database diagram I have created for a client management tool that will hold a table for clients. The clients table will have multiple locations, multiple websites, and multiple images. Based on the example given on chapter 7, (I believe, its chapter 7 where it talks about the normalization process. My apologies, my book actually wrapped in half, and half of it is somewhere.) it is the movie- actors example. You'll see I have created: client_location, client_website, and client_image I am unsure if these tables are necessary intermediaries (at the moment I'm still learning normalization). However, some advise would help me understand why they are necessary (or not?) Based on my understanding the "client" table is also an intermediary. So if I understand correctly, there are two intermediary tables? Is this okay to do? Thanks appreciate it Mark

This is focused on the information int he gray box ion page 307 - Secure Folder Permissions. As I understand it. (A.) /Users/username/uploads - more secure (B.) /Users/username/sitename/uploads - less secure Question: Where is the web directory, /sitename/ ? If this is so, and 777 (or 755) permissions is set on it, then (B.) and all the files inside of this directory are exec, read, writeable and susceptible to malicious users. Would (A.) then be the accurate example of placing the uploads folder in a secure area of the server? I am using linux: Debian GNU/Linux 6.0 PHP Version 5.2.17 Thanks, Mark

[u][i]CREATE VIEW the_name_of_the_view AS[/i][/u] SELECT * FROM table1 I'm still on chapter 10 of the book. Are creating views addressed in later chapters? As easy it is to create as mentioned, I would like to learn about it in further detail. Ofcourse, finding it in this book is my first place of preference. Thanks, Mark

Thanks bahaa, I've crossed this somewhere in the book, better explained in the last chapter I think. I will study this further when I get to it.

Masterlayouts, I am only inclined to relate the term transactions with something having to do with ecommerce. Does the transaction involve using the session id? It seems that when recording a post data (or many) per unique user, utilizing the session variable is required. Is it not?

Back to Paul's method, I didn't see this at first, I missed something very important: } elseif (mysqli_affected_rows ($dbc) === 0) { // use three equal signs so there is no confusion with a false return I've never used the three equal signs for comparisons, while the comment says so there is no confusion with a false return. Is this a special note for the programmer to avoid confusion with the code in question?

Masterlayouts, I like the extra step you've taken to investigate further. Paul's suggestion also works. But with your suggestion, it would make sense to combine the notification into one condition like so: if (mysqli_affected_rows($dbc) >= 0) I tried using your method on the edit_user.php script, and after a few tries solely on user experience I found that it can also be confusing if the notification says the submitted info has been edited even though it technically wasn't. I am just speaking in terms of user experience though. We must consider reasons why a user might be editing his/her information. Our first realization is that a user would want to update his information. However, there are instances where a user is unsure whether his/her info is correct to begin with. Therefore the user is forced to reset it. But with Paul's method, it gives the user a peace of mind that it was correct all along, and nothing had to be changed (or has been changed). Very confusing explanation, but to be considered. Both noteworthy scripts. thanks good work.

Very interesting. I think I was very curious about the ternary operator because on page 285 in making sortable displays, the operator is used again but instead it is passing values through another condition. Page 285: $sort = (isset($_GET['sort'])) ? $_GET['sort'] : 'rd'; All your comments made me understand this, thanks guys.

Ah, sorry misread. I do see now that the second function is just selecting the user_id.

Thanks Bahaa, I will use this method in my web applications.