Conditional Logic Problem (Pseudocode or PHP)

I need help with programming the conditional logic for the following problem in PHP:

File: report.php
-------------------------
This file lists ~20 questions each with multiple checkboxes to end user. The end user will select various checkboxes for various questions and on submit the page will return the total count of users that fit that criteria.

Logic:
If the end user selected multiple options in the same question then the options are “OR’ed”. EX: Return count of all users who selected Option 1 OR Option 3 on Question 5  
If the end user selected options from multiple questions then the questions are “AND’ed”. EX: Return count of all users who selected Option 4 on Question 2 AND Option 1 on Question 7

Advanced test case: Return count of users who answered (Option 1 OR Option 3 on Question1) AND (Option 2 OR Option 3 on Question 4) AND (Option 1 on Question 7).

The report.php file will post to process.php which I need to develop.

First, I need to capture all the variable data. I was going to _REQUEST all 100 or so variables but then thought about using explode on the _POST and loading the responses into an array. Any recommendations?

Then, I want to construct the query that will handle the above conditional logic. I was going to construct a string ($where) that contains something like this:

WHERE (q1=1 || q1=3) AND (q4_2 = 1 || q4_3 = 1) AND (q7 = 1)

Please help me understand the best way of implementing this. PHP or psuedocode would really be beneficial. Your time is much appreciated and thank you in advance.
LVL 1
ericcchamberlainAsked:
Who is Participating?
 
Ray PaseurCommented:
OK, I'll try to give you a few of my ideas here.  First, let's think about the data base.  Responses will need to coordinate three sets of information - the client ID, the question set and the answer set.  Not sure if you can assume that all clients will be asked the same questions, but it simplifies things if that is true.

Conceptually, except for variability of the questions and the complex reporting part, what you have here is something like a "choose your favorite color" quiz.  I recently did a demonstration script set for an EE question that covered this.  You can see the current status on my server, here:
http://www.laprbass.com/RAY_temp_jvsmooth_vote.php

The first part of this task was to create the tables of colors and votes.
<?php // RAY_temp_jvsmooth_create.php
error_reporting(E_ALL);


// DEMONSTRATE THE VOTING ALGORITHM - CREATE THE TABLES


// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}
// IF WE GOT THIS FAR WE CAN DO QUERIES


// REMOVE OLD VERSIONS OF THE TABLES; IGNORE ANY ERRORS (LIKE NO TABLE OF THIS NAME)
// mysql_query("DROP TABLE EE_vote_colors");
// mysql_query("DROP TABLE EE_vote_votes");


// CREATE THE COLOR TABLE
$sql
= "CREATE TABLE EE_vote_colors
( _key  INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, color VARCHAR(10) NOT NULL DEFAULT ''
)"
;
$res = mysql_query($sql) or die( "$sql<br/>" . mysql_error() );


// CREATE THE VOTES TABLE
$sql
= "CREATE TABLE EE_vote_votes
( _key       INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, color      VARCHAR(10) NOT NULL DEFAULT ''
, ip_address VARCHAR(16) NOT NULL DEFAULT 'unknown'
, when_voted TIMESTAMP
)"
;
$res = mysql_query($sql) or die( "$sql<br/>" . mysql_error() );


// LOAD THE STANDARD ROY-G-BIV DATA INTO THE COLOR TABLE
$res = mysql_query( "INSERT INTO EE_vote_colors ( color ) VALUES ( 'Red'    )" );
$res = mysql_query( "INSERT INTO EE_vote_colors ( color ) VALUES ( 'Orange' )" );
$res = mysql_query( "INSERT INTO EE_vote_colors ( color ) VALUES ( 'Yellow' )" );
$res = mysql_query( "INSERT INTO EE_vote_colors ( color ) VALUES ( 'Green'  )" );
$res = mysql_query( "INSERT INTO EE_vote_colors ( color ) VALUES ( 'Blue'   )" );
$res = mysql_query( "INSERT INTO EE_vote_colors ( color ) VALUES ( 'Indigo' )" );
$res = mysql_query( "INSERT INTO EE_vote_colors ( color ) VALUES ( 'Violet' )" );

Open in new window


The next part was to create a script to report the votes and accumulate new votes.
<?php // RAY_temp_jvsmooth_vote.php
error_reporting(E_ALL);


// DEMONSTRATE THE VOTING ALGORITHM


// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}
// IF WE GOT THIS FAR WE CAN DO QUERIES


// GET THE ARRAY OF COLORS FROM THE DATA BASE
$colors = array();
$sql = "SELECT color FROM EE_vote_colors";
$res = mysql_query($sql) or die( "$sql<br/>" . mysql_error() );
while ($row = mysql_fetch_assoc($res))
{
    $colors[] = $row["color"];
}
// ACTIVATE THIS TO SEE THE COLORS
// var_dump($colors);


