Really need help

Anon

I asked this on the mysql list, but nobody seemed to have a working solution:

Ok, I've been battling this SELECT statement for the better part of the day.

The SELECT statement is this:

SELECT DISTINCT request.id AS requestid, request.date, request.type, request.status,
faculty.f_name, faculty.l_name, action.id AS actionid, faculty.id AS facultyid FROM faculty, request, action WHERE request.id=action.request_id AND request.requested_by=faculty.id AND request.status=2;

And the results are :

http://www.geology.ohio-state.edu/test/rfatest/rfaadmin.php?closed=Closed

Now, if you look at the table, at the far left cell under RFA#, you'll notice that they are all the same (2). That is because there were 4 actions performed on RFA # 2. However, I only need for the column to be displayed once. When the user clicks under 'last action' then they can see all 4 actions. But for this table, I just need to show the data once.

So, could any kind soul help me out?

Thanks

jalexf

I've had to do this but I solved it in PHP and not mySQL. I'm not slick enough with my queries, but it may be possible to do it.

Here is a quick PHP solution:

while ($row....etc) { //get your rows

$checkForUsed = "|$row[RFA]|"; // your RFA inside of pipes for delimiting

if (!strstr($usedNumbers,$row[RFA])) { // has this RFA been used?

print your row;
$usedNumbers .= "|$row[RFA]|"; // if this is the first time, add it to the list so it's not used again

}

..off the top of the head and typed into this silly little window so forgive any syntax.

jalexf

I forgot that I created a variable and then didn't use it. I remember when I had to do this, I couldn't get the strstr to take "|".$row."|" as a valid needle to search for, so I used an extra variable.

Here it is..

while ($row....etc) { //get your rows

$checkForUsed = "|$row[RFA]|"; // your RFA inside of pipes for delimiting

if (!strstr($usedNumbers,$checkForUsed)) { // has this RFA been used?

print your row;
$usedNumbers .= $checkForUsed; // if this is the first time, add it to the list so it's not used again

}

Anon

JAlexf,

I have my code setup like this currently:

$result=mysql_query($query);

//begin while loop to grab all needed data out of the database
while ($query_data=mysql_fetch_array($result)) {

//set up variables to echo the data
$rfaid=$query_data["requestid"];
$date=$query_data["date"];
...etc

So where would I put your code? before I setup the variables?

Thanks,
MT

jalexf

$result=mysql_query($query);

//clear the used list
$usedNumbers = "";

//begin while loop to grab all needed data out of the database
while ($query_data=mysql_fetch_array($result)) {

//set up variables to echo the data
$rfaid=$query_data["requestid"];
$date=$query_data["date"];

//format the current RFA for checking-- put it in pipes
$checkForUsed = "|$rfaid|";

// is this RFA already in the "used list?"
if (!strstr($usedNumbers,$checkForUsed)) {

// if not, print this row since it hasn't been used before
print your row;

// and add this RFA to the "used list" so that it won't be used again.
$usedNumbers .= $checkForUsed;

} // close if

} // close while

This replaces your current while loop. It will only allow each result to print one time since the second time it encounters the RFA, it will be in the $usedNumbers list (determined by the strstr command) and wlil not be printed again.

Anon

gee how did you get the neato pop up windows for the names?

Anon

Javascript. 🙂

Anon

Hehe 🙂

HTML LESSON 1:

VIEW > SOURCE

Tune in tomorrow for lesson 2...