Jump to content

Copy a auto increment soon as row is created


Shadowing

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

 <?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); ?> 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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"; 
?> 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

 

 

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.