0
mysql_query("SELECT DISTINCT lo_category FROM questions where question_text = '" . mysql_real_escape_string($_GET["choice"]) . "'");

So from the query above i will get the lo_categories that match with the choice.How do i get the number of categories,eg 

Category Cognitive : 3 matches
Category Psychomotor : 1 matches
Category Perception : 0 matches

($_GET["choice"]) is from the html checkbox with multiple choices. And this is my question table.

question_id  question_text               keyword  lo_category lo_domain
      1     Define the word paraphrase    Define  Knowledge   Cognitive 
      2     describe the meaning of NLP  describe Knowledge   Cognitive 
      3     describe the meaning of NLP  describe Perception  Psychomotor 
      4     describe the meaning of NLP  describe Receiving   Affective 
      5     Define this                    Define Knowledge   Cognitive

Updated with this ,there's error.Please help i'm so lost!!!

$yes=mysql_query("select lo_category, count(*) from questions where question_text='" . mysql_real_escape_string($_GET["choice"]) . "' group by lo_category order by lo_category");

while ($row = mysql_fetch_assoc($yes)) {
    echo '<b>Category :</b> ' . $row['lo_category'] . ',' . $row['count(*)'] . '<br />'; 
}

Please help me i'm a beginner in this..Thanks in advance!!!

Abby
  • 61
  • 1
  • 1
  • 8
  • May I suggest you get the value of $_GET['choice']; and assign it to it's own variable. Then sanitize it and make sure it is clean! You are asking for an SQL injection attack by taking direct URL parameter entries and inserting them into your SQL query. Someone can wreak total havock with your database like that. As it stands, your data is hugely at risk. – SimonDowdles Apr 08 '11 at 07:36
  • How to do that ? Please help me – Abby Apr 08 '11 at 07:40
  • Read up on sanitizing here: http://stackoverflow.com/questions/60174/best-way-to-stop-sql-injection-in-php – SimonDowdles Apr 08 '11 at 07:45

3 Answers3

1
select lo_category, count(lo_category) as countOflo_category group by lo_category
Shakti Singh
  • 84,385
  • 21
  • 134
  • 153
Andrew
  • 5,215
  • 1
  • 23
  • 42
  • formatted your answer feel free to rollback anything you don't want – Shakti Singh Apr 08 '11 at 07:20
  • So,by echo this query i will get the result i wan ? – Abby Apr 08 '11 at 07:21
  • help!! i got this error Warning: mysql_real_escape_string() expects parameter 1 to be string, array given in C:\xampp\htdocs\next.php on line 25 which is the query line – Abby Apr 08 '11 at 07:29
  • Yes,it worked.But i need them to match with the choice then only do the grouping to count categories – Abby Apr 08 '11 at 07:35
  • Try using COUNT(DISTINCT(lo_category)) AS countOflo_category – SimonDowdles Apr 08 '11 at 07:42
  • Then you have countOflo_category as a returned value / variable which should count only distinct categories. Is that what you need? – SimonDowdles Apr 08 '11 at 07:42
  • i have to count how many falls into same category so i cannot use distinct .I think i have problem with the GET[CHOICE] which im discussing below..You have any ideas how to fix it ? – Abby Apr 08 '11 at 07:53
0

Normally this would be something like:

