PHP - Pivot Table-like Output (indefinite Rows And Columns) From 'flat Data'

Hi All

I have been trying to solve my query problem for days with no luck. Please help

I'm trying to build a pivot table query from 2 part query in php.

Both query runs ok in mysql query browser but once I place the query into php it doesn't work.

 

Here is the my code

I have a table I want to turn into a pivot table

+------+---------+-------------+--------+
| id   | product | salesperson | amount |
+------+---------+-------------+--------+
|    1 | radio   | bob         | 100.00 |
|    2 | radio   | sam         | 100.00 |
|    3 | radio   | sam         | 100.00 |
|    4 | tv      | bob         | 200.00 |
|    5 | tv      | sam         | 300.00 |
|    6 | radio   | bob         | 100.00 |
+------+---------+-------------+--------+

//Building First Part of the Query to get all the columns
    $sql = "SELECT DISTINCT
  CONCAT(',SUM(IF(salesperson = "',salesperson,'",1,0)) AS `',salesperson,'`')
  AS countpivotarg
FROM  sales
WHERE salesperson IS NOT NULL ";


 
    $result = $mdb2->query($sql);
    $count = $result->numRows();


       if ($count>0){
 
       while ($row = $result->fetchRow()){


   $q .=  $row['countpivotarg']

   }


   }


/*This should give the following results to plug into the next query statement*/,
,SUM(IF(salesperson = "bob",1,0)) AS `bob`
,SUM(IF(salesperson = "sam",1,0)) AS `sam`


//Plug first query results into a second query statement//

$sql = 'SELECT product '  . $q .  ',COUNT(*) AS Total
       FROM sales
        GROUP BY product WITH ROLLUP;';


 
    $result = $mdb2->query($sql);
    $count = $result->numRows();


       if ($count>0){
 
       while ($row = $result->fetchRow()){


    echo "rows here";

   }
   }
 




 
//Results
+---------+------+------+-------+
| product | bob  | sam  | Total |
+---------+------+------+-------+
| radio   |    2 |    2 |     4 |
| tv      |    1 |    1 |     2 |
| NULL    |    3 |    3 |     6 |
+---------+------+------+-------+



Any help would be greatly appreciated.

Thanks

I'm having trouble with the logic for my query. Printing $sql gives me the following:

SELECT r.id,r.created,r.firstname,r.lastname,r.address1,r.city,r.state,r.zip,r.phone,r.email,r.comments,Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BBQ' at line 1

"BBQ" is the first $val from the loop. What am I missing?

Code: [Select]
      $sql = "SELECT r.id,r.created,r.firstname,r.lastname,r.address1,r.city,r.state,r.zip,r.phone,r.email,r.comments,";

            $dbQuery = mysql_result(mysql_query("SELECT description FROM events"), 0,0);           
        $size = count($dbQuery);           
        $i = 0;
            foreach ($dbQuery as $val)
            { //02
           if($val != '')
            { //02           
                       $sql .= "MAX(CASE e.description WHEN '". $val ."' THEN e.description END) AS `". $val ."`";                 
                  }
                  $i++;
          if($i != $size) $sql .= ",";
                 
            }//02
       
            if (!mysql_query($dbQuery)) {
                  die('Error: ' . mysql_error());
            }

     $sql .= "FROM requests r
                   LEFT OUTER JOIN (
                         registration_xref xref
                        INNER JOIN events e ON e.id_events = xref.event_id
                   ) ON r.id = xref.attendee_id
                   GROUP BY r.id";

Hello,

I have a script that processes data from a form, and then outputs data from a MySQL table based on which selections are made on the form.

What I'm trying to do is create columns showing data for each month depending on which months the user selects, and then a "Total" column at the end of that. The output first determines the "family" the results are in, then the ID the product is for the parent family, and then it outputs the data based on what ID is listed.

Unfortunately, I have no idea how to do this without nesting while loops inside other while loops which I know is bad for server performance - and yes this does take a long time to complete.  Here is some of my code for this to show what I'm doing wrong:

