[creativedesire]

Hello everyone.

I am making a table in a database where I can see what people are searching on my website. It has two columns: "keyword" and "searches". I want it so if a keyword has already been searched the number in the "searches" column goes up by 1. But so far all it is doing is repeating the keyword again with "1" in the "searches" column. (Please say if you don't understand)

Can any of you please help me. Here is the piece of code I am using:

$keyword = $_GET['keyword'];
$keywordexists = mysql_query("SELECT keyword FROM keywordsearch")or die(mysql_error()); 
$keywordrow = mysql_fetch_array($keywordexists);
if ($keywordrow == $keyword) {
	$searches = mysql_query("SELECT searches FROM keywordsearch WHERE keyword = '$keyword'")or die(mysql_error()); 
	$searches = mysql_result($searches,"searches");
	$searches1 = $searches+1;
	$mysqlsearches = "UPDATE keywordsearch SET searches='$searches1' WHERE keyword='$keyword'";
	$add_keyword = mysql_query($mysqlsearches) or die(mysql_error());
} else {
	$insert = "INSERT INTO keywordsearch (keyword, searches) VALUES ('$keyword', 1)";
	$add_keyword = mysql_query($insert) or die(mysql_error());
}

Any help would be much appreciated, thank you for your time. Merry Christmas.

[creativedesire]

Don't worry people I got the answer from another site. If you want the answer here it is:

Quote

This line:

if ($keywordrow == $keyword) {

should never evaluate to "true". "$keywordrow" is an array from the fetch. Even if it's only a single element in that array, it's still an array.

The following might be more efficient, as the number of hits to the database is reduced:

$keywordexists = mysql_query(
"SELECT searches FROM keywordsearch " .
"WHERE keywordsearch = '$keyword' ") or die etc.

$searches = 0;
if ($row = mysql_fetch_array($keywordexists)) {
$searches = $row[0];
}

OK, at this point, if the keyword is not in the database, $searches will == 0. And that's all we care about at this point, because if that's 0, we need to INSERT, otherwise UPDATE. And it's quick because we don't need to count anything, and we don't care if there's more than one matching record. (there shouldn't be of course, but if you didn't set a unique constraint, there might be).

Now we can branch based on that $searches

if ($searches == 0) {
// you know the code to insert a new keyword row
} else {
$result = mysql_query(
"UPDATE keywordsearch SET searches = searches + 1 ".
"WHERE keyword = '$keyword' ") or die etc.
}

What this does is insert the new keyword if searches == 0, and you already have good code that ought to work for that. But if the keyword is already there, this Update query will simply update the existing value to itself plus one.

This has a number of good points, including not needing to be transaction bound. Your code had the problem where if 1000 people hit your database all at once, there was a serious chance that 50 people would calculate the "search + 1" to be 51, another 100 people would calculate it to 52, and another 500 would calculate it to be "53", and the resulting value would be whomever hit the database last.

It's undeterministic what the final value would be, but it would likely be less than 1000.

In the query I've presented, if a 1000 people hit the database at once, a 1000 people are going increment the value to 1 plus whatever it was before, so it won't matter what order they hit it in. When they're all finished, it's going to 1000 greater than when they all started.

It's undeterministic which ones will hit the database in what order, but it won't matter. The total is what you want, and the total will be correct.

Of course, you STILL have a problem if a 1000 people hit the database at once, and the keyword didn't exist before -- there's a good chance that some fraction of that 1000 will attempt to insert a brand new keyword, so you'll end up with 25, 50, maybe 500 new rows for that keyword, depending on processor dynamics. Then, those 500 or so rows will be all be incremented for the ones who manage to hit the database after the rows have already been inserted.

The resolution to this problem is left as an exercise for the reader!