I have a query which gets the information that I want using SUBSTRING_INDEX in MySQL. I am trying to convert this to use the Joomla query building syntax, 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 I really can't figure out how to implement the SELECT statement?

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

  • 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 Mar 30, 2016 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, 2016 at 15:30

1 Answer 1

$db = JFactory::getDBO();
try {
    $query = $db->getQuery(true)
                ->select("b.product_sku, a.virtuemart_media_id, a.virtuemart_product_id")
                ->from("#__virtuemart_medias a")
                ->innerJoin("#__virtuemart_products b ON a.SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(a.file_url, '/', -1), '.', 1), '-', 1) = b.product_sku");
    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 agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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