// IF ANYTHING WAS POSTED RECORD THE VOTE
if (!empty($_POST["color_selections"]))
{
    $ipa = (!empty($_SERVER["REMOTE_ADDR"])) ? $_SERVER["REMOTE_ADDR"] : 'unknown';
    foreach($_POST["color_selections"] as $color => $nothing)
    {
        // NORMALIZE THE POST DATA
        $rgb = mysql_real_escape_string(ucfirst(strtolower(trim($color))));

        // SKIP FIELDS THAT ARE NOT PART OF OUR COLOR SET (POSSIBLE ATTACK?)
        if (!in_array($rgb, $colors)) continue;

        // RECORD A VOTE FOR THIS COLOR
        $sql = "INSERT INTO EE_vote_votes ( color, ip_address ) VALUES ( '$rgb', '$ipa' )";
        $res = mysql_query($sql) or die( "$sql<br/>" . mysql_error() );
    }
}


// SHOW THE STATISTICS FOR THE COLORS
foreach ($colors as $color)
{
    $sql = "SELECT ip_address, when_voted FROM EE_vote_votes WHERE color = '$color' ORDER BY when_voted DESC";
    $res = mysql_query($sql) or die( "$sql<br/>" . mysql_error() );
    $num = mysql_num_rows($res);
    $row = mysql_fetch_assoc($res);
    $ipa = $row["ip_address"];
    $whn = $row["when_voted"];
    echo "<br/>";
    echo number_format($num);
    echo " VOTES FOR ";
    echo $color;
    if ($num)
    {
        echo " MOST RECENTLY ";
        echo $whn;
        echo " FROM IP ";
        echo $ipa;
        echo PHP_EOL;
    }
}


// CREATE THE FORM TO RECEIVE THE VOTES
echo '<form method="post">';
echo "<br/>VOTE FOR YOUR FAVORITE COLORS" . PHP_EOL;
foreach ($colors as $color)
{
    echo "<br/>";
    echo '<input type="checkbox" name="color_selections[';
    echo "$color";
    echo ']" />';
    echo $color;
    echo PHP_EOL;
}
echo '<br/><input type="submit" value="VOTE NOW" />' . PHP_EOL;
echo '</form>';

Open in new window


In your case you are accumulating multiple "votes" per question - ie, there is a zero-to-many relationship between selected Options and Questions.  And if we can assume that each client takes the quiz once, we have a one-to-many relationship between the clients and each question.

Random thought: explode is a term of art in PHP - it refers to the process of splitting a string into a numerically indexed array.  So it sounds a little out of place when you refer to "exploding the $_POST variable" since $_POST is already an array.
http://us2.php.net/manual/en/function.explode.php

Does any of this seem helpful to you?
0
 
Ray PaseurCommented:
Have you started any code yet?  How strong are you in PHP programming?
0
 
Aaron TomoskySD-WAN SimplifiedCommented:
What database is the information in? Mysql?
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
ericcchamberlainAuthor Commented:
Hello Ray, thank you for your reply. I am an intermediate level PHP programmer with 1 year of experience in programming with PHP and a comp sci background.

I have not started any code yet because I'm not sure what the best way to implement this would be. I started two different approached then thought about how extremely inefficient / tedious this would be and that there had to be a better way.

The problem I'm having is the mixing of AND and OR logic into groups, and keeping track of when to add the logic operators. I though about doing this as a multi-dimensional array such as:

Exploding the $_POST variable to and dumping it into a multi-dimensional array like: $array[question][option]  ... then constructing where SQL WHERE string by looping through the array so that the options are OR'ed and the questions are AND'ed.

0
 
ericcchamberlainAuthor Commented:
Hello aarontomosky:

You are correct, the database is MySQL. I'm working on a LAMP stack.
0
 
