CODEWITHSUNDEEP
phpmysql
EMMANUEL OKELLO
EMMANUEL OKELLO

Reputation: 178

Different rows in table but with same foreign key with MySQL and php

How to have different rows in table but with same foreign key, having the event as the same, but not inserting into event table again?

The Event_ID is consistent across event table and participant table but I need it to be the same for the same event in participant table and not to insert into event table for same event

this is my code snippet :

$whatsapp=mysql_query("SELECT $event_name FROM event");
$resultwhatsapp = mysql_query($whatsapp,$conn);
if(mysql_num_rows($resultwhatsapp) == 0)
{
  $sql1 = "INSERT INTO event VALUES('','".$event_name."',        '".$event_venue."', '".$event_date."', '".$event_sum."', '".$event_ran."', '".$event_ex."')";
  $result1 = mysql_query($sql1, $conn);
  $event_id = mysql_insert_id($conn);
  //mysql_free_result($result1);
}
//inserting into the participant table.
$qry = "INSERT INTO participant  VALUES('','$event_id','".$userTypeID."','".$full_name."','".$gender."','".$or    ganisation."','".$phone."','".$email."')";
$resultqry = mysql_query($qry, $conn);
//mysql_free_result($resultqry);

//$sql1 = "INSERT INTO event VALUES('','$part_id', '".$event_name."',  '".$event_venue."', '".$event_date."', '".$event_sum."', '".$event_ran."',  '".$event_ex."')";
//$result1 = mysql_query($sql1, $conn);
//mysql_free_result($result1);
}

Upvotes: 0

Views: 238

Answers (2)

The Sammie
The Sammie

Reputation: 1248

Your question seems ambiguous, but i am going to try and answer it in context....

So you have EVENTS and PARTICIPANTS and you want to know which participants are going for a certain event.

So your might want to create a 3rd table call it ATTENDEES for each participant who registers for an event, insert the event_id and participant_id into the ATTENDEES table.

So if you want to know which participants are going for a certain event, you just look for all attendees in the ATTENDEES table where EVENT_ID for that event.

Also read on many to many relations to get a better picture

Upvotes: 1

Migisha
Migisha

Reputation: 415

This seems a very good problem.

Here's what you're doing:

  • You push data to the event table.
  • You pick the eventID from the event table and use it to push other data to the participant table.

Here's what you want to do:

  • Push data to event table.
  • Push data to participant table with same eventID from the push to event table.
  • Have that eventID remain the same in both event and participant tables for the same event.

Here's how to do it:

  • Create a separate field in event table to store eventID. Alternatively, you can leave the current eventID for counting purposes, or remove AUTOINCREMENT from it's properties so you can take control of it. The aim of this step is to be able to manually manage your eventID variable.
  • Declare [at least mentally] eventID numbers with their corresponding events. This way, you know what eventID represents what event, and vice-versa. This information will come in handy in the future.
  • Create a [non AUTOINCREMENT] field in your participant table with eventID. This will be the foreign key from your event table.
  • At every INSERT INTO event statement, use the eventID<->event matching you made at step 2 to manually push an eventID to the event table with the data you're pushing. This ensures that dance_event and run_event each have unique eventID values and dance_event and run_event will each have their eventID uniform across rows.
  • At every INSERT INTO participant statement, repeat step 4 above and submit the eventID based on the eventID<->event matching you made at step 2. This ensures that you have the same evendID across participant rows that attended the same event.

Since the eventID is standard across the two tables, it can be used to query for events that a participant attended, as well as participants that attended a particular event.

----ORRRRRR----

You can go @TheSammie way and create a third 'buffer' table to help smooth things over.

How it works:

  • Create a table, say, attendees.
  • After every INSERT INTO event and INSERT INTO participant, do a INSERT INTO attendees VALUES ( your_eventID , your_participantID )

That's it! Shorter than the blubber at the top, and still gets the job done!

PS: In my past encounters with this issue, I've used @TheSammie's solution and it has worked like a charm!

Upvotes: 0

Related Questions