Code: [Select]
$searchdatabase5 = "SELECT field3 FROM slssum2 WHERE company = '$companyname' AND CONCAT(year,month) BETWEEN '$startyear$startmonth' AND '$endyear$endmonth' AND field3 != 0 AND field4 != '995' GROUP BY field3";
$run5 = mysql_query($searchdatabase5) or die(mysql_error());
while($row5 = mysql_fetch_assoc($run5))
{
 $field5_3 = $row5['field3'];
 echo '<tr><td colspan="0"><hr style="width: 100%; height: 1px; color: #a0a0a0;"></td></tr><tr><td colspan="0" bgcolor="#ccffcc"><strong>FAMILY '. $field5_3 .'</strong><br /></td></tr>';
   $searchdatabase12 = "SELECT field4 FROM slssum2 WHERE company = '$companyname' AND CONCAT(year,month) BETWEEN '$startyear$startmonth' AND '$endyear$endmonth' AND field3 = '$field5_3' AND field4 != 0 GROUP BY field4";
   $run12 = mysql_query($searchdatabase12) or die(mysql_error());
   while($row12 = mysql_fetch_assoc($run12))
   {
    $field12_4 = $row12['field4'];
$searchdatabase6 = "SELECT field3,field4,field5,field6 FROM slssum2 WHERE company = '$companyname' AND CONCAT(year,month) BETWEEN '$startyear$startmonth' AND '$endyear$endmonth' AND field4 = '$field12_4' GROUP BY field3,field4,field5";
$run6 = mysql_query($searchdatabase6) or die(mysql_error());
while($row6 = mysql_fetch_assoc($run6))
{
$field6_3 = $row6['field3'];
$field6_4 = $row6['field4'];
$field6_5 = $row6['field5'];
$field6_6 = $row6['field6'];
echo '<tr><td colspan="0">'. $field6_5 .' - '. $field6_6 .'</td></tr><tr><td valign="middle" align="left" bgcolor="#ccc" width="183px">Units</td>';
$searchdatabase7 = "SELECT * FROM slssum2 WHERE company = '$companyname' AND CONCAT(year,month) BETWEEN '$startyear$startmonth' AND '$endyear$endmonth' AND field5 = '$field6_5' ORDER BY year,month ASC";
$run7 = mysql_query($searchdatabase7) or die(mysql_error());
while($row7 = mysql_fetch_assoc($run7))
{
$field7_3 = $row7['field3'];
$field7_4 = $row7['field4'];
$field7_5 = $row7['field5'];    
$field7_9 = $row7['field9'];
$field7_10 = $row7['field10'];
$field7_month = $row7['month'];
$field7_year = $row7['year'];
$startmonth2 = ltrim($startmonth, '0');
echo '<td bgcolor="#e6e6e6" width="95px" nowrap="nowrap" align="right">'; echo $field7_10; echo '</td>';
  }
Here is the output:



I know the code is very bad, but what would be the best way to recreate what I have in the image?

Thank you.

right now it displays the first column of the array as a header for each foreach loop then i've got the data displayed under each header and it has 4 columns or a number of my choosing with data displayed under the Table Headings. I am trying to make it display 4 or so columns but with the each group having a certain number of grouped table (data/header) in each column. a different amount for each column that i can choose

IF Possible i'd like the choice of being able to remove the headers eg. Z when there is no data in them.

This is an example of how it is being displayed right now

Any Help would be greatly appreciated

Quote

A
Airlie Beach   Andergrove   Alexandra   Armstrong Beach
Alligator Creek
B
Bucasia   Blacks Beach   Beaconsfield   Bakers Creek
Balberra   Bloomsbury   Breadalbane   Ball Bay
Belmunda
C
Cannonvale   Calen   Crystal Brook   Cremorne
Chelona   Campwin Beach   Cape Hillsborough
Conway


Heres The Code i have so far.

Code: [Select]
<?php
  $file = "widget.csv";
@$fp = fopen($file, "r") or die("Could not open file for reading");
$outputArray = array();
$count = 0;

while(($shop = fgetcsv($fp, 1000, ",")) !== FALSE)
{
    $outputArray[array_shift($shop)] =  array_filter($shop);
}

echo "<table>";
foreach ($outputArray as $alphabetical=>$value){
    echo "<th colspan='4'><b>" . $alphabetical ."</b></th>";
    echo "<tr>";
foreach ($value as $key){
$afterkey=preg_replace('/\s+/','-',$key);
    if ($count == 4){
    echo '</tr><tr><td><a href="map.php?mapaddress='.$afterkey.'-qld&mapname='.$key.'">'.$key.'</a></td>';
    $count = 0;
    }else{
    echo '<td><a href="map.php?mapaddress='.$afterkey.'-qld&mapname='.$key.'">'.$key.'</a></td>';
    }
$count++;    
}
    echo "</tr>";
$count = 0;
}
    echo "</table>";

    echo "\n<br />";

?>


Heres an example of what i am trying to do.

A D K N S AlexandraHeadlands DickyBeach KingsBeach Nambour SandstonePoint Aroona Diddillibah KielMountain Ninderry ShellyBeach Doonan KundaPark NoosaHeads SippyDowns B Dulong Kuluin Ningi SunriseBeach Beachmere DeceptionBay Kilcoy NorthArm SunshineBeach BanksiaBeach Noosaville Scarborough Beerburrum E L Beerwah EerwahVale Landsborough O T Bellara Elimbah Tanawha Bellmere Eudlo M P TowenMountain Birtinya Eumundi Maleny Petrie Tewantin Bongaree Mapleton Palmview TwinWaters Bokarina F Marcoola Palmwoods BribieIslandArea Flaxton MarcusBeach Parklands U Buddina ForestGlen MaroochyRiver Parrearra UpperCaboolture Burnside Maroochydore PeregianBeach Buderim G Minyama Pinbarren V Burpengary GlassHouseMountains MoffatBeach PointArkwright Valdora BliBli Mons PelicanWaters H Montville PacificParadise W C Highworth Mooloolaba WeybaDowns CoolumBeach Hunchy Mooloolah Q Warana Caboolture MountainCreek WestWoombye CabooltureSouth I MountCoolum R Woombye Caloundra ImageFlat Morayfield Rosemount Woorim CastawaysBeach Mudjimba Redcliffe WamuranBasin Chevallum J Woodford CoesCreek WoodyPoint Cooroy Wamuran Currimundi Wurtulla X Y YandinaCreek Yandina Z

Hello, I seem to have some problem with my script that has a goal of outputting data about the file size when a filename is queried.   The sql table name is file The table columns are as followed:  id | name | mime | size   The file name is stored in name. The script that i have that gets the file name is:

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">

	<head>
		<meta http-equiv="content-type" content="text/html;charset=utf-8" />
		<meta name="generator" content="Adobe GoLive" />
		<title>File Select</title>
					
	<!--The following script tag downloads a font from the Adobe Edge Web Fonts server for use within the web page. We recommend that you do not modify it.--><script>var __adobewebfontsappname__="dreamweaver"</script><script src="http://use.edgefonts.net/aguafina-script:n4:default.js" type="text/javascript"></script>
</head>

	 <body>
			
		<div id="title">
			  <h3 align="center">File Upload</h3>
			</div>
  <form action="result.php" method="post" name="fileID" target="_self" class="inp" AUTOCOMPLETE="ON">       
 <h1>
 
<!--Input file name-->

	<label for="fileID">File Name: </label>
              <input type="text" name='file1' id='sampleID' list="samp"> </input><br>
             <datalist id="samp">
 
<?php
$connect = mysql_connect('localhost', 'root', '');


mysql_select_db("test_db");
$query = mysql_query("SELECT * FROM  `file` ORDER BY  `file`.`name` ASC LIMIT 0 , 30");
WHILE ($rows = mysql_fetch_array($query)):
   $File_name = $rows['name'];  
  
echo "<option value=$File_name>$File_name/option> <br>";
endwhile;
?>
</datalist>

<input type="submit" class="button" >
</form>
</body>
</html>

 

Here is a link to the website I am designing. http://173.167.65.189/singleton/
the username and password is ksingleton
at the bottom where the calendar is the query is searching for scheduled events, i am only posting the company_id right now,  the problem I am having is I want a scheduled event that continues through the week to stay in line with the one before.  But if the schedule before ended and a new one continues it moves the next date company_id up.  How can i align the table rows or create an empty table row on all other days if there was a schedule before.  here is the code for the calendar.

Quote

<?php
$db = new mysqli();
$setdate=strtotime($_GET['date']);
$session=$_GET['session'];
$calview=$_GET['calview'];
if ($setdate <> "") {
   $date=$setdate;
} else {
$date=time();
}
$day=date('d',$date);
$month=date('m',$date);
$year=date('Y',$date);
$first_day=mktime(0,0,0,$month,1,$year);
$title=date('F',$first_day);

$day_of_week = date('D', $first_day);

switch($day_of_week) {
case "Sun": $blank = 0;break;
case "Mon": $blank = 1;break;
case "Tue": $blank = 2;break;
case "Wed": $blank = 3;break;
case "Thu": $blank = 4;break;
case "Fri": $blank = 5;break;
case "Sat": $blank = 6;break;
}

$days_in_month = cal_days_in_month(0, $month, $year);

echo "<table cellpadding=0 cellspacing=0 border=0>";
echo "<tbody>";
echo "<tr>";
echo "<td><a href=?session=$session&calview=day>Day</a></td>";
echo "<td><a href=?session=$session&calview=week>Week</a></td>";
echo "<td><a href=?session=$session&calview=month>Month</a></td>";
echo "</tr>";
echo   "<tr>";
echo      "<td colspan=3>";
if ($calview=="month") {
echo         "<table border=1 width=800>";
echo            "<tbody>";
echo               "<tr height=25><th colspan=7> $title $year </th></tr>";
echo               "<tr height=25><td width=58 align=center>Sunday</td><td width=58 align=center>Monday</td><td width=58 align=center>Tuesday</td><td width=58 align=center>Wednesday</td><td width=58 align=center>Thursday</td><td width=58 align=center>Friday</td><td width=58 align=center>Saturday</td></tr>";

$day_count = 1;

echo               "<tr height=50>";

while ( $blank > 0 ) {
echo               "<td></td>";
$blank = $blank-1;
$day_count++;
}

$day_num = 1;

while ($day_num <= $days_in_month ) {
$newdate = $month ."/". $day_num ."/". $year;
$msqlnewdate = date('m/d/Y',strtotime($newdate));
echo "<td valign=top><table cellpadding=0 cellspacing=0 border=0><tr><td><a href=?session=$session&calview=day&date=$newdate> $day_num </a></td></tr>";
$db = new mysqli('localhost','username','password', 'rsdata');

if (!$db) {
   echo 'ERROR: Could not connect to database.';
} else {
$query = $db->query("SELECT * FROM rentals WHERE date_format(str_to_date(pdate_out,'%Y-%m-%d'), '%m/%d/%Y') <= '$msqlnewdate' AND date_format(str_to_date(pdate_in, '%Y-%m-%d'), '%m/%d/%Y') >= '$msqlnewdate'");
if ($query) {
   while ($result = $query->fetch_object()) {
      echo "<tr><td>".$result->company_id."</td></tr>";
   }
}
echo "</table>";
}


$day_num++;
$day_count++;

if ($day_count > 7) {
echo "</tr><tr>";
$day_count=1;
}
}

while ($day_count >1 && $day_count <=7 ) {
echo "<td></td>";
$day_count++;
}
echo "</tr></table>";
} else if ($calview=="week") {

} else {

}
         
echo      "</td>";
echo   "</tr>";
echo "</tbody>";
echo "</table>";
?>


MOD EDIT: DB credentials edited out.

Now I am stumped this is far any help would be awesome

This is what I have gotten so far, this is what it does right now is display each Header above each bit of data and spans across 5 columns for each foreach loop but what i am trying to understand here is i am trying to get the header then the data then another header and data and so on but only about 4 or number of my choosing grouped header/data but select how many are in each column up to 5 columns. Heres an example of what i am trying to do.

A D K N S AlexandraHeadlands DickyBeach KingsBeach Nambour SandstonePoint Aroona Diddillibah KielMountain Ninderry ShellyBeach Doonan KundaPark NoosaHeads SippyDowns B Dulong Kuluin Ningi SunriseBeach Beachmere DeceptionBay Kilcoy NorthArm SunshineBeach BanksiaBeach Noosaville Scarborough Beerburrum E L Beerwah EerwahVale Landsborough O T Bellara Elimbah Tanawha Bellmere Eudlo M P TowenMountain Birtinya Eumundi Maleny Petrie Tewantin Bongaree Mapleton Palmview TwinWaters Bokarina F Marcoola Palmwoods BribieIslandArea Flaxton MarcusBeach Parklands U Buddina ForestGlen MaroochyRiver Parrearra UpperCaboolture Burnside Maroochydore PeregianBeach Buderim G Minyama Pinbarren V Burpengary GlassHouseMountains MoffatBeach PointArkwright Valdora BliBli Mons PelicanWaters H Montville PacificParadise W C Highworth Mooloolaba WeybaDowns CoolumBeach Hunchy Mooloolah Q Warana Caboolture MountainCreek WestWoombye CabooltureSouth I MountCoolum R Woombye Caloundra ImageFlat Morayfield Rosemount Woorim CastawaysBeach Mudjimba Redcliffe WamuranBasin Chevallum J Woodford CoesCreek WoodyPoint Cooroy Wamuran Currimundi Wurtulla X Y YandinaCreek Yandina Z


This is an example of how it is being displayed right now

A
Airlie Beach   Andergrove   Alexandra   Armstrong Beach
Alligator Creek
B
Bucasia   Blacks Beach   Beaconsfield   Bakers Creek   
Balberra   Bloomsbury   Breadalbane   Ball Bay   Belmunda
C
Cannonvale   Calen   Crystal Brook   Cremorne   
Chelona   Campwin Beach   Cape Hillsborough   
Conway



Heres The Code i have so far.


Code: [Select]
<?php
  $file = "widget.csv";
@$fp = fopen($file, "r") or die("Could not open file for reading");
$outputArray = array();
$count = 0;

while(($shop = fgetcsv($fp, 1000, ",")) !== FALSE)
{
    $outputArray[array_shift($shop)] =  array_filter($shop);
}

echo "<table>";
foreach ($outputArray as $alphabetical=>$value){
    echo "<th colspan='4'><b>" . $alphabetical ."</b></th>";
    echo "<tr>";
foreach ($value as $key){
$afterkey=preg_replace('/\s+/','-',$key);
    if ($count == 4){
    echo '</tr><tr><td><a href="map.php?mapaddress='.$afterkey.'-qld&mapname='.$key.'">'.$key.'</a></td>';
    $count = 0;
    }else{
    echo '<td><a href="map.php?mapaddress='.$afterkey.'-qld&mapname='.$key.'">'.$key.'</a></td>';
    }
$count++;    
}
    echo "</tr>";
$count = 0;
}
    echo "</table>";

    echo "\n<br />";

?>


Heres my CSV File:


Quote

A,Airlie Beach,Andergrove,Alexandra,Armstrong Beach,Alligator Creek,,,,,,,,,,,,,,
B,Bucasia,Blacks Beach,Beaconsfield,Bakers Creek,Balberra,Bloomsbury,Breadalbane,Ball Bay,Belmunda,,,,,,,,,,
C,Cannonvale,Calen,Crystal Brook,Cremorne,Chelona,Campwin Beach,Cape Hillsborough,Conway,,,,,,,,,,,
D,Dows Creek,Dumbleton,Dolphin Heads,,,,,,,,,,,,,,,,
E,Eimeo,Eton,Erakala,,,,,,,,,,,,,,,,
F,Foulden,Foxdale,Flametree,Farleigh,Freshwater Point,,,,,,,,,,,,,,
G,Glen Isla,Glenella,,,,,,,,,,,,,,,,,
H,Homebush,Hampden,,,,,,,,,,,,,,,,,
I,,,,,,,,,,,,,,,,,,,
J,Jubilee Pocket,,,,,,,,,,,,,,,,,,
K,Kinchant Dam,Kolijo,Koumala,Kuttabul,,,,,,,,,,,,,,,
L,Laguna Quays,,,,,,,,,,,,,,,,,,
M,McEwens Beach,Mackay,Mackay Harbour,Mandalay,Marian,Mia Mia,Middlemount,Midge Point,Mirani,Moranbah,Mount Charlton,Mount Jukes,Mount Julian,Mount Marlow,Mount Martin,Mount Ossa,Mount Pelion,Mount Pleasant,Mount Rooper
N,Narpi,Nebo,Nindaroo,North Eton,,,,,,,,,,,,,,,
O,Oakenden,Ooralea,,,,,,,,,,,,,,,,,
P,Palmyra,Paget,Pindi Pindi,Pinevale,Pioneer Valley,Pleystowe,Preston,Proserpine,,,,,,,,,,,
Q,,,,,,,,,,,,,,,,,,,
R,Racecourse,Richmond,Riordanvale,Rosella,Rural View,,,,,,,,,,,,,,
S,St Helens Beach,Sandiford,Sarina,Seaforth,Slade Point,Shoal Point,Shute Harbour,Shutehaven,Strathdickie,Sugarloaf,Sunnyside,,,,,,,,
T,Te Kowai,The Leap,,,,,,,,,,,,,,,,,
U,,,,,,,,,,,,,,,,,,,
V,Victoria Plains,,,,,,,,,,,,,,,,,,
W,Wagoora,Walkerston,Woodwark,,,,,,,,,,,,,,,,
X,,,,,,,,,,,,,,,,,,,
Y,Yalboroo,,,,,,,,,,,,,,,,,,
Z,,,,,,,,,,,,,,,,,,,

Now I am stumped this is far any help would be awesome

This is what I have gotten so far, this is what it does right now is display the first column of the array as a header for each foreach loop then i've got the data displayed under each header in the loop but what i am trying to understand here is i am trying to get under each header it to have 4 columns or a number of my choosing. Heres an example of what i am trying to do.

A airlie beach Andergrove Alexandra Armstrong Beach Alligator Creek B Bucasia Blacks Beach Beaconsfield Bakers Creek Balberra Bloomsbury Breadalbane Ball Bay Belmunda
This is an example of how it is being displayed right now

A
Airlie Beach   Andergrove   Alexandra   Armstrong Beach   Alligator Creek
B
Bucasia   Blacks Beach   Beaconsfield   Bakers Creek   Balberra   Bloomsbury   Breadalbane   Ball Bay   Belmunda
C
Cannonvale   Calen   Crystal Brook   Cremorne   Chelona   Campwin Beach   Cape Hillsborough   Conway
Heres The Code i have so far.

Code: [Select]
<?php
$file = "widget.csv";
@$fp = fopen($file, "r") or die("Could not open file for reading");
$outputArray = array();
while(($shop = fgetcsv($fp, 1000, ",")) !== FALSE)
{
    $outputArray[array_shift($shop)] =  array_filter($shop);
}
print_r($outputArray);
echo "\n<br />";
//display
echo "<table>";
foreach ($outputArray as $alphabetical=>$value){
echo "<th><b>" . $alphabetical ."</b></th>";
echo "<tr>";
foreach ($value as $key){
echo "<td>" . $key . "</td>";

}
echo "</tr>";
}
echo "</table>";

echo "\n<br />";

?>
Heres my Csv File which i am using as an array in the code

Quote

A,Airlie Beach,Andergrove,Alexandra,Armstrong Beach,Alligator Creek,,,,,,,,,,,,,,
B,Bucasia,Blacks Beach,Beaconsfield,Bakers Creek,Balberra,Bloomsbury,Breadalbane,Ball Bay,Belmunda,,,,,,,,,,
C,Cannonvale,Calen,Crystal Brook,Cremorne,Chelona,Campwin Beach,Cape Hillsborough,Conway,,,,,,,,,,,
D,Dows Creek,Dumbleton,Dolphin Heads,,,,,,,,,,,,,,,,
E,Eimeo,Eton,Erakala,,,,,,,,,,,,,,,,
F,Foulden,Foxdale,Flametree,Farleigh,Freshwater Point,,,,,,,,,,,,,,
G,Glen Isla,Glenella,,,,,,,,,,,,,,,,,
H,Homebush,Hampden,,,,,,,,,,,,,,,,,
I,,,,,,,,,,,,,,,,,,,
J,Jubilee Pocket,,,,,,,,,,,,,,,,,,
K,Kinchant Dam,Kolijo,Koumala,Kuttabul,,,,,,,,,,,,,,,
L,Laguna Quays,,,,,,,,,,,,,,,,,,
M,McEwens Beach,Mackay,Mackay Harbour,Mandalay,Marian,Mia Mia,Middlemount,Midge Point,Mirani,Moranbah,Mount Charlton,Mount Jukes,Mount Julian,Mount Marlow,Mount Martin,Mount Ossa,Mount Pelion,Mount Pleasant,Mount Rooper
N,Narpi,Nebo,Nindaroo,North Eton,,,,,,,,,,,,,,,
O,Oakenden,Ooralea,,,,,,,,,,,,,,,,,
P,Palmyra,Paget,Pindi Pindi,Pinevale,Pioneer Valley,Pleystowe,Preston,Proserpine,,,,,,,,,,,
Q,,,,,,,,,,,,,,,,,,,
R,Racecourse,Richmond,Riordanvale,Rosella,Rural View,,,,,,,,,,,,,,
S,St Helens Beach,Sandiford,Sarina,Seaforth,Slade Point,Shoal Point,Shute Harbour,Shutehaven,Strathdickie,Sugarloaf,Sunnyside,,,,,,,,
T,Te Kowai,The Leap,,,,,,,,,,,,,,,,,
U,,,,,,,,,,,,,,,,,,,
V,Victoria Plains,,,,,,,,,,,,,,,,,,
W,Wagoora,Walkerston,Woodwark,,,,,,,,,,,,,,,,
X,,,,,,,,,,,,,,,,,,,
Y,Yalboroo,,,,,,,,,,,,,,,,,,
Z,,,,,,,,,,,,,,,,,,,

i have 8 division (div), i want to display 4 rows in 4 division and the remain 4 rows in the next 4 division here is my code structure for carousel

<div class="nyie-outer">    
<div class="container">
    <div  id="nyieFull" class="carousel slide carousel-multi-item" data-interval="false" data-ride="carousel">
        <div class="outer-controls-top">
        <div class="nyie-title"><h5>THIS IS NYIE</h5></div>
        <div class="controls-top">    
            <a class="btn-floating" href="#nyieFull" data-slide="prev"><i class="fa fa-chevron-left"></i></a>
            <a class="btn-floating" href="#nyieFull" data-slide="next"><i class="fa fa-chevron-right"></i></a>
        </div>
        </div>
        <div class="carousel-inner" role="listbox">
            <div class="carousel-item active">
                 first row

                 second row

                 third row

                 fourth row
            </div><!--/.First four rows here-->
            <div class="carousel-item">

                  fifth row

                 sixth row

                 seven throw

                 eighth row

            </div><!--/.second four rows here-->
        </div
    </div>    
</div> 
</div>

 

 

sql code   

CREATE TABLE  product(
prodId INT(11) NOT NULL UNIQUE AUTO_INCREMENT, 
image VARCHAR(255) NOT NULL,
prodName VARCHAR(255) NOT NULL,
prodPrice VARCHAR(255) NOT NULL,
description TEXT NOT NULL
PRIMARY KEY (prodId) 
);

php code

<?php 
include_once('db.php');
$sql = "SELECT image,prodName, prodPrice, description FROM product ORDER BY prodId ASC LIMIT 4";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
    // output data of each row
        while($row = mysqli_fetch_array($result)) {
            $activeprod =  '
            <div class="col-lg-3">
            <a href="more.php" class="newAdded">    
            <figure class="figure">   
               <img src="images/'. $row['image'] .'" alt="" class="img-responsive" >
                <figcaption class="figure-caption">
                    <span class="prodName"><b>Name: </b>'. $row['prodName'] .'</span>
                    <span class="prodPrice"><b>price: </b>'. $row['prodPrice'] .'</span>
                    <span class="desc"><b>description: </b>'. $row['description'] .'</span>    
                </figcaption>    
            </figure> 
            </a>
            </div>
            ';
    }
} 
?>

how can i echo that result in those rows 

 

 

 

Hi,

having stopped php for a while, i'm a little rusty, can someone help me - instead of the results returning in a row by row format, can i have it so it returns column by column?
so, rather than result 1 being first, then 2 underneath then 3 etc etc, can i have result 2 next to result 1?

heres my code

$result = mysql_query("SELECT * FROM members ORDER BY member_id DESC")   
or die(mysql_error());  

echo "<table border='2'>";
echo "<tr> <th></th> <th></th> <th></th> </tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
// Print out the contents of each row into a table
echo "<tr><td>"; 
echo $row[printf('<img src="%s" height="96" width="128" style="border: 0" />', $row['profile_pic_short'])];
echo "</td><td>"; 
echo "<a href=\"PHP-Login/advert_pages/view_profile_specific.php?seller_name=$row[login]\">". $row['login'] . "</a>";
echo "</td></tr>"; 

} 

echo "</table>";
?>


all help appreciated

thanks

I am fetching a data from database which comes out like this

EmpID   First Name   DoJ   Supervisor
'34698'    'Ian Bobby'    '2011-08-09'    'Daniel Lin'
'34684'    'Sally Dodd'    '2011-08-09'    'James Kirk'
'34871'    'Alan Dandy'    '2011-08-09'    'Pamela Roy'

I want to Transpose it like this
EmpID   '34698'   '34684'   '34871'
First Name    'Ian Bobby'    'Sally Dodd'    'Alan Dandy'
DoJ    '2011-08-09'    '2011-08-09'    '2011-08-09'
Supervisor    'Daniel Lin'    'James Kirk'    'Pamela Roy'

Please help

I'm wanting to output data into two columns.  I've searched for this in a few places.  A vast majority of the search results deal with getting data from multiple columns, and the the ones that deal with what I'm looking for don't really have any solutions attached to them.

I'm not fully worried about the columns being equal length.  The data is broken up into a certain number of rows in a given year.  I have about 12 years, probably 6-8 rows per year.  I just don't know how to get it into different columns.  I don't care if it goes left to right then down or down then left to right.

If there has been a solution to that here already, feel free to point me in that direction.  Point me in any applicable direction, I'd fine with.  I just can't locate anywhere what trigger would cause that.

Not sure if this is the right forum, but I have a database that I will need to populate with a large number of rows (2000+).

I have written a PHP script that uploads individual entries.

Is it possible to use something like a spreadsheet where I can set out the rows/columns as they will appear in the database, and then upload in one go rather than uploading each row individually?

Thanks for any observations and/or help.

Hi, I need help to check multiple rows for many columns, so that the script won't re-insert a duplicate entry. For eg:


$isRowExist=mysql_query("SELECT (field1,field2) from table1 WHERE field1='$f1' AND field2='$f2' ");

if(mysql_num_rows($isRowExist)==1)
   print "already in table1!";
else
   //proceed to insert into table table1


*BUT I tried this and it says mysql_num_rows expects resource, and this warning only shows when I have multipel columns I want to compare (it works just fine with one column in the WHERE clause above)

Please help!

I create a post just now but cannot seem to find it to add to it. Anyhow, I have 2 tables, I need to multiply qty from the one table to the mass from the other to get a total, which I have achieved with this code:

$sql_total_mass = "
	SELECT jobs_assembly.assemble_qty, jobs.mass,
	(jobs_assembly.assemble_qty * jobs.mass) AS 'sum'	
	FROM jobs_assembly
	LEFT JOIN jobs on jobs_assembly.jobs_id = jobs.id
	LEFT JOIN job_names ON jobs.job_names_id = job_names.job_id
	WHERE jobs_assembly.assemble_date = '$link_date'
	ORDER BY job_names.job_name, jobs.assembly
	";

	$result_total_mass = mysqli_query($conn, $sql_total_mass);

	if (mysqli_num_rows($result_total_mass) > 0) {
		while($row_total_mass = mysqli_fetch_assoc($result_total_mass)) {
			echo $row_total_mass['sum'].'<br />';
		} //while
	} //if

Now I need to take all these totals and make a grand total. So basically add all the results from $row_total_mass[‘sum’] together and show it.