[RESOLVED] Complcated Group/subgroup problem - expert help needed?

[mhenke]

Hi,

I'm trying to list a group of ID's, each of which has a number of items in it.

So:

ID 1

item 1
item 2

ID 2

item 1

ID 3

item 1
item 2

etc

The items open in collapsible divs, and each item has and edit form in another collapsible div, like:

item 1
----- edit form
item 2
----- edit form

I added paging: it should work so that there is a certain number of ID's shown, with all items that belong to that ID in it (now it isn't working correctly, it cuts the items group into 2 sometimes, spanning 2 pages).

Now, my question: I have coded the page. It generates the lists as described, but it cuts the last ID into 2, from one page to the next... Also, to make the javascript open/close the divs to work properly I would need to know how many ID's and how many items there are on the page... The code is messy, but it kinda works...

I have attached a screenshot of the result page.

Here's the code:

<?
$today = date("Y-m-d");

if(isset($_GET['view'])){
	  $per_page = strip_tags($_GET['view']);
	  } else {
	  $per_page = '5';
	  }

dbsystem();

  // One query grabing all the pertinent data

$lookup = "SELECT SQL_CALC_FOUND_ROWS";

$lookup = $lookup. "  

pro_roh_eingang.id as subcat_id, 
pro_roh_eingang.charge, 
pro_roh_eingang.lieferant, 
pro_roh_eingang.qty_kg,
pro_roh_eingang.qty_l,
pro_charge.charge as cat_id,
pro_charge.date,
pro_roh.id,
pro_roh.name,
pro_roh.abb

FROM 

pro_charge,pro_roh_eingang,pro_roh

WHERE

pro_roh_eingang.charge = pro_charge.charge

AND

pro_roh_eingang.id = pro_roh.id

AND

pro_charge.date <= '$today'

AND 

pro_charge.active = '1'

ORDER BY pro_charge.date DESC

";

  if(!isset($_GET['page'])) { 
$page = 1; 
} elseif($_GET['page'] == ''){
$page = 1;
} else { 
$page = strip_tags($_GET['page']); 
}

$prev_page = $page - 1; 
$next_page = $page + 1;

$page_start = ($per_page * $page) - $per_page;

$lookup = $lookup." LIMIT $page_start, $per_page";

$query = mysql_query($lookup);

$result = mysql_query("SELECT FOUND_ROWS()");
       $total = mysql_fetch_row($result);
       $num_rows = $total[0];

   if($num_rows <= $per_page) { 
$num_pages = 1; 
} elseif (($num_rows % $per_page) == 0) { 
$num_pages = ($num_rows / $per_page); 
} else { 
$num_pages = ($num_rows / $per_page) + 1; 
} 
$num_pages = (int) $num_pages; 

if ($page > $num_pages || $page < 0) {
$warning = ("<div align=\"center\">invalid page number</div> "); 
}

if ($page != $num_pages) {
$nextlink = ("<span>|</span> <a href=\"?page=".$next_page."\">next &raquo;</a>");
}

if ($prev_page != 0) {
echo ' <a href="?page='.$prev_page.'&view='.$per_page.'">< Zurück</a> |';
}

/* this loops the pages and displays individual links */

$forend = (($page + 5) < $num_pages) ?  ($page + 5) : $num_pages;

$forstart = $page - 5;
while($forstart <= 0)
{
 $forstart ++;
}

for ($y = $forstart; $y <= $forend; $y++) {

if ($y != $page) {
echo ' <a href="?page='.$y.'&view='.$per_page.'">'.$y.'</a> ';
} else {
echo ' <strong>'.$y.'</strong> ';
}
}

/* this displays the "Next" link */

if ($page != $num_pages) {
echo '| <a href="?page='.$next_page.'&view='.$per_page.'">Weiter ></a> [ '.$num_pages.' Seiten ]';
} 

