3

I have a varchar(255) field in one of my Jdatabase tables containing values separated by commas. Precisely, it contains numbers separated by commas.

Table 1

I want to retrieve the individual values using a custom PHP module. Actually, I need to select records (retrieve data) from another table which have an id that exists in the comma-separated list of ids.

enter image description here

What would be the best way to achieve this?

  • Hmm, do the ID's need to be comma separated, or can they be stored as a JSON array/object? Using JSON would be the best approach and make the database query you need easier – Lodder Aug 18 '16 at 9:47
  • I have no experience with JSON arrays. As you can see in the images, the ids are present in table 1 separated by commas. I guess the individual ids need to be put in a PHP array, then using a loop we can execute the query to retrieve data from the second table? – saibbyweb Aug 18 '16 at 9:52
  • Ok, do you have access to PhpMyAdmin? If so, what's the column name that contains the comma separated values? – Lodder Aug 18 '16 at 9:53
  • Yes, I do. classes_attended is the column name that contains the comma separated values in table 1. – saibbyweb Aug 18 '16 at 9:55
  • Hmm I started messing round with some code, but I'm personally still unsure as so what the best method would be. There might be some nice MySqli function that can be used for comma separated values. Perhaps a join query. Only method I'd be able to write relatively quickly would not be the best approach – Lodder Aug 18 '16 at 10:31
4

Just 2 simple queries, with first you select commas and then you check if IDs are inside that string. Just tested and it works using MySql IN() function with $commas string value fetched from $db->loadResult().

Can't thing about anything simpler.

// First get all commas
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select($db->qn('Commas'))
      ->from($db->qn('#__classes_attended'))
      ->where($db->qn('Id') . ' = 1');
$db->setQuery($query);

$commas = $db->loadResult();


// Select them from second table
$query = $db->getQuery(true);
$query->clear();
$query->select($db->qn('Data_needed'))
      ->from($db->qn('#__jdatabse_table_2'))
      ->where($db->qn('Id') . ' IN (' . $commas . ')');
$db->setQuery($query);

$results = $db->loadObjectList();
0

What would be the best way to achieve this?

There are two pieces of "best practice" that I am compelled to share for this question.

  1. Don't make more trips to the database than you need to.

    Because your query logic can be baked into a single query, it should be. The hero to call upon in this instance is FIND_IN_SET(). It works in a similar fashion to IN, but IN won't work on a comma-delimited column value.

    Raw Query: (db-fiddle.com demo)

    SELECT name
    FROM `#__table2`
    WHERE FIND_IN_SET(id, (SELECT classes_attended FROM `#__table1` WHERE id = 1))
    

    *This could have been performed as a JOIN instead of a subquery (as shown in the stackoverflow link above my snippet), but I prefer this approach because you only need to relate to a single row one time.

    Joomla/PHP Syntax: (tested locally; includes query dump and error checking)

    $id = 1;
    try {
        $db = JFactory::getDbo();
    
        $subquery = $db->getQuery(true)
                       ->select($db->qn("classes_attended"))
                       ->from($db->qn("#__table1"))
                       ->where($db->qn("id") . " = " . (int)$id);
    
        $query = $db->getQuery(true)
                    ->select($db->qn("name"))
                    ->from($db->qn("#__table2"))
                    ->where("FIND_IN_SET(" . $db->qn("id") . ", (" . $subquery . "))");
    
        $db->setQuery($query);
        JFactory::getApplication()->enqueueMessage($query->dump(), 'info');  // don't show your rendered query to the public
        if (!$result = $db->loadColumn()) {
            echo "<p>No Rows Found</p>";
        } else {
            echo "<pre>";
            var_export($result);  // prints indexed single-dimensional array of values
            echo "</pre>";
        }
    } catch (Exception $e) {
        JFactory::getApplication()->enqueueMessage("Query Syntax Error: " . $e->getMessage(), 'error');  // don't show $e->getMessage() to public
    }
    
  2. Normalize your data structure.

    Using FIND_IN_SET() is a symptom that your database design is not optimized.

    Don't take my word for it, listen to Bill Karwin -- a man who literally wrote a book about bad SQL coding practices.

    You can't optimize this query with indexes. This is one of many reasons why storing comma-separated lists of values is a bad idea in a relational database.

    Source: https://stackoverflow.com/a/44183060/2943403

    Normalizing your data in this case means creating a new table and abstracting the classes_attended data into rows that contain id and class_attended. Doing so will make your data easier to query and allow you to manufacture more complex queries with greater efficiency.

        id    |    classes_attended
    -----------------------------------
         1    |        1,3,6,7
    

    becomes

        id    |    class_attended
    ---------------------------------
         1    |          1
         1    |          3
         1    |          6
         1    |          7
    

    Here is a beginner's link about Normalization.

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.