[RESOLVED] View Multiple Tables MYSQL/PHP

[benwilliamsnz]

Hello, Im not sure if i worded the header appropriatly but...

I have a script for viewing "rows" in my database. In the Database I have a column "Cat".

Now the script only allows me to view all in the 'sales' table. I want to be able to split these into categories from the "Cat" column.

I only have the values 1, 2 and 3 for the "Cat" column.

How do I categorize the output into 3 different html tables?

Thanks

Ben Williams

<table width="900" border="0">
  <tr>
    <td width="175">&nbsp;</td>
    <td width="610"><div align="left"></div></td>
    <td width="101">&nbsp;</td>
  </tr>
<?php
$i=0;
while ($i < $num) {

$image1=mysql_result($result,$i,"Image1");
$image2=mysql_result($result,$i,"Image2");
$product=mysql_result($result,$i,"Product");
$description=mysql_result($result,$i,"Details");
$prodid=mysql_result($result,$i,"ProductID");
$price=mysql_result($result,$i,"Price");
$condition=mysql_result($result,$i,"State");
$cat=mysql_result($result,$i,"Cat");

?>
<tr>  

    <td><div align="center"><a href="product.php?id=<?php echo $prodid; ?>"><img src="<?php echo $image1 ; ?>" width="160" height="120" /></a></div></td>
    <td><a href="product.php?id=<?php echo $prodid; ?>"><?php echo $product; ?></a></td>
    <td>$<?php echo $price; ?></td>
</tr>
<?php
$i++;
}
?>

[Weedpacket]

for($i = 0; $i < $num; ++$i)
{
    $row = mysql_fetch_assoc($result);
    $tables[$row['Cat']][] = $row;
}
ksort($tables); // put the cats into a consistent order
...
foreach($tables as $cat => $rows)
{
    // Start table for Cat $cat (1, 2 or 3)
    foreach($rows as $row)
    {
        // Table row filled with stuff like $row['Product'], $row['Details'] etc.,
    }
    // end table
}

[benwilliamsnz]

Thanks Weedpacket, but im still new to all of this and not sure what you mean to put the cats in a consistant order? Otherwise everything else looks pretty clear.

[benwilliamsnz]

This is what I have now

<?php
for($i = 0; $i < $num; ++$i)
{
    $row = mysql_fetch_assoc($result);
    $tables[$row['Cat']][] = $row;
}
ksort($tables); // put the cats into a consistent order

foreach($tables as $cat => $rows)
{
    '<table width="900" border="0">'// Start table for Cat $cat (1, 2 or 3)

foreach($rows as $row)
{
 '<tr>  
     <td><div align="center"><a href="product.php?id='. $row['ProductID'] .'"><img src="'. $row['Image1'] .'" width="160" height="120" /></a>         </div></td>
     <td><a href="product.php?id='. $row['ProductID'] .'">'. $row['Product'] .'</a></td>
     <td>'. $row['Price'] .'</td>
  </tr>'  // Table row filled with stuff like $row['Product'], $row['Details'] etc.,
}
'</table>'// end table
} ?>

but am getting the error

Parse error: syntax error, unexpected T_FOREACH in /home/zestblen/public_html/acs-computers/salestest.php on line 37

which is the

foreach($tables as $cat => $rows)

statement

[Weedpacket]

benwilliamsnz wrote:

put the cats in a consistant order

That's just what that line does. Without it, the cats would be listed in whatever order they came out of the database in.

but am getting the error

You're missing all your [man]echo[/man] statements.

[Derokorian]

Also missing terminating semi-colons after the strings.

[benwilliamsnz]

Whoops amature mistake 😉

Have echod and semi coloned, but still no sucsess, something to do with the

foreach($rows as $row)

part. Says its unexpected? I say it should expect what it ever you give it but PHP says differently 😕

I have had a play around by taking it out, messing around with the { }s but no luck.

<?php
for($i = 0; $i < $num; ++$i)
{
    $row = mysql_fetch_assoc($result);
    $tables[$row['Cat']][] = $row;
}
ksort($tables); // put the cats into a consistent order

foreach($tables as $cat => $rows)
{
    '<table width="900" border="0">'// Start table for Cat $cat (1, 2 or 3)

foreach($rows as $row)
{
 '<tr>  
     <td><div align="center"><a href="product.php?id='. echo $row['ProductID']; .'"><img src="'. echo $row['Image1']; .'" width="160" height="120" /></a></div></td>
     <td><a href="product.php?id='. echo $row['ProductID']; .'">'. echo $row['Product']; .'</a></td>
     <td>'. echo $row['Price']; .'</td>
  </tr>' 
}
'</table>'
}
?>

[Weedpacket]

Still missing all the echo statements and semicolons.

[benwilliamsnz]

Ah ha, gotcha thanks!

Another error

Warning: ksort() expects parameter 1 to be array, null given in /home/zestblen/public_html/acs-computers/salestest.php on line 29

Warning: Invalid argument supplied for foreach() in /home/zestblen/public_html/acs-computers/salestest.php on line 31

Can you please explain to me this line?

 $tables[$row['Cat']][] = $row;

Also can you point me in the right direction for the definitions of 'strings' and 'arrays' as I am not familier with what the differences are yet I have just started learning.

Thanks

[benwilliamsnz]

I have sorted the errors now, forgot to put the $num line back in 🙂

Will work on it and see how I go.

If you can still explain that line and give me a few pointers would be much appriciated.

Thanks
Ben.

[benwilliamsnz]

Solved. Much appriciated 🙂

[benwilliamsnz]

Sorry to be a pest haha, but i have almost got what I need in the script but I need to limit each table to 5 results at random. How is this possible? Dosent need to have next page etc just need to limit.

Thanks.

[benwilliamsnz]

I have solved the limit to 5 randomly.

I used ORDER BY rand() in the SQL statement,

and used array_slice for limiting.

Works like a charm.

[Derokorian]

You should use a LIMIT 5 in your SQL instead of using array_slice. Since array_slice requires all the results from the DB then uses only some, instead of only getting the 5 results you will use.

[benwilliamsnz]

I was going to try that but i thought in logic that would only display 5 results when I want 15 if you know what I mean. 5 on each table.

Or would it do the job? I dont know much about php, just on a learning streak using trial and error. Takes a while to do something but i get there in the end.

Ben.

[Derokorian]

I guess that depends on your SQL statement (which if I'm not mistaken is no where to be found in this thread). If you are using union, you could limit each subquery, if you're using joins it would be different still (might be as easy as limit 15) if you're using 3 queries just limit 5 on each of them.

[benwilliamsnz]

Can you explain what 'union' and joins are please.

this is just a basic query that I am using.

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM sales ORDER BY rand()";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();

[benwilliamsnz]

Just thought I'd add that the database is not big and it never will be, maybe 100 to 150 entries max. But I would like to know a better way for bigger databases. For future reference.

[Derokorian]

benwilliamsnz;11000373 wrote:

Can you explain what 'union' and joins are please.

this is just a basic query that I am using.

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM sales ORDER BY rand()";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();

I was going to try that but i thought in logic that would only display 5 results when I want 15 if you know what I mean. 5 on each table.

What tables does "each table" refer to? this query only selects from one table.

[benwilliamsnz]

HTML Table, 3 of them, with 5 results of each category.