$open = 1;  

  $day_open = 1;
  $current_catid = '';

 while ($row = mysql_fetch_array($query)) {

 $date = explode('-', $row['date']);
 $date2 = $date[2] ."-". $date[1] ."-". $date[0];

  	if($row['date'] == $today){
$charge_day = "Heute (Charge ".$row['cat_id'].")";
} else {
$charge_day = $date2." (Charge ".$row['cat_id'].")";
}


if ($current_catid != $row['cat_id']) {


  // Close previous subcat table if not 1st time through
  if ($current_catid != '') { echo '</div>'; }

  //First entry for this category - show category header
  $current_catid = $row['cat_id'];




  ?>
<div class="today"><a href="javascript:void(0);" onclick="opendiv('day<? echo $day_open ?>');opensub();changeicon('icon<? echo $day_open ?>')"><img src="gfx/icon_plus.gif" id="icon<? echo $day_open ?>" width="11" height="11" border="0" /></a> &nbsp;&nbsp;<a href="javascript:void(0);" onclick="opendiv('day<? echo $day_open ?>');opensub();changeicon('icon<? echo $opday_open ?>')"><? echo $charge_day ?></a> </div>
<div id="day<? echo $day_open ?>" class="prod">
  <?
$day_open = $day_open +1;



} // End if 

//Display the subcat data



?>
<form action="<? $_SERVER['PHP_SELF'] ?>" method="post" style="margin:0" id="change<? echo $open ?>">
  <a href="javascript:void(0);" <?  if($row['qty_kg'] == '0' && $row['qty_l'] == '0'){ ?>style="background-color:#FEFB83" <? } ?> onclick="opensub('prod<? echo $open ?>')" class="prodlink">
  <div style="width:126px; float:left"><? echo $row['cat_id'] ?>-<? echo $row['abb'] ?></div>
  <div style="width:328px; float:left"><? echo $row['name'] ?></div>
  <div style="width:auto; float:left">10.000</div>
  </a>
  <div id="prod<? echo $open ?>" class="prodsub">
    <div class="sub">
      <div style="width:146px; float:left; padding-top:4px">Rohstoffchargen Nr.</div>
      <div style="width:6px; float:left"><img src="gfx/nav_div.gif" width="2" height="21" /></div>
      <div style="width:200px; float:left; padding-top:4px">Rohstoffname</div>
      <div style="width:6px; float:left"><img src="gfx/nav_div.gif" width="2" height="21" /></div>
      <div style="width:200px; float:left; padding-top:4px">Lieferant</div>
      <div style="width:6px; float:left"><img src="gfx/nav_div.gif" width="2" height="21" /></div>
      <div style="width:200px; float:left; padding-top:4px">Menge</div>
    </div>
    <div class="stoff2">
      <div style="width:146px; float:left">
        <select name="charge" id="charge" class="input2">
		<option selected="selected" value="<? $row['cat_id'] ?>"><? echo $row['cat_id'] ." > ". $date2 ?></option>
 <?
	  	echo charge();
	  ?>
        </select>
      </div>
      <div style="width:200px; float:left">
        <select name="roh" class="input2" onchange="getSupplier(this.value,'supplier<? echo $open ?>')">
          <option selected="selected" value="<? echo $row['id'] ?>"><? echo $row['name'] ?></option>
         <?
echo rohstoffe();
	?>
        </select>
      </div>
      <div style="width:200px; float:left" id="supplier<? echo $open ?>">
        <select name="lieferant" class="input2">
          <option selected="selected" value="<? echo $row['lieferant'] ?>"><? echo $row['lieferant'] ?></option>

    </select>
  </div>
  <div style="width:200px; float:left">KG
    <input name="qtykg" type="text" class="input2" size="4" value="<? echo $row['qty_kg'] ?>"  />
    L
    <input name="qtyl" type="text" class="input2" size="4" value="<? echo $row['qty_l'] ?>"/>
  </div>
  <div style="width:200px; float:left"><input name="change<? echo $open ?>" type="submit" value="&auml;ndern" /></div>
</div>
  </div>
  <?

	  $open = $open +1;


  } //End while loop
  ?>
  </form>
</div>
<div style="height:19px; background-color:#F1F5F8;border-bottom:#9cb6e7 solid 3px;padding:6px 15px 0 0; text-align:right">
<?

if ($prev_page != 0) {
echo ' <a href="?page='.$prev_page.'&view='.$per_page.'">< Zurück</a> |';
}

/* this loops the pages and displays individual links */

$forend = (($page + 5) < $num_pages) ?  ($page + 5) : $num_pages;

$forstart = $page - 5;
while($forstart <= 0)
{
 $forstart ++;
}

for ($y = $forstart; $y <= $forend; $y++) {

if ($y != $page) {
echo ' <a href="?page='.$y.'&view='.$per_page.'">'.$y.'</a> ';
} else {
echo ' <strong>'.$y.'</strong> ';
}
}

/* this displays the "Next" link */

if ($page != $num_pages) {
echo '| <a href="?page='.$next_page.'&view='.$per_page.'">Weiter ></a> [ '.$num_pages.' Seiten ]';
} 

?>
</div>
<script type="text/javascript">
<!--


function opendiv(id) {  
for (i=1;i<=<? echo $day_open -1 ?>;i++) { 
var divname = 'day'+i;
var divStyle = document.getElementById(divname).style;  

divStyle.display=(id==divname)?'block':'none';
} 
}

function changeicon(id) {  

for (i=1;i<=<? echo $day_open -1 ?>;i++) { 
var iconname = 'icon'+i;  

var iconStyle = document.getElementById(iconname);
iconStyle.src=(id==iconname)?'gfx/icon_minus.gif':'gfx/icon_plus.gif';
} 
}

//-->
</script>

[sneakyimp]

It's remarkable how complex paging can be...all the links that need to be defined, etc.

I am guessing that your LIMIT part of your query refers to items and not ids, correct? If that is the case, there's really no way around it with your current setup. What happens, for instance, when you have a single ID that has twice as many items as $per_page allows?

I think it would be wise to limit the number of IDs per page rather than the number of items. This might require that you create two queries - 1) one to fetch $per_page IDs and 2) another to fetch ALL of the items belonging to those IDs.

[mhenke]

I saw that one coming 😉

It would have been great if it was possible with one query... but I guess it's just impossible.

Does this mean that I have to run the query to find the items that match the IDs in a WHILE loop? That would run the query several times, which is even worse...

[sneakyimp]

you can retreive your IDs first, then run a WHILE loop that puts all the ids in an array like this:

$query = "select id from id_table where foo='bar'";
$result = mysql_query($query)
  or die('query failed');

$ids_found = array();
while($row = mysql_fetch_assoc($result)) {
  $ids_found[] = $row['id'];
}

then, once you have your array of $ids_found, you can run one more query using IN:

$query = "select * from item_table where id_id IN (" . implode(',', $ids_found) . ")";

know what i mean?

[mhenke]

I will study this, thx so much for your help 🙂

One day later: OK this worked 🙂

One thing > ORDER BY can give VERY interesting results...