Looking for any handy SQL queries

[Whatcha]

Laggin behind in my sql knowledge at present any one know any good hints & tips sites. Or very good in depth tutorials

[weirfire]

Try www.php.net , http://www.w3schools.com/sql/default.asp , http://www.mysql.com/

Thats all my favourites for sql help.

[Whatcha]

Yer got the links, good for the basic stuff but sorry to say find mysql.com manual one of the most mind boggling manuals ever.

would like to try and find some with examples to semi complex queries i.e. insert into mailing list * from (select unique email from users) etc

reading examples gives me ideas on streamling and automation

[weirfire]

Well I personally built my own database connection functions which I use all the time.

They go like this;

// Function to open a database connection
function OpenDB(){
$dbh=mysql_connect ("localhost", "$username", "$password")
or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("$db");

return $dbh;
}

// Function to close a database connection
function CloseDB($dbh){
mysql_close($dbh);
}

// Then I set an includes to the file with the function list as follows;
include 'includes/functions.php';

// Then I call my OpenDB function
$dbh = OpenDB();

// Then I run my mysql query and extract the data
$query = "SELECT * FROM table WHERE id >5";
$result = mysql_query($query);
while($row = mysql_fetch_array($result)){

$fieldname = $row['fieldname'];
$fieldname2 = $row['fieldname2'];

echo $fieldname . ' - ' . $fieldname2 . ' <br> ';

}

// Then I close the database
CloseDB($dbh);

[weirfire]

One fantastic method for searching for keyterms I've found is

SELECT * FROM table WHERE fieldname LIKE '%keyword%'

This returns all the results from the table where the value in the field has the keyword somewhere in the text.

[MadKad]

I am not that great on sql my self but that tip is a good tip I would probably give it a go if I ever need to, thank you

[weirfire]

SQL isn't that difficult to learn. Once you get the whole idea of sets and the correct terminology its fairly straight forward.

I did advanced databases at university using Oracle. Now that was tough!! I think I had 1 query which recurred from about 8 seperate queries and was about 250 lines long lol.

[MadKad]

LOL weirfire, I hate database's I am getting there I think but Whatcha will have to say if I am or not here does all the db and php stuff and is really fast and good

[weirfire]

Yeah, I might have a few jobs for him soon. I reckon there's about 6 big jobs starting shortly which is a bit too much to take on at one time. My main helper is exam (David) though as he's a great reliable PHP programmer.

[Poster]

One commonly used sql query is finding the nth largest record from a table. For example, you may require 5th largest number from an id column.

In that case, in Oracle you would have to solve it by using a subquery. In SQL Server, the following query would work.

select top 1 empno from employee where empno not in (select top 4 empno from employee order by empno) order by empno

I think the query is the same in My SQL also. Please let me know.