ajoo

Hi Psycho, Yes you were right. Also the temporary table does not show in the phpadmin tables. However queries excuted against the tmp table work !! So the table is created and somewhere there but not visible !! Thank you.

Hi Psycho ! Thank you for the response. Yes I did think about that but failed to see the temporary table tmp_result in the tables in phpmyadmin. So maybe it doesn't show out there at all ! I'll run a query against it anyways and revert. Thanks you.

hmmm even this works and displays the result set correctly. SELECT st_id from (( SELECT mr.st_id as st_id from mr,ar where mr.mid = ar.st_id and ar.aid = 1) UNION (SELECT ar.st_id as st_id from ar WHERE ar.aid = 1 && ar.role <> 'master')) tt It's only the CREATE TEMPORARY TABLE ... bit that returns an empty result set ( zero rows ).

Hi all ! while this query (with table names shortened for clarity) works great and returns a result set just fine, SELECT mr.st_id as st_id from mr,ar where mr.mid = ar.st_id and ar.aid = 1 UNION SELECT ar.st_id as st_id from ar WHERE ar.aid = 1 && ar.role <> 'master' this following one executes and returns a zero result set. CREATE TEMPORARY TABLE tmp_result SELECT st_id from (( SELECT mr.st_id as st_id from mr,ar where mr.mid = ar.st_id and ar.aid = 1) UNION (SELECT ar.st_id as st_id from ar WHERE ar.aid = 1 && ar.role <> 'master')) tt I wish to use the result set obtained from the first query to join with another query a few lines away. Please suggest what is going wrong here & if this approach is fine and workable. Thanks all !

Hi, I think you are getting it wrong still. Guru Jacques suggested that you add a single table containing all the skills and associate users to those skills.

Hi Kicken, Thanks for the reply. I guess I need more practice before I would be able to figure out which tables require to be split and which not. Database normalization and the desire to do things right have me all confused. I will keep in mind the two points that you have enlisted above before I decide on splitting a table next time. Regarding the other example, it is similar & so reinforces what was proposed by Guru Jacques. I am also using a common uid (as in the three of your tables) from the user_detail table for the different types of users in my tables. Thank you very much.

Hi Guru Jacques, Why am i splitting the database !? Good question sir !! Just because I thought that it could be segmented further and normalized. you know like separating the address from the contacts and so on. Do you think I need not split it !? is it normalized as it is and needs no improvement? I'll be happy to let it be !! I can start working on the tables you suggested. Thank you.

Hi Guru Jacques, I have the following table user_detail(uid (PK, AI), reg_date, role, fname, lname, dob, age, gender, fathername, spouse, add1, add2, city, state, country, zip, email, cell, phone, user_status) I was using a single query to insert these values in the table as follows $query = "INSERT into user_detail(reg_date, role, fname, lname, dob, age, gender, fathername, spouse, add1, add2, city, state, country, zip, email, cell, phone, user_status) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; Now I am splitting the table user_detail as follows: user_detail(uid(PK) ,fname,lname,dob,reg_date) user_add(uid(FK), house_no, add_street, add_landmark, street,city, zip, state) user_contact(uid(FK), email, phone, cell) Since all these values are being spewed by a single form and must be stored simultaneously would i not have to use a transactional query for inserting values into the 3 different tables? So I think this is the first example of a multiple insert that I was talking about in the earlier message. So now I think that a transactional query is the best way to go about inserting the data in these tables. Or is there another easier way to form an insert query to accomplish this? Thank you very much !

Hi Funster and Guru Jacques, Thanks for taking out time to reply, I asked that generally, but once I get implementing this, I'll get back with any examples that I come across. Those are two lines for the #xxx street and maybe landmark. A person can use one or both. Maybe I should use just one text field for that. Should the the field approved_by_manager In the AM table, and the fields approving_assistent_manager_id, approved_by_manager in the DM table be the actual AM_ID and DM_ID from their respective AM, DM tables or should they be like a 0,1 flag fields If they should be the AM_ID and DM_ID respectively( which is what I feel they are) shouldn't there be another field like recruited (a recruitment status field) which is a 0,1 flag field indicating that person is recruited or not ? Thanks again Funster and Guru Jacques.