TommySzalapskiCommented:
I would use the bitwise operators to make each question one integer. If you've never used bitwise operations, then this is a good time to learn.
Quick tutorial
1 as a byte is stored as 00000001 (integers have more bytes, but I don't want to type that many 0s)
2 is 00000010
4 is 00000100
8 is 00001000
etc
So if you set each option to a power of two and bitwise or them together (using the | operator) then you can keep each question as one integer.
8 | 2 = 00001010 etc
The & operator does the same thing except it ands them. So 00000111 & 00001110 = 00000110
The other operator that's useful here is << which shifts the bits to the left
So 00000001 << 1 = 00000010
and 00000001 << 3 = 00001000
$a << $b is the same as $a * 2^$b only much faster.

So if a question has n options (must be less than the number of bits in an int so a 4-byte int can have 32 options)

int $q1 = 0
for i = 1 to n
{
  if(Option i is true) then
    $q1 = $q1 |  ( 1 << (i-1))
}
Now to compare your $q1 with the stored $Q1just compare $q1 | $Q1. If it's > 0 then one of the options matches. If $q1 = 0 then no option was selected and it can be ignored.

Where ( ($q1 == 0 || $q1 | $Q1 > 0) && ($q2 == 0 || $q2 | $Q2 > 0)
0
 
TommySzalapskiCommented:
I'm not that good at PHP at all. But in C 0 means false and !=0 means true so you could shorten it to
Where (!$q1 || $q1|$Q1) && (!$q2 || $q2|$Q2)
0
 
Ray PaseurCommented:
@TommySzalap… in PHP, 0 == NULL == FALSE, etc.  Double equal signs are used to test for equivalent loosely-typed meanings, whereas triple equal signs are used to test for identical meanings.  0!== NULL !== FALSE, etc.

TRUE, non-zero values, not NULL strings all evaluate TRUE.
0
 
ericcchamberlainAuthor Commented:
Thank you Ray_Paseur and TommySzalapski for your insight. I am not sure if using bitwise operators makes sense for this senario but I have found both of your responses helpful.

I wrote the following to show you what I want to avoid doing, since it is tedious and inefficient. There must be a more elegant solution.

In the code below I am constructing the MySQL conditional logic to append onto the WHERE in the query that will return the count of users that fit that criteria.

 
<?php 
global $query; 

function group_string ($string) {
	$string = " (" . $string . ") "; 
	return $string; 
}

//Question 1 
if ( $_REQUEST["q1_1"] == 1 || $_REQUEST["q1_2"] == 1 || $_REQUEST["q1_3"] == 1 ) { 
	$temp = "";
	if ($_REQUEST["q1_1"] == 1) {
		$temp .= "q1 = 1";
		if ($_REQUEST["q1_2"] == 1) {
			$temp .= " OR q1 = 2";
		}
		if ($_REQUEST["q1_3"] == 1) {
			$temp .= " OR q1 = 3";
		}
	}
	else if ($_REQUEST["q1_2"] == 1) {
		$temp .= "q1 = 2";
		if ($_REQUEST["q1_3"] == 1) {
			$query .= " OR q1 = 3";
		}	
	}
	else if ($_REQUEST["q1_3"] == 1) {
		$temp .= "q1 = 3";
	}	
	$temp = group_string($temp);
	$query .= $temp; 
}

//Question 2
if ( $_REQUEST["q2_1"] == 1 || $_REQUEST["q2_2"] == 1 || $_REQUEST["q2_3"] == 1 || $_REQUEST["q2_4" == 1 || $_REQUEST["q2_5"] == 1] ) { 
	$temp = "";
	if ($_REQUEST["q2_1"] == 1) {
		$temp .= "q2 = 1";
		if ($_REQUEST["q2_2"] == 1) {
			$temp .= " OR q2 = 2";
		}
		if ($_REQUEST["q2_3"] == 1) {
			$temp .= " OR q2 = 3";
		}
		if ($_REQUEST["q2_4"] == 1) {
			$temp .= " OR q2 = 4";
		}
		if ($_REQUEST["q2_5"] == 1) {
			$temp .= " OR q2 = 5";
		}
	}
	else if ($_REQUEST["q2_2"] == 1) {
		$temp .= "q2 = 2";
		if ($_REQUEST["q2_3"] == 1) {
			$temp .= " OR q2 = 3";
		}
		if ($_REQUEST["q2_4"] == 1) {
			$temp .= " OR q2 = 4";
		}
		if ($_REQUEST["q2_5"] == 1) {
			$temp .= " OR q2 = 5";
		}
	}
	else if ($_REQUEST["q2_3"] == 1) {
		$temp .= "q2 = 3";
		if ($_REQUEST["q2_4"] == 1) {
			$temp .= " OR q2 = 4";
		}
		if ($_REQUEST["q2_5"] == 1) {
			$temp .= " OR q2 = 5";
		}
	}
	else if ($_REQUEST["q2_4"] == 1) {
		$temp .= "q2 = 4";
		if ($_REQUEST["q2_5"] == 1) {
			$temp .= " OR q2 = 5";
		}
	}
	else if ($_REQUEST["q2_5"] == 1) {
		$temp .= "q2 = 5";
	}
	$temp = group_string($temp);
	$query .= " AND " . $temp; 
}

//Question 3 
if ( $_REQUEST["q3_1"] == 1 || $_REQUEST["q3_2"] == 1 ) { 
	$temp = "";
	if ($_REQUEST["q3_1"] == 1) {
		$temp .= "q3 = 1";
		if ($_REQUEST["q3_2"] == 1) {
			$temp .= " OR q3 = 2";
		}
	}
	else if ($_REQUEST["q3_2"] == 1) {
		$temp .= "q3 = 2";
	}
	$temp = group_string($temp);
	$query .= " AND " . $temp; 
}

//Question 4
if ( $_REQUEST["q4_1"] == 1 || $_REQUEST["q4_2"] == 1 || $_REQUEST["q4_3"] == 1 || $_REQUEST["q4_4" == 1 || $_REQUEST["q4_5"] == 1] || $_REQUEST["q4_6"] == 1 ) { 
	$temp = "";
	if ($_REQUEST["q4_1"] == 1) {
		$temp .= "q4 = 1";
		if ($_REQUEST["q4_2"] == 1) {
			$temp .= " OR q4 = 2";
		}
		if ($_REQUEST["q4_3"] == 1) {
			$temp .= " OR q4 = 3";
		}
		if ($_REQUEST["q4_4"] == 1) {
			$temp .= " OR q4 = 4";
		}
		if ($_REQUEST["q4_5"] == 1) {
			$temp .= " OR q4 = 5";
		}
		if ($_REQUEST["q4_6"] == 1) {
			$temp .= " OR q4 = 6";
		}
	}
	else if ($_REQUEST["q4_2"] == 1) {
		$temp .= "q4 = 2";
		if ($_REQUEST["q4_3"] == 1) {
			$temp .= " OR q4 = 3";
		}
		if ($_REQUEST["q4_4"] == 1) {
			$temp .= " OR q4 = 4";
		}
		if ($_REQUEST["q4_5"] == 1) {
			$temp .= " OR q4 = 5";
		}
		if ($_REQUEST["q4_6"] == 1) {
			$temp .= " OR q4 = 6";
		}
	}
	else if ($_REQUEST["q4_3"] == 1) {
		$temp .= "q4 = 3";
		if ($_REQUEST["q4_4"] == 1) {
			$temp .= " OR q4 = 4";
		}
		if ($_REQUEST["q4_5"] == 1) {
			$temp .= " OR q4 = 5";
		}
		if ($_REQUEST["q4_6"] == 1) {
			$temp .= " OR q4 = 6";
		}
	}
	else if ($_REQUEST["q4_4"] == 1) {
		$temp .= "q4 = 4";
		if ($_REQUEST["q4_5"] == 1) {
			$temp .= " OR q4 = 5";
		}
		if ($_REQUEST["q4_6"] == 1) {
			$temp .= " OR q4 = 6";
		}
	}
	else if ($_REQUEST["q4_5"] == 1) {
		$temp .= "q4 = 5";
		if ($_REQUEST["q4_6"] == 1) {
			$temp .= " OR q4 = 6";
		}
	}
	else if ($_REQUEST["q4_6"] == 1) {
		$temp .= "q4 = 6";
	}
	$temp = group_string($temp);
	$query .= " AND " . $temp; 
}

echo $query;

?>

Open in new window


I'm looking for a more elegant way to constructing the following:
(q1 = 1 OR q1 = 3) AND (q2 = 1 OR q2 = 3 OR q2 = 4) AND (q3 = 1) AND (q4 = 1 OR q4 = 2 OR q4 = 3 OR q4 = 4 OR q4 = 5 OR q4 = 6)



0
 
TommySzalapskiCommented:
Well, the first thing to do would be to put it all into loops. Again, I know algorithms and some SQL but almost no PHP (I looked up the bitwise operators this morning) so you'll likely have to adjust this code a bit. I know C, and PHP looks like C with $ in front of all the variables.

$NoneInQuery = True;
//Loop through the questions (get the question count somehow)
for($i = 1; $i <= $QUESTION_COUNT; $i++)
{
  //Reset these for each question 
  $temp = "";
  $All0SoFar = True; 
  //Loop through the responses. Count them somehow or use exception handling
  for($j = 1; $j <= $RESPONSE_COUNTS[$i]; $j++)
  {
    if ( $_REQUEST["q".strval($i)."_".strval($j)] == 1)
    {
      //If we haven't found one yet, don't add the OR
      if($All0SoFar == False)
      {
        $temp .= " OR ";
      }
      $temp .= "q".strval($i)." = ".strval($j);
      $All0SoFar = False;
    }
  }
  If($All0SoFar == False) //We found at least one
  {
    If($NoneInQuery == False)
    {
      $query .= " AND ";
    }
    $query .= $temp;
    $NoneInQuery = False;
  }
}

Open in new window

0
 
TommySzalapskiCommented:
I missed the parentheses. Change line 28 to the following and it should be almost perfect.
$query .= "(".$temp.")";

There may be typos and such, but I think you can get it from there.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.