Joining 3 MySQL tables in PHP, how to best code it

[csdude55]

I have 3 MySQL tables that are set up like this:

// PM
id (Auto Increment) 
ref_id (reference id to pm_data) 
subject_id (reference id to pm_subject)
username
sender
folder
status (0 or 1) 
postdate

// PM Subject
subject_id (Auto Increment) 
hash (UNIQUE, an MD5 of subject) 
subject

// PM Data
ref_id (Auto Increment)
hash (UNIQUE, an MD5 of message)
message

I'm inserting via PHP, but I'm not sure if this is the best way to do it. The goal is to use integers in the "pm" table instead of long, repeated strings of text, with the idea that it should be smaller and run considerably faster.

This seems overly complicated and doing too many queries, so I'm hoping you guys can advise:

// First, insert the SUBJECT
$write_subject = sprintf("INSERT IGNORE INTO pm_subject (hash, subject) VALUES (MD5('%s'), '%s')",
 $subject,
 $subject);

 mysql_query($write_subject);

if (mysql_affected_rows() > 0)
 $subject_id = mysql_insert_id();

else {
 $select_subject = sprintf("SELECT subject_id FROM pm_subject WHERE hash = MD5('%s') LIMIT 1",
  $subject);

 list($subject_id) = mysql_fetch_row(mysql_query($select_subject));
}

// Then insert the MESSAGE
$write_message = sprintf("INSERT IGNORE INTO pm_data (hash, message) VALUES (MD5('%s'), '%s')",
 $message,
 $message);

 mysql_query($write_message);

if (mysql_affected_rows() > 0)
 $ref_id = mysql_insert_id();

else {
 $select_message = sprintf("SELECT ref_id FROM pm_data WHERE hash = MD5('%s') LIMIT 1",
  $message);

 list($ref_id) = mysql_fetch_row(mysql_query($select_message));
}

// Then insert the PM
$insert_pm = sprintf(<<<EOF
INSERT INTO pm (ref_id, subject_id, username, sender, folder, status, postdate) VALUES
 ('%s', '%s', '%s', '%s', '%s', '%s', '%s'),
 ('%s', '%s', '%s', '%s', '%s', '%s', '%s')
EOF
 ,

 // Inbox
 $ref_id,
 $subject_id,
 mysql_real_escape_string($recipient),
 mysql_real_escape_string($sender),
 'inbox',
 '1',
 $timestamp

 // Sent
 $ref_id,
 $subject_id,
 mysql_real_escape_string($sender),
 mysql_real_escape_string($recipient),
 'sent',
 '0',
 $timestamp);

 mysql_query($insert_pm);

Can you guys suggest a better way to code all of this without running so many queries?

Once I get the coding figured out, I'll also be converting the usernames to integers, but I need to get the coding straight first. I should also mention that I'm modifying my old programs for this, which is why it's using MySQL... the new system uses MySQLi, but I have to accommodate the old system for now.

[csdude55]

Maybe this will be simpler... which do you guys think would be faster and take up less server resources:

Option A checks to see if a row has been inserted; if so it used mysql_insert_id(); to get the ID, and if not then it does a SELECT statement. There are 2 inserts, so it does this twice:

if (mysql_affected_rows() > 0)
 $subject_id = mysql_insert_id();

else {
 $select_subject = sprintf("SELECT subject_id FROM pm_subject WHERE hash = MD5('%s') LIMIT 1",
 $subject);

 list($subject_id) = mysql_fetch_row(mysql_query($select_subject));
}

if (mysql_affected_rows() > 0)
 $ref_id = mysql_insert_id();

else {
 $select_message = sprintf("SELECT ref_id FROM pm_data WHERE hash = MD5('%s') LIMIT 1",
 $message);

 list($ref_id) = mysql_fetch_row(mysql_query($select_message));
}

Or Option B, which eliminates both of the mysql_insert_id(); statements, and instead just does a SELECT... JOIN for both tables at once:

$select = sprintf(<<<EOF
SELECT pm_subject.subject_id, pm_data.ref_id FROM pm_subject, pm_data
 WHERE
 pm_subject.hash = MD5('%s') AND
 pm_data.hash = MD5('%s')
 LIMIT 1
EOF
 ,
 $subject,
 $message);

list($subject_id, $ref_id) = mysql_fetch_row(mysql_query($select));

Both should give me the same data at the end, I'm just not sure which one is going to process faster. And I can't really test it right now because I only have 8 rows of data in there for testing... when I go live, though, it will be closer to 5 million rows, so I'm hoping to get the structure figured out before I go live and THEN find out that it's running too slow.

[topr8]

if you want faster, more efficient and safer.

IMO you should develop stored procedures on the mysql/mariadb server, you could do all that logic directly in the Stored Procedures,
that way you can run as many queries or whatever as you like with just one db call, also it is highly likely that the SP will be cached by the server - meaning it will run quicker the next time it is called.

[DaveWave]

You should use transactions when you want to ensure multi table updates either all happen or none at all. You can do this client side within php or my pref would be always to do inside a stored procedure.