MYSQL查询 - 我的大脑爆炸了

Question

this is my first question here, sorry if i'm breaking any etiquette. I'm kinda into coding, but sometimes my brain is hard to swallow path of logic steps.

Currently i'm working on my own small web-app, where i have public events, and i'm making my own guestlist.

By the time, i've solved these things:(i think so)

• Getting from Facebook page all events;
• From current event view get all attendees;
• Live search function and ordering array alphabetically.

To Do:

• Complete CHECK button function - when checked, one get removed from lis;
• Other analysis functions.

Problem:

Currently i'm getting all attendees from JSON string, then converting to array, putting it all in database. I can't decide on SQL logic.

I have all list with people - json->array->db then it reads from db and show wich is checked wich one not, like comparing with table that is from JSON.

Current algorithm is - getting json, and in foreach cycle, everytime i load it writes in DB, using INSERT IGNORE it ignores if it's same userid, so i have db of all atendees.

How to arrange my database? I'm thinking about making tables:

guests - USERID ; EVENT ID; NAME; [for huge list of all people] checkins - USERID; CHECKEDEVENTID; DATETIME; [for getting stats]

My goal is to make "Checking In" door-app, so in the end i see, that those and those users are attending more on those kind of events, than these one...

So how could i make like stats, like - EVENT - attended Y people of X, and more global SQL queries, like, USER Y came to EVENTS A,B,C. Or, most checkings happening at timespan [probably some bars or chart]....

Should i make for each event new table to store all guest there to see all atendee statistics, and checking table for checkin stats?

Answer 1

For the what you refer to as the "Check" feature, it sounds like you want (roughly*) the following tables:

create table users
(
  userid float NOT NULL,
  username varchar(64)
);

create table events
(
  eventid float NOT NULL,
  eventname varchar(64),
  eventstart date,
  eventlength float 
 );

 create table checkin_activity
 (
   userid float not null,
   eventid float not null,
   checkin_time date
 );

_{* This is a highly simplified database schema. You'll want to make sure you add the necessary keys, constraints, etc., and make sure the data types on your columns are appropriate. I didn't give that much thought with this quick example.}

Using the entries in the USERS and EVENTS tables, you'll populate the CHECKIN_ACTIVITY table with what you refer to as the "Check" button. You can join queries against these tables as needed to run reports and so on.

NOTE: You mention:

Current algorithm is - getting json, and in foreach cycle, everytime i load it writes in DB, using INSERT IGNORE it ignores if it's same userid, so i have db of all atendees

You should avoid writing to the database within a for loop (into a table I didn't account for above; let's call it the EVENT_ATTENDEES table). Instead, build an INSERT ALL query and executing it once so you're not hitting the database's transaction handler n times.

INSERT ALL
  INTO event_attendees (eventid, name) VALUES (1, 'John') 
  INTO event_attendees (eventid, name) VALUES (1, 'Jane')
  INTO event_attendees (eventid, name) VALUES (1, 'Colin')
SELECT * FROM dual;

This is especially important if this kind of load is something you'll be doing often.