Hello, I have a webpage with an HTML form that sends a keyword that is searched to an SQL database that I have. I have a column named Search_attempts in the database that I am trying to have increment if the same keyword is entered in the HTML form. An example would be if the word Dog was entered 2 times, Instead of there being the word dog in the keyword column 2 times, I would want it in there 1 time, and have the Search_attempts column have the number 2.
Do you mean you want to remove duplicates in the search text or do you want to save a counter when a value is searched for?
If you want to remove dups in the input text, that is fairly easy. Saving a counter for every search input can get complicated. Is all of your searches just one word? If so, you can keep an array of the searches made and keep a counter for each. But, I am curious why you would need to do that.
If all your searches are just one word each, you can create a “searches” table with “word” and “count” and just increment them as needed. Perhaps you can explain a little more on what you are trying to do.
Ya, I can try and explain some more. So the PHP script has an HTML form that sends a word entered by a user over to an SQL database that I have created. Inside that database there is a table with a number column that is the primary key which acts as which number keyword that is. the next column is Keyword. that is the word that the user entered. The final column in the table is named Search_attempts. That is the column that I am trying to get to work. I want it to show the number of times that Keyword was entered. Say the word Dog was entered 3 times, and Cat entered 2 times. I would want the search_attempts column to show 3 and 2 in the Dog and Cat row.
Okay, first, you should TRIM() the inputs as users might add extra spaces. Also, in your example, you should also check to make sure only one word is entered and no special characters so your database can not be corrupted.
Next, you would just have to run an UPDATE query based on the word entered. Therefore, you can use a query loosely something like this:
UPDATE table-name
SET Search_attempts = Search_attempts + 1
WHERE Keyword = $input_keyword
Should work… Good luck
$sql1 = “INSERT into knktable (Keyword) VALUES (?)”;
That is the part of my php scripts that sends it to the SQL database. so would the WHERE statement have $sql1 for the $input_keyword?
You can use ON DUPLICATE KEY UPDATE plus some extra indexes on your table.
For this example, I’ve created a table as follows:
CREATE TABLE search_history (
id INT AUTO_INCREMENT PRIMARY KEY,
keyword VARCHAR(255) UNIQUE,
num_of_searches INT DEFAULT 1
);
Note the UNIQUE index on the keyword column.
Now you can run the following:
INSERT INTO search_history(keyword)
VALUES ("dog")
ON DUPLICATE KEY UPDATE num_of_searches = num_of_searches + 1;
The first time you run this query, your table will look like this:
+----+---------+-----------------+
| id | keyword | num_of_searches |
+----+---------+-----------------+
| 1 | dog | 1 |
+----+---------+-----------------+
Now if you run your insert query again, your table will look like this:
+----+---------+-----------------+
| id | keyword | num_of_searches |
+----+---------+-----------------+
| 1 | dog | 2 |
+----+---------+-----------------+
I just tried that and I understand how that should work, but it is not working with my table. Is there any way that you would be able to meet for a screen share? it should not take more than say 15 or 20 mins I would say.
I’m on and off my PC. What does your table look like? Run the query describe Search_attempts and show us that along with the query you’re using to insert.
I ran that code that you sent me which is good for only using the SQL database. it did increment which was cool. The problem was that when I used the HTML form to send the same word again I was receiving an error for the same keyword entry. This is because I changed the Keyword column to unique I believe.
What error are you seeing?
You need to use the INSERT … ON DUPLICATE KEY UPDATE … query in your php code.