Copy a auto increment soon as row is created

[Shadowing]

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.

[SergeiSS]

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.

[Psycho]

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.

[Shadowing]

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

[Shadowing]

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?

[Shadowing]

or do you mean selecting it like this

 

$pm_id_copy3 = "SELECT pm_id FROM pm WHERE mysql_insert_id() = mysql_insert_id()";

[Shadowing]

going to try this out first

 

$copy = mysql_insert_id();

 

then insert $copy into my insert query for the 2nd table

[Shadowing]

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

 

 

[Shadowing]

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

[SergeiSS]

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.

[Shadowing]

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.

[SergeiSS]

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.

[Shadowing]

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

[Psycho]

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.

[Shadowing]

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?

[kicken]

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.

 

 

[Psycho]

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.