Selecting data from 2 tables and inserting into a 3rd table

Question

Could someone help me to create a query using the Joomla API? I've been going round in circles.

What I have so far:

<?php
// Get a db connection.
$db = JFactory::getDbo();

// Create a new query object.
$query = $db->getQuery(true);

// Insert columns.
$columns = array('virtuemart_media_id', 'virtuemart_product_id');

// Insert values.
$values = array(); <---- How can i get the values

// Prepare the insert query.
$query
    ->insert($db->quoteName('#__virtuemart_product_medias'))
    ->columns($db->quoteName($columns))
    ->values(implode(',', $values));

// Set the query using our newly populated query object and execute it.
$db->setQuery($query);
$db->execute();

?/>

Now, the values I want are based on the following:

• virtuemart_media_id = #__virtuemart_medias.virtuemart_media_id
• virtuemart_product_id = #__virtuemart_products.virtuemart_product_id
• WHERE #__virtuemart_products.product_sku = #__virtuemart_medias.explode("-", strtoupper('file_url'), 2)

I want to insert the values into the #__virtuemart_product_medias table.

Answer 1

do you really need to process it with php? 1 query and MySQL will do it all very fast

insert into #__table3 (column1, column2)

select column1, column2

all in one query and you can use any join and I believe even union(union is not tested)

also you can build this query with joomla

and that's it. no looping necessary. tested - efficiency is at least 2500 times faster :) (real data migration test)

Answer 2

The explode function returns an array; your first code, does not make any sense for me; and I'm familiar with virtuemart tables; Getting the file_url from the query, I would do:

//Select just the product sku from the file url
$query = $db->getQuery(true);
$query->select($db->qn(array('file_url')))
  ->from($db->qn('#__virtuemart_medias'));
$db->setQuery($query);
$object =  $db->loadObjectList();

The big question is, what do you want to get from the virtuemart tables? Inside the second code, there is no virtuemart_product_id nor a product_sku inside the table virtuemart_medias.

I want to get the product_id from virtuemart_products, based on the sku from the file_url, then insert the virtumart_media_id and virtuemart_product_id into the virtuemart_product_medias table. Effectively linking any media files with the same product sku to the product itself.

//this is e.g. the file-url:
$file-url = '/blabla/stock/knife/picture1.png'; 
$path_parts = pathinfo($file-url);
//get the last dir (i.e.knife)
$skus = explode('/',path_parts['dirname']); // explodes /blabla/stock/knife
$product_sku = array_pop($skus); //here you've to proof if its empty..
if (empty($product_sku)) return false;
$filename = path_parts['basename']; //picture1.png
$db = JFactory::getDbo();
$query = $db->getQuery(true)
  ->select($db->qn(array('virtuemart_product_id','product_sku')))
  ->from($db->qn('#__virtuemart_products'))
  ->where($db->qn('product_sku').' = '.$db->q($product_sku));
$product = $db->loadObject();

then you have to insert the file into the table #__virtuemart_medias, get the virtuemart_media_id from the insertion; and afterwards, you have to insert the relation into the table #__virtuemart_product_medias... good luck. I think a good start is here: https://docs.joomla.org/Inserting,_Updating_and_Removing_data_using_JDatabase you may also use objects with the class JTable in Joomla.