Select All & Compare

[LinusIT]

Hi

I'm trying to select all the records in a table, display them in either a "dynamic" or a static pre coded table and then highlight the highest value.

So in my table I have the following:

id, name, item1, item2, item3, item4, item5

As for data it would be something like:

1, company1, 1, 2, 3, 4, 5
2, company2, 2, 4, 6, 8, 10
3, company3, 0, 8, 3, 12, 6

At present I have the table written statically which isn't a problem, I just can't see a way of displaying the data. It may be that the table has to be drawn dynamically.

The first thing I'd like to solve is displaying the data, any help would be great, thanks.

[JAB Creations]

When you say table I think most of us think database however you could be talking about an array. If you're using an array you can explore is as so...

print_r($my_array);
echo $my_array->sub_item;

If you're talking about a database you'll need to run a query using mysql_query() method.

I think you could just be looking to use a foreach loop though...

<?php
$arr = array(1, 2, 3, 4);

foreach ($arr as $key => $value) 
{
 echo '<div>key = '.$key.' and value = '.$value.'</div>';
 //if ($key=='') {}
 //if ($value=='') {}
}
?>

It's very important that you use the correct terminology to express what you're talking about otherwise your question may be too broad for most people to attempt to answer.

- John

[LinusIT]

Sorry, I can see where the confusion could come from.

I would like to select all records from a database table and then display them in a html table on the page.

The database table data would look something like this

id | name | item1 | item2 | item3
1 | companyA | 3 | 4 | 7
2 | companyB | 2 | 9 | 6
3 | companyC | 8 | 1 | 5

The html table would be displayed the other way around. For example the first column would be all the items and the subsequent columns would be the companies.

I hope this clarifies what I'm trying to achieve, will happily post some proper code if it'll help more.

[JAB Creations]

In that case simply put the row declaration inside of a while loop. This should get you moving in the direction you want...

- John

<?php
$query1 = "";
$result1 = mysql_query($query1);

if ($result1)
{
 $count1 = mysql_num_rows($result1);

 if ($count1>0)
 {
 while ($row1 = mysql_fetch_assoc($result1))
 {
 echo '<div><pre>';
 print_r($row1);
 echo '</pre></div>';
 }
 }
 else {echo '<div><p>no results</p></div>';}
}
else {mysql_error_report($query1,mysql_error(),__FUNCTION__);}
?>

[LinusIT]

Thank you John, that now shows in 3 seperate arrays all of the items and their respective values.

I understand roughly how the code you provided works but not enough in a way of how to use it.

Here's an example of how I'd like to display the data from those arrays:

<table cellpadding="5" cellspacing="5">
<tr>
<td></td>
<td>Company 1</td>
<td>Company 2</td>
</tr>
<tr>
<td>Item 1</td>
<td>Company 1 value</td>
<td>Company 2 Value</td>
</tr>
<tr>
<td>Item 2</td>
<td>Company 1 value</td>
<td>Company 2 Value</td>
</tr>
<tr>
<td>Item 3</td>
<td>Company 1 value</td>
<td>Company 2 Value</td>
</tr>
<tr>
<td>Item 4</td>
<td>Company 1 value</td>
<td>Company 2 Value</td>
</tr>
<tr>
<td>Item 5</td>
<td>Company 1 value</td>
<td>Company 2 Value</td>
</tr>
</table>

I would image instead of using a static table such as the above it would be inside php tags using a while statement.

Can you help with this please?

[JAB Creations]

The print_r($row1); shows you what the array contains for each iteration. It will show you the key/value pairs. To echo the values (e.g. company 1 value) you need to echo the key which belongs to the variable (e.g. echo $row1['the_key'] where 'the_key' is the name of the SQL column or the alias you may have assigned it).

So if you have a column in your database called 'company' then you would want to echo $row1['company'] to get the database value.

Database --> Column contains value.
PHP --> Variable contains keys, keys contain value.

<?php
while($row1 = mysql_fetch_assoc($result1))
{
 echo '<tr><td>'.$row1['company'].'</td><td>'.$row1['boss'].'</td></tr>'."\n";
}
?>

