I have two MySQL tables which I need to compare and then order.
One group of people choose their 5 favourite things which go into table A. Another group choose their 5 favourite things from a list of all the answers given in table A. The results of which go into table B.
So table A and B will have all the same values just with different frequencies.
What I want to be able to do is rank Table A depending on how popular each of the values in table B are.
i.e If person1 chose their favourite 5 things (stored in table A) as cat, dog, sheep, pig, cow and the most popular choices in table B were cat, dog, sheep, pig, cow then person1 would be at the top. If person1 chose all the unpopular values in table B then they would be at the bottom.
I hope this makes sense!!! Any help would be greatly appreciated. Thank you.
/* mysql code */
SELECT tbl_a.chooser_ID , count(*)
FROM `tbl_b` , `tbl_a`
WHERE tbl_a.choice =tbl_b.choice
GROUP BY tbl_a . chooser_ID
ORDER BY `count( * )` DESC
I’m having a bit of trouble setting this up to work (and understanding it!)
There seems to be a problem with the first count (*) but it may be how I have set it up.
The two tables are set up like this -
Table 1 is called group1 and has columns :
id,name,value1,value2,value3,value4,value5
Table 2 is called group2 and has columns :
id,name,value1,value2,value3,value4,value5
So what I need my php script to return is a list of all the names in table 1 sorted in an order that shows how closely the 5 values match the popularity of values chosen in table 2.
I’m guessing the solution above only works with a single value? I need it to take into account all five values. I’m not sure what or if there is statistical method or if it is necessary to come up with a scoring system?
the problem is your table setup - your structure is not very relational db friendly. is it possible to restructure the tables the way i have it or are these set in stone?
/* table setup */
tbl_a
--------------------
choice
chooser_ID
choice_num <-- only if it matters what choice number (1-5) the choice is
tbl_b
--------------------
choice
choice_num <-- only if it matters what choice number (1-5) the choice is
tbl_choosers
--------------------
chooser_ID
chooser_name
it’s not that complicated, so don’t worry. let me explain…
this table layout helps you because you can get the info you want without having to run more complex php. in your original layout, here’s what you would have to do to get the info you want:
query the db
return an array of rows from the db, loop through each row
in each row, push each choice into an associative array
when the loop ends, loop through the associative array
query the db again
return another array from the db, loop through each row
echo out the rank of each chooser
with the new layout here’s what you need to do:
query the db
return an array of rows, loop through each row
echo out the rank of each chooser
basically you only need to query the db once rather than twice. and the array you return from the db is less messy.
Ok I understand the benefits you have written but I am having difficulty understanding how to do it. But without wanting to sound like I want it given to me on a plate -
Does the table layout allow me to have 5 different choices?
What would the query be - MySQL is very basic I’m afraid and I have only ever worked with single tables.
this layout actually allows you to have infinite choices if you want. your number of choices should be limited by the form, not the db table. if the order of the choices is important, include the choice_num field that i mentioned previously.
referring back to my previous post…
/* mysql code */
SELECT tbl_a.chooser_ID , count(*)
FROM `tbl_b` , `tbl_a`
WHERE tbl_a.choice =tbl_b.choice
GROUP BY tbl_a . chooser_ID
ORDER BY `count( * )` DESC
Ok. I’m nearly clear on this one. I still have a few issues though.(sorry)
Is it the case that all the choices go into one column of a database table, they are just deferentiated by chooser id? Rather than chooser, and then 5 separate columns for each of the choices? Then the third table is used to retrieve other data held about the user which is differentiated by chooser id again- i.e name and other stuff if I wanted.
The main issue I have is what is the query returning? Could you break it down for my slightly.
Does it return a list of the choosers in an order of most relevence? I think my confusion also comes from the fact that it is not the choosers I want ordering but the people they are choosing from! Would I be right in thinking that it doesn’t matter which goes into which table I would just have a 4th table with a user Id, name etc for what we have been calling tbl_b in the previous set up example?
I am using CocoaMysql to set up my database by the way - would PhPMyAdmin help?
correct. this helps us avoid having redundant data, it will look something like this:
tbl_choosers
----------------------------------
chooser_ID name
1 joe
2 sam
i’m calling the people who make the tbl_a selections “choosers”. so when a “chooser” makes 5 choices, it goes into tbl_a. the people who make selections that go into tbl_b are not taken into consideration because their identity is irrelevant. only their choices are relevant. those choices go into tbl_b.
the query currently returns chooser_ID and the number of selections in tbl_b that the chooser has also made. those resulting rows are then ordered by the number of selections, from high to low. when you get this established, we’ll make the query slighly more complex to also grab the chooser’s name.
i’m not quite sure what you mean, but there is no need for a 4th table.
i don’t know anything about cocoamysql, but it sounds like a gui mysql interface. i just wanted to make sure you weren’t making it too hard on yourself to modify the tables.
if i’m not being clear with anything, just keep asking.
So I’ve set up the tables exactly as you suggested and the query gives this result:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /Library/WebServer/Documents/compare.php on line 13
&Chooser_ID=
This is my php:
$host = "127.0.0.1";
$user = "root";
$password = "********";
$connection = mysql_connect($host,$user,$password);
$database = "brandgarden";
$db = mysql_select_db($database,$connection)
or die ("connect problems");
$query = "SELECT tbl_a.chooser_ID , count(*)
FROM `tbl_b` , `tbl_a`
WHERE tbl_a.choice =tbl_b.choice
GROUP BY tbl_a . chooser_ID
ORDER BY `count( * )` DESC";
$qr = mysql_query($query,$connection);
$row = mysql_fetch_array($qr);
$rString .= "&Chooser_ID".$i."=".$row['chooser_ID'];
echo $rString."&";
?>
Any suggestions? I’ve tried using MySQL Query browser and it seems to throw a question mark around the first:
, count (*)
I don’t know if this because it Query Browser doesn’t handle multiple tables. Just thought I’d give you as much info as possible!
$host = "127.0.0.1";
$user = "root";
$password = "********";
$connection = mysql_connect($host,$user,$password);
$database = "brandgarden";
$db = mysql_select_db($database,$connection)
or die ("connect problems");
$query = "SELECT tbl_a.chooser_ID , count(*)
FROM tbl_b,tbl_a
WHERE tbl_a.choice=tbl_b.choice
GROUP BY tbl_a.chooser_ID
ORDER BY count(*) DESC";
$qr = mysql_query($query,$connection);
$rString = "";
while( $row = mysql_fetch_array( $qr ) ) {
$rstring .= '&chooser_ID='.$row[chooser_ID];
}
echo $rString;
i’m not sure why you had the variable $i in there. looks like maybe you meant to make a for loop. did you want the output to look like this…?
&chooser_ID1=22&chooser_ID2=92&chooser_ID3=76
i just made up the 22, 92, and 78, but is that what you wanted it to look like? if so, you’ll need a to create the variable $i and increment it by 1 in each loop through. let me know how the code i showed you works.
You’re right the $i was because it had come from a different php script that I had copy and pasted which was to give the result you descirbed. I don’t need the output like that.
The new code gives this error:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /Library/WebServer/Documents/compare.php on line 10
Could it be the way the tables are set up? Or is it the php
