phppup

I felt fairly confident of my understanding and code for a second table with a foreign key, yet in testing, when I deleted a record in Table1, it did NOT get deleted in Table2 (which holds the foreign key that references Table1) I even went so far as to replicate the steps from https://www.geeksforgeeks.org/mysql-deleting-rows-when-there-is-a-foreign-key/ but the final steps for After Deleting did not match up ( as I continued to have 4 records in the subordinate table) because the record that should have been removed was still there. I have tried to troubleshoot and ran SHOW GLOBAL VARIABLES LIKE 'foreign_key_checks'; directly in MySQL at phpMyAdmin and received Variable_name Value foreign_key_checks ON as my result. Guidance, please.

@schwim I was conflicted because I've heard that $_SESSION might be easily compromised, however, I would think reposting the same data in HTML through a hidden input as suggested by @requinix suggested, could be even more vulnerable. How would I do it securely? I found it interesting that the next post after this one titled login user only one device at a time seemed to align with a similar vagueness regarding the implementation of whether a SESSION or a TABLE would be more effective as a Best Practice to handle movement of information. Remarks?

Within the quiz that I am assembling (which does NOT allow a user to return backwards), there are answers to previous questions For example, #1 ....what color was John's coat. #3.... what was the problem with John's blue coat? #4.... After Mary fixed John's torn coat pocket, where did they go? etc. At the end, the person will be asked, "Do you want to submit these answers or take the test again"? If they decide to re-take the test, then no answers will be evaluated. If they submit their responses, then the replies will be saved in the database. I see two approaches: either I can save each answer in $_SESSION and INSERT them ALL if the user clicks the "SUBMIT my answers" button OR, I can INSERT and UPDATE to the table after each question is answered and either DELETE the record or overwrite it as the quiz progresses, until they decide to choose SUBMIT. Which is the best approach? Why? Are there any other realistic options to consider?

I'm guessing that yes, it's the number of queries that at to load, and retrieving extra days is still better than an extra query. As for the diagrams, awesome of you. I thought there was a sql clause like SHOW (which I have not gotten to work) that could be used.

