Write a qry in php and mysql to resort a list
Posted: Mon Sep 08, 2008 11:25 am
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;
}
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;
}