php&MySQL problem

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
Telos
Forum Commoner
Posts: 37
Joined: Sat Aug 02, 2003 9:03 am
Location: Finland

php&MySQL problem

Post by Telos »

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.

Code: Select all

<?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');
?>
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

I just skimmed through your question - so I might have picked you up wrong - but it sounds like you need a few AND clauses in the SELECT query?

Mysql manual on mysql.com explains syntax.
Telos
Forum Commoner
Posts: 37
Joined: Sat Aug 02, 2003 9:03 am
Location: Finland

Post by Telos »

No I don't think I can do it by that...the problem is that I don't know how many profileformulas there will be. There can be 1 or there can be 10 or anything...The solution I suggest will probably work if I knew how to code it :)
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

I would think that you can do this with query logic rather than filtering arrays in php.

1. Different research projects have lists of profile IDs.
2. Each profile ID has a variable number of formulas.

So, for a given research project, loop through the profile IDs performing a db query which uses query logic to apply all the appropriate formulas for the current profile ID.

There might also be some scope for abstracting the formula calling using variable function names. If some profile formulas are shared across different profiles, this would let you re-use the same code (perhaps you've already thought of that).

-> define php functions which create snippets of mysql query strings
-> then, inside your profile ID loop, a second loop can build the query string by looping through the function names specified in the profile formulas table

Something like this (rough outline):

Code: Select all

<?php

$profile_IDs = array('.. an array of profile IDs');
foreach($profile_IDs as $profile)
{
    // if profile formulas table has a column for formulas containing a space separated list of formula names.. (actually you could just add a col to profiles table)
    $mysql = "SELECT formulas FROM profile_formulas WHERE profile_ID='$profile'";
    $query = ..etc;
    $result = mysql_fetch_row($query);

    $formulas = $result['formulas'];
    $formulas = explode(' ', $formulas);
    
    foreach($formulas as $value)
    {
        $clauses .= $value();
    }
    $mysql_profile = "SELECT columns FROM table WHERE " . $clauses;
    $query_profile = mysql_query($mysql_profile) or die(..etc);
    
    // extract results from query - yet another loop
}

?>
You would also have a list of all formulas in one location (a php script somewhere) - easy to review or edit later - but this variable function idea might not be so useful if the forumulas aren't really shared across profiles.
Last edited by McGruff on Wed Aug 10, 2005 11:50 pm, edited 1 time in total.
Telos
Forum Commoner
Posts: 37
Joined: Sat Aug 02, 2003 9:03 am
Location: Finland

Post by Telos »

I have to really get into that what you suggested... Looks interesting and might work but I have to look into it more. Getting back to this topic when I know more about that.
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

Rgr. The table structure I suggested above - space separated values in a single column - is a bit of a kludge in some ways. If you want to fully normalise the db I guess each formula should have its own table with rows for each profile ID in which the formula is used.

Still, you don't always want to fully normalise a db. Sometimes it makes sense not to - provided a sub-normalised structure isn't likely to lead to problems in the future.
Telos
Forum Commoner
Posts: 37
Joined: Sat Aug 02, 2003 9:03 am
Location: Finland

Post by Telos »

ok, that doesn't work with the table structure I have. I have the formulas in the table like this (fields)

id = the id number of formula
profile_id = the id of the profile where this formula belongs
question = the id number of the question this formula affects
operator = the operator (= , < , > , <= , >=)
compare = the value of where to compare the question answer

So I can get things from the table like this

What I need: age under 25
What I do:
- question = 1 (the id number of question AGE)
- operator = <
- compare = 25

So any new ideas now?
I don't think the array thingie which I suggested earlier would be good enough for me so how would I do that?
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

I think the final mysql query (I'm assuming you are using mysql?) would be something like this:

Code: Select all

<?php
$mysql = "SELECT FROM table WHERE age<'25' AND gender='female' AND ..etc - any other formulas"
?>
So, if you query the formulas table on profile ID to get all the formulas for this profile, you can loop through the query creating an AND clause for each formula:

Code: Select all

<?php
$clause .= ", AND " . $column . $operator . "'" . $value  . "'";
?>
->In the first clause, of course, omit the ", AND".
->Also I'm assuming you have the $column used with the formula $operator and $value somewhere.

Adding "SELECT FROM table WHERE " completes the mysql query string - maybe you'd want to tack on an ORDER BY too.

I find when I'm building a string like this it's very easy to forget a space or a comma somewhere, so it's good to add a debug line to echo the query string.

I think your db structure is definitely better than the kludge I suggested. It does however restrict you to simple comparison formulas. That may be all you need but if the $column/$operator/$value data were stored in a single formula column, in future you would be free to create other types of formulas using the mysql functions.

Of course you will understand what you need to do much better than I - just wanted to make a general point that the choices you make in db design influence how easy it is to adapt the program if you need to add new functionality later on.
Post Reply