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...

Scrolling paging in PHP

Problem:

Database paging without paging page number.  This is same as Facebook and Tumblr like paging.

Solution:

By using Ajax you can achieve that like scroll paging.

Example:

Use below code. Assuming 1 to 10th records already showing.

index.php
<script type="text/javascript" src="jquery-1.6.js"></script>
<div id="mydiv">
<p>Database paging </p><p>without paging</p><p>page number</p><p>This is same as Facebook</p><p>and Tumblr.</p><p>like paging.</p><p>Assuming 1 to 10th records already showing.</p> <p>Next 11th to end of records will show when scroll page.</p>

<p>1 record</p><p>2 record</p><p>3 record</p><p>4 record</p><p>5 record</p><p>6 record</p><p>7 record</p><p>8 record</p><p>9 record</p><p>10 record</p>   </div>

<a id="loading_img" style="position:fixed; z-index: 99;bottom: 15px; right: 10px; display:none;">Loading... <img src="ajax-loader.gif" /></a>

<script type="text/javascript">
var page = 1;
$(window).scroll(function () {
currentX = $(window).scrollTop();
if  ($(window).scrollTop() == $(document).height() - $(window).height()){
$('a#loading_img').show('fast');
$.get('mydb.php?page=' + page, function(data) {
$('#mydiv').append(data);
$('a#loading_img').hide('fast');
}
);
page++;
}
});
</script>



mydb.php

<?php
$mysqli = new mysqli('localhost', 'root', '','mydb');
$pg=$_GET['page'];
$pg=$pg*10;
$sql='SELECT *FROM book_wise_test ORDER BY book_wise_test_id LIMIT '.$pg .', 10';
$result  = $mysqli->query($sql);
while($row = $result->fetch_assoc()) {
$book_wise_test_id=$row['book_wise_test_id'];
$book_nm=$row['book_nm'];
echo $book_wise_test_id.'======'.$book_nm.'<br/><br/>';
}
?>

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...

Set all remote host to mysql database

Problem:

How to set all remote hosts to MySQL?

While you  are working with remote production MySQL database, you need each time to open phpMyAdmin. It is time taking and working with it execute sql queries very slow. So you need a tool that connect with your remote production MySQL database. for security reason hosting company not enable connect all remote IP to MySQL database server. you need to set it by one remote IP or all IP. For one remote IP simple put the IP address in the field indicated in below image on the other hand if you need to access remote MySQL database from all IP the put % sign in that field. See below how to it.

Solution:

1) Open your control panel
2) Click on "Remote MySQL" link.
3) Put "%" in Host text box and click on "Add Host". see below image

Now you can connect your remote mysql database from any host.

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...

Error while import sql file in sqlyog

PROBLEM:
Sometime when you import sql file then you may surprise that error show any time.The sqlyog is the good tool to import or export mysql database, You may get error while import sql file in sqlyog. So how to tackle it?

SOLUTION:
This is because your insert statement is too big to execute. to solve these problem follow listed below statement:

open "my.ini" or "my.cnf" file in the mysql folder
and change this statement as :
"max_allowed_packet=500M"

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...