[PHP] You may also like... MySQL Query

[coolant]

I have a SQL table called users_fav_bands. I want to create a way so that it will show users similar bands to what bands page they are.

Here is the table..

+------------------------------+---------------------------+
|	users_fav_bands_user_id   |	users_fav_bands_band   |
|						  2   |					   4   |
|						  3   |					   4   |
|						  3   |					   3   |
|						  1   |					   2   |
|						  3   |					   2   |
|						  2   |					   2   |
+------------------------------+---------------------------+

If I am looking at band 2, I want it to display bands that other people like. So it will show that 2 people (people 2 and 3) enjoy band 4 and one person (3) enjoys band 3. Also, if you could show me how to limit it, so it would only show the top 5 bands that people enjoy.

Thanks :D

[i3iz]

youre confusing...

Limit 5 at the end of your select from query. for the last part

[coolant]

  i3iz said:

youre confusing...

Limit 5 at the end of your select from query. for the last part

It depends on the query. I tried playing around the SUM function and GROUP BY - but could not quite get it.

[da13ro]

Indead, confusing. Ill give it ago from what your telling me.

<?
//Do the appropriate mysql connections
$band = 2;
$result = mysql_query("SELECT COUNT(*) FROM `users_fav_bands` WHERE band = '$band'"); 
$num_rows = mysql_fetch_row($result);
$num_rows = $num_rows[0];
echo $num_rows;
?>

That would echo 2....right? Did you want to list the top bands then?

[coolant]

I want it to display the OTHER bands that people enjoy besides the one you are looking at.

For example - if you and a friend enjoy band 2, you can see that your friend also likes band 3.

[da13ro]

I dont follow sorry and probably dont have the mysql knowledge, GL.

[+theblazingangel MVC]

do you want the 'you may also like list' to be based on the most common other bands listed in other people's lists who also have the same band(s) as the current user, or do you want to limit it to 'friends' lists as you seemed to suggest in your third post?

breaking down the problem:

- get all the bands listed for the current user

- for each band, search for all entries with that band and grab the users id unless its the current users id or youve already got the id

- for each id you grabbed, get all the bands the user likes excluding ones the original user already has in their list, counting the number of timeseach band occured

- print out the top five bands other people had listed

would that be what you want to do? if so then we can attempt to code it!

did you want to limit it to only searching 'friends' lists not just anyones? would you want to limit searches to people who have say two bands the same as the current user and ignore other people with only one band the same?

[coolant]

  theblazingangel said:

the 'you may also like list' to be based on the most common other bands listed in other people's lists who also have the same band(s) as the current user

That's what I want - you can see what I was trying to do here - https://www.neowin.net/forum/index.php?showtopic=493940

[coolant]

bump

[+theblazingangel MVC]

sorry, i forgot about you, im pretty busy

try the following code, you just need to call the function passing in the id of the current user e.g. get_recommendations('2');

would you like me to add to it so only people with say 2 or more same bands as you are used to get recommendations from rather than just 1 or more.

