INSERT into two tables with same data

Tried several suggestions, nothing works

[viajeroyhz1]

Hi,

I have two tables of calendar events, one in a public access area and another in a members-only area.

When someone adds an event from the public area, it should be posted to both the public and members-only calendars.

Here is the code, under php:

$sql = "INSERT INTO " . M_DB_TABLE_PREFIX . "mssgs SET uid=$uid, m=$month, d=$day, y=$year, ";
$sql .= "start_time='$starttime', end_time='$endtime', title='$title', text='$text'";

$sql = "INSERT INTO " . DB_TABLE_PREFIX . "mssgs SET uid=$uid, m=$month, d=$day, y=$year, ";
$sql .= "start_time='$starttime', end_time='$endtime', title='$title', text='$text'";

$result = $lang['added'];

No error messages are displayed, and regardless of which sql statement I place first, the INSERT only works for the last statement.

How can I combine these statements so that both tables get the new events?

Thanks in advance for your help :)

[dreamcatcher]

Hi viajeroyhz1, welcome to WebmasterWorld. :)

I assume that you are running the query as mysql_query($sql)?

As you are concatenating your vars, the second set overwrite the first, hence only the first query gets executed.

$sql = "INSERT INTO " . M_DB_TABLE_PREFIX . "mssgs SET uid=$uid, m=$month, d=$day, y=$year, ";
$sql .= "start_time='$starttime', end_time='$endtime', title='$title', text='$text'";

$sql2 = "INSERT INTO " . DB_TABLE_PREFIX . "mssgs SET uid=$uid, m=$month, d=$day, y=$year, ";
$sql2 .= "start_time='$starttime', end_time='$endtime', title='$title', text='$text'";

mysql_query($sql);
mysql_query($sql2);

Something like that should work ok.

dc

[viajeroyhz1]

Thanks for the warm welcome, and Kudos for the solution; works like a champ!

Cheers,

viajeroyhz1

[viajeroyhz1]

I've made a slight change to the code mentioned above, as follows:

$sql1 = "INSERT INTO " . DB_TABLE_PREFIX . "mssgs SET uid=$uid, m=$month, d=$day, y=$year, ";
$sql1 .= "start_time='$starttime', end_time='$endtime', title='$title', text='$text'";

mysql_query($sql1);

$sql = "INSERT INTO " . M_DB_TABLE_PREFIX . "mssgs SET uid=$uid, m=$month, d=$day, y=$year, ";
$sql .= "start_time='$starttime', end_time='$endtime', title='$title', text='$text'";

The first statement posts to the Public calendar and the second to the Members calendar. I removed the second mysql_query() because it was causing double posting to the Members calendar.

Thanks again!

[Dijkgraaf]

A better solution would possible to rethink your architecture a bit.
Rather than having two tables containing some duplicated data, why not just have an extra field in the table indicating whether it is public or members only.
Then when you insert you just set this value to show which ones, and any queries can use it to retrieve only public or all.

[viajeroyhz1]

Thanks for the comment. The calendar was first used only for public access, then the client changed the requirements ( clients NEVER do that LOL ) and wanted the public events to be added to the member-only section with the proviso that member-only postings would show up only in the member section.

As the events are displayed in calendar format, rather than change the coding to have a P or M indicator for the data then query for that, it seemed easier to add the public posting to the member only table.

[viajeroyhz1]

Well... easier is NOT always faster! I ran into some errors when editing items displayed on the member-only calendar.

Thanks again dijkgraaf! As you suggested, I've added a category column to the messages table.