Basically this blog post is about dynamic columns in MySQL queries. Since you can't run "pivot" queries in MySQL, how do you write queries that return an arbitrary number of columns (perhaps based on the number of rows in another table)? The answer is simple: you can't... at least not in a single query. The best way to do this is to run two queries and generate the SQL for the 2nd query based on the results of the 1st query. Imagine this table of product sales: ProductID CustomerID Unit Sales 1 1 20 1 2 50 2 1 20 2 2 70 2 3 90 Maybe you want your result set to look like this... ProductID Customer 1 Customer 2 Customer 3 1 20 50 0 2 20 70 90 Write this query first: SELECT CustomerID, CustomerName FROM `customers` Store the results into $rows. For each $row, append this string to $fields: ", SUM(CASE WHEN CustomerID=$row[0] THEN `Unit Sales` ELSE 0 END) AS $row[1]" Then... write this query: "SELECT ProductID
Blog about science and technology, especially software development and space ships. ;) Blake is a software developer who specializes in building inventory management and project management solutions for small or mid-sized businesses. He also spends a fair amount of time on embedded software and database research.