This is a theoretical question. I'm creating an app which downloads a list of events around the city from a
MySQL DB and displays them in a
Users should be able to select an event and add to their own list of events they are interested in. At that point, I'm not sure what are the best practices.
So when a user selects an event item from the
RecyclerView, what's the best solution, I can only think of that:
Method 1: Add the chosen event to a
List<Event> which is then saved in
SharedPreferences as a
JSON string. Upload the list to the online
MySQL DB at a later point.
Any other suggestions?
About the query:
Also, could you give me a pointer on how to do the complex
query to the database.
I have these tables
Events,Accounts, GuestList. So, GuestList holds the ID of the event and of the account so that I keep track of which events a specific user wants to attend. I'm guessing I'd have to use some kind of JOIN?
You can see that if I want to get the data to display (the event information only for the user who is requesting it) I'd need to first query the
GuestList table to get the list of events. Then query the
Events table to get the information for all the events with the IDs we grabbed a moment ago. An example SQL statement anyone?
When user select event from your RecyclerView that he wants to attend, you should pass a query to the database storing the event to the user’s private event list. The optimal way is doing it asynchronously… It seems that you have many to many relationship between your models. Events, Account and the middle table is GuestList that stores the relations between your models. The best practice is to name it Events_Account and this convension means that this is the connection table. The examples I will show are on MS SQL but they are pretty much the same and the concept is same too. So basing on your models description the relations should be looking as something like that.
You can select your data using a basic query like this
When a user wants to grab info for a particular event you can simply add another where clause like this
You can pass a parameters in your method and do it with a pure query. Let's pretend that these are your variables @CurrentUserId and @Particular event. You can pass a values into the method and use them to select your item
This is the WORST practice – to use it as a pure query. The PHP as other languages are giving you prerequisites to shoot yourself in the leg. Using a pure db query in your code and relaying that the user will use properly the application is totally wrong. This way the user can simply send a SQL injection and dump your database. Here comes the ORM /Object Relational Mapping/ Like EntityFramework for .NET. The PHP equivalent is Propelorm. It maps your database and makes you access to the object very easy with the fluent syntax.
The query and also the ORM that is very simpler to use and saves you from SQL injection will both be transpiled into SQL language. The query that they will execute in SQL will be close to the one on the pictures but a bit uglier for a human. Don't worry about the query it will be very fact because this operations are executed on the database level and they will be very fast.
I hope that this will be useful and enough for you and solves your theoretical problem