Categories
SQL

Connecting MySQL Workbench to Server MySQL

Use: GRANT ALL ON *.* to root@’%’ IDENTIFIED BY ‘your-root-password’; Use % as host if you don’t know where user is connecting from. Also make sure Mysql is running and that iptables is not blocking the port. The above solves this error: Host … is not allowed to connect to this MySQL server

Categories
SQL

PHP MySQL Query based on number of results returned

$sql = “SELECT * FROM tbl”; $result = mysql_query($sql); if (mysql_num_rows($result)==0) { //do this } else { //do that }

Categories
SQL

MySQL: How to Copy tables into seperate Schemas

To Copy a single table in a Schema into another existing Schema CREATE TABLE myschema.mytbl LIKE myschema2.mytbl; INSERT INTO myschema.mytbl SELECT * FROM myschema2.mytbl; To Copy all the tables in a Schema into another existing Schema   In cmd/terminal, dump the database mysqldump -u root -ppass myschema > /path/to/myschema.sql In cmd/terminal, use the new database […]

Categories
Database SQL

Create a New MySQL User that only has access to a Particular Schema and Table

Create New MySQl User with Privileges GRANT ALL PRIVILEGES ON dbTest.* To ‘user’@’hostname’ IDENTIFIED BY ‘password’; So for example: Log in to Mysql… mysql -u [root] -p Then type: GRANT ALL PRIVILEGES ON [schema]. To ‘[user]’@'[hostname]’ IDENTIFIED BY ‘[password]’; How to View Current Users and Privileges SHOW GRANTS; or select user,host from mysql.user;

Categories
SQL

MySQL INSERT IF NOT EXISTS: Checking whether a record exists and inserting

MySQL Insert If Not Exists Why doesn’t MySQL Insert If Not exists work????? UPDATE: CHECK AT THE BOTTOM OF POST Bogdan has the answers . First of all, this information relates solely to MySQL and may not apply to MSSQL or PostgreSQL. Have you been trying to insert records into a table based on whether there […]