Looking for help on doing a simple category script

[Simon180]

Am trying to make a simple categorys script but am not having much luck .

CREATE TABLE `portraits` (
  `n` INT(11) NOT NULL AUTO_INCREMENT,
  `category` VARCHAR(25) NOT NULL DEFAULT 'Main',
  `collection_id` VARCHAR(5) NOT NULL DEFAULT '00000',
  `collection_name` VARCHAR(55) NOT NULL DEFAULT 'Free Gallery 1',
  `permissions` INT(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`n`)
) ENGINE=MYISAM AUTO_INCREMENT=99 DEFAULT CHARSET=latin1

above is my simple table set up basicly all I want is a category script that will check category with N and then show the collection_names that are in the same category so it would look somthing like this

Cartoon Faces: Gallery 1 Gallery 2
Flags: Gallery 1

can some one help me out, thanks

[broj1]

To follow good design practices you should keep categories in a separate table. But anyway:

• put a select element (a dropdown) or checkboxes on page to let people chose a category/ctagories

• create a select statement using a chosen category/categories construct a SQL statement to query the database:

  $query = "SELECT * FROM portraits WHERE catgory = $chosenCategory";

Then just display resulting rows in a form you think is appropriate (i.e. looping through a resultset using a while loop).

[Simon180]

not the way i want it to work I want it to list all gallerys in a pre defined database as a list for users to selects list would look like

Cartoon Faces: Gallery 1 Gallery 2
Flags: Gallery 1

each gallery would be a link, but I want a cat script so i can put more then one gallery into a cat like cartoon faces for example, I dont want use to select the cat only the gallery any ideas?

[Taywin]

Are you saying you want to display all categories, list their own collection name, and each collection name is a link?

For example...
Cartoo Faces: Gallery1 Gallery2
Flags: Gallery1

[broj1]

I am not sure if I understand the question. Can you post some data from the table, it may help me understanding (i.e. SELECT * FROM portraits LIMIT 10).

[Simon180]

ya I want it to list all the current items that are in the same cat as a list

[Taywin]

Then, there are 2 ways. One is to create a query that retrieves all database sorted by category. Then iterate through the data array to create your page. The other is to select only distinct category (using DISTINCT keyword), and use each category to collect all of the same collection_name...

[Simon180]

Idea is I want to match all the current gallerys that are in same category and list them as showing above by Taywin

so for example there 5 gallerys in category Citys and 10 gallerys in category Funny Faces it would list all the gallersy in there respected category.

with out the need to select a drop down or anything.

[Simon180]

here insert data

insert  into `portraits`(`n`,`category`,`collection_id`,`collection_name`,`permissions`) values (99,'Cartoon Faces','00000','Gallery 1',0),(100,'Cartoon Faces','00002','Gallery 2',0),(101,'Flags','00003','Gallery 1',0);

[broj1]

This is a rough code (might need some tuning):

// temporary array for reading data into
$tempArray = array();
$result = $mysqli->query("SELECT * FROM portraits ORDER BY category");
while ($row = $result->fetch_row()) {
    $category = $row['category'];
    $tempArray[$category][] = $row['collection_name'];
}
// process the temp array
foreach($tempArray as $cat => $galeryArray) {
    echo "$cat: " . implode(' ', $galeryArray) . '<br>';
}

The idea is to read all the data inot a temp associative array where the associative indexes are category names and values are array of galeries. You will have to do some fiddling to construct links. I used mysqli in OO mode. Adapt if you use something else.

[Simon180]

broj1, thanks any chance you can add this code to each

echo " : <div class='gallery'><a href='select_portrait.php?id={$row['collection_id']}&gallery={$row['collection_name']}'>{$row['collection_name']}</a></div>"

in place of the other echo ?

[broj1]

Not tested:

// temporary array for reading data into
$tempArray = array();
$result = $mysqli->query("SELECT * FROM portraits ORDER BY category");
while ($row = $result->fetch_row()) {
    $category = $row['category'];
    // changed to an array
    $tempArray[$category][] = array($row['collection_id'], $row['collection_name']);
}
// process the temp array
foreach($tempArray as $cat => $galeryArray) {
    echo "$cat: ";
    foreach($galeryArray as $gal) {
        echo "<div class='gallery'><a href='select_portrait.php?id={$gal[0]}&gallery={$gal[1]}'>{$gal[1]}</a></div>";
    }
    echo '<br>';
}

Didi you mean each galery is in own div? You have to style them display:inline-block then or something similar.

[Simon180]

ya now other issus is when it lists the cat name it will do it over is there away of only make it add cat name once so it would look like Faces: Gal1, Gal2 and not Faces: Gal1 Faces: Gal2 ?

[broj1]

I tested my code and it displays exactly what you wanted:

Cartoon Faces: Gallery 1 Gallery 2
Flags: Gallery 1

Here is the test code:

<html>
<head>
<style>
div.gallery {
    display: inline-block;
}
</style>
</head>
<body>
<?php
$mysqli = new mysqli('localhost', 'test', '', 'test');

// temporary array for reading data into
$tempArray = array();
$result = $mysqli->query("SELECT * FROM portraits ORDER BY category");
while ($row = $result->fetch_assoc()) {
    $category = $row['category'];
    // changed to an array
    $tempArray[$category][] = array($row['collection_id'], $row['collection_name']);
}
// process the temp array
foreach($tempArray as $cat => $galeryArray) {
    echo "$cat: ";
    foreach($galeryArray as $gal) {
        echo "<div class='gallery'><a href='select_portrait.php?id={$gal[0]}&gallery={$gal[1]}'>{$gal[1]}</a></div>&nbsp;";
    }
    echo '<br>';
}
?>  
</body>
</html>

[Simon180]

maybe because am using mysql and no sqli

[broj1]

It shouldn't matter as long as you use correct syntax. The following is a working example using mysql:

<html>
<head>
<style>
div.gallery {
    display: inline-block;
}
</style>
</head>
<body>
<?php
// $mysqli = new mysqli('localhost', 'test', '', 'test');
$con = mysql_connect('localhost', 'test', '');
mysql_select_db('test');

// temporary array for reading data into
$tempArray = array();
// $result = $mysqli->query("SELECT * FROM portraits ORDER BY category");
$result = mysql_query("SELECT * FROM portraits ORDER BY category");
// while ($row = $result->fetch_assoc()) {
while ($row = mysql_fetch_assoc($result)) {
    $category = $row['category'];
    // changed to an array
    $tempArray[$category][] = array($row['collection_id'], $row['collection_name']);
}
// process the temp array
foreach($tempArray as $cat => $galeryArray) {
    echo "$cat: ";
    foreach($galeryArray as $gal) {
        echo "<div class='gallery'><a href='select_portrait.php?id={$gal[0]}&gallery={$gal[1]}'>{$gal[1]}</a></div>&nbsp;";
    }
    echo '<br>';
}
?>  
</body>
</html>

Anyway, you better switch to mysqli or PDO (it is actually highly recommended, since mysql extension is deprecated, unmaintained and can be dropped anytime).