Shadowing Posted December 29, 2011 Share Posted December 29, 2011 is there a way to save a field in a session or variable while its being inserted into the data base? with out having to select it using another query $mail2 = "INSERT INTO pm SET pm_id = '', (turn pm_id into a $variable or session) WHERE blah blah blah the problem im having is Im creating two rows right after each other where each row is in differant tables and I need them to match ids and I have nothing to compare it to. only option i can think of is making a fast toggle switch or telling my select to grab in order which would work. Quote Link to comment Share on other sites More sharing options...
SergeiSS Posted December 29, 2011 Share Posted December 29, 2011 the problem im having is Im creating two rows right after each other where each row is in differant tables and I need them to match ids and I have nothing to compare it to. I'm not sure that I understand you exactly. But you may use 2 possibilities: (1) trigger allows you to do whatever you wish before/after performing any action in the table (for example, you may add or change any info in other table) and (2) if you use autoincremented field you may get it's new value just after insertion. Quote Link to comment Share on other sites More sharing options...
Psycho Posted December 29, 2011 Share Posted December 29, 2011 If you are setting "pm_id" manually then you already have the value. So, you could just set it in both quries as needed. But, if "pm_id" is getting set via an auto-increment id in one table and you need the same value for the insertion into a second table, then you would want to be using mysql_insert_id() to get the auto generated ID from teh first query so you can use that value as a foreign key in the subsequent query. Quote Link to comment Share on other sites More sharing options...
Shadowing Posted December 29, 2011 Author Share Posted December 29, 2011 thanks for the reply SergeiSS yah i need to get the new value after its inserted but i cant use anything in the the other columns to single it out on which one to get. I tried selecting the newest row that was created but that didnt work <?php $pm_id_copy3 = "SELECT pm_id FROM pm WHERE sendto = '".mysql_real_escape_string($_POST['message_to'])."' AND sentfrom = '$from' ORDER BY time DESC"; >? I would think that would grab the newest row that was created but for some reason it doesnt im letting the data base auto set the value Quote Link to comment Share on other sites More sharing options...
Shadowing Posted December 29, 2011 Author Share Posted December 29, 2011 mjdamato you are awesome that sounds like what I need im reading w3schools example of mysql_insert_id() can i replace id with pm_id? Quote Link to comment Share on other sites More sharing options...
Shadowing Posted December 29, 2011 Author Share Posted December 29, 2011 or do you mean selecting it like this $pm_id_copy3 = "SELECT pm_id FROM pm WHERE mysql_insert_id() = mysql_insert_id()"; Quote Link to comment Share on other sites More sharing options...
Shadowing Posted December 29, 2011 Author Share Posted December 29, 2011 going to try this out first $copy = mysql_insert_id(); then insert $copy into my insert query for the 2nd table Quote Link to comment Share on other sites More sharing options...
Shadowing Posted December 29, 2011 Author Share Posted December 29, 2011 im putting $_SESSION['copy'] = mysql_insert_id(); right after the first insert and im getting this error FUNCTION stargate.mysql_insert_id does not exist Quote Link to comment Share on other sites More sharing options...
Shadowing Posted December 29, 2011 Author Share Posted December 29, 2011 <?php $mail2 = "INSERT INTO pm SET sendto = '".mysql_real_escape_string($to)."', sentfrom = '".mysql_real_escape_string($from)."' , subject = '".mysql_real_escape_string($subject)."' , id= '".mysql_real_escape_string($to_id)."', message= '".mysql_real_escape_string($message)."', time= '".mysql_real_escape_string($phptime)."'"; $mail1 = mysql_query($mail2,); $_SESSION['copy'] = mysql_insert_id(); $copy = $_SESSION['copy']; // creates a new row for the message in the sent table $mail2 = "INSERT INTO sent SET sent_id = '$copy', sendto = '".mysql_real_escape_string($to)."', sentfrom = '".mysql_real_escape_string($from)."' , subject = '".mysql_real_escape_string($subject)."' , id= '".mysql_real_escape_string($from_id)."', message= '".mysql_real_escape_string($message)."', time= '".mysql_real_escape_string($phptime)."'"; $mail1 = mysql_query($mail2); ?> Quote Link to comment Share on other sites More sharing options...
SergeiSS Posted December 29, 2011 Share Posted December 29, 2011 As I see you are inserting THE SAME DATA into 2 different tables. I think it's better to change your algorithm. You'd better insert data into 1 table and just have a flag showing if this mail is sent or not. It keeps DB space, it makes easier your script. But if you still like to use 2 tables, try triggers, as I said before. You'll find it more convenient. Quote Link to comment Share on other sites More sharing options...
Shadowing Posted December 29, 2011 Author Share Posted December 29, 2011 what do you mean by triggers like a 1/0 toggle switch on a column? yah it was a decision when i was thinking rather or not to use more then one table. I went with a 2nd table to store sent mail in because i couldnt think of a way around the problem of someone deleting the message from their mail box which would then not show up as sent mail for the other user. Quote Link to comment Share on other sites More sharing options...
SergeiSS Posted December 29, 2011 Share Posted December 29, 2011 what do you mean by triggers Read here what I mean: http://en.wikipedia.org/wiki/Database_trigger I went with a 2nd table to store sent mail in because i couldnt think of a way around the problem of someone deleting the message from their mail box which would then not show up as sent mail for the other user. It's easier to do with help of flags. Quote Link to comment Share on other sites More sharing options...
Shadowing Posted December 29, 2011 Author Share Posted December 29, 2011 thanks for the link on that I managed to get this to work. now i know 100 percent how the script is reading querys. I guess i really didnt know before until now. I had the query below before the first insert so it couldnt grab the new pm_id cause it wasnt created yet. before now i thought SELECT queries only searched for data when they were called upon. <?php $pm_id_copy3 = "SELECT pm_id FROM pm WHERE sendto = '".mysql_real_escape_string($_POST['message_to'])."' AND sentfrom = '$from' ORDER BY time DESC"; ?> Quote Link to comment Share on other sites More sharing options...
Psycho Posted December 29, 2011 Share Posted December 29, 2011 yah it was a decision when i was thinking rather or not to use more then one table. I went with a 2nd table to store sent mail in because i couldnt think of a way around the problem of someone deleting the message from their mail box which would then not show up as sent mail for the other user. You are going about this the wrong way. Just because a user "deletes" a message from their inbox does not mean you have to delete the database record. I assume that users that send messages have an outgoing box with teh messages that they can delete as well. I can see two different approaches: 1. In the one table for messages you can create two columns - one for sender_delete and the other for receiver_delete. Set the initial values to 0. Then, when a sender/receiver "deletes" the message in their inbox/outbox set the appropriate value to 0. Then just change your select queries accordingly to only retrieve messages that are not deleted by the user for which you are fetching. 2. This will be a little more work, but would be more "proper" from a DB normalization perspective. Have one table for the messages, then a separate table to define the inbox (and folders) for a user. So, when a message is sent you would add the record to the messages table and add a record for the recipient in the "inbox" table. Then when the user "deletes" the message you would just delete the message from the inbox table - not the actual message. Quote Link to comment Share on other sites More sharing options...
Shadowing Posted December 29, 2011 Author Share Posted December 29, 2011 im curious why is it so bad to delete the message? Reason i thought deleting is a good idea cause i was thinking every time you do a select query the less rows possible would return faster results. am I wrong about that? cause even though you are singling out a hand ful of messages with a select query wouldnt the database still have to cycle through the messages that you dont want to retrieve? so if i have 50,000 messages in the inbox table and im grabing 100 out for a user wouldnt it be a larger load on the server since it would have to still search through all 50,000 rows to find the 100 rows that you want? Quote Link to comment Share on other sites More sharing options...
kicken Posted December 29, 2011 Share Posted December 29, 2011 wouldnt the database still have to cycle through the messages that you dont want to retrieve? Not if your table is indexed properly. The database will just scan the index to find the specific rows it needs, rather than having to go through every row. There are other ways to improve performance too such as partitioning. Until you reach numbers in the millions for your number of rows, you shouldn't see and big problems with just some indexes. Quote Link to comment Share on other sites More sharing options...
Psycho Posted December 30, 2011 Share Posted December 30, 2011 im curious why is it so bad to delete the message? Reason i thought deleting is a good idea cause i was thinking every time you do a select query the less rows possible would return faster results. am I wrong about that? cause even though you are singling out a hand ful of messages with a select query wouldnt the database still have to cycle through the messages that you dont want to retrieve? So, instead of keeping the records its better to maintain duplicate data? But, if you follow the second suggestion I proposed you could have a table that maintains each user's inbox. The minimum that table needs is two columns - the user_id and the message_id. Then, when a user deletes a message you can delete the record that associates the message with the user's inbox. But, you would still maintain the original record for the purpose of the sending user's outbox. But, I would use the same process for outbound messages as well. You could have a separate tables for users' inboxes and outboxes, but it makes more sense to use one table. That way you can easily add the ability to allow users to create custom folders. So, the table might look something like this: user_id, message_id, folder_id Where folder Id is a foreign key reference to values in a table of folders. You could hard-code the inbox and outbox for all users and allow users to create custom folders. Then when a user "deletes" a message you simply delete the record that associates the message with the user's folder. Then you could do a second query to delete the message IF it is not associated with any other user (i.e. if both the sender and recipient have deleted the message). But, that probably isn't necessary. If you properly index your database, it shouldn't have a problem with tens of thousands (if not hundreds of thousands) of records. And, if it does become a problem then you can archive/delete messages for which there are no users who have references to them. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.