Jump to content

phppup

Members
  • Posts

    667
  • Joined

  • Last visited

Posts posted by phppup

  1. 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.

  2. 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.

  3. 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.

  4. 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?

  5. 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?

  6. 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?

  7. 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?

  8. @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.

  9. 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

  10. 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?

     

     

  11. 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.

  12. 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?

  13. 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.

  14. 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?

  15. 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?

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

  17. 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? 

  18. 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?

     

     

     

  19. @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.

     

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.