Keep in mind for future reference that a PHP array's key may contain an array instead of a value; putting an array inside of an array is called multi-dimensional array. You would access it something like $array['company']['boss'].

- John

[JAB Creations]

CLARIFICATION

I can no longer edit the previous post, the PHP --> bit should be...


PHP --> Array contains keys, keys contain value.

- John

[LinusIT]

Ok so it's sort of working, maybe I have missed something here. What it's displaying now is the first row of data in my html table but nothing else. It'd be so much easier if I could provide a screen shot or something :(

<?php
$sql="SELECT * FROM table";
$result = mysql_query($sql) or die(mysql_error());
while($row1 = mysql_fetch_assoc($result)) { ?>

<table cellpadding="0" cellspacing="0">
<tr>
<td></td>
<td>Company 1</td>
<td>Company 2</td>
</tr>
<tr>
<td>Item 1</td>
<td>&pound;<?=$row1['item1']?></td>
<td>Next Row Value</td>
</tr>
<tr>
<td>Item 2</td>
<td>&pound;<?=$row1['item2']?></td>
<td>Next Row Value</td>
</tr>
<tr>
<td>Item 3</td>
<td>&pound;<?=$row1['item3']?></td>
<td>Next Row Value</td>
</tr>
</table>

How do I get row 2 values into the next cell on in the table?

I apologise if I'm being thick here and missing something obvious.

Appreciate your help.

[JAB Creations]

First off retain the query numbers. You'll commonly use the words count row, result, use them all with numbers to keep yourself organized as well as to ensure easy database error tracking, this practice has made working with databases much easier in the long run for me.

Just change the query columns/table as well as the row1 keys inside the table to suite what you need. Not that the table element is not inside the while loop, you want to make sure that you output valid XHTML code.

- John

<?php
$query1 = "SELECT boss, address FROM company";
$result1 = mysql_query($query1);

if ($result1)
{
 $count1 = mysql_num_rows($result1);

 if ($count1>0)
 {
 echo '<table summary="Company listings.">
 while ($row1 = mysql_fetch_assoc($result1))
 {
 echo '<tr><td>'.$row1['boss'].'</td><td>'.$row1['address'].'</td></tr>'."\n";
 }
 echo '</table>';
 }
 else {echo '<div><p>no results</p></div>';}
}
else {mysql_error_report($query1,mysql_error(),__FUNCTION__);}
?>

[LinusIT]

Thanks for your help, I don't think the above code is what I'm looking for though. I have got it working but in probably a bad way. I thought I'd share this and then hopefully be a little clearer on what I'm trying to achieve.

At the top of the page I have:

$query1="SELECT * FROM compare_pricelist WHERE pricelist_id = 2";
$result1 = mysql_query($query1) or die(mysql_error());

$query2="SELECT * FROM compare_pricelist WHERE pricelist_id = 3";
$result2 = mysql_query($query2) or die(mysql_error());

Then I have:

<table cellpadding="0" cellspacing="0" id="pricelist">
<tr>
<td></td>
<td>Company A</td>
<td>Company B</td>
</tr>
<?php while($row1 = mysql_fetch_assoc($result1)) {
while($row2 = mysql_fetch_assoc($result2)) {
?>
<tr>
<td><h2>Copper</h2></td>
<td></td>
<td></td>
</tr>
<tr>
<td>Dry Bright Wire</td>
<td>&pound;<?=$row1['copper_dry_bright']?></td>
<td>&pound;<?=$row2['copper_dry_bright']?></td>
</tr>
<tr>
<td>Greasy Bright Wire</td>
<td>&pound;<?=$row1['copper_greasy_dry_bright']?></td>
<td>&pound;<?=$row2['copper_greasy_dry_bright']?></td>
</tr>
<tr>
<td>Flat Electro</td>
<td>&pound;<?=$row1['copper_electro']?></td>
<td>&pound;<?=$row2['copper_electro']?></td>
</tr>

So now I've got 3 columns, one for the item name, the next one for Company A price and the last one for Company B price.

I'm hoping this will clear things up, although saying that it's probably me missing something.