Show all in table

[Self_Taught_Still_Learning]

Ok, so in a database table i have:

 

 

name         contact       code        type         price         quantity

Name 1      xxxxxxxxx     GHT3      Food        £10.50      2

Name 1      xxxxxxxxx     GHTd      Food        £10.30      2

Name 1      xxxxxxxxx     GHTs      Food        £10.90      2

Name 2      xxxxxxxxx     GHT3      Food        £10.50      2

Name 3      xxxxxxxxx     GHTs      Food        £10.50      2

Name 3      xxxxxxxxx     GHTd      Food        £10.50      2

 

 

I want them to read on another page like this:

 

Name               Contact              Code            Type          Quantity

Name 1            xxxxxxxxxx           GHT3           Food           2

                                                  GHTd           Food           2

                                                  GHTs           Food           2

                                                  Total Price: £xx.xx

Name 2            xxxxxxxxxx           GHT3           Food           2

                                                  Total Price: £xx.xx

Name 3            xxxxxxxxxx           GHTs           Food           2

                                                  GHTd           Food           2

                                                  Total Price: £xx.xx

 

 

So it only reads the name and number once but puts in the whole of what they are after. Hope someone understands this and can lend some help. Don't need the table doing just need to know the sql i would need to use.

[hansford]

Not quite sure of what you want - no code posted. But here's a little something that may/may not help.

$query = "SELECT * FROM table_name";

try {
	
    $con = new PDO('mysql:host=localhost;dbname=your_db_name', $username, $password);
    
    $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $result = $con->prepare($query);
    $result->execute();
}    
catch(PDOException $e) {
	
    echo $e->getMessage();
} 	
    $table = "<table cellpadding=10 cellspacing=0 border=1>";
    $table .= "<tr>";
    $table .= "<th>Name</th><th>Contact</th><th>Code</th><th>Type</th><th>Quanitiy</th>";
    $table .= "</tr>";
    
    while($row = $result->fetch(PDO::FETCH_ASSOC)) {
    	
    	$table .= "<tr>";
        $table .= "<td>" . $row['Name'] . "</td>";
        $table .= "<td>" . $row['Contact'] . "</td>";
        $table .= "<td>" . $row['Code'] . "</td>";
        $table .= "<td>" . $row['Type'] . "</td>";
        $table .= "<td>" . $row['Quanity'] . "</td>";
        $table .= "</tr>";
        
    } 
    $table .= "</table>";

[ginerjm]

I think the OP needs to think more about what to display rather than what to read.  The way this kind of thing is done (the same way every time) is to be sure the items are in the right sequence; check for a break in a category and output what needs to be done for that category; then output what is on the current record, remember to display those 'header columns' that need to be reset with this latest change.   Remember to check your category changes in reverse order too.  The major category, then the 2nd major one, and so on.  When a category changes you need to be sure to handle totals for each category below that one as well as the breaking one.

 

Once you figure this out you'll be amazed how easy it is and you'll have the technique for the future in all your projects.

[Self_Taught_Still_Learning]

 

Not quite sure of what you want - no code posted. But here's a little something that may/may not help.

$query = "SELECT * FROM table_name";

try {
	
    $con = new PDO('mysql:host=localhost;dbname=your_db_name', $username, $password);
    
    $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $result = $con->prepare($query);
    $result->execute();
}    
catch(PDOException $e) {
	
    echo $e->getMessage();
} 	
    $table = "<table cellpadding=10 cellspacing=0 border=1>";
    $table .= "<tr>";
    $table .= "<th>Name</th><th>Contact</th><th>Code</th><th>Type</th><th>Quanitiy</th>";
    $table .= "</tr>";
    
    while($row = $result->fetch(PDO::FETCH_ASSOC)) {
    	
    	$table .= "<tr>";
        $table .= "<td>" . $row['Name'] . "</td>";
        $table .= "<td>" . $row['Contact'] . "</td>";
        $table .= "<td>" . $row['Code'] . "</td>";
        $table .= "<td>" . $row['Type'] . "</td>";
        $table .= "<td>" . $row['Quanity'] . "</td>";
        $table .= "</tr>";
        
    } 
    $table .= "</table>";

<table>
<tr><td colspan=5>Table</td></tr>
<tr><td>Name</td><td>Contact</td><td>Code</td><td>Type</td><td>Price</td><td>Quantity</td></tr>
<tr><td>Name 1</td><td>xxxxxxxx</td><td>GTH1</td><td>Food</td><td>£10</td><td>2</td></tr>
<tr><td>Name 1</td><td>xxxxxxxx</td><td>GTH2</td><td>Food</td><td>£10</td><td>2</td></tr>
<tr><td>Name 1</td><td>xxxxxxxx</td><td>GTH3</td><td>Food</td><td>£10</td><td>2</td></tr>
<tr><td>Name 2</td><td>xxxxxxxx</td><td>GTH1</td><td>Food</td><td>£10</td><td>2</td></tr>
<tr><td>Name 2</td><td>xxxxxxxx</td><td>GTH3</td><td>Food</td><td>£10</td><td>2</td></tr>
<tr><td>Name 3</td><td>xxxxxxxx</td><td>GTH2</td><td>Food</td><td>£10</td><td>2</td></tr>
</table>

What i want is to be able to pull all data needed for each name in on go like

<table>
<tr><td colspan=5>Table</td></tr>
<tr><td>Name</td><td>Contact</td><td>Code</td><td>Type</td><td>Price</td><td>Quantity</td></tr>
<tr><td colspan=1>Name 1</td><td colspan=1>xxxxxxxx</td><td colspan=3>GTH1 x 2<br>GHT2 x 2<br>GHT3 x 2<br>Total Price: £30.</td></tr>
<tr><td colspan=1>Name 2</td><td colspan=1>xxxxxxxx</td><td colspan=3>GTH1 x 2<br>GHT2 x 2<br>Total Price: £20.</td></tr>
<tr><td colspan=1>Name 3</td><td colspan=1>xxxxxxxx</td><td colspan=3>GTH1 x 2<br>Total Price: £10.</td></tr>
</table>

Edited January 6, 2014 by Self_Taught_Still_Learning

[ginerjm]

Dump the colspans you dont' need them.

 

Learn how to put column headings in an html table  (read a reference on html - plenty out there).

 

Hopefully your sample is just not bothering to display actual variables and that you do know how to show php vars in a table.

[Self_Taught_Still_Learning]

Dump the colspans you dont' need them.

 

Learn how to put column headings in an html table  (read a reference on html - plenty out there).

 

Hopefully your sample is just not bothering to display actual variables and that you do know how to show php vars in a table.

 

This is what i'm using currently

 

$select = mysql_query("SELECT * FROM `table` ORDER by `name`");

while ($i = mysql_fetch_object($select)){

//Orders via name but each time its in a new row.

}

 

I want to be able to group the name together and just show the items that person has order in on row.

[jcbones]

Taking hansford's comment and expanding on it.

 

$query = "SELECT * FROM table_name ORDER BY name"; //query to send to database.

try {
    
    $con = new PDO('mysql:host=localhost;dbname=your_db_name', $username, $password); //pdo connection to database.
    
    $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //set attributes of connection.
    
    $result = $con->prepare($query); //prepare our query.
    $result->execute(); //execute the query.
}    
catch(PDOException $e) { //if an error occurred.
    
    echo $e->getMessage(); //get the error.
}     
    $table = "<table cellpadding=10 cellspacing=0 border=1>"; //start a table variable.
    $table .= "<tr>"; //append a table row to the variable.
    $table .= "<th>Name</th><th>Contact</th><th>Code</th><th>Type</th><th>Quanitiy</th>"; //append a table header row.
    $table .= "</tr>"; //append closing row tag.
    $name = NULL; //declare a name variable.
    $price = 0; //declare a price variable.
    while($row = $result->fetch(PDO::FETCH_ASSOC)) { //get the results from the database resource.
        if($name != $row['name'] && $price > 0) { //if the name variable doesn't match the row from the database,
                                                                                            //AND the price variable is greater than 0.
            $table .=  "<tr><td colspan=\"5\">Total: \${$price}</td></tr>"; //*Then we append the total row to the table variable.
            $price = 0; //and reset the price variable back to 0.
            //*NOTE: You may want to run number_format() on the price before sending it to the page.
        }
        $table .= "<tr>"; //but on every new database row, we append a new row to the table.
        $table .= ($name == $row['name']) ? "<td> </td>" : "<td>" . $row['name'] . "</td>"; //and a new name, UNLESS the name is the same as the last one.
        $table .= ($name == $row['name']) ? "<td> </td>" : "<td>" . $row['contact'] . "</td>"; //and a contact, UNLESS the name is the same on the last one.
        $table .= "<td>" . $row['code'] . "</td>"; //new codes are always appended.
        $table .= "<td>" . $row['type'] . "</td>"; //as are types.
        $table .= "<td>" . $row['quanity'] . "</td>";//and quantities.
        $table .= "</tr>"; //and of course we close each row.
        $name = $row['name']; //now that we are done, we need to make sure that we know what the current name is on the next loop.
        $price += $row['price'] * $row['quantity']; //appending the price of the current type, multiplied by the quantity.
    }
    $table .= "</table>"; //loops are done, close the table.
    
    //of course, now you need to echo the data.
    echo $table;

[ginerjm]

This tells me absolutely nothing other than you need to order by Name And Contact

[Psycho]

 

<?php

$query = "SELECT name, contact, code, type, quantity
          FROM table_name
        ORDER BY name";

try
{
    $con = new PDO('mysql:host=localhost;dbname=your_db_name', $username, $password);
    $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $result = $con->prepare($query);
    $result->execute();
}
catch(PDOException $e)
{
    echo $e->getMessage();
}     

$table = "<table cellpadding='10' cellspacing='0' border='1'>\n";
$table .= "<tr>\n";
$table .= "    <th>Name</th><th>Contact</th><th>Code</th><th>Type</th><th>Quanitiy</th>\n";
$table .= "</tr>\n";

$currentName = false;
while($row = $result->fetch(PDO::FETCH_ASSOC))
{
    //Check if this name is the same as the last
    if($currentName != $row['name'])
    {
        //Name is different, set new value to check
        $currentName = $row['name'];
    }
    else
    {
        //name is same as last, redefine name and contact to be empty space
        $row['Name'] = ' ';
        $row['Contact'] = ' ';
    }
    
    $table .= "<tr>\n";
    $table .= "<td>{$row['Name']}</td>\n";
    $table .= "<td>{$row['Contact']}</td>\n";
    $table .= "<td>{$row['Code']}</td>\n";
    $table .= "<td>{$row['Type']}</td>\n";
    $table .= "<td>{$row['Quanity']}</td>\n";
    $table .= "</tr>\n";
}
$table .= "</table>\n";


?>

[ginerjm]

Psycho - you are leaving out the display of the current row when there is a break on a category.  One needs to output the total line when there is a break  but then you have to display the current data in the next row,

[jazzman1]

It's mine using GROUP_CONCAT().

 

DB-name =  test

DB-table = freaks

<?php

 $username = 'lxc';

 $password = 'password';

 $dbh = new PDO('mysql:dbname=test;host=::1;charset=utf8', $username, $password);

 $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $dbh->prepare("SELECT freaks.name,freaks.contact, 
GROUP_CONCAT(freaks.code ORDER BY freaks.code DESC SEPARATOR '<br />') as Code, 
GROUP_CONCAT(freaks.type ORDER BY freaks.type DESC SEPARATOR '<br />') as Type,
GROUP_CONCAT(freaks.price ORDER BY freaks.price DESC SEPARATOR '<br />') as Price,
GROUP_CONCAT(freaks.quantity ORDER BY freaks.quantity DESC SEPARATOR '<br />') as Quantity 
FROM test.freaks
GROUP BY freaks.name");

$stmt->execute();

$outputs = array();

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$outputs[] = $row;
}

$stmt = null;

?>

<table border="1">
<tr><td colspan=6>Table</td></tr>
<tr><td>Name</td><td>Contact</td><td>Code</td><td>Type</td><td>Price</td><td>Quantity</td></tr>
<?php foreach ($outputs as $output): ?>
<tr><td valign="top"><?php echo $output['name']; ?></td>
<td valign="top"><?php echo $output['contact']?></td>
<td><?php echo $output['Code']?></td>
<td><?php echo $output['Type']?></td>
<td><?php echo $output['Price']?></td>
<td><?php echo $output['Quantity']?></td></tr>
<?php endforeach; ?>

</table>

Result:

<table border="1">
<tr><td colspan=6>Table</td></tr>
<tr><td>Name</td><td>Contact</td><td>Code</td><td>Type</td><td>Price</td><td>Quantity</td></tr>
<tr><td valign="top">Name 1</td>
<td valign="top">xxxxxxxx</td>
<td>GHTs<br />GHTd<br />GHT3</td>
<td>Food<br />Food  <br /> Food </td>
<td>11<br />11<br />10</td>
<td>2<br />2<br />2</td></tr>
<tr><td valign="top">Name 2</td>
<td valign="top">xxxxxxxx</td>
<td>GHT3</td>
<td>Food</td>
<td>11</td>
<td>2</td></tr>
<tr><td valign="top">Name 3</td>
<td valign="top">xxxxxxxx</td>
<td>GHTs <br />GHTd   </td>
<td>Food<br />Food</td>
<td>11<br />11</td>
<td>2<br />2</td></tr>

</table>

Edited January 6, 2014 by jazzman1

[Psycho]

Psycho - you are leaving out the display of the current row when there is a break on a category.  One needs to output the total line when there is a break  but then you have to display the current data in the next row,

 

No, I did not. You are apparently not reading the code correctly. I put comments in, but I will explain it in more detail:

    //Check if this name is the same as the last
    if($currentName != $row['name'])
    {
        //Name is different, set new value to check
        $currentName = $row['name'];
    }
    else
    {
        //name is same as last, redefine name and contact to be empty space
        $row['Name'] = ' ';
        $row['Contact'] = ' ';
    }
    
    $table .= "<tr>\n";
    $table .= "<td>{$row['Name']}</td>\n"; // 'Name' is always output
    $table .= "<td>{$row['Contact']}</td>\n"; // 'Contact' is always output
    $table .= "<td>{$row['Code']}</td>\n";
    $table .= "<td>{$row['Type']}</td>\n";
    $table .= "<td>{$row['Quantity']}</td>\n";
    $table .= "</tr>\n";

So:

 

IF: ($currentName != $row['name']), we reassign $currentName as $row['name']. The values for $row['Name'] and $row['Contact'] remain unchanged for that record.

 

ELSE: The values for $row['Name'] and $row['Contact'] are redefined to be a space characters

 

THEN: No matter what the result of the above If/Else statement we concatenate the table cells for 'Name' and 'Contact' onto the current $table variable:

    $table .= "<td>{$row['Name']}</td>\n";
    $table .= "<td>{$row['Contact']}</td>\n";

So, if this is the first iteration of a new name, the 'Name' and 'Contact' values from the query (which were unchanged) will be used in the output. Otherwise, if it is the same name as the last iteration, the empty space that was assigned to 'Name' and 'Contact' would be used in the output.

Edited January 6, 2014 by Psycho