Most Used Queries on SQL & COMMANDS
Reset Atuo_increment
ALTER TABLE tablename AUTO_INCREMENT = 1
=============================
SELECT MAX( `column` ) FROM `table` ;
========================
Track errors
if (!$check1_res) {
printf(“Error: %s\n”, mysqli_error($conn));
exit();
}
==========================================
DELETE EMPTY RECORS
DELETE FROM table_name WHERE some_column = ”
=====================================
ADD COLUMN IN TABLE
ALTER TABLE todosa_content
ADD COLUMN `title7` varchar(35) NOT NULL,
ADD COLUMN `subtilte7` VARCHAR(35) NOT NULL,
ADD COLUMN `content7` TEXT NOT NULL,
ADD COLUMN `content71` TEXT NOT NULL,
ADD COLUMN `content72` TEXT NOT NULL AFTER `content61`;
==============================
mysql>USE dbname;
mysql>SHOW TABLE;
mysql>DESCRIBE rowname;
Changing Collation on the structure
If you have any stored procedures that use database defaults, you must drop and recreate those stored procedures.
If the ALTER DATABASE command runs quickly, chances are the tables were not altered.
You may have to run ALTER TABLE on all your tables as follows:
ALTER TABLE tblname CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];
$updatedb=”UPDATE friend_users SET extout=extout+’1′ WHERE user_id=$recordid”;
$page_insert = mysqli_query($link1, “INSERT INTO friend_users (`user_id`) VALUES (‘$recordid’)”) or die(mysqli_error($link));
SUBSTRING(domain,4,5)
MySQL statement returns 3 numbers of characters from the 4th position of the string ‘domain’.
SELECT *, SUBSTRING(domain,4,5) FROM tracking_table ORDER BY rec_use_date DESC limit 100;
Printout
LIKE
SELECT * FROM items WHERE items.xml LIKE ‘%123456%’
SELECT lastName,firstName FROM Customer
WHERE lastName LIKE “B%”
AND city = “Indianapolis”
AND (phone LIKE “%8%” OR fax LIKE “%8%”)
LOCATE
mysql> SELECT LOCATE(‘st’,’myteststring’,6);
+——————————-+
| LOCATE(‘st’,’myteststring’,6) |
+——————————-+
| 7 |
+——————————-+
1 row in set (0.00 sec)
SELECT pub_name,LOCATE(‘at’,pub_name)
FROM publisher
WHERE locate(‘at’,pub_name)>0;
WORDPRESS TIP
$Table_Name = $wpdb->prefix.’tablename’; $SearchField = ‘%’. $YourVariable . ‘%’; $sql_query = $wpdb->prepare(“SELECT * FROM $Table_Name WHERE ColumnName LIKE %s”, $SearchField) ; $rows = $wpdb->get_results($sql_query, ARRAY_A);
CREATE A TABLE ON MYSQL
CREATE TABLE IF NOT EXISTS `categories` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(256) NOT NULL,
`description` text NOT NULL,
`created` datetime NOT NULL,
`modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=19 ;
I am the Coach
SELECT * FROM `appointments` JOIN `users` USING `user_id` WHERE `appo_date` BETWEEN 'X' AND 'Y'.
How set the db in appointments app
SELECT * FROM `appointments` JOIN `users` USING `user_id` WHERE `appo_date` BETWEEN 'X' AND 'Y'.
SAVE APPOINTMENT
4-book.php
<?php
require "2-lib-appo.php";
echo $_APPO->save ($_POST["date"], $_POST["slot"], $_POST["user"])
? "OK" : $_APPO->error;