Write a qry in php and mysql to resort a list

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
justmelat
Forum Newbie
Posts: 2
Joined: Mon Sep 08, 2008 11:06 am

Write a qry in php and mysql to resort a list

Post by justmelat »

I need to attached a link that will resort a table based on a field equaling NO. I am using some code already in the app and I thought I could easily modify it - it's not working.

I am using php 4.1, mysql5

case statement calls function:
case "show_gtm_requests":
$this->requests = getArchivedRequests();
$this->listTitle ="GTM No";
break;

the function looks in the first table (requests), get all records that are not archived (the R), then it moves to the next qry and looks in the answer table to find all the matching id numbers. It works for just pulling the archived record, but I need to add to that second query. I need to get all the records from ANSWER where 'TEXT' equals "No", then out of that I want all the ones WHERE Q_ID = Q_67

Could someone show me how to write this query. Everything I try pulls back either no records or too many. I need help.

Help please!

The function is below:

function getArchivedRequests($RF='*',$AF='*')
{
global $db;

$RL=array();
$sql = "SELECT ";
if (is_array($RF))
{
$sql .= implode(",",$RF);
} else
{
$sql .= "*";
}

$sql .= " FROM REQUEST WHERE R_STATUS != 'R' ORDER BY R_NUMBER DESC";

$result = mysql_query($sql,$db) or die($sql);


while ($row = mysql_fetch_assoc($result))
{
foreach ($row as $key=>$val)
{
$R[$row['R_NUMBER']][$key]=$val;
}

array_push($RL,"'".$row['R_NUMBER']."'");
}
//Now get the answers
if (!empty($RL))
{
$sql = "SELECT * FROM ANSWER WHERE R_NUMBER IN (".implode(",",$RL).") ";



//Quote the QIDs
if (is_array($AF))
{
foreach ($AF as $key=>$val)
{
$AF[$key]="'".$val."'";
}

$sql .= " AND Q_ID IN (".implode($AF).")";

}

$result = mysql_query($sql,$db) or die($sql);

while ($row = mysql_fetch_assoc($result))
{
$R[$row['R_NUMBER']]['ANSWER'][$row['Q_ID']]=$row['TEXT'];

}
}
return $R;

}
Attachments
tblView.GIF
tblView.GIF (4.58 KiB) Viewed 104 times
qryFromDB.GIF
qryFromDB.GIF (7.42 KiB) Viewed 110 times
justmelat
Forum Newbie
Posts: 2
Joined: Mon Sep 08, 2008 11:06 am

Re: Write a qry in php and mysql to resort a list

Post by justmelat »

WOW, i guess this is pretty hard
Post Reply