No problem. I just wanted to confirm that hopscotching through the tables regularly was the right way to go (versus a single table with so the necessary data parked and available). Perhaps I'm just thinking too "humanly" for setting up an "automated" system. So it's the number of queries that actually cause extra load? Is it preferable to gather all my data and then evaluate sql = "SELECT X FROM TABLE.... JOIN... JOIN.... JOIN if ( evaluate results to determine action... Or cascade through IFs that essentially create extra query trips $sql = "SELECT x from table1 WHERE if ( $x .... condition TRUE $sql2 = SELECT.. JOIN.. another condition... but has the potential to reduce the bed to access resources. Or does it not matter at all? PS: how do I extract the diagram of my db structure similar to what I've seen posted in this forum? _____ | | ----- ? Thanks.

No problem. I just wanted to confirm that hopscotching through the tables regularly was the right way to go (versus a single table with so the necessary data parked and available). Perhaps I'm just thinking too "humanly" for setting up an "automated" system. PS: how do I extract the diagram of my db structure similar to what I've seen posted in this forum? _____ | | ----- ? Thanks.

I'm getting accustomed to spreading my data over multiple tables, and trying to do it right. Assuming that several small tables i are more optimum than one big table (since there are less rows to touch - even if they are being ignored), is touching a table for a single column advisable? I know repetition is a no-no, but should oft needed data be stored together (even if unrelated)? Example: On login there is a check for user and password (from USERS). But then I want to make sure the account is active (it will expire after 30 days and days is stored with other timestamps) so check ACCOUNT activity. And that the user logged in within the last 5 days (check INFO) or used a PROMO table code etc, etc. Each of these tables needs to be visited to compare data. Is this the correct/best approach? Or is there an alternative way to make 'regularly required info' more accessible?

Thanks @Barand. I seem to have had it right, but obviously there is a wrinkle somewhere that I'll need to iron out while creating the PHP code. Along these lines, once the FK connections are established, will they enable a more direct INSERT of data from a form? Or do I need an INSERT statement for each table independently? Are JOINS only for data retrieval?

I think it's Maria DB, if that makes any difference. I don't have access to now, but when putting the code directly in as SQL through the admin access, I got a red dot and an "unrecognized expecting... " balloon message. I tried several variations to resolve the issue, but thought I'd better check my structure here (since everything else to establish the FK send pretty straight forward. Table1 id PK auto increment - users personal info - Table2 id PK auto increment favorite car year, make, model, color linkID FK references Table1(id) Table3 id PK auto increment favorite vacation location, hotel, duration linkID FK references Table1(id) My approach is that while tables 2 & 3 are separate from each other they are both connected to the individual user, and would connect to the respective ID. Is my approach valid, or do I need to connect the tables like a set of trains on a track rather than a motor boat with several water skiers?

It was so much simpler when I stored all my collected data on one big table (well, not really too big, actually) Now I am attempting to be more efficient by learning the proper use of foreign keys. Table1 id PK auto increment Table2 id PK auto increment linkID FK references Table1(id) Table3 id PK auto increment linkID FK references Table1(id) When I tried to implement this table structure, MySql would not allow me to create Table3. What am I doing wrong? Is my approach/understanding flawed? Do tables 2 & 3 need a primary key? Why?

@Phi11W A n EXTREMELY valid point. ABSOLUTELY!!!... but it's always a fair reminder to see. I believe this is why I ran into trouble initially, as I was not so keen on re-directing my data. It's been a few days since I had a chance to work on my code, but I think I'm going to try a hybrid that tests the code on the current page (so that PHP error messages could be visible on the same paged form) and passing the values for usage through $_SESSION. To your point, Phi11W, then someone sending data directly would hit a dead end because $email=$_SESSION['email'] and NOT the value of $_POST['email'] Any comments are welcome.

Does a user have to begin the form from the beginning if a field is invalid? If it passes as valid, then you redirect to another page?

I'm thinking it through. I actually may be able to adapt something with your suggestion that COULD do the trick. A little more thought and a review of what I've got and we'll see.

You may need to read my post that is at the START of this thread. That's where I describe the problem that I'm trying to resolve. Thanks.

I've generally stayed away from that methodology (not exactly sure of why) and used SELF-evaluating pages that retain the field data. ///I wonder if I can redirect my page to itself to recover from the "second click" issue that I'm having, although that might create new issues and more scripting (especially if I want the page to appear "user friendly") ////NOPE, that did not work very well

I've read that JavaScript is not a secure method to rely on for validation. I prefer to resolve this as a PHP problem rather than divert to JS as a solution.

I may just be having a mental block from reading the same reproduced examples online, but... As a concept: A form has several fields that are validated with PHP. They cannot be analyzed until the form is submitted, right? [assuming basic coding] So here's where my problem is: <form action="validation.php" method="post"> will continually bring a user to the validation.php page. If there is ONLY acceptable data, they can then be redirected to another page [mynextpage.htm], right? And if there is bad data, they will get a group of error messages and need to go back to the form. But I don't want a user to advance until all fields are acceptable, so I tried <form action= <? echo $validate; ?> method="post"> And then added a condition if($errors == 0) { $validate = "validation.php"; } It seems to work EXCEPT that the submit button needs to be clicked twice. My assumption is that the first click fills-in the variable, and the second click initiates the action. How can I make this work with one click, or better resolve this issue?

It's starting to make more sense.

I would have thought that less columns translates to faster/more efficient searching. If I have id, name, email, phone, birthday, favorite_color as fields then these are all 1:1. But if I primarily refer to id, name, and email, I would think a 1:1 with a table of "personal_details" would seem appropriate.

Are there any guides/tutorials online that you would recommend to upgrade my skills? I've found several but nothing that was spectacular. One site started Does this seem like an accurate statement?

I should auto increment my main table and then pass that id through to subsequent tables? In the past, I have used the id as a user_id as well. If I shouldn't store derived data (which I've seen before and applied) how can I create a system where the first person to complete all 4 questions is retained as cust#1, the second as #2, and so on? It seems a bit wasteful to seek the person that completed all questions in place 201 out of 2000 partial submissions instead of simply finding cust#201.

So essentially I'm overthinking and not effecting performance (significantly) by searching through 50 records looking for the 2 users that answered all 4 questions versus establishing a Table2 with only pertinent data?

After collecting basic identification info (ie.: name, email, phone) I am having users answer 4 multiple choice questions. After submitting an answer, the table will update the specific row of Table1. Not everyone will complete all 4 questions (for a variety of reasons). I want to have a separate table for individuals that have answered all 4 questions. My assumption is that it would be more efficient to use a separate table of "qualified individuals" for grading and contact than to unnecessarily evaluate data that is inapplicable. Perhaps something like; if ($Question4 != ""){ SQL = //copy the row from table1 to table2 } The REAL issue here is whether I need to list each column name EXCEPT for the auto incremented id OR is there a way to SELECT * (except for column 1 which is id) ? This will provide me with a table of only fully completed surveys with id numbers that are different from Table1, but sequential. Or should I just maintain Table1 and not be concerned with rows of incomplete information? Am I overthinking this? Or making progress on my approaches?

@Barand Thanks, I have that. But does MySql/PHP have a built in "understanding" when looking for submissions on May 5, 2022 between 9:05:00 AM and 1:30:00 PM? Or should I just record time the time data as a 24 hour clock without colons and search WHERE time > 90500 && time < 133000 ? (As opposed to h:i:s)

I am setting up a form and plan to have the data stored in a table. When I insert the information, I want to include the time and date of the submission. Is there a best practice for apply so that pitfalls are avoided? If I expect to want to review instances that occur from 9AM to NOON, or from one date to another, is it unwise to use colons, slashes, hyphens?