Arquiva-me

Found this somewhere on google.

If we need to compare two tables, this query will list the missing records on one of them (on table2):

SELECT t1.id
FROM table1 t1 LEFT JOIN table2 t2 ON t1.id = t2.id
WHERE t2.id IS NULL

Then you do it the other way around to check the mising records on the other table (table1).

To check for different values in identical fields:

SELECT t1.id
FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.id
WHERE t1.field1 <> t2.field1
OR t1.field2 <> t2.field2
etc…

This is a simple reminder of the mysql command line tool.

In windows, open a command line, navigate to mysql binary folder (if you have it on your PATH, you can skip the navigation part), and use:

To login:

mysql -u #username# -p#password#

(if you dont add the #password#.. it will ask you for one. Also, note that the password doesnt have a space between the switch)

To show all the databases:

mysql> show databases;

To switch a database:

mysql> use dbname;

To list the tables in the selected db:

mysql> show tables;

To see database’s field formats:

mysql> describe table_name;

To show columns and column information of table_name:

mysql> show columns from table_name;

All mysql commands apply:

mysql> select * from table_name where field_name="blahblah";

NOTE - Always end the command with ;

MYSQL Dump/Restore

On the binary folder, theres another command - mysqldump - that makes, well, dumps.

To make a dump of all the databases on the server:

mysqldump -u #username# -p#password# -A > dumpfile.sql

Dump only certain databases:

mysqldump -u #username# -p#password# db_1 db_2 db_3 > dumpfile.sql

Restore a database dump:

mysql --verbose -user=#username# --password=#password# db_1 < dumpfile.sql

NOTE - note the difference in the mysqdump and mysql when adding the switch for username and password, you can use either “-u #username#” or “–username=#username#”.

Command line help:

General help:

mysql> help

See the help contents:

mysql> help contents

To see help on a specific command/category type:

mysql> help _command_or_category_

Some examples:

mysql> help select
mysql> help administration
mysql> help me because i dont know anything about mysql and want to learn everything in 3 days (ok, doesnt work, but you can still try it for the error message)

Links to resources:

MYSQL commands

mysqldump

Like other people i’ve seen complaining about in the net, i keep forgetting how to do this.

There are several ways of doing this, but here’s a simple one:

$haystack = "Once upon a time, on a far away kingdom...";

$needle = "kingdom";

if(strstr($haystack,$needle)){
    echo "Word found.";
}else{
    echo "Couldn't find the word.";
}

The function strstr($haystack,$needle) is case sensitive in its search.

If it doesnt find the $needle, it will return false, otherwise returns the string from the first occurence of the needle to the end of the haystack text.

There’s also the function stristr($haystack,$needle) does case insensitive search.

Here are links to the manual:

strstr()
stristr()

Other alternatives (regEx, etc):

strrchr() - Find the last occurrence of a character in a string
substr() - Return part of a string
preg_match() - Perform a regular expression match