V2 echo variable only on lowest number

[Orangutang]

Hi,

This post asks the same question as my post yesterday but I wanted to start a new thread for clarity. (hopefully that's ok)

I created a form with php using counters which allows a user to raise a requisition and add rows (line items) pending how many items they want to add. The form has one requisition number and either 1, 2 or 3 items.

The user raises 2 requisitions with 3 items for each and INSERTS to db which looks like:

Req - Item - Status
abc - 1 - pending
abc - 2 - pending
abc - 3 - pending
xyz - 1 - pending
xyz - 2 - pending
xyz - 3 - pending

I SELECT these requisitions with

$reqquery = "SELECT * FROM `porders` WHERE `username` = '$username' AND `type` = 'request' ";
$userreqs = mysql_query($reqquery) or die(mysql_error());

while ($row = mysql_fetch_row($userreqs)) {
$reqno = $row[6]; $item = $row[8]; $status = $row[2];?>
<tr>
<td><?php echo $reqno;?></td>
<td><?php echo $item;?></td>
<td>><?php echo $status;?></td>
</tr>
}

This loops through and echos out all the 6 rows with the reqno, item and status shown. This was ok but it duplicates both reqno 3 times each.

So I changed <td><?php echo $reqno;?></td>
to
<td>
if ($item <= 1) {
echo $reqno;
}
</td>

This sorts it correctly and only displays the reqno on item 1.

And this is where I'm struggling:

The above SELECT has no status stipulation so it displays all the items so there is always a item 1 which makes it work.

I created another page which allows another user to change the status for each item in the db from pending to rejected.
This user changes the status of item 1 in the first requisition so the db looks like:

Req - Item - Status
abc - 1 - rejected
abc - 2 - pending
abc - 3 - pending
xyz - 1 - pending
xyz - 2 - pending
xyz - 3 - pending

Then I created another page for the original user to display all requisitions with status pending with the following:

$reqquery = "SELECT * FROM `porders` WHERE `username` = '$username' AND `type` = 'request' AND `status` = 'pending' ";
$userreqs = mysql_query($reqquery) or die(mysql_error());

And here's my problem, there's no item 1.

I've now changed
<td>
if ($item <= 1) {
echo $reqno;
}
</td>

to

<td>
if ($item == 1) {
echo $reqno;
}
elseif ($item == 2) {
echo $reqno;
}
elseif ($item == 3) {
echo $reqno;
}
</td>

And of course this echos out the reqno at every item duplicating it again.

IDEA

This idea makes sense for me to try but not sure how to construct it or if it will be valid, can't get it to work. I need to stipulate the reqno and item no in the above block like this:

<td>
if ($reqno == $reqno) & ($item == 1) {
echo $reqno;
}
elseif ($reqno == $reqno) & ($item == 2) {
echo $reqno;
}
elseif ($reqno == $reqno} & ($item == 3) {
echo $reqno;
}
</td>

All the data is in the row, the reqno, item no and status. the SELECT filters status and the above is hopefully saying

if the $reqno is the same as the $reqno (which it will be but it allows me to link the reqno with the item no) and the $item is = to 1 echo $reqno else do item 2 else do item 3

I apologise for the length of this, I did try and streamline it as much as possible but its still ended up as this.

Thanks in advance for any ideas O.

[coopster]

You could always use the DISTINCT keyword in your query. That way you would only get one row per request. Of course, you would need to specify only that value in your field list rather than using the asterisk to return all fields.

[Orangutang]

Hi coopster,

Thanks for the help but alas I couldn't get it to work. The DISTINCT does remove the duplicate reqno rows which was great but I need it to show all the rows and just remove the duplicate reqno from the row.

I tried this:

$reqquery = "SELECT DISTINCT reqno FROM `porders` WHERE `username` = '$username' AND `type` = 'request' AND `status` = 'pending' ";
$userreqs = mysql_query($reqquery) or die(mysql_error());

while ($row = mysql_fetch_array($userreqs)) { ?>
<tr>
<td><?php echo "{$row['reqno']}";?></td>
<td><?php echo "{$row['itemno']}";?></td>
<td><?php echo "{$row['status']}" ;?></td>
</tr>
<?php } ?>

on this in the db:

Req - Item - Status
abc - 1 - rejected
abc - 2 - pending
abc - 3 - pending
xyz - 1 - pending
xyz - 2 - pending
xyz - 3 - pending

and it displayed:

Req - Item - Status
abc - 2 - pending
xyz - 1 - pending

So it removed the duplicate reqno rows but I needed it to display: (bak = should be blank)

Req - Item - Status
abc - 2 - pending
bak - 3 - pending
xyz - 1 - pending
bak - 2 - pending
bak - 3 - pending

It could very well be that the way I tried to implement DISTINCT has a mistake but I've tried many variations of the above but they all come out the same and they all worked in the way I thought they should which has left me out of anything else to try.

Many thanks though.

[Orangutang]

Hi coopster and everyone.

I was having a look at the problem from a different angle and tried to work out how many different scenarios I had to enable this script to deal with which has led me to this.

I'm using this:
$reqquery = "SELECT * FROM `porders` WHERE `username` = '$username' AND `type` = 'request' AND `status` = 'pending' ";
$userreqs = mysql_query($reqquery) or die(mysql_error());

while ($row = mysql_fetch_row($userreqs)) {
$reqno = $row[6]; $item = $row[8]; $status = $row[2]
?>
<tr>
<td><?php echo $reqno;?></td>
<td><?php echo $item;?></td>
<td>><?php echo $status;?></td>
</tr>
<?php }

I have this in the db:

Req - Item - Status
abc - 1 - pending
abc - 2 - pending
abc - 3 - pending
hjk - 1 - rejected
hjk - 2 - pending
hjk - 3 - rejected
xyz - 1 - rejected
xyz - 2 - rejected
xyz - 3 - pending

And I want to display all the rows that are pending without duplicating the reqno anywhere and echoing the reqno on hjk item 2 and xyz item 3. From this I think the following scenarios could occur:

// first scenario will be Req abc and item 1
// second scenario will be Req abc and item 2
// third scenario will be Req abc and item 3
// fourth scenario will be Req abc and item 1 and 2
// fifth scenario will be Req abc and item 1 and 2 and 3
// sixth scenario will be Req abc and item 1 and 3
// seventh scenario will be Req abc and item 2 and 3

Then they start again with the next reqno.

A // if scenario 1 echo reqno on item 1, item 2 and 3 blank
B // if scenario 2 echo reqno on item 2 and item 3 blank
C // if scenario 3 echo reqno on item 3
A // if scenario 4 echo reqno on item 1, item 2 and 3 blank
A // if scenario 5 echo reqno on item 1, item 2 and 3 blank
A // if scenario 6 echo reqno on item 1, item 2 and 3 blank
B // if scenario 7 echo reqno on item 2 item 3 blank

Theres a lot similarity which I've highlighted with A, B or C and these are what I'm trying to address.

So I'm changing <td><?php echo $reqno;?></td>

to this: (the italics can be deleted, I've left in to help me expalin)

I'm trying to address A, B or C from the above:

<td><?php

This deals with A. if reqno is the same as reqno and if the item is 1 always do this.

if ($reqno == $reqno) {
if ($item == 1) {
echo $reqno;
}
//if ($item == 2) {
//echo blank;
//}
//if ($item == 3) {
//echo blank;
//}
}

This deals with B. If its not A it must be else hence the below. The only thing I can see to use is the reqno. By default if its not A it must be another reqno because the reqno is the only thing that changed and it must be item 2.

if the reqno is not identical to the reqno which it won't be and it is item 2 as it will be echo $reqno.

I tried != not equal as well as not identical but I can't get it to work.

elseif ($reqno !== $reqno) {
if ($item == 2) {
echo $reqno;
}
//if ($item == 3) {
//echo blank;
//}
}

And this deals with C in the same way as dealing with B.

elseif ($reqno !== $reqno) {
if ($item == 3) {
echo $reqno;
}
}

So I'm still left unable to address B, it won't echo out that $reqno. Have I written not identical incorrectly or is there another cmd I could try. How else would you say if reqno is not the same as reqno do ......

I'm not sure what your thoughts are on this? Does it look like I'm trying something feasible? Any ideas most welcome.