Hi all, Generally when a table is split into many to enforce normalization won't it require too many inserts commands to insert that data into those tables? Won't that dictate the use of transactions, almost always? Is there a simpler way to insert the data simultaneously into multiple tables? Specifically I have a table user_details (or you may say staff details ) user_details(uid, fname, lname, dob, gender, father_name, spouse_name, add1,add2,city,zip,state,country,email,cell,phone,user_ststus) uid is the PK and all these fields come from the same form and go into this single table. If I wish to keep the address (2 address fields, city, state, zip, country ) all in the same table then is there any scope for this table to be normalized further? And now, again if I understand correct, Guru Jacques suggests that I should use the uid to create 2 more tables for the assistant_manager(user_id) and the deputy_manager(user_id) and probably use those tables for approval of staff to enforce referential integrity. I would also like to confirm if, in the table applicants(application_id, last_name, first_name, ...), applicant_id would also be the same as staff_id. (Because the user_details form is being submitted by the AM or DM after that person has approved of him / her). The first leg of approval is complete since the form is being submitted. If this is wrong then what should be the applicant id and from where should / would it originate? Thanks all !

Hi Funster ! Thanks for the reply. I'll try and learn more on DB desgin and normalization. Did not really get what Guru Jacques was trying to tell you. I am sure he will respond. But from your reply I guess he was hinting at maybe just a single table. I am not sure if my assessment of his reply is correct.er I am sure he'll revert in good time. Yes ! Guru Jacques is a multi-million dollar Guru ! Has been extremely helpful ! You too ! Thanks again Funster.

Hi Funster and Guru Jacques. Thanks for the reply to you both. @Funster : I have almost created sample tables like you indicated and was studying the relations between them. I must admit that I have very little knowledge of good table designs. In fact I have been studying and trying to understand data normalization. I do feel that my tables need a lot of that. In fact I also wanted to ask how bad is it to have tables that are not normalized. is it a trade off on efficiency or can that bring down an application. It doesn't seem to but then like I admitted, I know very little of DB design. @ Guru Jacques : I haven't really understood what you were trying to point out to Funster. I have read your reply 3 times and I am still trying to understand it. Please elaborate, if possible with a small example. More than happy to be schooled by both of you ! Thank you both very much.

Thanks funster, I figured out the FK and PK once I sat down to draw the map for the tables and relate them. I'll try out the example once I have created the tables and revert. Much Obliged. Thanks.

Hi Funster, Thanks for the examples. I'll be grateful if you can explain a bit of what you have done. I don't understand for ex what's PK or FK Please elaborate a little. Also maybe you could explain how I might be able to use these tables to accomplish the two examples quoted above from my last post. Thanks loads.

Hi Funster, thanks again for that quick response. The approval is hierarchical. It has to be approved by the AM or Dm which ever initiates the process and then go upward. All I need is to be able to design an efficient link back relation using one or more tables so that for example if M needs to look at the performance of the team of a given AM namely, 1. All DM;s of the AM and all executives of those DM's // Complex 2. All executives of the AM or in case of a DM // Simple 1. all Executives of that DM It should be easy to query the database. Any help is appreciated. Thanks ! P.S. I replied before your second reply. Will check that and revert again.

Thanks funster and requinix for the reply. Requinix's rightly said " Sounds like it requires approval from both the M and AM. So two flags, ". It's a kind of double check you may say. Actually I am not exactly sure at this stage what data would be involved for performance. So I just took one bit of data P just to know how that one parameter would be handled and it would give me the idea to handle other data. Sorry that's a mistake on my part. No the manager M will not directly recruit the executives. Once again right now I am only interested in the minimum fields that would Grant the user Authorization ( a flag ) as well as take into account the user granting the authorization. @ Requinix. Very true Requinix. The idea is to backtrack the hierarchy to the Manager in each case. I had the simple case in mind like this :- ExecID AP_M AP_AM AP_DM ================================== 11 1 1 1 recruited by DM 12 1 1 recruited by AM but this does not really backtrack the hierarchy. It just shows who all approved rather than who all had to approve. For eg in the second case how can anyone know just by looking at the data that the whether the DM has not approved or the approval was not required. I hope I am able to explain myself. Besides if only one in a hundred cases does a DM recruit a franchisee, it will result in an entire column being wasted. (I think the term is that data is not normalized). Kindly help me grapple with this. Thank loads.

