Category: SQL

SQL – find records from one table which don’t exist in another

company_services +—-+——+————–+ | id | name | company_id| +—-+——+————–+ | 1 | IT services| 102| +—-+——+————–+ | 2 | hardware| 105| +—-+——+————–+ company_products +—-+——+————–+ | id | title| company_id | +—-+——+————–+ | 1 | Mouse| 101| +—-+——+————–+ +—-+——+————–+ | 2 | Keyboard| 102| +—-+——+————–+ SELECT count(*)FROM company_services WHERE company_id NOT IN (SELECT company_id FROM company_products)

Resetting forgotten phpmyadmin password

Simply change or reset your MySQL root password by doing the following: Run the following commands in Terminal Step 1 :Stop the MySQL server sudo service mysql stop Step 2 : Start mysqld sudo mysqld –skip-grant-tables $; Step 3:Login to MySQL as root mysql -u root mysql Step 4 :Change MYSECRET with your new root

Change db collation of all tables in mysql

SELECT CONCAT( ‘ALTER TABLE `’, t.`TABLE_SCHEMA` , ‘`.`’, t.`TABLE_NAME` , ‘` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;’ ) AS stmt FROM `information_schema`.`TABLES` t WHERE 1 AND t.`TABLE_SCHEMA` = ” ORDER BY 1 LIMIT 0 , 50 Above mysql query generate Alter table commands for the specified database. Run these sql statement as group.

How can be list the installed SQL Server instances

Press window+R windows run window will open and then type cmd Command prompt will open Run the following command  OSQL -L in in command prompt. It will list the server instance names. Example C:Userssmarttips>OSQL -L Servers: (local) smarttipsserver Free Download of the Microsoft sqlserver 2012 from following link:

zend mysql between query

$aEventsSelect = Engine_Api::_()->getItemTable(‘event’)->select(); if(count($aEventIds) > 0){ $aEventsSelect->where(“event_id IN (?)”, $aEventIds); } if( $filter == “past” ) { $aEventsSelect->where(“endtime <= FROM_UNIXTIME(?)”, time()); } else { $aEventsSelect->where(“endtime > FROM_UNIXTIME(?)”, time()); } if($eventdate != ‘0’) { $whereSql = “‘{$eventdate}’ BETWEEN DATE(starttime) AND DATE(endtime)”; $aEventsSelect->where($whereSql); }

mysql concatenate update query

We can append datat to the existing data in mysql field. Column name is email and value is “” UPDATE `engine4_users` SET email = CONCAT( email, ‘_test’ ) After the query execution the _test dat will be appended to email Output:info@smarttips.com_test

copy data from one mysql field to another mysqlfield

Mysql have this option to copy one field data to another field. For this purpose run a simple query. $UserlevelMigrationQuery     = “UPDATEusers SET  level_id = ‘4’, preunii_email =  email, migration_date = NOW(), email_verify = ‘0’ WHERE level_id = 7″; $UserlevelMigrationResult    = mysql_query($UserlevelMigrationQuery, $connection)  or die(mysql_error());

.htaccess redirect not working apache

1.Configure apache mod_rewrite,run in terminal a2enmod rewrite 2. add the following code to /etc/apache2/sites-available/default DocumentRoot /var/www <Directory /> Options FollowSymLinks  AllowOverride All </Directory> <Directory /var/www/> Options Indexes FollowSymLinks MultiViews AllowOverride All Order allow,deny allow from all </Directory> 3.Restart apache /etc/init.d/apache2 restart    

how to find and kill a process using particular port

Use the following command for stop a process using port 80 .This example is based on Ubuntu Linux operating system. Commands need to run in terminal sudo netstat -lpn |grep :80 tcp6       0      0 :::80                   :::* LISTEN      1185/apache2 sudo kill 1185