1

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.

  • You're using $db-> explode(). Try using just explode(). What exacting are you exploding though? – Lodder Mar 29 '16 at 13:34
  • I have deleted my previous comment and updated my post, as i figured i need to select the values as part of the query, so i can then use them in an insert query via a loop. – Dtorr1981 Mar 29 '16 at 13:54
  • @Dtorr1981 I have the necessary knowledge to write a single query that will resolve your question. What I don't have is any experience with virtuemart nor any table structure/data to play with. If you can create a minimal SQLFiddle Demo and express your desired result, I am confident that I can code this up for you (and future researchers). Please help me to help you and this community. Resolving open questions on StackExchange sites helps everyone. – mickmackusa Jun 11 '18 at 3:35
  • Again, I would like to offer my assistance if you can provide sample table schemas and data for me to test with and your exact expected result. – mickmackusa Dec 8 '18 at 12:14
  • I am still happy to help. If you supply the ingredients, I'll bake the cake. – mickmackusa Jan 28 at 11:48
2

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)

0

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.

  • I want to get the product_id from virtuemart_products, where the prudtc_sku = 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. Does that make sense? – Dtorr1981 Mar 29 '16 at 14:34
  • I updated answer – Tom Kuschel Mar 29 '16 at 15:26
  • Hi Tom, thank you. I have code already that inserts the file details into the #__virtuemart_medias table, its the last bit i am stuck with? I have read the joomla docs page and still can't figure it out. I'm guessing i need to use an inner join between the virtuemart_products and the virtuemart_medias table? But this is where i get stuck, as i don't know what i need to do to get the $values for the insert query. – Dtorr1981 Mar 30 '16 at 9:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.