SQLSTATE[42S02]: Base table or view not found: 1146 Table doesn't exist

I have faced the very strange Mysql problem. I have the database name "DAVP" and having various tables. All the tables are existing with few of records.

Now I have queried to select some rows from one of table and surprise query showing error that the table "sark_rec" is not existing. It is wondering that the table "sark_rec" is existing!!. 

I spend lot of time in Google but did not found any solution related to this strange problem. 

So lastly I have solved it myself. I have just added database name just before table name with dot. see below solution sql query.


select *from DAVP.sark_rec;

Fore more, continue here...

How to use ON DUPLICATE KEY in insert statement

How to use ON DUPLICATE KEY in insert statement

Problem:
In case of inserting or updating record we use separate logic using if statement or case statement:
e.g.:

if(i==0)
insert here

if(i=1)
update here

we can terminate the above if statement by using On Duplicate Key.
by using this only one statement can be use for insert and update record.

*note: user_nm coulmn must be unique

Example:

INSERT INTO `posts` (`user_nm`, `no_of_update`, `inserted_on`, `update_on`)
VALUES ('a_user', 1, NOW(), NOW())
ON DUPLICATE KEY
UPDATE `no_of_update` = `no_of_update` + 1, `update_on` = NOW()

Fore more, continue here...

Email Blaster

Email Blaster
*Features
- Send unlimited emails.
- Send test email, so you can see your email format and looks.
- Show real time progress in counting sent emails.
- Show progress in percentage(%)
- Send email both format Text and HTML format.
- Alternate Solution of Cron Job(Less feature).
- Alternate Solution of SQL Agent Job(Less feature).
- Solution of Script Timeout.

Fore more, continue here...

Replace in mysql

PROBLEM:

How to Replace in mysql?

SOLUTION:
If you have a table in my sql database which having '('    ')'   or  any other character and you want to replace it then use as:

UPDATE tble_nm  SET taluk = REPLACE(taluk, '(', '')  WHERE taluk LIKE '%)%'

Fore more, continue here...

SQL query for two column from and to with same value

SQL query for two column from and to with same value

Suppose you are developing a software, you have a database table with following structure:
zip_id from_zip1 from_zip2 ship_cost
1         0         100         40
2         101         200         50
3         201         350         100

In the data entry screen you put the Zip code as 315. Zip code 315 coming under zip id 3 and it's shipping cost is 100.
you want when user put zip 315 or any other zip then it's according shop cost automatically show.
So what will the sql query of this?

This will the query of that:

SELECT *FROM ship
WHERE (from_zip2>=315 AND from_zip1<=315)
ORDER BY from_zip2 DESC LIMIT 0, 1

Fore more, continue here...

Difference between primary key and unique key in sql

Difference between primary key and unique key in sql.
======================================================

Primary key                            Unique key
1-can not be null                    1-can be null and multiple null
2-can not be same value        2-can not be same value
3-only be one column name  3-multiple column could be unique

suppose emp_id is primary key and city is the unique key
primary key                                   unique key
-----------------------------------------------------------------------------------
emp_id        emp_nm        sal        city
-----------------------------------------------------------------------------------
0                  AA            18000        (null)      
1                 BB             18000        NEW DELHI
3                CC             18000        NOIDA
2                DD             18000        (null)
4                EE             18000        KANPUR
5                FF             18000        (null)
6                GG             18000        (null)
----------------------------------------------------------------------------------

Fore more, continue here...