People Connected by Choices

The essential components of TwoWayStreet are its share-opps, the people (business reps) that make them, and the people (patrons) that respond to them. They are connected by choices — the responses of the patron to the share-opps prompt.

But who are these people?

We use Google Sign In both for business admin and patron verification. These unique records are stored in a table “persons.” A table “businesses” and a table “patrons” has records that point to a record in the persons table. We have set things up so that patrons interact with share-opps, and are not exclusive to any one business. There might be a time in the future, when we wish to allow the patron to view their history with multiple businesses. But for now, they simply login per a business storefront.

A business signs in, and can make share opps. The admin sees a list of existing opps along with a results button if there is at least one interaction with a patron.

What connects the patrons to a share-opp is a database linking table “patrons__opps.” While querying the list of each of her share-opps, there is a separate query to look for any respondents, which generates the buttons you see above.

    $sql_count = "SELECT patron FROM patron__opps p_o
    WHERE p_o.pointopp=$id"; 
    
    $checker = $db->query($sql_count); 
    $numPatrons = $checker->num_rows;
    if($numPatrons>0) {
       $results_btn = "<a title='See results from the $numPatrons respondents' class='btn-results' href='question-results.php?oppId=$id&displayAjax=20'>$numPatrons</a>"; 
    } else {
       $results_btn = "<span class='noResults'>None</span>"; 
    }

When clicking the results button, a more specific query is done to obtain the breakdown of the results. Depending on the type of user interaction, a Google Chart template is called to display the results.

The business admin also sees a list of patrons who have answered at least one sharing-opp. Clicking a patron, he sees the share-opps they’ve responded to. Clicking a title, he sees the response. (note: some share-opps offer the option to the user to take anonymously)

But how does the data of responses get into the database?

In an earlier post, we showed how a share-opp is rendered to the patron. This includes JavaScript to validate the response, and prepare it for the server. It sends a data object via Ajax to a server file that is connected to a database. It has the info it needs (patron id, share-opp id, list of responses) to make an entry into the all important linking table (patrons__opps) and then to insert into the relevant tables (selection choices, ratings choices, rankings choices) with the data and connect it to the newly inserted linking table value.

//need oppid , the memberid (from common ) , the timestamp (default)
$sql = "INSERT into patron__opps(pointopp,patron,anon) VALUES($oppid,$patron_id,$anon)"; 
// echo $sql; 
$ins = $db->query($sql); 
//get that insert id ... 
$patron_opp_id = $db->insert_id; 

//need to first look at userAction 

//formulate sql for table ui_selections .. need to know optionType 
if($optionType=="text") {
	$opt_key = "option_text_id"; 
} else {
	$opt_key = "option_img_id"; 
}

if($userAction!="rate" && $userAction!="rank") {
	$sqlsubstr = "INSERT into ui_selections SET patron_opp='$patron_opp_id',$opt_key="; 
	foreach($sel_options as $option_id) {
		$sql = $sqlsubstr.$option_id; 
		//echo "--$sql <br/>"; 
		$ins = $db->query($sql); 
	}
} else if($userAction=="rank") {
	$sqlsubstr = "INSERT into ui_rankings SET patron_opp='$patron_opp_id',$opt_key="; 
	for($i=0;$i<count($rankings);$i++) {
		$pos = $i+1; 
		$sql = $sqlsubstr.$rankings[$i].",position=".$pos;  
		//echo "--$sql <br/>"; 
		$ins = $db->query($sql); 
	}


} else if($userAction=="rate") {
	$sqlsubstr = "INSERT into ui_ratings SET patron_opp='$patron_opp_id',$opt_key=";
	foreach($ratings as $rating) { //choice is key, rating is value
		foreach($rating as $k=>$v) {
		$sql = $sqlsubstr.$k.",rating=".$v; 
		// echo $sql; 
		$ins = $db->query($sql);
		}

	}
}