[RESOLVED] help with search term

rutin

hi there,

i am having some sort of trouble that i cant solve.
my sql table structure is this :


CREATE TABLE `search_terms` (           

                `term` varchar(255) default NULL,     

                `count` int(10) unsigned default '0'  

              ) ENGINE=InnoDB DEFAULT CHARSET=latin1

Now my HTML and PHP code :




<font face="Verdana" size="1">
<table cellpadding=3 cellspacing=0 border=0>
<tr>
<th>Searched</th>
<th>Counts</th>
<tr>
</table>

<?php
$q = mysql_query("SELECT term,count, COUNT(count) AS 'total' FROM search_terms   GROUP BY term ORDER BY count DESC LIMIT 10") or die(mysql_error());
$i=1;

while ($data = mysql_fetch_row($q)) {
?>
<table cellpadding=3 cellspacing=0 border=0>
<tr>
<td width="10"><?=$i?>.</td>
<td width="100"><?=$data[0]?></a></td>
<td width="10"><?=$data[1]?></td>
</tr>
</table>

<?php
$i++;
}
?>
 </font>

The problem is my output,

Number 1.
In my firs row i have Cunt which is 100

Than from number 2 - 10
In my second row i have Search Term, and Cunt
( eg. 2. Cake - 90 )

How can i skip this count in first row output only search terms and counts for that row in mysql tabel?

Sorry for my bad english

Thank you,

justsomeone

Sorry, but I can't understand your question.

Can you please show a sample of the output you are currently getting and a sample of the output which you would prefer?

Thanks.

rutin

This is real output.


<table cellSpacing="0" cellPadding="3" border="0" id="table83" width="150">
	<tr>
		<td>
		Number.</td>
		<td>
		Searched</td>
		<td>
		Count</td>
	</tr>
</table>

<table cellSpacing="0" cellPadding="3" border="0" id="table82" width="150">
	<tr>
		<td>
		1.</td>
		<td>
		</td>
		<td>
		644</td>
	</tr>
</table>
<table cellSpacing="0" cellPadding="3" border="0" id="table82" width="150">
	<tr>
		<td>
		2.</td>
		<td>
		Cookies
		</td>
		<td>
		644</td>
	</tr>
</table>

As you can see from outout, it takes count and out it into first row?
Thank you

justsomeone

Without really knowing what's in your table, it's hard to diagnose what the root problem is. It looks like there may be datat in your table which you don't expect to be there. There are a couple of approaches here:

Longest but best.
Use a mysql query tool to examine the data in your table. Is there data there with a blank value for "term"? Should there be? Can you delete this data from the table, and stop any more data like this from being entered in future?
Not as long, not as good.
Change your SQL query in this script to filter out the data you don't want to see. This may need a little playing around to get it exactly right, but something like the following should do it.
```
$q = mysql_query("SELECT term,count, COUNT(count) AS 'total' FROM search_terms WHERE (term not = "" AND term not NULL)  GROUP BY term ORDER BY count DESC LIMIT 10") or die(mysql_error());
```

Quickest and worst.
If the unwanted data is always the first row, just skip the first row when outputting:

$i=1;

while ($data = mysql_fetch_row($q)) {
    if ($i > 1){
?>
<table cellpadding=3 cellspacing=0 border=0>
<tr>
<td width="10"><?=$i?>.</td>
<td width="100"><?=$data[0]?></a></td>
<td width="10"><?=$data[1]?></td>
</tr>
</table>

<?php
    }
$i++;
}

rutin

this is my code that inserts the data into table.
i think this is problem


<?php

   $pagenum = 0;

   $searchtype = $_REQUEST['searchtype'];
   $searchingred = $_REQUEST['searchingred'];
   if ($_REQUEST['pagenum']) {$pagenum = $_REQUEST['pagenum'];}

   $sql = "select * from search_terms where term = '$searchingred'";

   $result = mysql_query($sql ,$db);

   if (mysql_num_rows($result) > 0) {

  $row = mysql_fetch_row($result);
  $count = $row[1];
  $newcount = $count + 1;
  $sql = "update search_terms set count = $newcount where term = '$searchingred'";

   } else {

  $sql = "insert into search_terms (term, count) values ('$searchingred', 1)";

   }

   $result = mysql_query($sql ,$db);

?>

Thank you for helping me with this

justsomeone

Your code seems to be instering or updating the correct record if the form data is valid, but I notice that your code doesn't do any input validation. What happens if the user doesn't key in a search term in this form?

Have you actually checked your data to find out if there are actually records in there with a blank "term"?

rutin

Thank you very much for help with this.
I am very sure now, the problem was witht the search form.
There was no form validation.

It must have been that users were just clicking search
That is the reason i have that empty row without text.

Now i added some input validation to a form, i should be ok now.

Thanks,

justsomeone

You're welcome 🙂

The input validation should stop any further blank rows being inserted. If you already have some blank rows, which you don't want, you will need to delete them, or else they will continue to show up in the searches.