Word searching
1.
SELECT * FROM TABLE WHERE MATCH (`field`) AGAINST ('Keyword')
(Fastest)
2.
SELECT * FROM TABLE WHERE MATCH (`field`) AGAINST ('+Keyword' IN BOOLEAN MODE)
(Fast)
3.
SELECT * FROM TABLE WHERE RLIKE '(^| +)Keyword($| +)'
OR
SELECT * FROM TABLE WHERE
RLIKE '([[:space:]]|[[:<:]])Keyword([[:space:]]|[[:>:]])'
(Slow)
Contains searching
1.
SELECT * FROM TABLE WHERE MATCH (`field`) AGAINST ('Keyword*' IN BOOLEAN MODE)
(Fastest)
2.
SELECT * FROM TABLE WHERE FIELD LIKE 'Keyword%'
(Fast)
3.
SELECT * FROM TABLE WHERE MATCH (`field`) AGAINST ('*Keyword*' IN BOOLEAN MODE)
(Slow)
4.
SELECT * FROM TABLE WHERE FIELD LIKE '%Keyword%'
(Slow)
Recordsets
1.
SELECT SQL_CALC_FOUND_ROWS * FROM TABLE WHERE Condition LIMIT 0, 10
SELECT FOUND_ROWS()
(Fastest)
2.
SELECT * FROM TABLE WHERE Condition LIMIT 0, 10
SELECT COUNT(PrimaryKey) FROM TABLE WHERE Condition
(Fast)
3.
$result = mysql_query("SELECT * FROM table", $link);
$num_rows = mysql_num_rows($result);
(Very slow)
Joins
Use an INNER JOIN when you want the joining table to only have matching records that you specify in the join. Use LEFT JOIN when it doesn’t matter if the records contain matching records or not.
SELECT * FROM products
INNER JOIN suppliers ON suppliers.SupplierID = products.SupplierID
Returns all products with a matching supplier.
SELECT * FROM products
LEFT JOIN suppliers ON suppliers.SupplierID = products.SupplierID
WHERE suppliers.SupplierID IS NULL
Returns all products without a matching supplier.
Tag Archive for mysql
MySQL search
`No such file or directory – /tmp/mysql.sock`
#go to the shell to find out where your socket actually is locate mysql.sock #modify database.yml accordingly socket: /path/to/mysql.sock
mysql rename table
RENAME TABLE old_table TO backup_table, new_table TO old_table;
php while statement associative array from mysql
while($row = mysql_fetch_assoc($res)) {
}
typo3 open database connection
$connection = mysql_connect(TYPO3_db_host, TYPO3_db_username, TYPO3_db_password) or die ('cannot reach database');
$db = mysql_select_db(TYPO3_db) or die ("this is not a valid database");
mysql insert query
INSERT into table_name (column1, column2) values (value1, value2)
mysql select query
SELECT column_names FROM table_name WHERE conditions
mysql update query
UPDATE table_name SET column_name1 = value1, column_name2 = value2 WHERE conditions
Enum to Array / SQL to PHP
function LoadTypeValues($table, $column)
{
global $db;
// Create a SQL Query to get the Columns Type information,
// Open a database connection, execute the query, and retrieve
// the result.
$sql = "show columns from $table like '$column'";
// Get the Type information, Remove "xxx(" from the front
// and ")" from the end. Split the comma delimited values
// into an array.
$requete = mysql_query($sql, $db);
while($enum = mysql_fetch_array($requete)) {
$enum = $db->f('Type');
$off = strpos($enum,"(");
$enum = substr($enum, $off+1, strlen($enum)-$off-2);
$values = explode(",",$enum);
// For each value in the array, remove the leading and trailing
// single quotes, convert two single quotes to one. Put the result
// back in the array in the same form as CodeCharge needs.
for( $n = 0; $n < Count($values); $n++) {
$val = substr( $values[$n], 1,strlen($values[$n])-2);
$val = str_replace("''","'",$val);
$values[$n] = array( $val, $val );
}
}
// return the values array to the caller
return $values;
}
sql de-dupe
SELECT BookISBN, count(BookISBN) FROM Books GROUP BY BookISBN HAVING COUNT(BookISBN)>1;