homepage Welcome to WebmasterWorld Guest from 54.163.72.86
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
INSERT into two tables with same data
Tried several suggestions, nothing works
viajeroyhz1




msg:4054628
 5:39 pm on Jan 5, 2010 (gmt 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 :)

 

dreamcatcher




msg:4055070
 11:56 am on Jan 6, 2010 (gmt 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

viajeroyhz1




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

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

Cheers,

viajeroyhz1

viajeroyhz1




msg:4055206
 3:08 pm on Jan 6, 2010 (gmt 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!

Dijkgraaf




msg:4055429
 7:58 pm on Jan 6, 2010 (gmt 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.

viajeroyhz1




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

viajeroyhz1




msg:4055495
 9:33 pm on Jan 6, 2010 (gmt 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved