Announcement

Collapse
No announcement yet.

selecting specific data, using sql builder, help needed and appreciated

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • selecting specific data, using sql builder, help needed and appreciated

    hi guys, i am tying to have simple school mysql db with 15 tables (15 class rooms), each table has unified fields/simple data like this: ID, DATE, TIME, CLASSROOM_NO, STUDENTS_ALL, STUDENTS_CURRENT, COMMENTS

    approach is to be able to keep updating this data all the time through a form that increment that db, that is fine, it is working fine.

    in a grid application, i want to display the data from each table of those 15 tables, each one only the latest raw added to database... so if in classroom table no1 i have 10 records, it will show me the latest updated record only... same thing for class rom table no2...etc up to no.15

    i could select all tables in sql builder but i am not sure how limit the view of the rows to show me only one latest update of each table... so please help

    last time ago, i used simple php display for one table similar to this, and worked, it shows the latest record of one table only, but i want them show in a grid application like a table

    Code:
    $result = mysqli_query($con,"SELECT id, date, time, classroom_no, students_all,     student_current,comments FROM classroom_1 ORDER BY id DESC LIMIT 1");

    any idea is appreciated

  • #2
    Hi,
    I'm sorry to say but having 15 tables with the exact same structure is a very bad idea. Sooner or later you are running into trouble.
    You are at the first bump.

    Since you have a classroom_no which identifies each room uniquely you should have all your assignments in one table.
    You should also add another field (type timestamp) i.e. last_edit and set it's default to CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP
    to have it filled automatically by MySQL.

    To get the data you want, you just need: SELECT id, date, ..., MAX(last_edit) FROM your_table GROUP BY classroom_no

    Hope this helps

    jsb
    Last edited by jsbinca; 05-27-2014, 01:15 PM.

    Comment


    • #3
      thank you dear, you are right, you idea and approach is absolutely correct.

      i was thinking to separate the tables so i can have update for each classroom alone, i may need to update one of them per day, or 4 times per day for another class room... so i can store that data in that table, then later on display the only latest update of that table by LIMIT 1 which is working just fine

      now to have the select to get from each table the latest row in scriptcase seems complicated, even i don't think it is like that, i just want to use the same database i have with all its tables, because the forms are not build using script case, i am trying to add one grid only to select for me the last updated row from each table and show them as a grid... so any idea without changing the current DB is appreciated, i don't want to change everything just for this, specially that the scope of this is not going to change or upgrade later on... otherwise, if i see the need or i will have to update again, it is much better to use the way you are talking about...

      nobody had to select data using sql builder from more than one table yet keeping the last updated row only? guys? any hint is appreciated here


      Mike

      Comment


      • #4
        SELECT 1 as id, date, time, ... FROM classroom_1 WHERE id = (SELECT MAX(id) FROM classroom_1)
        UNION
        SELECT 2 as id, date, time, ... FROM classroom_2 WHERE id = (SELECT MAX(id) FROM classroom_2)
        UNION
        SELECT 3 as id, date, time, ... FROM classroom_3 WHERE id = (SELECT MAX(id) FROM classroom_3)
        ...
        UNION
        SELECT 15 as id, date, time, ... FROM classroom_15 WHERE id = (SELECT MAX(id) FROM classroom_15);


        If this doesn't work in scriptcase (it's horrible anyway) and don't want to change your db, create a temporary table with the same structure as your other tables
        and base your grid on it.
        Iterate through a loop from 1 to 15 and insert the records you selected from the other tables into the temporary table.

        jsb

        Comment


        • #5
          If the key is increasing you can also do a select order by something descending. Then the most recent record is the first. So a limit 1 might do.

          I try to understand what you are trying to achieve. But I have to agree with jsb, using 15 tables is making things more difficult. You can easily update a table by key which would be classroom.

          But I guess you have a good reason to do it this way.
          Albert Drent
          aducom software netherlands
          scriptcase partner, reseller, support and (turn-key) development
          www.scriptcase.eu / www.scriptcase.nl

          Comment


          • #6
            50% or more of the work is a good normalized database.

            i was thinking to separate the tables so i can have update for each classroom alone, i may need to update one of them per day, or 4 times per day for another class room... so i can store that data in that table, then later on display the only latest update of that table by LIMIT 1 which is working just fine
            Sorry, but I still don't understand, as jsb and aducom sais, why you are not using a single table. You will be more happy. You just need to have a field to differentiate each classroom. If you need to add more classrooms, you just need to give it a value and store on same table. What will happen if you need to add 2 more classrooms? You will need to add to more tables and change all selects? Bad approach.
            /Giuseppe

            Professional Scriptcase Services
            Some Customers opinions

            Comment


            • #7
              ok guys, i have to change the database design because it was decided to add more values for each classroom! and i really need your help to design it, i am not expert to use the partitioning or other expert stuff, i believe scriptcase can do it, in an easy way…i just want to know how to start from the beginning again
              Here is my full scenario, in brief
              1- i have the paper form/report which I could made as electronic, easy, a classroom that can be submitted anytime by teacher to school administration – example:
              · Classroom: (1 to 15) e.g. 6
              · Total students: XX (fixed number) e.g. 20
              · Available students: xx (variable) e.g. 15
              ·
              · Absent students: automatic simple calculation (total – available) e.g. 5
              ·
              · Students took permissions: xx (variable, part of the absent number) e.g. 3
              · Student without permissions: simple calculation (absent – took permissions) e.g. 2
              ·
              · Date of this update: date - automatic
              · Time of this update: time – automatic

              2- Updates for each classroom vary, may be done one per day, maybe 4 times per hour... (i know it doesn't make sense, but please bear with me)
              3- The school administration do rely on the latest report update from each classroom teacher, hence, they will look at the latest report that submitted by each teacher...
              Example:

              Class no total available absent With permission Without permission date time
              6 20 15 5 3 2 28/5 11:11
              6 20 14 6 4 2 28/5 12:00
              6 20 11 9 5 4 28/5 13:00
              So, what I’ve done is to select the data, FROM classroom_6 ORDER BY id DESC LIMIT 1" that works fine in display.php (away from scriptcase) – now administration can see classroom_6 from display6.php and will give them only the latest update…

              4- i have 15 classrooms
              5- the data should be kept anyway in the database for statistics part later on that will use sc to have for each classroom how many students were available per day ( I will take the average per day if more than one update was done in that classroom…)

              6- I’ve done this for the whole 15 classrooms and all is fine, you can view the latest update from each classroom… and I can get statistics using sc….
              Need now is to have them all as a table to display only latest update from each classroom update….

              Example:
              Class no total available absent With permission Without permission date time
              1 20 10 5 3 2 27/5/2014 11:11
              2 25 14 6 4 2 27/5/2014 12:00
              3 20 11 9 5 4 27/5/2014 13:20
              4 15 9 3 1 2 27/5/2014 11:00
              5 22 20 2 1 1 27/5/2014 12:00
              6 20 12 8 5 3 27/5/2014 13:13
              7 34 31 3 2 1 27/5/2014 14:10
              8 21 20 1 0 1 28/5/2014 12:10
              9 16 14 2 2 0 28/5/2014 13:44
              10 11 10 1 1 0 28/5/2014 8:60
              11 15 11 4 0 4 28/5/2014 11:00
              12 22 19 3 1 2 28/5/2014 12:12
              13 17 14 3 2 1 28/5/2014 13:19
              14 31 27 4 3 1 28/5/2014 12:11
              15 11 7 4 2 2 28/5/2014 11:15

              So, do you think we can go with this db structure to achieve this? Or I have to do a complete change?

              Please again, I am sure you will give me strong ideas, but be sure that my experience is not that much so if there will be partitioning or “key” and this things, then I will be lost


              Any help or idea (with details) is highly appreciated

              Mike

              Comment


              • #8
                For the complexity you are talking, I think this approach is better.

                Now you have your table, if you want, you can create a VIEW to just show one record for each classroom and each day with average of students
                /Giuseppe

                Professional Scriptcase Services
                Some Customers opinions

                Comment


                • #9
                  well,
                  thanks for reading all that, appreciated your step in

                  i think even the create a VIEW is new to me, so i will try,

                  and obviously waiting for jsbinca as well, was the one who brought to my attension that i will get in trouble soon or later, also the big Albert can give a hint on how to use scriptcase to achieve this all of you guys are lot of help

                  Comment


                  • #10
                    A VIEW is a not physical representation of data. It allows to you to do something like SELECT * FROM VIEW_CUSTOMERS where VIEW_CUSTOMERS really is a SELECT.
                    Imagine you have a customers table, and you need differents ways to show data. You can do this by using views.

                    http://www.w3schools.com/sql/sql_view.asp
                    /Giuseppe

                    Professional Scriptcase Services
                    Some Customers opinions

                    Comment


                    • #11
                      yes i was reading and youtubing all about create view since you posted your last reply thanks a lot, i think this will work for me, and yet it is avaialable in scriptcase

                      well, then you agree with me to keep the normalization up to a table for classroom? instead of having only 3,4 tables and use complicated sql queries to select multiple data, right?

                      i hope jsbinca does agree so i get rest and continue with this setup

                      Comment


                      • #12
                        Originally posted by itsme3 View Post
                        yes i was reading and youtubing all about create view since you posted your last reply thanks a lot, i think this will work for me, and yet it is avaialable in scriptcase

                        well, then you agree with me to keep the normalization up to a table for classroom? instead of having only 3,4 tables and use complicated sql queries to select multiple data, right?

                        i hope jsbinca does agree so i get rest and continue with this setup
                        Simple things needs simple solutions.

                        You don't have a complex data relation, and this way you will manage more simple. I think it's good as is.
                        /Giuseppe

                        Professional Scriptcase Services
                        Some Customers opinions

                        Comment

                        Working...
                        X