If you are using Xampp server then use the following method go to the below folder phpmyadmin/libraries/config.default.php inside config.default.php edit the line as like below $cfg[‘ExecTimeLimit’] = 0;
Fatal error: Out of memory (allocated 998506496) (tried to allocate 496760462 bytes)phpMyAdmin\libraries\import.lib.php on line 373 Step1:Copy the .sql file into the xammp folder in the below path xampp/mysql/bin Step2:create a database called testdatabase in phpmyadmin step3: Execute the below command through command prompt C:\xampp\mysql\bin>mysql -u root -D testdatabase < db_dump_db.sql
There are different options for copying table from one database to another database. Two methods i m giving here 1.In phpmyadmin one option is there to copy tables.Phpmyadmin if you go to operations tab->copy database to using the below options using a checkbox. Structure only Structure and data Data only CREATE DATABASE before copying Add… Read More »
Below query execution throwing the error Column ’emp_id’ in IN/ALL/ANY subquery is ambiguous select employees.emp_id,emp_reg_date from employees join emp_linking_table on emp_linking_table.emp_id=employees.emp_id where emp_id not in( select emp_id from data_record_table) solution: if you change condition like as below will give proper result where employees.emp_id not in( select emp_id from data_record_table)
create a table using the below sql statement
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
CREATE TABLE IF NOT EXISTS `students` ( `id` int(10) unsigned NOT NULL, `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `first_name` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL, `last_name` varchar(250) COLLATE utf8_unicode_ci NOT NULL, `email` varchar(250) COLLATE utf8_unicode_ci NOT NULL, `age` int(10) NOT NULL, `totalmarks` varchar(250) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- Dumping data for table `students` -- INSERT INTO `students` (`id`, `created_at`, `updated_at`, `first_name`, `last_name`, `email`, `age`, `totalmarks`) VALUES (23, '2015-04-28 06:09:38', '2015-05-15 23:55:29', 'John', 'mathew', 'vasanthanpv@gmail.com', 33, '333'), (26, '2015-05-16 01:19:24', '2015-05-16 01:19:24', 'vasanthan', 'pv', 'vasanthanpv@gmail.com', 44, '333'), (27, '2015-05-16 01:19:46', '2015-05-16 01:19:46', 'Martin', 'jacob', 'vasanthanpv@gmail.com', 22, '22'), (28, '2015-05-16 01:20:06', '2015-05-16 01:20:06', 'Manu', 'Ram', 'vasanthanpv1@gmail.com', 32, '55'), (30, '0000-00-00 00:00:00', '0000-00-00 00:00:00', NULL, '', '', 0, ''); |
if we use the below mysql query output as like below
|
1 2 3 |
SELECT id,case age when '' then 'No Age' else age end as sudent_ages,case first_name when '' then 'No First Name' else first_name end as sudent_first_name from students |
you can see the result with NULL value for the first_name field. if you use the query as in a newformat ..with IFNULL condition it wil work. below query will work for you SELECT… Read More »
Below are the two database tables tbl_questions
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE IF NOT EXISTS `tbl_questions` ( `id` int(10) NOT NULL, `question` varchar(250) NOT NULL ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; -- -- Dumping data for table `tbl_questions` -- INSERT INTO `tbl_questions` (`id`, `question`) VALUES (1, 'Prime minister India'), (2, 'President of India'); |
tbl_answer
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
<pre class="lang:mysql decode:true " >CREATE TABLE IF NOT EXISTS `tbl_answer` ( `id` int(10) NOT NULL, `question_id` int(10) NOT NULL, `answer` varchar(250) NOT NULL, `status` varchar(3) NOT NULL ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1; -- -- Dumping data for table `tbl_answer` --</pre> INSERT INTO `tbl_answer` (`id`, `question_id`, `answer`, `status`) VALUES (1, 1, 'Narendra Modi', 'y'), (2, 1, 'Manmohan singh', 'n'), (3, 1, 'Sonia Gandhi', 'n'), (4, 1, 'Rahul gandhi', 'n'), (5, 2, 'Pranabh Mukherji', 'y'), (6, 2, 'Prakash karat', 'n'), (7, 2, 'Sasi tharoor', 'n'), (8, 2, 'APJ abdul Kalam', 'n'); |
tbl_questions tbl_answer Output would be like this i need to get ouput as above, anybody is there to help. please comment….
This is the query i used to fetch count of multiple fields from my table.I used four times select query inside a query before doing join
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
select proj.id,proj.projectname,count(bugtbl.id) as totalbug,(select count(id) from tbl_bug_history where bug_id = bugtbl.id and status = 'invalid' ) as invalidcount,(select count(id) from tbl_bug_history where bug_id = bugtbl.id and status = 'feedback') as feedbackcount,(select count(id) from tbl_bug_history where bug_id = bugtbl.id and status = 'duplicate') as duplicatecount,(select count(id) from tbl_bug_history where bug_id = bugtbl.id and status = 'normal') as normalcount from tbl_projects proj left join tbl_bug bugtbl on bugtbl.project_id=proj.id group by proj.projectname |
you can see the database structure and query result as below
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 |
CREATE TABLE IF NOT EXISTS `tbl_projects` ( `id` int(11) NOT NULL AUTO_INCREMENT, `projectname` varchar(250) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; -- -- Dumping data for table `tbl_projects` -- INSERT INTO `tbl_projects` (`id`, `projectname`) VALUES (1, 'project-one'), (2, 'project-two'), (3, 'project-three'), (4, 'project-four'); CREATE TABLE IF NOT EXISTS `tbl_bug_history` ( `id` int(10) NOT NULL AUTO_INCREMENT, `bug_id` int(10) NOT NULL, `status` varchar(100) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ; -- -- Dumping data for table `tbl_bug_history` -- INSERT INTO `tbl_bug_history` (`id`, `bug_id`, `status`) VALUES (1, 1, 'invalid'), (2, 1, 'invalid'), (3, 2, 'invalid'), (4, 2, 'invalid'), (5, 3, 'duplicate'), (6, 3, 'duplicate'), (7, 4, 'feedback'), (8, 4, 'feedback'), (9, 5, 'duplicate'), (10, 5, 'duplicate'), (11, 6, 'duplicate'), (12, 6, 'invalid'), (13, 7, 'feedback'), (14, 7, 'normal'), (15, 8, 'duplicate'), (16, 8, 'normal'), (17, 9, 'feedback'), (18, 9, 'feedback'), (19, 10, 'invalid'), (20, 10, 'feedback'); CREATE TABLE IF NOT EXISTS `tbl_bug` ( `id` int(10) NOT NULL AUTO_INCREMENT, `project_id` int(10) NOT NULL, `bugname` varchar(250) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ; -- -- Dumping data for table `tbl_bug` -- INSERT INTO `tbl_bug` (`id`, `project_id`, `bugname`) VALUES (1, 1, 'first-bug'), (2, 1, 'second-bug'), (3, 1, 'bug-third'), (4, 1, 'bug-four'), (5, 1, 'bug-give'), (6, 1, 'master-bug'), (7, 2, 'error-notice'), (8, 3, 'invalid bug'), (9, 4, 'insufficinet memory'), (10, 4, 'hello bug'); |
We have two database tables. while updating the table entry trigger will execute. tbl_experience tbl_salary after updating the experience in the tbl_experience table. automaticaly update the salary column in the tbl_salary Step1: Use the below code to create tables through phpmyadmin-paste the below code into the query window.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
CREATE TABLE IF NOT EXISTS `tbl_experience` ( `id` int(10) NOT NULL AUTO_INCREMENT, `name` varchar(250) NOT NULL, `exp` int(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ; -- -- Dumping data for table `tbl_experience` -- INSERT INTO `tbl_experience` (`id`, `name`, `exp`) VALUES (1, 'vasanthan', 1), (2, 'rajesh', 2); CREATE TABLE IF NOT EXISTS `tbl_salary` ( `id` int(10) NOT NULL AUTO_INCREMENT, `empid` int(10) NOT NULL, `salary` int(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; |
Step2: execute the below script through… Read More »
Example: we have two tables tbl_students,tbl_marks using a join query to fetch the students not having records in marks table.It should work using a single query see the blow example without using subquery. For creating students and marks table use the below query
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
CREATE TABLE IF NOT EXISTS `tbl_marks` ( `id` int(10) NOT NULL AUTO_INCREMENT, `studentid` int(10) NOT NULL, `marks` int(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ; -- -- Dumping data for table `tbl_marks` -- INSERT INTO `tbl_marks` (`id`, `studentid`, `marks`) VALUES (1, 2, 10), (2, 3, 15), (3, 4, 11), (4, 5, 19), (5, 6, 20); -- -------------------------------------------------------- -- -- Table structure for table `tbl_students` -- CREATE TABLE IF NOT EXISTS `tbl_students` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ; -- -- Dumping data for table `tbl_students` -- INSERT INTO `tbl_students` (`id`, `name`) VALUES (1, 'vasanthan'), (2, 'jayesh'), (3, 'nasar'), (4, 'sreejith'), (5, 'arun'), (6, 'clinton') |
use the below query to display null records
|
1 2 3 4 |
SELECT name, marks FROM tbl_students LEFT JOIN tbl_marks ON tbl_students.id = tbl_marks.studentid WHERE marks IS NULL |
use “utf8_general_ci” in the collation while creating table