Selecting a database:
mysql> USE database;
Listing databases:
mysql> SHOW DATABASES;
Listing tables in a db:
mysql> SHOW TABLES;
Describing the format of a table:
mysql> DESCRIBE table;
Creating a database:
mysql> CREATE DATABASE db_name;
Creating a table:
mysql> CREATE TABLE table_name (field1_name TYPE(SIZE), field2_name TYPE(SIZE)); Ex: mysql> CREATE TABLE pet (name VARCHAR(20), sex CHAR(1), birth DATE);
Load tab-delimited data into a table:
mysql> LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table_name; (Use n for NULL)
Inserting one row at a time:
mysql> INSERT INTO table_name VALUES ('MyName', 'MyOwner', '2002-08-31');
(Use NULL for NULL)
Retrieving information (general):
mysql> SELECT from_columns FROM table WHERE conditions; All values: SELECT * FROM table; Some values: SELECT * FROM table WHERE rec_name = "value"; Multiple critera: SELECT * FROM TABLE WHERE rec1 = "value1" AND rec2 = "value2";
Reloading a new data set into existing table:
mysql> SET AUTOCOMMIT=1; # used for quick recreation of table mysql> DELETE FROM pet; mysql> LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table;
Fixing all records with a certain value:
mysql> UPDATE table SET column_name = "new_value" WHERE record_name = "value";
Selecting specific columns:
mysql> SELECT column_name FROM table;
Retrieving unique output records:
mysql> SELECT DISTINCT column_name FROM table;
Sorting:
mysql> SELECT col1, col2 FROM table ORDER BY col2; Backwards: SELECT col1, col2 FROM table ORDER BY col2 DESC;
Date calculations:
mysql> SELECT CURRENT_DATE, (YEAR(CURRENT_DATE)-YEAR(date_col)) AS time_diff [FROM table]; MONTH(some_date) extracts the month value and DAYOFMONTH() extracts day.
Pattern Matching:
mysql> SELECT * FROM table WHERE rec LIKE "blah%"; (% is wildcard - arbitrary # of chars) Find 5-char values: SELECT * FROM table WHERE rec like "_____"; (_ is any single character)
Extended Regular Expression Matching:
mysql> SELECT * FROM table WHERE rec RLIKE "^b$";
(. for char, [...] for char class, * for 0 or more instances
^ for beginning, {n} for repeat n times, and $ for end)
(RLIKE or REGEXP)
To force case-sensitivity, use “REGEXP BINARY”
Counting Rows:
mysql> SELECT COUNT(*) FROM table;
Grouping with Counting:
mysql> SELECT owner, COUNT(*) FROM table GROUP BY owner; (GROUP BY groups together all records for each 'owner')
Selecting from multiple tables:
(Example)
mysql> SELECT pet.name, comment FROM pet, event WHERE pet.name = event.name; (You can join a table to itself to compare by using 'AS')
Currently selected database:
mysql> SELECT DATABASE();
Maximum value:
mysql> SELECT MAX(col_name) AS label FROM table;
Auto-incrementing rows:
mysql> CREATE TABLE table (number INT NOT NULL AUTO_INCREMENT, name CHAR(10) NOT NULL);
mysql> INSERT INTO table (name) VALUES ("tom"),("dick"),("harry");
Adding a column to an already-created table:
mysql> ALTER TABLE tbl ADD COLUMN [column_create syntax] AFTER col_name;
Removing a column:
mysql> ALTER TABLE tbl DROP COLUMN col;
(Full ALTER TABLE syntax available at mysql.com.)
Batch mode (feeding in a script):
# mysql -u user -p < batch_file
(Use -t for nice table layout and -vvv for command echoing.)
Alternatively: mysql> source batch_file;
Backing up a database with mysqldump:
# mysqldump --opt -u username -p database > database_backup.sql
(Use ‘mysqldump –opt –all-databases > all_backup.sql’ to backup everything.)
(More info at MySQL’s docs.)
# id jack
it will show something like that:
# uid=10002(jack) gid=2524(psacln) groups=2524(psacln)
remember the uid of jack which is 10002, we will need it later.
create user via the command below and use jack’s uid like this (this will let jill access subfolder):
# /usr/sbin/useradd -u 10002 -o -d /var/www/vhosts/example.com/custom_folder -g psacln -s /bin/false jill
# /etc/passwd jill (enter the new password and confirm it, does not have to be the same as jack’s)
First we need to install Java, which is the easy part.
yum install java-1.6.0-openjdk-devel yum install java-1.6.0-openjdk
Find out where java is installed
[root@lin02 ~]# which java /usr/bin/java [root@lin02 ~]#
And add this info to your .bashrc
vi .bashrc JAVA_HOME=/usr/bin/java PATH=$PATH:$HOME/bin:$JAVA_HOME/bin
Download your Glassfish package and unpack in /usr/lib/java/
cd /usr/lib/java/glassfish3/ ./bin/asadmin start-domain
And your done!
<?php
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, "http://rss.cnn.com/rss/money_news_international.xml");
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
$output = curl_exec($ch); curl_close($ch);
$xml = new SimpleXMLElement($output);
$count = 0;
$limit = 2;
foreach( $xml->channel->item as $item ) {
echo '<a target="_blank" href="'.$item->guid.'">'.$item->title.'</a>';
echo ''.(substr($item->description, 0, 100)).'...';
$count++;
if($count == $limit) {
break;
}
}
?>
<?php
/** * Webservice created by Michael Schouman *
* 31-08-2011
*
* For Examples and documentation: michael@schouman.info
*
* TODO: Filter strange characters
*
*/
// setting variables
$db = 'DATABASE';
$user = 'USERNAME';
$pass = 'PASSWORD';
$host = 'HOST';
// date time genarator
$dateTime = new DateTime("now"); $datenow = $dateTime->format("Y-m-d H:i:s");
// connect to mysql database
$conn = mysql_connect($host, $user, $pass) or die('Error connecting to mysql');
mysql_select_db($db);
// get some parameters
$action = $_REQUEST['action'];
$condition = $_REQUEST['condition'];
$message = $_REQUEST['message'];
$created = $_REQUEST['created'];
// Array used to encode the JSON
$arr = array();
switch($action){
// Simple select
case "select":
$result = mysql_query("SELECT * FROM TABLE WHERE BINARY ROW = '$condition' LIMIT 1");
$row = mysql_fetch_array($result);
if (empty($row)){
$arr = array('success' => 'false');
} else {
$arr = array('success' => 'true');
}
echo json_encode($arr);
break;
// Simple select
// Bigger select
case "bigselect":
$result = mysql_query("SELECT ALL YOUR TABLES FROM TABLES WHERE BINARY ROW = '$condition'");
while($art = mysql_fetch_object($result)){
$arr[] = $art;
}
echo json_encode($arr);
break;
// Bigger select
// Simple insert
case "insert":
$result = mysql_query("INSERT INTO TABLE(YOUR STUFF) VALUES (YOUR STUFF)");
if (!$result) {
$arr = array('success' => 'false');
} else {
$arr = array('success' => 'true');
}
echo json_encode($arr);
break;
// Simple insert
}
?>
How to access
// Simple select
ws.php?action=select&condition=VARIABLE
// Bigger select
ws.php?action=bigselect&condition=VARIABLE
// Insert
ws.php?action=insert&condition=VARIABLE&message=joepie%20het%20werkt&created=2011-03-25%2005:03:53