Welcome to WebmasterWorld Guest from 54.146.221.231

Forum Moderators: open

Message Too Old, No Replies

INSERT into two tables with same data

Tried several suggestions, nothing works

     
5:39 pm on Jan 5, 2010 (gmt 0)

New User

5+ Year Member

joined:Jan 5, 2010
posts: 5
votes: 0


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 :)

11:56 am on Jan 6, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member dreamcatcher is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Mar 30, 2003
posts:3719
votes: 0


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

1:09 pm on Jan 6, 2010 (gmt 0)

New User

5+ Year Member

joined:Jan 5, 2010
posts: 5
votes: 0


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

Cheers,

viajeroyhz1

3:08 pm on Jan 6, 2010 (gmt 0)

New User

5+ Year Member

joined:Jan 5, 2010
posts: 5
votes: 0


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!

7:58 pm on Jan 6, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:May 31, 2005
posts:1108
votes: 0


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.
8:20 pm on Jan 6, 2010 (gmt 0)

New User

5+ Year Member

joined:Jan 5, 2010
posts: 5
votes: 0


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.

9:33 pm on Jan 6, 2010 (gmt 0)

New User

5+ Year Member

joined:Jan 5, 2010
posts: 5
votes: 0


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.