Quick bit of help with a sql query

[matthew1471]

Hi all,

I need some help with a mysql query...

i have a db full of records like:

id - searchterm - location - date
1 - ipod - us - 24/11/2007
2 - iphone - us - 24/11/2007
3 - - us - 24/11/2007


The id column is an auto-increment, so increases everytime a search is completed and logged. However, occasionally some users don't enter a search term, so I've ended up with an incorrect number of searches compared to search numbers (as in example 3 above)

I've been doing my own head in trying to work out how to reset the auto-increment after i've removed all the blank searches.

I hope i've explained it well enough... can someone help?

 

[dsiomtw]

If you just need to know how many searches just do a count(*) query on the table where searchterm is not null. Easy.

 

[DaveEMG]

Yeah, I think I agree with dsiomtw...

SELECT COUNT(id) AS SearchCount FROM 'SearchTable' WHERE Searchterm IS NOT NULL

Should do the trick.

There's no way to reset an auto-increment. The only choice is to basically copy the contents of one database to the other using a query to limit the results to only those that have a searchterm.

 

[elucidex]

Don't know if this will help you much, but you should check to see if the search query is blank before inserting it into the database. Should be a simple conditional like if ($searchQuery == "")

 

[matthew1471]

great stuff cheers for your help.

i'll definitely stop blank strings being added.

 

[illusion]

ALTER TABLE tablename AUTO_INCREMENT = XX

Just use that. Except change the XX to be the next value that you want. So if you have 10 values and you delete the 3rd value and the 7th value and reorder them then you would use the following

ALTER TABLE tablename AUTO_INCREMENT = 9

And the next addition would start at 9 instead of 11. Enjoy