PHP, SQL Pivot

Dear Expert,

How to use php or sql make pivot data like attachment?
Pivot.xlsx
arkam chouAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
You gave to change the distribution of your items for each brand to match the information in the output.

I.e. You have beer with 4 TVs 7 radios and 9 magazines but the output you want to end up with is not 4 7 9 but 3, 2, 1 the only way is to alter the number of TVs, radios, and magazines are referenced in the source data for beer.

Within php, you can once you get the data as it is now, and modify the output to the user
Reflecting anything you want not related to the dat.

P
0
Ray PaseurCommented:
The data appears to be internally inconsistent, so it's not likely that we can find a programmatic solution for you.  By "inconsistent" I mean this:

1. In the top example, Beer, TV, 555 are in the same row
2. In the bottom example, Beer, TV intersect at 222

That leaves us with the question, "Which is it?"

The usual design of something like this would have pivot tables to identify many-to-many relationships between any of the related tables.  If one table is "beer" and another table is "tv" then the "beer_tv" table would be the junction table (or pivot table) and it would contain two relevant data columns.  One column would have the key of the row in "beer", the other column would have the key of the related row in "tv".
0
arkam chouAuthor Commented:
sorry for hide many column in excel in attachment. for this requirement I have 3 table
1. dataentry: for input daily data
2. brandname: store name of brand
3. spendtype: store type of spend

for store database like this, can I get result like in attachment?
Pivot.xlsx
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Ray PaseurCommented:
You can get some kind of result, but the final data set still does not make sense.  Here's what I understand and do not understand.

The Brand Name and Spend Type tables make sense.   I understand these to contain keys and named columns.

The upper left table DataEntry relates brandID and spendID to NumberofSpend column.  This makes sense, too.

The upper right table replaces brandID and spendID with BrandName and SpendName - this makes sense.

The lower right table is where it breaks down.  Look at the Beer row.  In the upper right table, Beer:Radio shows a value of 222, but in the lower right table, Beer:Radio shows 555.  I cannot understand the rules that could get 555 out of the other data.
0
arkam chouAuthor Commented:
sorry for wrong putting in order.

Please find attachment again.
Pivot.xlsx
0
Ray PaseurCommented:
OK, thanks - it looks correct now.  I wasn't sure whether it was a copy/paste error or whether I did not understand something about the data set, so this new example makes much more sense!  For better or worse, computer programming is an activity that requires meticulous attention to detail.

I don't know how much background you have in the principles of computer science and data base structure, but if you're new to this sort of thing, this article can help you find some good learning resources, and more importantly, can help you stay away from the many bad examples of PHP code that litter the internet.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html

As time permits today I'll try to put together an example that will show you how to relate these tables in a SQL query.  Once you see how the code and data work together, it will probably make sense.
0
Julian HansenCommented:
This should be close to what you want
<?php
// SAMPLE DATA
$rows = array(
  array('d.01','Bank','TV','111'),
  array('d.02','Beer','Radio','222'),
  array('d.03','Drug','Magazine','333'),
  array('d.04','Bank','Radio','444'),
  array('d.05','Beer','TV','555'),
  array('d.06','Drug','TV','666'),
  array('d.07','Bank','Magazine','777'),
  array('d.08','Beer','Magazine','888'),
  array('d.09','Drug','Radio','999')
);

// ARRAY TO STORE RESULTS OF PIVOT
$pivot = array();
// ARRAY TO STORE HEADERS FOR SPEND
$spends = array();

// LOOP THROUGH RESULT SET ADDING ITEMS TO PIVOT ARRAY
foreach($rows as $row) {
  $brand = $row[1];
  if (empty($pivot[$brand])) {
    $pivot[$brand] = array();
  }
  // CREATE HEADER ARRAY
  $spend = $row[2];
  $spends[$spend] = $spend;
  $pivot[$brand][$spend] = $row[3];
}
// OUTPUT RESULTS IN A TABLE
echo <<< TABLE
  <table>
    <tr>
      <th>Brand Name</th>
TABLE;
// DUMP THE HEADER ROW OF SPENDS
foreach($spends as $b) {
  echo <<< TH
      <th>{$b}</th>
TH;
}
echo <<< TR
    </tr>
TR;
// CREATE A DATA ROW FOR EACH ITEM IN THE PIVOT
// USING THE KEY (BRAND) AS THE FIRST TD ELEMENT
foreach($pivot as $b => $p) {
  echo <<< TR
    <tr>
      <td>{$b}</td>
TR;
  foreach($spends as $b) {
  
    echo <<< TD
      <td>{$p[$b]}</td>
TD;
  }
  echo <<< TR
    </tr>
TR;
}
echo <<< TABLE
  </table>
TABLE;
?>

Open in new window

0
Julian HansenCommented:
The SQL query that can be used to extract the required data from the database is as follows

SELECT B.`brandName`, s.`SpendName`, d.`numberofSpend` 
FROM dataentry d LEFT JOIN brandname b ON d.`brandID` = b.`brandId`
LEFT JOIN spendtype s ON s.`SpendID` = d.`spendID`;

Open in new window

0
arkam chouAuthor Commented:
Dear julianH,

this is right. can we get result like your php by doing all with sql query?
0
arnoldCommented:
Do you have a sample PHP that connects to your mysql server and your db, then generatse the query from your other question.

presumably you have something like the msqli_query
http://www.w3schools.com/php/func_mysqli_query.asp
http://www.w3schools.com/php/php_ref_mysqli.asp


In short, the example julian provided is what you will use with the response from your mysql query returns a 3x3 array.
You will fetch a row at a time, and separate each element with the <tr><td </td><td>

etc.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.