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

Applications SQL Builder
#1

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

$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

#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

#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

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

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

#7

ok guys, i have to change the database design because it was decided to add more values for each classroom! :frowning: 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:

[TABLE]
[TR]
[TD]Class no
[/TD]
[TD]total
[/TD]
[TD]available
[/TD]
[TD]absent
[/TD]
[TD]With permission
[/TD]
[TD]Without permission
[/TD]
[TD]date
[/TD]
[TD]time
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]20
[/TD]
[TD]15
[/TD]
[TD]5
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]28/5
[/TD]
[TD]11:11
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]20
[/TD]
[TD]14
[/TD]
[TD]6
[/TD]
[TD]4
[/TD]
[TD]2
[/TD]
[TD]28/5
[/TD]
[TD]12:00
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]20
[/TD]
[TD]11
[/TD]
[TD]9
[/TD]
[TD]5
[/TD]
[TD]4
[/TD]
[TD]28/5
[/TD]
[TD]13:00
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[/TABLE]

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:
[TABLE]
[TR]
[TD]Class no
[/TD]
[TD]total
[/TD]
[TD]available
[/TD]
[TD]absent
[/TD]
[TD]With permission
[/TD]
[TD]Without permission
[/TD]
[TD]date
[/TD]
[TD]time
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]20
[/TD]
[TD]10
[/TD]
[TD]5
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]27/5/2014
[/TD]
[TD]11:11
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]25
[/TD]
[TD]14
[/TD]
[TD]6
[/TD]
[TD]4
[/TD]
[TD]2
[/TD]
[TD]27/5/2014
[/TD]
[TD]12:00
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]20
[/TD]
[TD]11
[/TD]
[TD]9
[/TD]
[TD]5
[/TD]
[TD]4
[/TD]
[TD]27/5/2014
[/TD]
[TD]13:20
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]15
[/TD]
[TD]9
[/TD]
[TD]3
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]27/5/2014
[/TD]
[TD]11:00
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]22
[/TD]
[TD]20
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]27/5/2014
[/TD]
[TD]12:00
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]20
[/TD]
[TD]12
[/TD]
[TD]8
[/TD]
[TD]5
[/TD]
[TD]3
[/TD]
[TD]27/5/2014
[/TD]
[TD]13:13
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]34
[/TD]
[TD]31
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]27/5/2014
[/TD]
[TD]14:10
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]21
[/TD]
[TD]20
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]28/5/2014
[/TD]
[TD]12:10
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]16
[/TD]
[TD]14
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]0
[/TD]
[TD]28/5/2014
[/TD]
[TD]13:44
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]11
[/TD]
[TD]10
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]28/5/2014
[/TD]
[TD]8:60
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]15
[/TD]
[TD]11
[/TD]
[TD]4
[/TD]
[TD]0
[/TD]
[TD]4
[/TD]
[TD]28/5/2014
[/TD]
[TD]11:00
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]22
[/TD]
[TD]19
[/TD]
[TD]3
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]28/5/2014
[/TD]
[TD]12:12
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]17
[/TD]
[TD]14
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]28/5/2014
[/TD]
[TD]13:19
[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]31
[/TD]
[TD]27
[/TD]
[TD]4
[/TD]
[TD]3
[/TD]
[TD]1
[/TD]
[TD]28/5/2014
[/TD]
[TD]12:11
[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]11
[/TD]
[TD]7
[/TD]
[TD]4
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]28/5/2014
[/TD]
[TD]11:15
[/TD]
[/TR]
[/TABLE]

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 :slight_smile:

Any help or idea (with details) is highly appreciated

Mike

#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 :wink:

#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 :slight_smile: all of you guys are lot of help

#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

#11

yes i was reading and youtubing all about create view since you posted your last reply :slight_smile: 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 :slight_smile:

#12

[QUOTE=itsme3;23922]yes i was reading and youtubing all about create view since you posted your last reply :slight_smile: 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 :)[/QUOTE]

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.