I need a solution on how to structure some code... something practical

[cs.punk]

I have got a database with categories and 'entries'.

 

I would like it all to show up for example:

//First Category

 

///Entry 1

///Entry 2

 

//Second Category

///Bla bla

///Bla bla

 

But should I extract all the data and insert into arrays? And sort them? Or..?

 

Here my code:

 

<?php
$con = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);

$sql_cat = "SELECT * FROM categories";
$mq_cat = mysqli_query($con, $sql_cat) or die (mysqli_error($con));

// We don't add 'article' as we just want to show a list of all 'articles' with a link to each individual
$sql_ent = "SELECT title, date, category_id, id FROM categories";
$mq_cat = mysqli_query($con, $sql_ent) or die (mysqli_error($con));

// Lets check if there is any data at all, maybe the database was crashed or wiped
if (mysqli_num_rows($mq_cat) <= 0 && mysqli_num_rows($mq_ent) <= 0)
{echo "<p class='error'>Hmm... A strange error has occured... Please try again later. We apologise for this.</p>";
}
else
{// No there is data. So lets continue

   // Lets get the categorys into an array
   $cat_count = 0;  
   while ($row = mysqli_fetch_assoc($mq_cat))
    {$categories['name'][] = $row['name'];
  $categories['id'][] = $row['id'];
  
  $cat_count ++;
}

   // Now lets get the entries/articles into an array
   $ent_count = 0;  
   while ($row = mysqli_fetch_assoc($mq_ent))
    {$entry['title'][] = $row['title'];
  $entry['date'][] = $row['date'];
  $entry['cat_id'][] = $row['id'];
  $entry['id'][] = $row['id'];
  
  $ent_count ++;
}

   for ($i = 0; $i <= $cat_count; $i ++)
    {
}
}
?>

 

Thanks in advance

[Zyx]

Simply sort everything in the valid order and the task becomes much easier:

 

$data = array();

$stmt = $pdo->query('SELECT * FROM categories ORDER BY id');
while($row = $stmt->fetch(PDO::FETCH_ASSOC))
{
   $data[] = $row;
}
$stmt->closeCursor();

$stmt = $pdo->query('SELECT i.* FROM items i, categories c WHERE i.category_id = c.id ORDER BY c.id, i.id');
$idx = 0;
while($row = $stmt->fetch(PDO::FETCH_ASSOC))
{
   if($row['category_id'] != $data[$idx]['category_id'])
   {
      $data[++$idx]['items'] = array();
   }
   $data[$idx]['items'][] = $row;
}
$stmt->closeCursor();

 

The key is to sort the items in the same order, as their categories in the first place, then you'll get the items that belong to the same category in one place of the results.

[Mark Baker]

$sql_cat = "SELECT * FROM categories";
$mq_cat = mysqli_query($con, $sql_cat) or die (mysqli_error($con));

// We don't add 'article' as we just want to show a list of all 'articles' with a link to each individual
$sql_ent = "SELECT title, date, category_id, id FROM categories";

Should $sql_ent select from articles rather than categories?

 

You'd be better off using a single query with a join to select data from both categories and articles in one go, and you could then use an ORDER BY clause in your sql to order the results of the query

[cs.punk]

Simply sort everything in the valid order and the task becomes much easier:

 

$data = array();

$stmt = $pdo->query('SELECT * FROM categories ORDER BY id');
while($row = $stmt->fetch(PDO::FETCH_ASSOC))
{
   $data[] = $row;
}
$stmt->closeCursor();

$stmt = $pdo->query('SELECT i.* FROM items i, categories c WHERE i.category_id = c.id ORDER BY c.id, i.id');
$idx = 0;
while($row = $stmt->fetch(PDO::FETCH_ASSOC))
{
   if($row['category_id'] != $data[$idx]['category_id'])
   {
      $data[++$idx]['items'] = array();
   }
   $data[$idx]['items'][] = $row;
}
$stmt->closeCursor();

 

The key is to sort the items in the same order, as their categories in the first place, then you'll get the items that belong to the same category in one place of the results.

 

I am not so familiar with OOP...Uhm and can't really see whats it doing..

 

Theres a 'article' table(article, title, date, category_id) and a 'categories'(name, id) table. And the articles are linked into the categories via the category id.

[Zyx]

It's just PDO, the default database access interface for PHP since 5.1. Basically, you use it in the same way, as the old extensions, except that the functions have been replaced with methods. I recommend you to learn it, it's not so hard as you think, even if you are not familiar with OOP.

 

First, you get all the categories and put them into an array. Then, you get all the articles (named "items" in my code, but it's not a problem to change it) and sort them with the category ID in the first place. This makes the articles that belong to the same category appear in one place in the result set. While fetching the articles, you check the 'category_id' field in the returned row against the category ID pointed by the $idx variable. If they are not equal, it means that a new category begins and we must move to the next index. Because categories and articles are sorted with the same criteria, it guarantees that everything will match in the output.