Jump to content

[Solved] No-Code: Linking Data


Recommended Posts

Hello all, I have a problem which i don't know what the best way to solve is: The situation:A user creates an appointment in an online calendar. While he's filling out the form, he has the option to share this appointment with one or multiple other users (the form is still not submitted).After the form is submitted, the appointment will be stored in the database and the selected users, if any, will be sent an invitation to join the appointment. They are free to accept/deny the invitation. My problem:How do i link the invitations to the appointment, while at the same time the appointment still isn't stored in the database? (it will only be stored once the form is submitted). My first solution:I thought of storing the appointment in the database first, then immediately check for the last database-entry, get the unique-id of the row and send invitations referencing that unique-id. But this won't be accurate if at the same time multiple users create appointments, and appointments can have the same title/name.So i ditched this solution, thinking it wasn't very accurate/precise. My second solution:Then i thought of creating a unique_id with PHP, and using this unique_id to reference the invitations to the appointment. But using this approach, the 'appointments table' will have 2 unique_id's per row; i.e., the appointmentId (INT, PRIMARY KEY, AUTO_INCREMENT) and the php-created unique_id.I believe this trick will do, but it isn't very efficient because you now have 2 unique id's per appointment, and the php_created unique_id's is solely for linking the invitations to the correct appointment. What is the best way to create a fail-safe system, yet effective ??? Thnx in advance

Edited by Donotknow
Link to post
Share on other sites

To start off, all your tables must have at least one unique column (or pair of columns) in order to make a decent application. What you can do is have one table for the invitations, one for the appointment and one table to relate the two. It's a simple table with two fields that relates invitations to appointments:It would look like this:

invitation | appointment----------- ------------   1	   |	 1   2	   |	 1   3	   |	 2   4	   |	 3   5	   |	 4

The first column is a foreign key to the ID of the invitation, the second column is a foreign key to the ID of the appointment.

Link to post
Share on other sites

Hi ingolme,Thank you for the post. I am already familiar with the use of Foreign Keys and referencing. If i use 3 tables, as you mentioned, then wouldn't it be the same as 'store appointment first, then look up the unique id of it; after that, relate the two'?. I mean, i still would need the unique id of the appointment, and because that id is created as soon as the appointment is stored in the DB, then i would still need to look the appointment up in the DB to get it's unique ID (and the problem here is: what criteria do i use? The solution i see here is: since the unique id is created as soon as the appointment is stored in the database and i don't know this unique id unless i manually look it up in the DB-table, I need to create predefined PHP-unique id, and store that along with the appointment in the same row. This way, this preknown unique-PHP-Id can also be stored along with the invitations that belong to that appointment, and thus there is a direct and precise relationship between appointment and invitations). But there must be another way, right? Or is this a good way? What i also could've done was: Let the user first make the appointment and store it, THEN give him the option of viewing the appointment and sending invitations. He could do this on a page with www.example.com?id=appointmentId for example, where he sees the information about the already stored appointment, and has the option to send invitations. Using this approach, it is simple to relate appointment and invitations, since the appointmentId is in the URL and can be extracted from there easily.And with this approach, it would be easy to use 2 or 3 tables to reference appointments and invitations to eachother, because a unique id of the appoint is known. But I need to do both at the same time (i.e.; create appointment and relate invitation before the appointment is stored in DB).

Edited by Donotknow
Link to post
Share on other sites

If you're using PHP, you can use mysql_insert_id() to get the last AUTO_INCREMENT id.

// Add new appointment mysql_query( ... );$appointment_id = mysql_insert_id(); // Add new invitationmysql_query( ... );$invitation_id = mysql_insert_id(); // Add a relationmysql_query( ... $invitation_id, $appointment_id ... );

Link to post
Share on other sites

Thnx for the quick reply. I quickly check the php manual to see if there are any pointers to look out for using that method, and i stumbled across these 2 notes: Note: Because mysql_insert_id() acts on the last performed query, be sure to call mysql_insert_id() immediately after the query that generates the value.Note: The value of the MySQL SQL function LAST_INSERT_ID() always contains the most recently generated AUTO_INCREMENT value, and is not reset between queries.(http://php.net/manua...l-insert-id.php) Let's say i use

line1: mysql_query(...);Xline2: $appointmentId = mysql_insert_id();

to get the last entered row-id. What if in the miliseconds between line 1 and line 2 (marked X), some other user creates/stores a different appointment (assuming by chance he hit the submit button at the right time). Will line2 then get the id of that other appointment? What are chances of this happening (taking intoaccount a lot of daily active users), if possible? (this was my problem, at least i thought it was possible, and why i didn't implement this method straight away)

Edited by Donotknow
Link to post
Share on other sites

mysql_insert_id() uses the ID that was inserted with the last query that was sent from that specific connection. There won't be interference between different users using the script at the same time, because each connection is independent from eachother. The mysql function LAST_INSERT_ID() does not take the connection into account, so it will give trouble if several users are running a query at the same time. That's why mysql_insert_id() is more reliable. As you can see in my previous post, you should use mysql_insert_id() right after the query that updated the ID.

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...