function get_recomendations($user_id)
{
	// get current users five favourite bands (assumes only five bands listed)
	$query = "SELECT users_fav_bands_band FROM users_fav_bands WHERE users_fav_bands_user_id='$user_id' LIMIT 5"
	$result = mysql_query($query);
	if ($result == false)
	{
		echo "error occured";
		return false;
	}

	// extract id's of users five favourite bands from results and store them as a string in query format
	$count = 0;
	while ($row = mysql_fetch_assoc($result))
	{
		extract($row);

		if ($count = 0)
		{
			$band_ids = "users_fav_bands_band=" . $users_fav_bands_band;

		} else {

			$band_ids .= " OR users_fav_bands_band=" . $users_fav_bands_band;
		}

		$count++;
	}

	// get id's of users who have one or more of the same bands
	// - this uses the string created above, and excludes the current user from results
	$query = "SELECT DISTINCT users_fav_bands_user_id FROM users_fav_bands WHERE users_fav_bands_user_id<>'$user_id' AND ($band_ids)"
	$result = mysql_query($query);
	if ($result == false)
	{
		echo "error occured";
		return false;
	}

	// extract id's of returned users from results and store them as a string in query format
	$count = 0;
	while ($row = mysql_fetch_assoc($result))
	{
		extract($row);

		if ($count = 0)
		{
			$other_users_ids = "users_fav_bands_user_id=" . $users_fav_bands_band;

		} else {

			$other_users_ids .= " OR users_fav_bands_user_id=" . $users_fav_bands_band;
		}

		$count++;
	}

	// alter band id string so it can be used to exclude the songs from results
	//  - swaps '=' with '<>' in the string to exclude data
	$temp = explode('=',$band_ids);
	$band_ids = implode('<>',$temp);

	// get id's of songs the other users have which you havent got
	//  - this uses the string of user id's created above limiting results to users who have some of the sam ebands as you
	//  - this also uses the string of band id's created above, this time to exclude songs you already have from the results
	//  - it also creates a temporary table from which results can be retrieved with further queries, rather than returning data
	$query = "CREATE VIEW other_songs AS SELECT users_fav_bands_band FROM users_fav_bands WHERE ($other_users_ids) AND ($band_ids)"
	$result = mysql_query($query);
	if ($result == false)
	{
		echo "error occured";
		return false;
	}

	// get list of bands from temporary table created above
	$query = "SELECT DISTINCT users_fav_bands_band FROM [other_songs]"
	$result = mysql_query($query);
	if ($result == false)
	{
		echo "error occured";
		return false;
	}

	// for each band count the number of times its listed in the temporary table that was created above
	{
		$other_bands = array();
		$count = 1;

		// fetch each unique band id
		while ($row = mysql_fetch_assoc($result))
		{
			extract($row);

			// count how many times it occurs
			$query = "SELECT count(users_fav_bands_band) FROM [other_songs] WHERE users_fav_bands_band IN ($users_fav_bands_band)"
			$result = mysql_query($query);
			if ($result == false)
			{
				echo "error occured";
				return false;

			} else {

				// store the bands id and the number oftimes it occurs in array
				$other_bands[$count][id] = $users_fav_bands_band;
				$other_bands[$count][count] = $result;
			}

			$count++;
		}
	}

	// get recommended bands, starting with the one that occured the most times
	{
		// set max number of recommended entries to get
		$limit = 5;

		// if the number of bands is less than the limit, reduce the limit
		if (count($other_bands) < $limit) { $limit = count($other_bands); }

		// do searches
		for ($i=1; $i<=$limit; $i++)
		{
			$max = 0; // store current largest number of recurrances
			$maxArrID = 0; // store id of array entry for highest recurring band

			// search array for the band that reccurs the most
			//  - this takes into account entries that occur the same number of times and will print them out one at at time starting at the first occuring one!
			for ($j=0; $j<count($other_bands); $j++)
			{
				// if number of occurances for current array entry larger than number stored, set this entry as the largest
				if ($other_bands[$j][count] > $max)
				{
					$max = $other_bands[$j][count]; //store number of largest occurances found
					$maxArrID = $j; //store id of the array entry that had it
				}

				// print recommendation to screen
				echo "band id #" . $j . ": " . $other_bands[$maxArrID][id] . "<br />\n";

				// remove entry from array so the next highest recurrer can be found
				unset($other_bands[$maxArrID][id]);
				unset($other_bands[$maxArrID][count]);
			}
		}
	}

	return true;
}

[coolant]

Hey, thanks for that script - but sadly, it doesn't work. I tried playing with it a bit, but it keeps getting stuck on

$band_ids .= " OR users_fav_bands_band=" . $users_fav_bands_band;

As I get the error

Notice: Undefined variable: band_ids in /Library/WebServer/Documents/test.php on line 31
error occured

Any ideas?

[+theblazingangel MVC]

ah, spotted the bug, in the first WHILE statement, swap

if ($count = 0)

with

if ($count == 0)

ive attached the fixed code in a text file if you need it ;)

get_recommendations_php.txtFetching info...

edit: i noticed i made a spelling mistake in the functions name, ive changed it and re-uploaded the attached text file

[coolant]

I fixed both of the if ($count = 0)'s to reflect that change.

Now I get an error after the SQL command

CREATE VIEW other_songs AS SELECT users_fav_bands_band FROM users_fav_bands WHERE ($other_users_ids) AND ($band_ids)

I'm running MySQL 5.0.19. Any more ideas? :D

[+theblazingangel MVC]

ive setup a test database so i can run the code and check it works. ive solved the problem above but found no results are being printed out so im doing some tests to find the problem. at the moment ive found that the second query is for some reason returning the wrong results and im trying to find out why. i'll post the code when ive fixed it.

[+theblazingangel MVC]

okay, serves me right trying to write so much code without testing it, all together i've found several missing semi-colons, incorrect use of views, two instances of '=' instead of '==', an incorrectly initiated loop, and even an infinate loop.

anyway, heres the fixed code and hopefully it should work fine now :D

get_recommendations_php.txtFetching info...

note, i should have said before that this code requires MySQL v5.0.1 or greater. and btw v5.0.24a is the latest stable release (i noticed you said you have v5.0.19).

[coolant]

If I put get_recommendations(1), I get..

recommended band #1's ID: 4
recommended band #2's ID: 3
recommended band #1's ID: 
recommended band #2's ID:

I'm not sure what the results mean. Thanks for trying :)

[+theblazingangel MVC]

well, if you passed in the user id '1', according to the example table you posted in the first post, it would find bandID '2' to be the only band listed for userID '1'. it would then look for other people with bandID '2' listed and would find userID's '2' and '3'. it would then look at what other band's those users like which would be '3' and '4'. because two people like bandID '4', bandID '4' is the first recommended result. bandID '3' is then the second recommendation.

the numbers printed as recommendations are the ID's of the recommended bands, i can get it to print the names of the bands if i know the exact layout of the table that stores them (+table name)

in the results you posted above, were four lines really printed out, because if so thats another bug i need to fix.

[psuedo]

SELECT userbands.id, bands.id, bands.title FROM userbands, bands LIMIT 5

is there a way u can do something with this? u can select anything in one query using above's method

[+theblazingangel MVC]

@psuedo, that only gets the first five entries in the table. that doesnt solve the problem at all, sorry.