I have a query which gets the information i want using SUBSTRING_INDEX in MySQL, i am trying to convert this to use with the Joomla API, but i am not sure how it should be formatted? Here is my query:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(`file_url`, '/', -1), ".", 1),"-",1) AS product_sku, `virtuemart_media_id`, `virtuemart_product_id` FROM `jos_virtuemart_medias` INNER JOIN `jos_virtuemart_products` ON SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(`jos_virtuemart_medias`.`file_url`, '/', -1), ".", 1),"-",1) = `jos_virtuemart_products`.`product_sku` WHERE 1

I'm getting stuck at the first hurdle as im really can't figure out how to implement the Select statement?

Any advise would be most appreciated? I have had a look at joomla docs but it doesn't cover the inclusion of MySQL functions.

Regards Donna

  • sometime it's easier to just use regular SQL like you did and use $db->setQuery($query). I'm not sure you can do that with the SQL Joomla API – Pierre-André Vullioud Mar 30 '16 at 14:48
  • @Pierre-AndréVullioud thank you. Are you able to help me convert the select into an insert statement? Im struggling to understand it due to the inner join. – Dtorr1981 Mar 30 '16 at 15:30
$db = JFactory::getDBO();
try {
    $query = $db->getQuery(true)
                ->select("B.product_sku, A.virtuemart_media_id, A.virtuemart_product_id")
                ->from("jos_virtuemart_medias A")
                ->innerJoin("jos_virtuemart_products B ON A.SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(A.file_url, '/', -1), '.', 1), '-', 1) = B.product_sku");
    $db->setQuery($query);
    if (!$results = $db->loadAssocList()) {
        echo "No Qualifying Results";
    } else {
        echo "<table>";
            echo "<tr>";
                echo "<th>Product SKU</th>";
                echo "<th>Media ID</th>";
                echo "<th>Product ID</th>";
            echo "</tr>";
            foreach ($results as $row) {
                echo "<tr>";
                    echo "<td>{$row['product_sku']}</td>";
                    echo "<td>{$row['virtuemart_media_id']}</td>";
                    echo "<td>{$row['virtuemart_product_id']}</td>";
                echo "</tr>";
            }
        echo "</table>";
    }
} catch (Exception $e) {
    echo "Syntax Error";
    // echo $query->dump();    // don't show actual details to the public
    // echo $e->getMessage();  // don't show actual details to the public
}
  • Full disclosure: I've never used virtuemart; I don't know if these columns exist or if this query logic will be successful. I am merely converting your posted query.
  • Because product_sku in your JOIN contains your desired file_url substring, we'll just use that table's column value to avoid that nasty triple-SUBSTRING_INDEX() call in the SELECT clause.
  • By assigning table aliases (A & B) you can reduce query bloat.
  • I don't know if there is a cleaner way to isolate the file_url substring because I don't know what format that data is in -- I must say it looks rather heavy-handed though.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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