1

I am working on an events calendar using PHP and MySQL (V5.1) where the admin would add an event, and then an attendance list for that event would be created as well. I have three tables: events, attendance and members. So far I can create the event using information that is entered thorugh a PHP form. I'm trying to update the attendance table by inserting the event id from the event that has just been created, as well as pulling in the list of members (using their member ID) from the members table. Nothing is being added to attendance the table though. Can someone let me know what I should I be doing differently?

Some of the fields I am using in the tables:
Events: event_ID (Primary key, auto-increment), name, location, date
Attendance: attendance_ID (Primary key, auto-increment), event_ID, member_ID, attending
Members: member_ID (Primary key, auto-increment), name, email

enter code here

Here is the code:

mysql_query("SET AUTOCOMMIT=0");
mysql_query("START TRANSACTION");

$query1 = mysql_query("INSERT INTO events (name , location , date) VALUES ('".mysql_real_escape_string($name)."' , '".mysql_real_escape_string($location)."' , '".mysql_real_escape_string($date)."')");

$query2 = mysql_query("INSERT INTO attendance (event_ID , member_ID) SELECT LAST_INSERT_ID(), members.member_ID FROM members");

if ($query1 and $query2) { 
mysql_query("COMMIT");
} else {
mysql_query("ROLLBACK");

}

3 Answers3

1

You could use mysql_insert_id()

$query1 = mysql_query("INSERT INTO events (name , location , date) VALUES ('".mysql_real_escape_string($name)."' , '".mysql_real_escape_string($location)."' , '".mysql_real_escape_string($date)."')");

$insert_id = mysql_insert_id() ;

$query2 = mysql_query("INSERT INTO attendance (event_ID , member_ID) SELECT {$insert_id}, members.member_ID FROM members") ;
sybear
  • 7,837
  • 1
  • 22
  • 38
0

A couple of important points. First, if you getting your last insert ID you should execute LOCK and UNLOCK queries first:

  • LOCK TABLES events WRITE;
  • UNLOCK TABLES

Second, you can use the mysqli_insert_id() method to get the ID of the last insert. This means that you must have an AUTO_INCREMENT field in the table you are inserting.

DSJ
  • 106
  • 3
  • Thanks everyone. It turns out the reason my code was not working was actually in a different part of the file entirely (Just a hint, if you're going to create a copy of a page to test additional code, always remember to update your form action. Ooops). For the record, all of the suggestions will populate the table as intended, as would my original code. The suggestion by &DSJ to use MySQLi and locking the tables does seem like a better practice than what I was using though, so I went with that. – Michael George Feb 18 '13 at 12:31
0

Put VALUES into $query2 to form a correct SQL-statement:

$query2 = mysql_query("INSERT INTO attendance (event_ID , member_ID) **VALUES (**SELECT LAST_INSERT_ID(), members.member_ID FROM members");
michi
  • 6,565
  • 4
  • 33
  • 56
  • What do you say about [that](http://stackoverflow.com/questions/25969/sql-insert-into-values-select-from)? – sybear Feb 17 '13 at 18:46