[Setting up foreign key]

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?

[Setting up foreign key]

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?

[transfer of form data]

@Phi11W 

Quote

But if you validate and then redirect, then the page at the end of that redirection must repeat the validation, otherwise someone could send data to it directly, bypassing the validation. 

A n EXTREMELY valid point.

Quote

do you validate the form value submitted from the HTML "select" list that you sent?  You probably should ...

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.

[transfer of form data]

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?

[transfer of form data]

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.

[transfer of form data]

Quote

What second click?

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.

[transfer of form data]

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

[transfer of form data]

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.

[transfer of form data]

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?

 

 

[Database reference material]

It's starting to make more sense.

[Database reference material]

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.

[Database reference material]

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

Quote

Unless you have a good reason not to, a 1:1 relationship usually indicates that you’d be better off combining the two tables’ data into a single table.

Does this seem like an accurate statement?

[database structure]

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.

[database structure]

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?

[database structure]

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?

[working with time/date]

@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)

[working with time/date]

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? 

[Trying to avoid problem with type=color]

I recently had occasion to use

<input type="color">

It seemed like a nice idea until I viewed it on an IE 11 browser.

Since IE11 does not support the tag, it provided a text input instead of the COLOR input functionality.

The input field CAN be used as a substitute as long as acceptable color NAMES are typed, but the field size is much wider than it would be as type=color in a newer browser (and that alters the page layout).

Since a user COULD be using an older browser, it seems that controlling the WIDTH of the input should solve that problem.

The real issue is a way to create a script to determine whether the setting adjustment is even necessary.

Something like

if (input [type='color'] != a cute button with a color graphic) {
  input.width = 10px;
  }

Insightful responses, please.

PS: While on the topic, does anyone happen to know the DEFAULT settings for input="text" with regard to the height, width, font size, font family of the field?

 

 

 

[using a readfile() value as a variable]

@Barand Thanks.

But this solution looks like it will only identify one (the first) instance of cat.

Suppose the file contained assorted sentences of "Funny things my pet does."

While each paragraph will contain a sentence "My [type of pet] does this silly thing [period]." not every paragraph will contain "cat" because a "cat" is not the only pet.

 

[using a readfile() value as a variable]

This is why I inquired about the loop.

[using a readfile() value as a variable]

If "cat" is found, then I want the entire sentence that contains cat.

I imagine I can find a way to get from "cat" to a period (at end of sentence).

[using a readfile() value as a variable]

I see how substr_count works.

But I want to add to it  with

echo "Occurrence $cnt says" . $blahblahblah

Is there a built-in function to use for this? Or do I need to create a loop like

if( i=0; i<$cnt; i++){
  //if this position was already used then continue
  //otherwise, i assume i will get there same data i times
               
                 }

to get a complete listing?

[using a readfile() value as a variable]

Thanks.

It's a great starting point.

And yes, I'm very familiar with the manuals string reference pages (and have found them helpful in the past).

Let's see where all this takes me next.  LOL

[using a readfile() value as a variable]

@ginerjm I think I became intrigued with querySelectorAll.

Having said that, what is the best way to move through the data (most likely as a string) to achieve an accumulation of like instances.

eg: find all instances of "cat"

Do I count the line that the first instance occurred on and create a clause to search for an instance where $line > $the_previous ?

 

[using a readfile() value as a variable]

Some good information that I will have to try out.

Since I am already somewhat invested in the JavaScript method, it will be interesting to put them side by side to see if I get identical results.

Since I need to"read" the file from the server, it would be much cleaner to keep this completely within a PHP format.

Thanks for the help.