Cohesive Web

In MySql database the auto increment value of a field is a very important feature that we use frequently for our projects. Value of a auto increment field is a auto generated number. This number generates when you insert a row in the table. Most of the cases we make data type of that field as integer and as primary key. In many of my projects I have used this field value as the primary ID and mapped other data stored in other tables.

In PHP a new value of this type of field can be accessed after inserting a record in the table, using the function mysql_insert_id(). But you can many such cases where you need to access this value before inserting the record. Earlier I used a technique to get that value. Execute a simple sql query to get that value. The sql is:
[code lang=”sql”]
SELECT MAX(prodId) FROM ProductTable
[/code]
Where prodId is the field name with auto increment and is the ProductTable table name. Now add 1 with returned value and you will get the new value for that field. However this process may produce incorrect result if you do delete operation upon that table. Let me explain a little more.

Say currently the ProductTable contains 100 records. So the possible next record Id will be 101. Now if you delete record no. 21 or 49 or 65 or 78 you will still have the next record Id as 101. But say if you delete record 100. Logically you should get the next record Id as 100. Your sql query will give the value as 99 and adding 1 with it will produce 100. Seem no problem, right? Wrong, just insert a record and you will find the prodId for the record is 101. How this happened? MySql database stores 1 as starting value when you create the table. Now every time you insert a record the auto increment value get incremented by 1.This way it produces a unique number every time you insert a record in the table. Generally table in MySql database don’t reuse the deleted Id. As a result the above said process will not work in case deletion of record.

Continue reading ‘Auto increment Value of a MySql table in PHP’ »

Here are some tricky Sql statements that can be used in some specific cases. I figured them out in certain cases where I needed to do data manipulation directly on the database instead of writing script to accomplish them. I will describe them in case study style.

Case 1:

In one of my project there is a user table containing fields like userId, UserName, UserLoginID, Password etc. The UserLoginID field is basically the email address. Almost 95% of the email addresses belongs to a specific domain that is the domain of the project. Let’s say testproject.com. The table contains approx 12,000 records. In that project client decided to shift their main tld from .com to .org like testproject.com to testproject.org. Now they asked me to change the all the users’ email from .com to .org. That means user1@testproject.com to user1@testproject.org I needed to do this job within a shortest span of time. So I decided to run a sql command directly in the database to do the modification. A simple UPDATE sql won’t work in that situation as I needed to change some part of a field and also update statement shouldn’t affect those rows which do not contain testproject.com. So I tried with different types of update sql to do the job but finally derived the following one :-

[code lang=”sql”]
UPDATE tblUser SET UserLoginID = REPLACE(UserLoginID, ‘ testproject.org’, ‘ testproject.com’);
[/code]

The beauty of this statement lies in the usage of replace function. This replace function will synchronously select records having ‘testproject.com’ implementing functionality of the Where clause and generate the value to be set for the field UserLoginID as @testproject.org

Continue reading ‘Tricky Sql Statements’ »