MySQL Tips

[shahid_146]

http://www.binarytrends.com/article_details/22/A-Few-MySQL-Tips-You-Might-Not-Know

MySQL is a database server suitable for small and a little bigger sized database applications. It has been the all time favourite since its creation. It works on windows as well as Unix / Linux but when run on Unix / Linux, it can provide multithreading which boosts its performance a lot. MySQL supports standards SQL (ANSI) and can be used with a variety of web technologies pretty efficiently. PHP with MySQL is one of the best combinations used by millions of websites and they are happy with it. And the beauty of both PHP and MySQL is that they are open source meaning FREE!! In this article I will describe a few MySQL functions, some of them I had been desperate for back at some point in my web development journey.

Last Inserted Row Number

Often when working with multiple tables, you run an INSERT query on a table and then you need to find out its ID in order to be able to use it to update another table. I used to use the MAX function to perform this until I found this useful function called mysql_insert_id. This function, when given the link identifier, retrieves the AUTO_INCREMENT ID of the last INSERT operation. Below is an example of how it can be used:

// Insert query for a table with an AUTO_INCREMENT field named order_id
// the values for the rest of the fields product name, quantity, price are being inserted

$ins_qry = “INSERT INTO orders values (‘Umbrella’,’2’,’50’)”;

//Run the query myConn represents the connetion string

$qryresource = mysql_query($ins_qry, $myConn);

// Get the inserted ID

$inserted_id = mysql_insert_id($myConn);

// $inserted_id now contains the id for the last inserted row

NOTE: If the link identifier is not specified, the last link opened by mysql_connect() is assumed.


FULLTEXT Search

When working with searches on text fields in MySQL, the LIKE queries are very commonly used. The situation gets complicated when it comes to using them on large tables. MySQL now provides an alternative, which is easy to use and more efficient. The FULLTEXT can create indexes of CHAR, VARCHAR and TEXT fields and organise them efficiently. It can also return a relevance value based on how well each result matched the words a user is looking for. To CREATE a table and ask MySQL to start indexing a field or set of fields you would write something like this:

CREATE TABLE articles (
-> article_id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
-> title VARCHAR(200),
-> body TEXT,
è FULLTEXT (title,body)

You can also ALTER an existing table to start building an index of some of the columns:

ALTER TABLE articles ADD FULLTEXT art_search (title, body)

You will then need to INSERT or MODIFY a record to get MySQL start indexing it.
With the FULLTEXT indexing turned on you can use the MATCH() function to search for a string against the columns included. By default the search is not case-sensitive but you can change it by using a binary collation. The following SELECT statement will return all indexes in the title and body columns against the word “MySQL database”, along with the relevance score of each result:

SELECT id, body, MATCH (title,body) AGAINST
-> ('MySQL Database') AS score
-> FROM articles WHERE MATCH (title,body) AGAINST
è ('MySQL Database');


The MySQL FULLTEXT implementation regards any sequence of true word characters (letters, digits, and underscores) as a word. That sequence may also contain apostrophes ('), but not more than one in a row.
For more reading on the FULLTEXT features please refer to the MySQL manual at http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

 

[brianmn]

These are very good tips. I have been looking for a mysql autoincrement thing for a long time now. This one seems pretty easy.

But lets say i have a table like this:
ID Product Quantity
1 a 50
2 b 34
3 c 45

Now i delete the product with an id of 2 so the table looks like this:
ID product quantity
1 a 50
3 c 45

Will this variable $inserted_id, in your case with the next row i insert contain, a 4? So that i could then turn around and insert it into the mysql table.

Or is there a better way of doing this. I just want to make it so that each record has an ID that goes up by 1 each time, and that starts at either 0 or 1.

 

[shahid_146]

In this case $inserted_id will return 4 meaning regardless of any record you delete, the next available auto increment number will be picked by this function.

 

[NuPagady]

Nice Article. Thanks for the link.

 

[brianmn]

In this case $inserted_id will return 4 meaning regardless of any record you delete, the next available auto increment number will be picked by this function.

Thanks, i'll be using this for my site now.