Attempt to update one mySQL table with data from two other tables is failing

Question

I am trying to update one table which holds the inputted data from my employees. The table has three columns: entry id, field id, and value. I am wanting to make sure there is a row for every field in the this table according to each entry. As such I have the written the following php/sql script. The thought process was get two arrays (one containing the entry ids and the other the field ids) and check the input table to see if there was an existing row for every field and entry. If not then use an inert into command to add a row with the value 0, but this command is failing. My guess is a timeout error due to the intensity of this function. Any suggestions?

$db = JFactory::getDBO();

$field_query = 'SELECT id FROM `jos_directory_field`';
$db->setQuery( $field_query );
$fields = $db->loadObjectList();

$entry_query = 'SELECT id FROM `jos_directory_entry`';
$db->setQuery( $entry_query );
$entries = $db->loadObjectList();

  for($e=0;$e count($entries);$e++) {
    for($i=0;$i count($fields);$i++) {
      $insert_query = 'INSERT INTO `jos_directory_enf` (entry_id,field_id,field_value)';
      $insert_query .= 'SELECT '.$entries[$e]->id.','.$fields[$i]->id.',0';
      $insert_query .= 'FROM dual WHERE NOT EXISTS (SELECT * FROM `jos_directory_enf`';
      $insert_query .= 'WHERE entry_id = '.$entries[$e]->id.' and field_id = '.$fields[$i]->id.')';
      $db->setQuery( $insert_query );
      $db->query();
    }
  }

And how big are the tables - you could be getting out-of-memory from PHP itself, simply because of your table size (mmm, maybe somewhat unlikely). Also, what COMMIT level are you running under? — Clockwork-Muse
Do you have an actual table called DUAL? Or is this a carry-over from ORACLE syntax? — Sparky
@MarcB It ran for about 30 to 60 seconds before bringing an internal server error. — user830381
@X-Zero My assumption of the problem is a timeout via PHP. I do not know what the COMMIT level is. — user830381

outis outis · Accepted Answer · 2011-10-25T00:10:12

It's too bad Joomla doesn't appear to support prepared statements, as they can help with performance for repeated queries.

However, in this case there's a better option. You should be able to replace the PHP code with a single SQL statement, making use of the IGNORE clause for INSERT statements. First, make sure you have a unique index on the entry_id and field_id columns of jos_directory_enf. Then the SQL statement would be something like:

INSERT IGNORE INTO `jos_directory_enf` (entry_id,field_id,field_value)
  SELECT e.id, f.id, 0
  FROM jos_directory_entries AS e
    JOIN jos_directory_field AS f
;

.

Attempt to update one mySQL table with data from two other tables is failing

1 Answers