select lo_category, count(*) from questions where question_text=<choice> group by lo_category order by lo_category
BugFinder
  • 17,474
  • 4
  • 36
  • 51
  • Hi,i got this error 'Warning: mysql_real_escape_string() expects parameter 1 to be string, array given in C:\xampp\htdocs\next.php on line 25' at this line' $yes=mysql_query("select lo_category, count(*) from questions where question_text='" . mysql_real_escape_string($_GET["choice"]) . "' group by lo_category order by lo_category");' – Abby Apr 08 '11 at 07:29
  • What is the value of $_GET["choice"] ? – BugFinder Apr 08 '11 at 07:30
  • question_text that is selected from the html checkbox `echo ' ' . $row["question_text"] . '
    ';`     – Abby Apr 08 '11 at 07:33
  • it's String selected from checkbox.Multiple selections. – Abby Apr 08 '11 at 07:34
  • No from the code above, the value is "2" not the question. As well ast the choice is an array, not a string. What you meant was name="choice" value=$row["question_text"] – BugFinder Apr 08 '11 at 07:43
  • so what should i put in the value if i wan the choice to be the question_text so that i can run the group query ? – Abby Apr 08 '11 at 07:49
  • Exactly as I showed above. where I said name="choice" value=$row["question_text"] – BugFinder Apr 08 '11 at 07:57
  • ok,` echo ' ' . $row["question_text"] . '
    ';` still giving me the error.     – Abby Apr 08 '11 at 08:03
  • here's my form `echo "
    "; while($row = mysql_fetch_assoc($qsq)) { echo ' ' . $row["question_text"] . '
    '; } echo"
    "; echo "
    ";`     – Abby Apr 08 '11 at 08:06
  • This isnt in the same form as you're trying to display the results is it? eg, it hasnt actually had the form data back yet? – BugFinder Apr 08 '11 at 08:09
  • Yeah.This is the form that gives the `GET[CHOICE]` While this is the one to display the group result `$yes=mysql_query("select lo_category, count(*) from questions where question_text='" . mysql_real_escape_string($_GET["choice"]) . "' group by lo_category order by lo_category"); while ($row = mysql_fetch_assoc($yes)) { echo 'Category : ' . $row['lo_category'] . ',' . $row['count(*)'] . '
    '; } `     – Abby Apr 08 '11 at 08:11
  • Please clarify, its the same PHP page? – BugFinder Apr 08 '11 at 08:13
  • Different page.After select ,click button to go to the next.php(another page) and also is the button that submit the CHOICE – Abby Apr 08 '11 at 08:23
  • Can you do me a favor and at the top of your next.php code, can you put "print_r($GET);" for me, and post the result – BugFinder Apr 08 '11 at 08:26
  • I think the problem is the choices is in array so i can't use it in the query `"select lo_category, count(*) from questions where question_text='" . mysql_real_escape_string($_GET["choice"]) . "' group by lo_category order by lo_category");` because it is multiple choice. Do you know how to fix it ? – Abby Apr 08 '11 at 08:27
  • Currently its NOT multi choice. So, can you post me the output as requested. To be multi choice we can fix later. – BugFinder Apr 08 '11 at 08:27
  • Array ( [choice] => Array ( [Define the word paraphrase] => 2 [describe the meaning of NLP] => 2 ) ) – Abby Apr 08 '11 at 08:37
  • Then thats why, your form is still " ' . $row["question_text"] . '
    ';" not the code we changed, each value is 2.. this is why I know     – BugFinder Apr 08 '11 at 08:40
  • The form I corrected would have given you the get choice with a single text entry of the last question selected – BugFinder Apr 08 '11 at 08:40
  • But i changed it to this already ` echo ' ' . $row["question_text"] . '
    ';` Isit stuck or something with the old code ? i save it already ...     – Abby Apr 08 '11 at 08:43
  • you sure you uploaded the file? or hit refresh in your browser not just "back"? – BugFinder Apr 08 '11 at 08:44
  • Hey i got it .THANK YOU VERY MUCH =D – Abby Apr 08 '11 at 08:49
  • Hi,now i have problem with the multiple choices.The query only manage to give me result of 1 choice but i have actually selected a few. – Abby Apr 08 '11 at 10:39
  • Always get a working thing before breaking it with something new. So, to make your query work with an array, you need to parse through your array, to test for question = choice1 or choice 2 or choice 3 or choice 4.. to choice n.. You should also rename your variable to "choice[]" so that php sees the results as additiona not overwriting the original value – BugFinder Apr 08 '11 at 10:50
0

$result =mysql_query("SELECT DISTINCT lo_category FROM questions where question_text = '" . mysql_real_escape_string($_GET["choice"]) . "'");

mysql_num_rows($result)this will return the noumber of row your reslut set contains

nanda kumar
  • 31
  • 3
  • Or could simply add the COUNT() function before the DISTINCT function and avoid an additional mysql_num_rows() call. – SimonDowdles Apr 08 '11 at 07:47