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 →If you want to empty or truncate a mysql table using codeigniter active record, use the following code. 🙂 To empty $this->db->empty_table(); This will generate a delete SQL string and runs the query. // DELETE FROM mytable To truncate $this->db->from(‘mytable’); $this->db->truncate(); // or $this->db->truncate(‘mytable’); This will generate a truncate SQL string and runs the query. […]
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 →