Ok, here's the situation:
I'm creating a research system and I have among others an answer, profile, profileformula and answerer tables. Profiles are there to profilize answerers like this:
Profile: List all under 25 years old females.
ProfileFormulas related to this profile are:
question AGE < 25
question GENDER = female
I have a problem related to reporting and showing all answerers in different profiles. I can do it if a profile has only 1 profileformula but I need it to work with more. So now I do it like this.
1. I get all the profile id's which are related to specific research
2. Then I get all the profileformulas which are related to specific profile.
3. I get all the answerers from the answers table where the answer is according to the formula
4. I get the answerer details according to the answerer id which I got from the last query.
This doesn't work if there are more than 1 profileformula in pne profile. So I've thought the following solution
1. When I get the answerer id's like in stage 3 earlier I put the to array so the array has all the user id's which full the first profileformula.
2. When I start to check the next formula I go through the array and if the id which I get from the second formula check is on the array I mark it as checked. When the query array has gone through I delete all the names from the array which are not checked.
4. I continue until I've gone through all the profileformulas and as a result I have an array which has all the id's of such answerers who fullfill all the profileformula requirements.
The problem is that I don't know how to code this? Anyone can help? here's the report.php code as it is at the moment so maybe that will clear things a bit for you.
| php: |
<?php
session_start();
// Tarkistetaan onko käyttäjä sisäänkirjautunut ja omaa tarvittavat käyttöoikeudet
if($_SESSION['sessio_sukunimi'] == '')
header('location: login.php');
if($_SESSION['sessio_kayttajataso'] < 5)
header('location: insufficient_userlevel.php');
// Haetaan käyttäjän valitsema teema
if($_SESSION['sessio_theme'] == '')
$theme = 'default';
else
$theme = $_SESSION['sessio_theme'];
include('sivupohjat/'.$theme.'_header.php');
// Otetaan parametrit vastaan
$tutkimus_id = $_GET['tutkimus'];
dbConnect();
$sql = mysql_query("SELECT id, nimi, kuvaus FROM ir_profiili WHERE tutkimus = $tutkimus_id");
while($result = mysql_fetch_array($sql))
{
$profiili_id = $result['id'];
$profiili_nimi = $result['nimi'];
$profiili_kuvaus = $result['kuvaus'];
echo("PROFIILI: <b>$profiili_nimi</b><br>$profiili_kuvaus<br><br>");
// Kun tiedetään profiili_id niin haetaan kaavat tuolle id:lle
$sql2 = mysql_query("SELECT kysymys, operaattori, vertailu FROM ir_profiilikaavat WHERE profiilitunnus = $profiili_id");
while($result2 = mysql_fetch_array($sql2))
{
$kysymys = $result2['kysymys'];
$operaattori = $result2['operaattori'];
$vertailu = $result2['vertailu'];
// Haetaan vastaajat jotka ovat profiilin mukaisia
echo("<ul type=\"disc\">");
$sql3 = mysql_query("SELECT DISTINCT vastaaja FROM ir_vastaus WHERE kysymys = $kysymys AND vastaus $operaattori '$vertailu' AND vastaus != ''");
while($result3 = mysql_fetch_array($sql3))
{
$vastaaja_id = $result3['vastaaja'];
// Kun tiedetään vastaajatunnus niin voidaan hakea vastaajan tiedot vastaajataulusta
$sql4 = mysql_query("SELECT etunimi, sukunimi, email FROM ir_vastaaja WHERE id = '$vastaaja_id'");
$result4 = mysql_fetch_array($sql4);
$etunimi = $result4['etunimi'];
$sukunimi = $result4['sukunimi'];
$email = $result4['email'];
echo("<li><b>$sukunimi, $etunimi</b> <a href=\"mailto:$email\" title=\"Mailaa\">$email</a></li>");
}
echo("</ul>");
echo("<br>");
}
}
include('sivupohjat/'.$theme.'_footer.php');
?>
|
|
|
