Should this be two queries?

Supposing I have a database table for ‘products’, then another table ‘features’ which contains features belonging to the products. It’s a “many to many” relationship, so there is another look-up table ‘feat_lut’ which links product IDs to feature IDs.
A single query with two joins will find all the data I need for one product.

$fields = "products.id, pnum, name, pdesc, price, img, features.fdesc";

$query = "SELECT $fields FROM products
JOIN feat_lut ON products.id = pid
JOIN features ON fid = features.id
WHERE products.id = :id" ;

All selected fields are in ‘products’ except for ‘fdesc’ (feature description) which is in ‘features’.
‘pid’ (product ID) and ‘fid’ (feature ID) are in the look-up table.

Running a query like this straight in Mysql will give a row for every feature assigned to the target product, where all data is the same except for the ‘fdesc’.

The result I want (in php) is to create a single object with a property which is an array made up of the features associated with the chosen product.

But using PDO::FETCH_CLASS I get my object with all the product properties, but the features just has a string for one feature.

I’m thinking this maybe isn’t possible without selecting the features in a separate query afterwards.
Or I use the above query, then process the number of rows to get my array of features and build the object manually at the same time.
But PDO does have lots of clever fetch types, most of which I have yet to discover and explore.
So it there an efficient way to do this, or is that wishful thinking?

What I get is like:-

object(product)[3]
  public 'id' => string '1' (length=1)
  public 'pnum' => null
  public 'name' => string 'product 1' (length=9)
  public 'pdesc' => string 'description 1' (length=13)
  public 'fdesc' => string 'feature 1' (length=9)
  public 'price' => null
  public 'img' => string 'photo1.jpg' (length=10)

What I want is like:-

object(product)[3]
  public 'id' => string '1' (length=1)
  public 'pnum' => null
  public 'name' => string 'product 1' (length=9)
  public 'pdesc' => string 'description 1' (length=13)
  public 'fdesc' => array (size=4)
     '0' => string 'feature 1' (length=9)
     '1' => string 'feature 2' (length=9)
     '2' => string 'feature 3' (length=9)
     '3' => string 'feature 4' (length=9)
  public 'price' => null
  public 'img' => string 'photo1.jpg' (length=10)

Unfortunately I don’t know any clever way to do this, although I’ll be interested to see if anyone else knows of one!

I’ve always dealt with situations like this by making two queries and then constructing the final objects manually. I suspect this is probably the optimum solution in terms of efficiency, as otherwise you’re transferring multiple copies of the data for each product in your DB result.

1 Like

Yes, I suppose what I’m wanting is two data sets, a single row of data from products, and multiple rows from features. Which would naturally lend itself to two queries.
But the fact I can select all the required data from a single query lead me to think it may be an option. It’s just the form the data comes in is not useful for the structure of object I require and therefore would need some additional processing to achieve that.
I was just trying something using the single query, using a while to build the array of features, then passing all the data to the object. But it felt a bit messy, when it’s quite neat that pdo can put the product data directly into an object (sans features). But wasn’t sure if it’s still more efficient than a second query.

I thought this may be a fairly standard thing that people need to do, so wondered if there was a standard way of doing it, or some clever pdo tool for it.

You know that feeling when you have some code that works and does exactly what you want it to do. But it’s ugly and verbose enough that it sets off alarm bells that this is not the right way to do it? :grimacing:
Now I’ll try it with two queries…

2 Likes

I was wondering the same and decided to change my search routine into a single query: which is build from a user’s input text search box.

$users input ==> "computer   a   t"

$where = 
   CONCAT( `author`, `memo`) LIKE "%computer%" AND 
   CONCAT( `author`, `memo`) LIKE "%a%" AND 
   CONCAT( `author`, `memo`) LIKE "%t%"

//===========================================
function getCombined( $sWhere )
{
  $dTABLE = dTABLE;

  $result = <<< ____TMP
    SELECT * FROM `$dTABLE` WHERE $sWhere LIMIT 0,5;
    SELECT COUNT(*) AS `recNo`  FROM `$dTABLE` WHERE $sWhere ;
____TMP;

  return $result;
}

// getCombined( $sWhere ) ==> 
$sql =  

  SELECT * FROM `books` WHERE 
     CONCAT( `author`, `memo`) LIKE  "%computer%" AND 
     CONCAT( `author`, `memo`) LIKE  "%a%" AND 
     CONCAT( `author`, `memo`) LIKE  "%t%" LIMIT 0,5;
  SELECT COUNT(*) AS `recNo` FROM `books` WHERE 
     CONCAT( `author`, `memo`) LIKE  "%computer%" AND 
     CONCAT( `author`, `memo`) LIKE  "%a%" AND 
     CONCAT( `author`, `memo`) LIKE  "%t%" ;


================= // ==================
Output:

Title: Franklin and the Computer
Jennings, Sharon (author)
70 Baht #470471 Type: Children

Title: What the Dormouse Said: How the Sixties Counterculture Shaped the Personal Computer Industry
Markoff, John (author)
240 Baht #438796 Type: History

Title: Barron’s AP Computer Science
Teukolsky, Roselyn (author)
240 Baht #442339 Type: Science

Total Results: 3

Edit:
Just noticed some online search result discrepancies :frowning:

I’ve run into this problem a couple of times and I don’t know of any neat way to do this using MySQL and PDO. However, when I really wanted one query I used a query without a join and for the items that were supposed to be an array I used a subquery that selected all required fields as a string using the CONCAT_WS function and then in PHP I deconstructed the string (using explode(), etc.) into array. So in your case I would use a subquery selecting from features. Sometimes I even managed to “fetch” multi-dimensional arrays this way by nesting subqueries and using two different string separators - it worked but looked really convoluted! There is no way to name the fields in this pseudo-array in MySQL so exploding it into a nice associative array requires accessing “fields” by numeric indexes.

But now I use a much cleaner solution - I simply use PostgreSQL. In PostgreSQL I can fetch multiple rows in a subquery and turn them into a JSON string. Then in PHP I just run json_decode() on that string and I get an associative array of sub-items of a row - just how it should be. It’s really nice because it’s like fetching multi-dimensional structures from database and I don’t have to limit myself to flat data :smiley:

3 Likes

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.