Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2008

    Unanswered: creating a query to return an x/y matrice of unknown x and y length

    I'm trying to find a way to store a number of x/y matrices and their content in a SQL table, and creating a single query that will return one of these matrices. The reason for wanting to do this is that I am doing an application where I need to have an unknown number of HTML tables with an unknown number of rows and columns.

    I have three tables in a mySQL db: list, labels, and content.

    The idea is to be able to create a matrix with entries from list along one axis, entries from labels along another axis, and entries from content as the intersection of the two. The content table has a field that corresponds to a field in labels, and a field that corresponds to a field in list so that I am able to place it correctly in the matrix.

    Like so:

    list(1)----| content(list1, label1) | content(list1, label2) | content(list1, labeln)
    list(2)----| content(list2, label1) | content(list2, label2) | content(list2, labeln)
    list(n)----| content(listn, label1) | content(listn, label2) | content(listn, labeln)

    There must be a way of doing this in a single SQL query, but I can't quite figure out how.

    Currently I have this PHP code to do it, but it is quite a mess, lots of unnecessary loops, and gets problematic once I want to sort the query based on the content table.

    (simplified for easier reading)

    PHP Code:
    //first get the labels in an array
    $q "SELECT column_name, type, label
        FROM label
        WHERE module_id = 
        ORDER BY column_number
    $result mysql_query($q,$conn);

    $counter 1;
    $row mysql_fetch_array($result)){
    $_SESSION['list']['column_name'][$counter] = $row['column_name']; 
    $_SESSION['list']['type'][$counter] = $row['type']; 
    $_SESSION['list']['label'][$counter] = $row['label']; 
    $_SESSION['list']['length'] = $counter;
    $counter ++;

    $q "SELECT name, id FROM list Where moduleid = $moduleid";      
    $result mysql_query($q,$conn) or die(mysql_error());
    $c 1;
    $row mysql_fetch_array($result)){ 
    $tablecolumn 0;
    $c<=$_SESSION['list']['length']) { // for each record in column_counter array find out which content post matches
    $tablecolumn $tablecolumn++;
    $label $_SESSION['list']['label'][$c];
    $q1 "SELECT * FROM content Where list_id = '$row[id]' and label ='$label'";  // get items that correspons to current row
    $result1 mysql_query($q1,$conn) or die(mysql_error())    ;
    $array mysql_fetch_array($result1)){
    $list['list'][$c][$tablecolumn] = array('1'$array[content_date], 0) ;
    $list['list']['columns'] = $tablecolumn;

    So the question is: can I create the array in a single SQL query, and if so how?

    I don't necessarily need a full answer, guidance in the right direction would be just as great. I don't mind thinking a bit for myself.

    I'm quite new to both PHP and MYSQL so forgive me if my code is a mess, or if this is a stupid question...

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    i'm going to ask you to step away from your particular problem and think about the following one

    suppose you have an e-commerce store, with an unknown number of customers, who purchase some unknown number of items from an inventory of an unknown number of products...

    like this --

    customer(1)--| content(customer1, product1) | content(customer1, product2) | content(customer1, productn)
    customer(2)--| content(customer2, product1) | content(customer2, product2) | content(customern, productn)

    is this how you see it? because SQL is lousy at producing this type of layout

    what if a customer doesn't buy any of a particular product? there'd be a hole in the matrix

    what if nobody bought a particular product, then there'd be an entire column empty, and now you have to do extra work to actually make sure that column is still there, albeit empty, and not missing entirely

    you see where i'm going with this?

    normally, you get results from sql that look like this:

    content(customer1, product42)
    content(customer2, product9)
    content(customer2, product37)

    in other words, the result is a simple table

    if you wanna throw them into a matrix, you can do that in the application layer, yes?

    okay, let me ask you another question: do you know about join queries so that you don't have to perform a second query inside a loop based on an initial query? | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2008
    First - thanks a lot for your reply.

    "is this how you see it? because SQL is lousy at producing this type of layout" - unfortunately yes....

    Maybe if I explain a bit of the background of the application I can better explain the problem I'm having.

    I have an application where users can create one or more extended to-do lists. The user can append one or more columns, each column being of a certain type. This type indicates what content is to be displayed in the HTML cells in the column. For instance type 1 is text, type 2 is a date, etc. Maybe one user needs a to-do list with name(type:1), description(type:1) and due date(type:2) and another needs a to-do list with name(type:1), startdate(type:2), enddate(type:2) and and first milestone(type:2)

    Now I can't simply create a table to hold the information since I don't know how many fields it should have. (technically I suppose I could create a table with 100 fields to make sure I've got plenty, but that doesn't seem like a good solution)

    So I thought of the above solution which lets a user create as many columns in his table as he likes.

    In your customer example I would want to return an empty field if there was no match, like so:

    content(customer1, product2)
    content(customer2, product2)
    content(customer3, '')

    The empty result in the array would simply display an empty cell in the HTML table. (A credible scenario would be a user having a to-do list with 3 columns, and only filling out two of them for a particular entry)

    About joins - yes I have heard of them and tried looking into it. Maybe this is the key to doing this, but I haven't been able to make it work.

    Can I create a query like this (pseudocode):

    SELECT name FROM list WHERE moduleid = $moduleid";

    JOIN SELECT FROM content, list WHERE ´content.$label1 = somenumber AND content.list_id = [the current ID in list that were parsing through]

    JOIN SELECT FROM content, list WHERE ´content.$label2 = somenumber AND content.list_id = [the current ID in list that were parsing through]

    where I select the rows I'm interested in in the list table, and then create a number of joins equal to the number of columns in the users to-do list, appending a column for each join.

    And thanks again for replying

  4. #4
    Join Date
    Apr 2002
    Toronto, Canada
    you say you have this application already? hard to understand that it's already working if you are strugg-a-ling (sorry; obligatory PTI reference) with the database design

    what you seem to want is called an EAV (entity-attribute-value) design

    two points:

    1. EAV is fairly complex, and you should not attempt it if you have little experience with joins


    2. EAV is bad news in any case

    see | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts