Show all in table

Self_Taught_Still_Learning · January 5, 2014

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 · January 5, 2014

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 · January 6, 2014

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 · January 6, 2014

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 · January 6, 2014

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 · January 6, 2014

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 · January 6, 2014

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 · January 6, 2014

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

Psycho · January 6, 2014

<?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 · January 6, 2014

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 · January 6, 2014

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 · January 6, 2014

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

Sign In

Show all in table

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information