Jump to content

foundherent

Members
  • Content Count

    13
  • Joined

  • Last visited

Community Reputation

0 Neutral

About foundherent

  • Rank
    Member
  1. Perfect thank you. My campus table is a bit more robust because it also has an address that can change. So the name change may take place at different times from the address change. The table you gave me earlier will still accomplish this for all intents and purposes, but it seems to be counterproductive to the idea of normalizing the data since the name and address would theoretically be duplicated. Should I disperse this data into 2 tables for the sake of normalization or am I overthinking it?
  2. Apologies campus_id is a similar table but I wanted to keep it simple. What you're defining helps me significantly, thank you. Forgive my ignorance, but this does create a table that does not necessarily have a uid in the instance of names being identical(which may be a possibility for my table). Is this ok for this instance?
  3. Apologies for any confusion - campus_id is from a separate table that I'm hoping will work similarly, but for this example we would be querying the client_id for the latest client_name.
  4. Good Afternoon Team, Did something incredibly dumb and am essentially starting from scratch. I'm hoping to get a stronger foundation this time around. My goal is to create a simple table like so... client_id|client_name|timestamp(not sure timestamp is even necessary?) The client_id is unique and never changes. Their client_name, however, does change! So I'm wanting to do this in a way that enables me to query the client_name for all timestamps before yyyy-mm-dd versus after yyyy-mm-dd. My previous structure(go easy on me!) is below... t1.client_id|client_name_assignment_id|timestamp t2.client_name_assignment_id|client_id|client_name|timestamp I'd then query the client_name based on the client_id and isolate the latest timestamp for said campus_id and it's respective client_name. All help appreciated!
  5. Thank you for this...a couple of questions. Can you explain the "na" prior to the first JOIN command? Also the 'latest' piece I can't find any documentation for this, This finally helps me to understand how to get the max piece to work - I was only able to get it to extract one row but the way you join these make perfect sense. This is very helpful thank you.
  6. Thank you for your input. Your description of the GROUP BY function is very helpful. ALL results ORDER BY campus_name_assignment_table.edited_timestamp DESC Except that once you have a particular campus_id displayed, you want the other campus_name_assignment_table rows for that same campus_id to follow. Am I right in assuming that this is the problem you are having? Not exactly and I apologize if the inquiry was not clear. My goal is to have a table display the most recent campus name assignments ONLY. So therefore, it would only show one campus name assignments per campus id. This name assignment would be selected by looking at each campus id and then selecting the name assignment from the record that contained the latest edited_timestamp for that respective campus id. If that makes sense? So campus_id 1 is named "School of Technology" but then renames itself to "School of Technology and Mathemetics". A query of this table would NOT show previous name assignments for each campus_id, only the latest.
  7. I honestly am not sure the answer this question because tbh I'm still not 100% clear/confident on the semantics but I am currently making progress so I'm optimistic! Essentially what I wanted to do was take the most-recent version of the campus_name_assignment for each entity campus_id....My initial inquiry understood this to be accomplished by the process of 'ordering' followed by 'grouping'. As shown in my solution above, the 'grouping' phase can include a sort/order parameter(new to me!), which then allows for me to sort/order by the timestamp within the query, thereby rendering a second 'grouping' phase in the encompassing query to eliminate the older timestamps from the new table! I imagine there's a smoother way to accomplish this but for my current mastery of sql this will work for now - any input is appreciated!
  8. Only halfway understanding why but I got the following to work...if anyone has any input it would be appreciated. SELECT * FROM( SELECT grouped_campus_name_assignments.campus_name_assignment, grouped_campus_name_assignments.campus_id FROM (SELECT campus_name_assignment_table.campus_name_assignment, campus_name_assignment_table.campus_id, campus_name_assignment_table.edited_timestamp FROM campus_name_assignment_table GROUP BY campus_name_assignment_table.campus_id, campus_name_assignment_table.edited_timestamp DESC) AS grouped_campus_name_assignments GROUP BY grouped_campus_name_assignments.campus_id) AS latest_campus_name_assignments INNER JOIN campus_table ON latest_campus_name_assignments.campus_id=campus_table.campus_id
  9. Good Evening Team, I have a simple client list for our schools and sometimes my schools change names but more or less remain the same entity. I sometimes need to refer to the previous name and sometimes the current. I accomplish this with the following 2 tables... campus_table.campus_id, campus_table(other static fields that are not relevant here) campus_name_assignment_table.campus_name_assignment_id campus_name_assignment_table.campus_id campus_name_assignment_table.edited_timestamp My goal is to query the latest campus_name_assignment unique to each campus_id. My latest attempt at a query looks like so... SELECT campus_table.campus_id, campus_name_assignment_table.campus_name_assignment, campus_name_assignment_table.edited_timestamp FROM campus_table INNER JOIN campus_name_assignment_table ON campus_table.campus_id=campus_name_assignment_table.campus_id I would like to ORDER these results by edited_timestamp DESC and then to GROUP them by campus_id. This does not seem to be working in any capacity. There may be some nuance tied to the fact that my version of XAMPP uses MariaDB and I found the following documentation on this, but either I don't understand it or it is incorrect. Any/all help is very much appreciated. I'm kinda frustrated that I cannot get the query itself to work correctly as it seems that the order piece is being ignored - I've tried workarounds such as the HAVING MAX() extension included after GROUP BY but this similarly does not seem to work. I've also tried to order the campus_name_assignment_table in an alias'd subquery but as soon as it gets into the main query it completely re-sorts. https://mariadb.com/kb/en/library/why-is-order-by-in-a-from-subquery-ignored/
  10. This explanation makes perfect sense but the concept of scope makes me very worried that creating two queries will not properly include the LAST_INSERT_ID correctly. Will test when I'm back on my system but if I'm not overthinking this aspect any elaboration will help prevent any further newbie posts. Thanks again for all the help.
  11. Good Afternoon Team, Am sitting with something simple using the language below. If I copy the echo output of my query as included below it works perfectly in phpmyadmin but doesn't work on a website. Variables all seem to echo consistently/correctly and POST checks seem to verify this is working correctly as well. I worry the error comes with the syntax I used in combining the sql queries. That, or perhaps LAST_INSERT_ID does not work in the php script as well as it does in phpmyadmin. All help appreciated. if(isset($_POST[`region_id`])) { $competition_id = htmlentities($_POST[`competition_id`],ENT_QUOTES,`UTF-8`); $division_name = htmlentities($_POST[`division_name`],ENT_QUOTES,`UTF-8`); $region_id = htmlentities($_POST[`region_id`],ENT_QUOTES,`UTF-8`); $division_edit_submit = "INSERT INTO division_table (competition_id,division_name) VALUES (`$competition_id`,`$division_name`); INSERT INTO region_assignment_table (division_id,region_id) VALUES ((LAST_INSERT_ID()),`1`)"; mysqli_query($connection,$division_edit_submit); echo $division_edit_submit; }
  12. Hello Team, My job changed a few years back but I'm once again trying to learn to become better with php. I wanted to catch up to where I was around this time a few years back and honestly the language is coming back quickly but the quirky bottle-neck errors are still lingering. Hoping someone can take a look at the simple page I created below - post/get/session globals do not seem to be working, additionally, the semicolon after echo command does not seem to end the php command on the semicolon(displays everything). It may be a browser or something more simple but am hoping someone can at least look at my code below to see if there are any red flags. <?php print_r($_GET); echo '<p>Hello World</p>'; ?> <html> <body> <h2>Campus Information</h2> <form action="campus_test_form.php" method="get"> Campus Name:<br> <input type="text" name="campus_name" required> <br> Campus Abbreviation:<br> <input type="text" name="campus_abbreviation" maxlength="8" required> <br> <input type="submit" value="Submit"> <input type="reset"> </form> </body> </html>
×
×
  • 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.