Categorizing mySQL table with php

Question

I have a mySQL table named businesses with 4 columns.

name, url, description, category

I am trying to have the entire directory display on a page under each corresponding category name. Is there a better way to do this than what I have so far? I would like to keep all data in one table rather than having a table for each category.

<?php

$con = mysql_connect('localhost','username','password')
    or die('Could not connect: ' . mysql_error());
mysql_select_db('relicc_dogedir')
    or die('Could not select database');

$result = mysql_query("SELECT * FROM businesses ORDER BY category",$con);

$column = array();
    while($row = mysql_fetch_array($result))
    {
            $column[] = $row['category'];
    }
    $merged_column = array_unique($column);
    $refined_categories = array_values($merged_column);

    foreach ($refined_categories as &$refined_category)
    { ?>
        <h3><?php echo $refined_category;?></h3><?php 

        //Display sorted business names with corresponding category


    }

mysql_close($con);
?>

Can you explain it properly i am not getting your question

Janak Prajapati
– Janak Prajapati

2014-02-04 13:43:02 +00:00
Commented Feb 4, 2014 at 13:43 — Janak Prajapati
– Janak Prajapati, Commented Feb 4, 2014 at 13:43
Yes. You could desist from employing deprecated functions.

Strawberry
– Strawberry

2014-02-04 13:48:54 +00:00
Commented Feb 4, 2014 at 13:48 — Strawberry
– Strawberry, Commented Feb 4, 2014 at 13:48
Michal Brasna understood what I was trying to do.

Joe Fusco
– Joe Fusco

2014-02-04 14:55:22 +00:00
Commented Feb 4, 2014 at 14:55 — Joe Fusco
– Joe Fusco, Commented Feb 4, 2014 at 14:55

Community · Accepted Answer · 2023-11-17 19:42:58Z

0

First, you should know that the mysql extension is deprecated, you should use MySQLi or PDO_MySQL.

You could first retrieve the categories, and then foreach categories retrieve the corresponding businesses.

    try
    {
        $dbh = new PDO('mysql:host=localhost;dbname=YOURDB', 'USERNAME', 'PASSWORD');
    }
    catch(PDOException $e)
    {
        echo $e->getMessage();
        die;
    }
    
    $stmt = $dbh->query('SELECT category FROM businesses GROUP BY category');
    $stmt->setFetchMode(PDO::FETCH_ASSOC);
    
    $categories = $stmt->fetchAll();
    foreach ($categories as $category)
    {
        echo '<h3>'.$category['category'].'</h3>';
    
        $stmt = $dbh->prepare('SELECT * FROM businesses WHERE category = :categoryName');
        $stmt->bindParam(':categoryName', $category['category'], PDO::PARAM_STR);
        $stmt->execute();
        $businesses = $stmt->fetchAll();
    
        foreach ($businesses as $business)
        {
            echo $business['name'];
            echo '<br />';
        }
    }

edited Nov 17, 2023 at 19:42

CommunityBot

11 silver badge

answered Feb 4, 2014 at 13:58

Brice

1,0269 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Michal Brašna Over a year ago

@Fuscon Good for you, but be carefull about making queries in loop.

Brice Over a year ago

@MichalBrašna, true. But given this code, it is very easy to move the query out of the loop.

Joe Fusco Over a year ago

whats the risk of putting the query in loops? I'm new to php. And what would be the solution?

Michal Brašna Over a year ago

@Fuscon It is well answered in stackoverflow.com/questions/946214/…

Joe Fusco Over a year ago

Where do i put the if statement to display an ad after a certain category?

Michal Brašna · Accepted Answer · 2014-02-04 14:10:49Z

0

You could group your data by category in PHP.

...
$result = mysql_query("SELECT * FROM businesses ORDER BY category, name", $con);

$data = array();
while($row = mysql_fetch_array($result))
{
    $data[$row['category']][] = $row;
}

foreach($data as $categoryName => $categoryData) {
    echo '<h3>' . $categoryName . '</h3>';
    foreach ($categoryData as $business) {
        // display sorted business data using business
        // echo $business['name'];
    }
}
...

edited Feb 4, 2014 at 14:10

answered Feb 4, 2014 at 13:43

Michal Brašna

2,33315 silver badges17 bronze badges

Collectives™ on Stack Overflow

Categorizing mySQL table with php

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related