MySQLi
Förkortningar
- MySQLi - MySQL Improved
- SQL - Structured Query Language
Funktioner för felmeddelande i MySQL med PHP
echo mysqli_connect_errno();
echo mysqli_connect_error();
Funktion för att på ett säkert sätt ta bort skadlig kod och specialtecken med PHP
mysqli_real_escape_string($con, $_GET['match'])
MySQL med PHP
$con = mysql_connect("host","user","password");
mysql_select_db("database", $con);
mysql_set_charset("utf8mb4");
$match = mysql_real_escape_string($_GET['match']);
$query = "SELECT * FROM table WHERE column = '" . $match . "' ORDER BY id LIMIT 0, 10";
$result = mysql_query($query);
$num = mysql_num_rows($result);
$row = mysql_fetch_assoc($result);
mysql_data_seek($result, 0);
while($row = mysql_fetch_array($result)) {
echo $row['column'];
}
mysql_close($con);
$result = mysql_query("DESCRIBE name_of_some_table");
$result = mysql_query("SHOW TABLES");
$result = mysql_query("SHOW DATABASES");
$result = mysql_query("SHOW VARIABLES LIKE 'character_set_database'");
$result = mysql_query("SHOW TABLE STATUS WHERE NAME LIKE 'table'");
$result = mysql_query("SHOW FULL COLUMNS FROM table");
Status för tabell
$result = mysql_query("SHOW TABLE STATUS WHERE NAME LIKE 'table'");
Flytta kolumn
$result = mysql_query("ALTER TABLE table MODIFY column int FIRST;");
Ändra namn, typ och plats för kolumn
$result = mysql_query("ALTER TABLE `table` CHANGE COLUMN oldcolumn newcolumn int(11) AFTER `othercolumn`;");
$result = mysql_query("ALTER TABLE `table` CHANGE COLUMN oldcolumn newcolumn int(11) AFTER FIRST;");
MySQLi med PHP
$con = mysqli_connect("host","user","password","database");
mysqli_select_db($con, 'database');
mysqli_set_charset($con, "utf8mb4");
$match = mysqli_real_escape_string($con, $_GET['match']);
$query = "SELECT * FROM table WHERE column = '" . $match . "' ORDER BY id LIMIT 0, 10";
$result = mysqli_query($con, $query);
$num = mysqli_num_rows($result);
$row = mysqli_fetch_assoc($result);
mysqli_data_seek($result, 0);
while($row = mysqli_fetch_array($result)) {
echo $row['column'];
}
mysqli_close($con);
$result = mysqli_query($con, "DESCRIBE name_of_some_table");
$result = mysqli_query($con, "SHOW TABLES");
$result = mysqli_query($con, "SHOW DATABASES");
$result = mysqli_query($con, "SHOW VARIABLES LIKE 'character_set_database'");
$result = mysqli_query($con, "SHOW TABLE STATUS WHERE NAME LIKE 'table'");
$result = mysqli_query($con, "SHOW FULL COLUMNS FROM table");
Status för tabell
$result = mysqli_query($con, "SHOW TABLE STATUS WHERE NAME LIKE 'table'");
Flytta kolumn
$result = mysqli_query($con, "ALTER TABLE table MODIFY column int FIRST;");
Ändra namn, typ och plats för kolumn
$result = mysqli_query($con, "ALTER TABLE `table` CHANGE COLUMN oldcolumn newcolumn int(11) AFTER `othercolumn`;");
$result = mysqli_query($con, "ALTER TABLE `table` CHANGE COLUMN oldcolumn newcolumn int(11) FIRST;");
MYSQL_BOTH, MYSQL_ASSOC och MYSQL_NUM
$list = mysql_fetch_array($result, MYSQL_BOTH);
$list = mysql_fetch_array($result, MYSQL_ASSOC);
$list = mysql_fetch_array($result, MYSQL_NUM);
Töm tabell
TRUNCATE TABLE `tabell`
Flusha tabell
FLUSH TABLE `tabell`
Radera tabell
DROP TABLE `tabell`
Spara med REPLACE INTO
REPLACE INTO table (col1, col2, col3) VALUES 'val1', 'val2', 'val3')
INNER JOIN
SELECT cat.id, us.user, cat.import FROM categories cat INNER JOIN users us ON cat.uid = us.id;