[auromed]

I'm working on a little project that I am using to learn PHP and need a bit of help. I have been working with PHP/mysql for about a week, and have been searching most of the day for some help on a piece of it, but haven't had any luck...

The Basics
The function of this code is to take a user id (hard coded in this example) and pull up that user's ranking in 4 subjects. The users rankings are then used to pull any questions from a questions table which have a ranking less than that of the user's ranking in the 4 subjects. That part I've accomplished.

The questions

The next step will be to take the user's ranking, and subtract the users rank in each subject from each of the questions rank in each subject, then adding those together to generate a question score.

The code I have below calculates the value between the the user ranking and the question ranking for subject.

1. I've done it the manual non-looped way, and have tried to write some loops, but haven't had any luck. I tried adding the $x variables to an array, and tried to use another for loop inside the foreach, but am not sure how to create a variable to hold each subject value in order to calculate the separate values for each subject, so that I can add them together. My first thought is to create a temporary array for each of the subtracted values, then another entry for the sum. Should I be looking at that, or is there a better easier way?

for example:

 $score = array('$userRating->subject_1 - $rows[1]', $userRating->subject_2 - $rows[2]... 

2. After being able to calculate the question score my first thought is to try to add it to the $entry array as an additional value for each of the rows (which are arrays themselves, not sure if thats the right terminology). I should then be able to sort the $entry array by those values to select the question with the lowest score (and therefore closest to the users ranking.) Is that a good direction, or should I be looking at some other way?

<pre>

<?php

$mysql = new mysqli("localhost", "root", "pass", "development");
$getUserRating = $mysql->query("SELECT subject_1, subject_2, subject_3, subject_4 FROM user_rating WHERE user_id=6");

$userRating= $getUserRating->fetch_object();

print_r($userRating);
?>
<br><br>
<?php
$getQuestions = $mysql->query("SELECT ques_id, subject_1, subject_2, subject_3, subject_4 FROM questions WHERE subject_1 <= $userRating->subject_1 AND subject_2 <= $userRating->subject_2 AND subject_3 <= $userRating->subject_3 AND subject_4 <= $userRating->subject_4");

$entry = $getQuestions->fetch_all();

foreach($entry as $rows)
	{
	print_r($rows);
	$x1 = $userRating->subject_1 - $rows[1];
	$x2 = $userRating->subject_2 - $rows[2];
	$x3 = $userRating->subject_3 - $rows[3];
	$x4 = $userRating->subject_4 - $rows[4];
	$y = $x1+$x2+$x3+$x4;
	echo($y . "</br>");
}

?>
</pre>

Output from current code

stdClass Object
(
[subject_1] => 1
[subject_2] => 2
[subject_3] => 3
[subject_4] => 4
)



Array
(
[0] => 1
[1] => 1
[2] => 1
[3] => 1
[4] => 1
)
6
Array
(
[0] => 5
[1] => 1
[2] => 2
[3] => 3
[4] => 4
)
0
Array
(
[0] => 24
[1] => 1
[2] => 2
[3] => 2
[4] => 2
)
3
Array
(
[0] => 40
[1] => 1
[2] => 2
[3] => 1
[4] => 4
)
2



The DB Schema

questions

Field Type Null
ques_id int(11) No
ques_text text
subject_1 int(11)
subject_2 int(11)
subject_3 int(11)
subject_4 int(11)


user_rating

Field Type Null
user_id int(11) No
subject_1 float
subject_2 float
subject_3 float
subject_4 float


users

Field Type Null
user_id int(11) No
user_name varchar(40)

This post has been edited by auromed: 16 August 2010 - 02:24 PM

[ellisgl]

I'm lazy, can you run the following in mysql and give us the output:

SHOW CREATE TABLE questions;
SHOW CREATE TABLE user_rating;
SHOW CREATE TABLE users;

[auromed]

ellisgl, on 18 August 2010 - 02:47 PM, said:

I'm lazy, can you run the following in mysql and give us the output:

SHOW CREATE TABLE questions;
SHOW CREATE TABLE user_rating;
SHOW CREATE TABLE users;

The output is below...

| questions | CREATE TABLE `questions` (
`ques_id` int(11) NOT NULL,
`ques_text` text NOT NULL,
`subject_1` int(11) NOT NULL,
`subject_2` int(11) NOT NULL,
`subject_3` int(11) NOT NULL,
`subject_4` int(11) NOT NULL,
PRIMARY KEY (`ques_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

| user_rating | CREATE TABLE `user_rating` (
`user_id` int(11) NOT NULL,
`subject_1` float NOT NULL,
`subject_2` float NOT NULL,
`subject_3` float NOT NULL,
`subject_4` float NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1


| users | CREATE TABLE `users` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`user_name` varchar(40) NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 |

I read back through the original post, but am not totally sure I explained what I am trying to do. I'm not always great at getting the things rattling around in my head out in a coherent manner.

I mostly just want to be able to sort the questions table, to get the question which will end up closest to the users ranking, without going over. (Sounds kinda like the Showcase Showdown on the Price is Right.)

[ellisgl]

auromed, on 18 August 2010 - 07:19 PM, said:

The output is below...

| questions | CREATE TABLE `questions` (
`ques_id` int(11) NOT NULL,
`ques_text` text NOT NULL,
`subject_1` int(11) NOT NULL,
`subject_2` int(11) NOT NULL,
`subject_3` int(11) NOT NULL,
`subject_4` int(11) NOT NULL,
PRIMARY KEY (`ques_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

| user_rating | CREATE TABLE `user_rating` (
`user_id` int(11) NOT NULL,
`subject_1` float NOT NULL,
`subject_2` float NOT NULL,
`subject_3` float NOT NULL,
`subject_4` float NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1


| users | CREATE TABLE `users` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`user_name` varchar(40) NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 |

I read back through the original post, but am not totally sure I explained what I am trying to do. I'm not always great at getting the things rattling around in my head out in a coherent manner.

I mostly just want to be able to sort the questions table, to get the question which will end up closest to the users ranking, without going over. (Sounds kinda like the Showcase Showdown on the Price is Right.)

Yeah - I'm like that in my thoughts too. So basically you want to figure out a skill level for a user and only give the user questions that are that skill level or less?

[auromed]

I think I figured out a dirty work around to my problem. I know it's probably not the cleanest code, but it works at the moment.

I added an array_push to my loop that calculates the question's value relative to the user's value. This adds the per user value to the questions array, which I can then sort on, to find the question closest to the user's rating.

pre>

<?php

$mysql = new mysqli("localhost", "root", "pass", "development");
$getUserRating = $mysql->query("SELECT subject_1, subject_2, subject_3, subject_4 FROM user_rating WHERE user_id=6");

$userRating= $getUserRating->fetch_object();

print_r($userRating);
?>
<br><br>
<?php
$getQuestions = $mysql->query("SELECT ques_id, subject_1, subject_2, subject_3, subject_4 FROM questions WHERE subject_1 <= $userRating->subject_1 AND subject_2 <= $userRating->subject_2 AND subject_3 <= $userRating->subject_3 AND subject_4 <= $userRating->subject_4");

$entry = $getQuestions->fetch_all();

foreach($entry as $rows)
	{
	print_r($rows);
	$x1 = $userRating->subject_1 - $rows[1];
	$x2 = $userRating->subject_2 - $rows[2];
	$x3 = $userRating->subject_3 - $rows[3];
	$x4 = $userRating->subject_4 - $rows[4];
	$y = $x1+$x2+$x3+$x4;
	array_push ($rows, $y);
	echo($y . "</br></br>");
	echo("Array with combined value </br>");
	print_r($rows);
}
?>
</pre>

Now, I just need to write something to sort the questions from this last code, and then return the question ID on the top. I can probably go back and clean up the code around $x1 = $userRating->subject_1 - $rows[1]; to use a loop, by working with some pointers but I need to read that section of the book again.

Anyway... Feel free to give pointers or more efficient ways to achieve the same thing.

[auromed]

Chalk another one up to explaining it to someone else makes you realize you were doing things wrong the whole time.

Instead of trying to dump the whole DB query to an array, I realized that was wasteful of memory and should just use a running count to do the same thing. In case anyone who was reading is interested...

<pre>

<?php
// Assign some variables
$quesVarBest = 16;
$quesVarBestId = array();


$mysql = new mysqli("localhost", "root", "pass", "development");

// Get User Rating
$getUserRating = $mysql->query("SELECT subject_1, subject_2, subject_3, subject_4 FROM user_rating WHERE user_id=8");

$userRating= $getUserRating->fetch_object();


print_r($userRating);
echo("-- Start Question Calculation Section -- </br></br>");

$getQuestions = $mysql->query("SELECT ques_id, subject_1, subject_2, subject_3, subject_4 FROM questions WHERE subject_1 <= $userRating->subject_1 AND subject_2 <= $userRating->subject_2 AND subject_3 <= $userRating->subject_3 AND subject_4 <= $userRating->subject_4");

while($rows =$getQuestions->fetch_assoc())
	{

// Calcluate variance score for each question

	$questionVariance = ($userRating->subject_1 - $rows["subject_1"]) + ($userRating->subject_2 - $rows["subject_2"]) + ($userRating->subject_3 - $rows["subject_3"]) + ($userRating->subject_4 - $rows["subject_4"]);
	$rows["combined"] = $questionVariance;
	
// Save question(s) with best variance to quesVarBest array
	echo ($questionVariance . "<-- Variance</br>");
	echo ($quesVarBest . "<-- Best Variance</br>");
		if ($questionVariance == $quesVarBest)
		{
		$quesVarBestId[] = $rows ["ques_id"];
		echo ("equal variance </br>");
	    }
	
	if ($questionVariance < $quesVarBest)
		{
		unset ($quesVarBestId);
		$quesVarBest = $questionVariance;
		echo ("better variance </br>");
		$quesVarBestId[] = $rows ["ques_id"];
		}

	echo("</br>Array with combined value </br>");
	print_r($rows);

	}
	//check for more than one entry in the array
		print_r($quesVarBestId);
		if (count($quesVarBestId) == 1)
			{
			echo("</br></br>Only One Value - ");
			print_r($quesVarBestId[0]);
			}
		if (count($quesVarBestId) > 1)
			{
			echo("</br></br>More Than One Value - ");
			$rand = array_rand($quesVarBestId);
			print_r($quesVarBestId[$rand]);
			}
			
			

?>
</pre>