Gossamer Forum
Home : Products : Gossamer Links : Version 1.x :

SQL features and tricks

Quote Reply
SQL features and tricks
I'm starting a thread here on stuff that I've found in my reading, and sort of half remember having been asked more than once (or at least once) on the Forums.

Code:
Random Numbers/Random Record:
The discussion on random numbers for banners have been discussed several
times, and in several ways. Getting a random record (a la jump.cgi) has
been asked a few times. In the next release of MySQL (current 3.23.xx.beta)
There is a RAND() function that can be used in a Select statement:
> SELECT * FROM table_name ORDER BY RAND() Limit 1
Would return a single random record. Limit 5, would return 5 random records.
.

Count & Distinct
> Select COUNT(DISTINCT Field_name) FROM Table_name
Would return the count of distinct field names.
.
> Select COUNT(*) FROM people WHERE sex='M'
Would return the number of "Male" records in the table 'people'.
.
> Select sex, COUNT(*) FROM people GROUP BY sex
Would return a table with the number of M values and number of F values:
M | nn
F | nn
.
and finally,
.
> Select ID, Name, COUNT(*) AS count FROM Category
-> GROUP BY Name HAVING count > 1 ORDER BY count DESC
Would show which category Names in the Category table were duplicated.
ie: duplicate categories. (note: the -> means "continued" and should not
be typed into your query) You'd then:
> Select ID, Name from Category where Name = 'name_found_above'
to get the ID's of the records that needed merging, or just type the
name into the Links Admin and it would show both Category Records.
.
More later.



http://www.postcards.com
FAQ: http://www.postcards.com/FAQ/LinkSQL/

Quote Reply
Re: SQL features and tricks In reply to
Hello pugdog,

How to update records in database links with 2 different categories to 1 categorie with a sql statement ?

So:

ID Category
1 Games
2 Books

I wanna have after sql statement without losing any other values in fields:

1 Games_and_Books
2 Games_and_Books

Hope you can help me.

Regards Startpoint.

Quote Reply
Re: SQL features and tricks In reply to
If you just want to "move" the links from one category to the other, so there is one Games_and_Books category, then delete the other:

If the two ID's are 1 and 2, and you want to keep category 2, then you want to move the links from category 1 to category 2:

UPDATE Links SET CategoryID=2 WHERE CategoryID=1

This will "move" any _links_ that are in Category 1 to now be in Category 2.

This will work as long as there are no subcategories in Category 1. If there are, you'd need to go in and edit any of the subcategory names in the Category table from:

Games/ and change that to Games_and_Books/

If you only have a few, you can do that from the Links Admin menu. If you have a lot of them, you'd want to do a REPLACE function on them.


http://www.postcards.com
FAQ: http://www.postcards.com/FAQ/LinkSQL/

Quote Reply
Re: SQL features and tricks In reply to
Thanks pugdog.

I have the mysql book here.... i will learn it all very soon i hope:)..


Regards Startpoint.

Quote Reply
Re: SQL features and tricks In reply to
Pugdog,

You dissapoint me ;) - You should know theres a work around to the rand() function so that people with an older version can use it as well - this also works well as a way to weight something. I use this method to get a random thing...

select url,title,description,id*0+RAND() as rand_col from tbl_name order by rand_col

You make the ID all ones or 2 & 3 etc if you want to increase its weighting. Its better to make code that doesn't require the latest software to work. Anyway - I don't have too many SQL tricks to give away... Signing off.

Discuss webhosting at
The Webhost Area Forums!
http://www.webhostarea.com/forums/
Quote Reply
Re: SQL features and tricks In reply to
Yeah... but look at all the work you have to do to do that.

From perl you are better off just passing it

$total = $db->total();
$offset = int rand $total;

Then,

SELECT * FROM Links LIMIT $offset, 1


The way it's done in jump.cgi

Far less resource usage -- look at all the work the SQL engine has to do to generate that sort of query. Not terrible on a database of <1000 records, but what if I have 300,000 Links in my database??

I think that is the purpose of the ability to use RAND() in the SELECT statement for returning a random link. If there is a specific short cut, the amount of resources necessary to do this sort of thing becomes trivial. And, selecting a random link is a very popular sort of thing (perhaps not as the database was originally intended, which is why it's only in version 3.24.23+ <G>)


One thing your select statement does, especially if you pull the data out into a new table, is allow for selecting EVERY link AT LEAST once, and ONLY once, in random order. This would be great for randomizing names in a grade book, for example, so that every student is called on at least once, but no student knows when his/her name will be called. (you generate several such lists, and rotate them.... I had a history teacher 25 years ago who did something very similar, but without the use of computers or databases).




http://www.postcards.com
FAQ: http://www.postcards.com/FAQ/LinkSQL/

Quote Reply
Re: SQL features and tricks In reply to
Yeah - well I don't use that code in a big databases, so calls like that aren't that important to me. On my site, you know that little thing on the site that selects a random guide from my site, displays the summary and link to the full guide. Obviously for something as small as that it makes absolutely no difference :)

The same is for an advertising program which needs to select a random banner, most exchanges won't use more then 150 or so ads at a time, so that sort of code is a good way to use weighting, and to handle the selection of a banner. PHP has a rand function, so I could use the same method as you used as well I guess.

Discuss webhosting at
The Webhost Area Forums!
http://www.webhostarea.com/forums/