Reports based on Database info

[Bman900]

Alright so I have a script that takes in questions and stores them into a database. What I want to do is have like a Most asked question's report. Basicly I want to look trough the databse and if there are two questions that are the same I want to print it in a table with a number next to it identifying how many times it apears.

 

Next I redirect the user based on keywords found in their questions which is also stored in the database. I then want to make a report on how many times each keyword apears in all of the questions in the databse.

 

Where would I even start on this complicated journey?

[forumnz]

It's not complicated. Start here w3schools.com/php

[Ken2k7]

Well start with the first part. Add a DB field to keep track of how many times a question appears. Though I don't know why you would have 2 of the same question.

 

The keywords is a pain. You'll probably have to end up stripping the user's response by words and querying the DB to see how many of them match and what they are. You'll have to decide on an algorithm.

[Bman900]

Well what if I do how many times a keyword has redirected to a page? could that be simpler?

[Bman900]

And for the questions, they are user inputed. So lets say user A asks: How was my day? and user B also asks: How was my day?

 

I want a report saying that How was my day? has been asked twice.

[Ken2k7]

Yeah, store them in the DB under different users.  :-\ Then run a query on the question you want.

[Bman900]

Is there a way to run a query that checks if a table has multipes of the samething and than print those out?

[Ken2k7]

Well you can run a SQL where the WHERE clause has the question you want to search for and if it returns more than one result, then you have multiples.

[Bman900]

Well the thing is I wont be looking for a specific question, am just seeing if there are repeats and how many times it is repeated.

[Bman900]

Ok I got the following code to check for doubles:

$double = mysql_query("SELECT question FROM question GROUP BY question HAVING count(*) > 1")

or die(mysql_error());

 

Now is there a way to then display how many times those records are displayed in the database?

[trq]

$double = mysql_query("SELECT COUNT(id) FROM question WHERE id IN(SELECT id FROM question GROUP BY question HAVING count(id))");

[Bman900]

Ok that looks like it might work but I need just a bit more help.

 

Here is the code I used to display the query I ran:

 

<table width="500" border="2" cellspacing="0" cellpadding="0">
  <tr>
    <th>Question Asked 2 times</th>
  </tr>
<?php
while($row2 = mysql_fetch_assoc( $double ))
{
    echo "<tr>";
    echo "<td>".$row2['question']."</td>";
    echo "</tr>";
} 
?>
  </tr>
</table>

 

Obviously I need to make some changes to that but when I added   

echo "<td>".$row2['id']."</td>";

 

But that didn't work. So how would I display this correctly?

[trq]

You need to use an alias.

 

$double = mysql_query("SELECT COUNT(id) AS cnt FROM question WHERE id IN(SELECT id FROM question GROUP BY question HAVING count(id))");

 

Now echo $row['cnt'].

[Bman900]

I did that and got:

 

Question Number

              10

[trq]

Obviously you still need to select question in your query if you want the question as well.

[Bman900]

Well I am displaying my question, here is the code:

 

$double = mysql_query("SELECT COUNT(id) AS cnt FROM question WHERE id IN(SELECT id FROM question GROUP BY question HAVING count(id))");



?>
<table width="500" border="2" cellspacing="0" cellpadding="0">
  <tr>
    <th>Name</th> 
    <th>Number</th>
  </tr>
<?php
while($row2 = mysql_fetch_assoc( $double ))
{
    echo "<tr>";
    echo "<td>".$row2['question']."</td>";
    echo "<td>".$row2['cnt']."</td>";
    echo "</tr>";
} 
?>
  </tr>
</table>

 

 

Or are you talking about the selecting it in my query?

[Bman900]

Obviously you still need to select question in your query if you want the question as well.

 

I see what you mean now but I can't seem to do it right. So far I have:

 

$double = mysql_query("SELECT question AND COUNT(id) AS cnt FROM question WHERE id IN(SELECT id FROM question GROUP BY question HAVING count(id))");

 

What am i doing wrong?

[xtopolis]

SELECT question AND COUNT(id) AS cnt

 

->SELECT question, COUNT(id) AS cnt

[Bman900]

Ah this is frustrating, it got me a value but it is still not right.

 

I need to combine this:

 

$double = mysql_query("SELECT question FROM question GROUP BY question HAVING count(*) > 1")
or die(mysql_error()); 

 

with:

 

$double = mysql_query("SELECT COUNT(id) AS cnt FROM question WHERE id IN(SELECT id FROM question GROUP BY question HAVING count(id))");

 

Please help me, am ripping my hair out here

[Ken2k7]

HAVING count(id) ... ? something missing there.

[Bman900]

HAVING count(id) ... ? something missing there.

 

There we go!

$double = mysql_query("SELECT question, COUNT(id) AS cnt FROM question WHERE id IN(SELECT id FROM question GROUP BY question HAVING count(id) > 1)");

 

I finally go something that makes sense but I only get one entry and I know there are more doubles and triples in my table.

 

My while statement is:

 

<?php
while($row2 = mysql_fetch_assoc( $double ))
{
    echo "<tr>";
    echo "<td>".$row2['question']."</td>";
    echo "<td>".$row2['cnt']."</td>";
    echo "</tr>";
} 
?>

[Bman900]

Any one?

[fenway]

Why not simply:

 

$double = mysql_query("SELECT question, COUNT(id) AS cnt FROM question GROUP BY question HAVING cnt > 1");