Think you have 2 tables with foreign key. Then, you can select some records using LEFT JOIN. SELECT * FROM talbe_1 LEFT JOIN talbe_2 ON talbe_1.fk_id = talbe_2.fk_id WHERE talbe_1.status = 1; But, if you need to delete using LEFT JOIN then you get a sql error. DELETE * FROM talbe_1 LEFT JOIN talbe_2 ON […]
Continue Reading →Codeigniter Active Record syntax for join $this->db->join(); Ex: $this->db->select(‘*’); $this->db->from(‘user’); $this->db->join(‘review’, ‘review.id = user.id’); $query = $this->db->get(); And it will generate following sql. SELECT * FROM user JOIN review ON review.id = user.id if you need several joins in one query, you have to call join function multiple times. If you need a specific type […]
Continue Reading →After installing MySQL and Apache/PHP, the database connections didn’t work from PHP. They worked only if connect using the MySQL client. Must be a problem with the PHP side. There is a sock file that PHP was trying to use, and it was in /var/mysql/mysql.sock and loged that the file was not there. Warning Error: […]
Continue Reading →There are two functions on sql to change string to lowercase. Those are LOWER() and LCASE() and both do the same thing at the end. Example SELECT LOWER(keyword) FROM your_table;
Continue Reading →Think you need to duplicate existing mysql table as a new empty table. That means only structure of an existing table to new one. Use following sql to do that. create table users2 like users;
Continue Reading →In the example below, you can combine process-list, status and version command to get all the output together as shown below. # mysqladmin -u root -ptmppassword process status version +—-+——+———–+—-+———+——+——-+——————+ | Id | User | Host | db | Command | Time | State | Info | +—-+——+———–+—-+———+——+——-+——————+ | 43 | root | localhost | […]
Continue Reading →# mysqladmin -u root -ptmppassword stop-slave Slave stopped # mysqladmin -u root -ptmppassword start-slave mysqladmin: Error starting slave: The server is not configured as slave; fix in config file or with CHANGE MASTER TO
Continue Reading →First identify the hanging MySQL client process using the processlist command. # mysqladmin -u root -ptmppassword processlist +—-+——+———–+—-+———+——+——-+——————+ | Id | User | Host | db | Command | Time | State | Info | +—-+——+———–+—-+———+——+——-+——————+ | 20 | root | localhost | | Sleep | 64 | | | | 24 | root | […]
Continue Reading →# mysqladmin -u root -ptmppassword flush-hosts # mysqladmin -u root -ptmppassword flush-logs # mysqladmin -u root -ptmppassword flush-privileges # mysqladmin -u root -ptmppassword flush-status # mysqladmin -u root -ptmppassword flush-tables # mysqladmin -u root -ptmppassword flush-threads flush-hosts: Flush all information in the host cache. flush-privileges: Reload the grant tables (same as reload). flush-status: Clear status […]
Continue Reading →# mysqladmin -u root -ptmppassword shutdown # mysql -u root -ptmppassword ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ Note: You can also use “/etc/rc.d/init.d/mysqld stop” to shutdown the server. To start the server, execute “/etc/rc.d/init.d/mysql start”
Continue Reading →