How can i put a column of results into an array and compare then with another column with PHP?

Go To StackoverFlow.com

1

My system is base on driving permit application. Where users can apply for different level of permit, for example if user want to apply for class 5 permit, they must have pre requisites of class 2 and 3.

In my database, i have 2 related tables, 1 is the permit where it stores all the permit that users have applied for or is waiting for approval. The other table is the description of the permit type where it indicates each available permit that users can apply for together with its pre-requisites and description.

permit ( PID,EID, PTYPE,STATUS,DATETIMEAPP)

type (TYID,PTYPE, PREREQ1, PREREQ2, REQEXP, DES)

My plan is to retrieve data from the database

$query= "select PTYPE from permit where eid = $user"
$result = mysql_query($query, $conn);

Store the result as an array

while ($row = mysql_fetch_assoc($result))
{
    $result_array[] = $row;
}

retrieve the pre requites from the type table

$query= "select PREREQ1 , PREREQ2 from type where TYID = $appliedPermit"
$result = mysql_query($query, $conn);

Compare the 2 results together with a boolean ( I not sure how can i code this)

If result is true then proceed else return to previous page with a pop up to inform user that they have not met pre-requisites. ( i know i need the if else statement but not sure how to write the condition)

Do you guys think that i am on the right path?? I am not sure if it will work...but hope any one here have a better suggestion.

Restructure a little:

My system aims to allow users to apply for driving permit...there are a total of 9 permits -Class 1, Class 2, Class 3. Cat 2PG, Cat 3PG, Car4PG, Cat 1OR, Cat 2OR, Cat 3OR, Cat 2TT and Cat 3TT. For example Cat 3TT require users to have Cat 2TT and Cat 2PG. in another words, the pre-requisites of Cat 3TT is Cat 2TT and Cat 2PG.

I would like to use PHP to draw all the required values from the database and compare them to see if the user have met the pre-requsites before allowing him to proceed to the medical questionnaire if he haven got 1.

The Problem is: I do not know how to code it out logically, I tried but it did not work. Hoping to get more opinions and solutions to my problem. Thank You very much. Help is greatly appreciated.

2012-04-03 21:19
by Hubert
after reading your question a couple of times, i am still not quite sure WHAT it is you actually want to compare. could you elaborate or reformulate your question - thrau 2012-04-03 21:36
@thrau hmm...i did a restructuring of the question...hope its better...i add it at the bottom of the codings - Hubert 2012-04-03 22:11


2

You can check the prerequisites in only one query :

$query = "SELECT t.tyid, t.prereq1, t.prereq2
, (CASE WHEN (t.prereq1 IS NOT NULL) AND (p1.ptype IS NULL) THEN 1 ELSE 0 END) AS missing1
, (CASE WHEN (t.prereq2 IS NOT NULL) AND (p2.ptype IS NULL) THEN 1 ELSE 0 END) AS missing2
FROM type AS t
LEFT JOIN permit AS p1 ON (t.prereq1=p1.ptype) AND ( p1.eid = $user )
LEFT JOIN permit AS p2 ON (t.prereq2=p2.ptype) AND ( p2.eid = $user )
WHERE ( t.tyid IN (".implode(',', $appliedPermit).") )";

$result = mysql_query($query, $conn);
while ($row = mysql_fetch_assoc($result)) {
  echo "permit ".$row['tyid']. " : "
  echo "prerequisites = ".$row['prereq1'].", ".$row['prereq2'];
  if ( ($row['missing1']==1) && ($row['missing1']==1) ) {
    echo ", result = ok <br>";
  } else {
    echo ", result = failed <br>";
  }
}

This query returns the two prerequisites id ; but also missing1 value is 1 if prerequisite #1 is missing, and missing2 value is 1 if prerequisite #2 is missing.

Note: I've updated my post to reply to your two comments below.

2012-04-03 21:44
by Skrol29
If not all my permit have 2 pre requisites, for example like in the database, some prequisites will have null value, will this query still work? Thanks so muc - Hubert 2012-04-03 22:12
Yes it will work, but it needs a small adjustment for a better meaning of missing1 and missing2. Replace the missing1 expression with (CASE WHEN (t.prereq1 IS NOT NULL) AND (p1.ptype IS NULL) THEN 1 ELSE 0 END), and so one for missing2. Then missing1 and missing2 will be equal to 1 only if there is a actually a prerequisite and that it is missing - Skrol29 2012-04-03 23:33
my $appliedPermit will be an array of result...how to i make it compare individually? Can i use a while loop like how i row out the data from database?Thanks in advanc - Hubert 2012-04-04 11:59
or should i use a foreach() - Hubert 2012-04-04 12:40
hi...do you mind to explain what doe LEFT JOIN permit AS p1 ON (t.prereq1=p1.ptype) AND ( p1.eid = $user ) LEFT JOIN permit AS p2 ON (t.prereq2=p2.ptype) AND ( p2.eid = $user ) do?? is it that it can compare only 2 of its past permit - Hubert 2012-04-04 12:55
hi...may i check with you how can i define the missing1 and missing 2 to use in my if else statement - Hubert 2012-04-04 16:41
@Hubert: I've updated my post to reply to your comments about $appliedPermit being an array, and how to check missing1 and missing2 - Skrol29 2012-04-05 00:45
@Hubert: you said "do you mind to explain what doe LEFT JOIN...". The first LEFT JOIN clause will give the permit record that corresponding to prereq1 and user $user. But it will also return a record with all null values for each column if no record is found (that is what the LEFT does). It is the same for the second LEFT JOIN - Skrol29 2012-04-05 00:51


1

Run the following query and then print the result to the user as the prereqs he needs.

 SELECT prereq1, prereq2 from type 
 where tyid = $appliedPermit 
 and prereq1 not in (select  ptype from permit where eid = $user) 
 and prereq2 not in (select ptype from permit where eid= $user);
2012-04-03 21:52
by kasavbere