Hi all, I wish to define a hierarchical relation in a table or set of tables. I'll use an example to describe the problem. In a hotel there is One Manager(M), Assistant Managers(AMs) and a Deputy Managers(DMs) and each of them can recruit executives for the hotel. 1. The Manager(M) can recruit the AMs and the DMs. 2. Further the AM can recruit a DM which must be approved(AP_M) by the Manager. // AP_M -> field for approval by the Manager 3. The AM can directly recruit executives and they must also be approved by the Manager.(AP_M) 4. The DM can recruit executives and they must be approved by the AM (AP_AM) and the Manager (AP_M). // AP_AM -> approval by AM Further : a) The Manager can view the performance(P) of all the AMs, DMs, and executives in the hotel whether recruited by himself or by the AMs or DMs. b) An AM can view the performance (P) of all the DMs that he recruits, all executives recruited by his DM's and all executives recruited by the AM directly. c) The Manager cannot recruit executives. That is done by the AM and DM. Please can someone help me define the table/tables and help implement these relations in them. Thanks all!

Hi Guru Jacques, hmmm ofcourse you are right !

Hi all !, I was just wondering if it's possible to autoset the value of a field in mysql table based on the value of another 2 fields values. for example if I wish to implement AND logic on some fields : F1 F2 F1_F2 ======================== 0 0 0 1 0 0 0 1 0 1 1 1 ===================== If I have columns F1, F2 & F1_F2, can the mysql database auto set the value of the F1_F2 based on the values of F1 and F2 as in the table above. If so kindly show how it can be done. Please note that the values of F1 and F2 will be set programmatically using PHP. Thank You.

Thanks Guru Jacques for that insight on other security areas. I would like to clarify if it would be OK to have the captcha ON from the very beginning on these pages since we do not want the user to know if the given email ID is registered or not. I mean we don;t wait for n number of false tries before the captcha is displayed, we use it for every request. Thank you.

Hi Guru Jacques, Thanks for the reply. I must be getting paranoid about security. Now that you mention it and I am thinking about it I am not so sure which threat I am worried about. I think I had in mind someone impersonating the pages related to password resetting, be it the password email request page or the page in response to that request. Since these pages are public and can be accessed directly I was getting a bit worried. If that's OK so long as the form and fields authentication / validation is good and if you think there is nothing else that needs to be looked into then I am good. Thanks.

Hi all ! I would have liked to continue this question on my previous post but since it became too long I thought I'ld post a new one. I would like to add the following bit of code on my reset page $current = 'http://' . $_SERVER['HTTP_HOST'] . $_SERVER['REQUEST_URI']; if(isset($_SERVER['HTTP_REFERER'])) $referrer = $_SERVER['HTTP_REFERER']; if ( $referrer === $current ) { }else { } to ensure that the page is being called from where it should be called. Is this OK or is there a better ( read more secure ) way to do it? (I think I read in one of the posts, quite some time ago, that this was not altogether secure). Since the password reset page is publicly accessible what other security concerns can turn up because of that and what care should be taken. Thanks all !

Hi Psycho, Thanks for the advice. I think you are referring to a second channel of authentication like maybe via an sms. As of now, I would be more than pleased if I can just get this logic to be implemented correctly. Certainly I would like to implement that at a later stage maybe. I have that in mind already. Thanks again !

Thanks funster for all the help and discussion & for being such a sport

Hi Guru Jacques and Funster, Thanks for the replies and the discussion. Your discussion on the various techniques will go a long way to clear the doubts of many New comers like me, especially for issues that are related to security so closely. Thanks very much. I think I will stick with the advise offered by Guru Jacques since that relates more to the security of applications on the internet. I think I have made up my mind, with the input from all who replied to this, to keep it simple, all in one table. This is how I think I will do it: Have just one field for the hashed token and time stamp which I guess can be used both for account activation and password reset. There can be no confusion in the tokens since the password reset can only take place after the account activation. Have 2 boolean fields: one for account activation and one for password resetting. The password reset field can be toggled for each valid password reset request. And later reset back once the password has successfully changed. Once the account is activated i'll change the related token field to null. Same for password reset. So that would be a total of 4 additional fields in the table. If there is still a flaw here in the logic, kindly alert me. Thanks a lot every one.