MYSQL-TOOLS

Comparison of graphical tools for MySQL

Home Blog

Difference MyISAM and InnoDB

Печать PDF

Отличия между движками MyISAM и InnoDB простым языком.

 

В данной таблице я попробовал показать на простых примерах разницу между MyISAM и InnoDB:

Описание MyISAM InnoDB
Транзакционный движек?

 

Транзакция (Transaction) — блок операторов SQL , который в случае ошибки в одном запросе, возвращается к предыдущему состоянию (Rollback), и только в случае выполнения всех запросов подтверждается (Commit)

 

Differences PRIMARY KEY VS UNIQUE in MySQL

Печать PDF

So what's different from the PRIMARY KEY and UNIQUE CONSTRAINT?

 

1. PRIMARY KEY does not allow NULL in their fields, and UNIQUE - allow.

2. PK - there can be only one. UQ - can be any number.

3. When you create a PRIMARY KEY by default creates a clustered index, and when you create a UNIQUE not.

 


 

CRUD MySQL in PHP

Печать PDF

CRUD — (create read update delete) abbreviation naming four basic functions for working with databases - create, read, update and delete.

Examples

OperationMySQL statment HTTP operation
Create INSERT POST
Read SELECT GET
Update UPDATE PUT or PATCH
Delete DELETE DELETE

 

The classic application to work with the database is usually called CRUD - on the first letter of standard operations, Create, read, update and delete.

It's like the notes in music - any application consists of these basic elements. In our case it is a PHP web page that displays a list of records in the database as a list of (R), allows you to add new records in the MySQL database (C), edit them (U) and delete (D) from database.

With the help of this php application class is implemented through a few stitches.

Here they are:

 

<?

include 'safemysql.class.php';
$db = new SafeMysql();
$table = "test"

if(
$_SERVER['REQUEST_METHOD']=='POST') {
if (isset(
$_POST['delete'])) {
$db->query("DELETE FROM ?n WHERE id=?i",$table,$_POST['delete']);
} elseif (
$_POST['id']) { 
$db->query("UPDATE ?n SET name=?s WHERE id=?i",$table,$_POST['name'],$_POST['id']);
} else { 
$db->query("INSERT INTO ?n SET name=?s",$table,$_POST['name']);

header("Location: http://".$_SERVER['HTTP_HOST'].$_SERVER['PHP_SELF']);  
exit;  
}  
if (!isset(
$_GET['id'])) {
$LIST = $db->getAll("SELECT * FROM ?n",$table);
include
'list.php'
} else {
if (
$_GET['id']) {
$row = $db->getRow("SELECT * FROM ?n WHERE id=?i",$table,$_GET['id']);
foreach (
$row as $k => $v) $row[$k]=htmlspecialchars($v); 
} else { 
$row['name']=''
$row['id']=0

include
'form.php'
}

 

Template files:
list.php
<a href="/?id=0">Add item</a>
<? foreach ($LIST as $row): ?>
<li><a href="/?id=<?=$row['id']?>"><?=$row['name']?></a>
<? endforeach ?>


form.php
<form method="POST">
<input type="text" name="name" value="<?=$row['name']?>"><br>
<input type="hidden" name="id" value="<?=$row['id']?>">
<input type="submit"><br>
<a href="/?">Return to the list</a>
</form>
<? if ($row['id']):?>
<div align=right>
<form method="POST">
<input type="hidden" name="delete" value="<?=$row['id']?>">
<input type="submit" value="Удалить"><br>
</form>
</div>
<?endif?>

 

Problems at imposition HTML emails

Печать PDF

Quite often have to make regular mailings with the news. Send text or simple HTML mailings layout is almost always not satisfied. Designers showing creativity, and we then fills bumps during the layout of their models with correct display in a variety of email clients.

Below is a list of the problems we encountered and their solutions.

  • For not to be encoding issues you need to impose in UTF-8, in the HEAD to specify <meta http-equiv=«Content-Type» content=«text/html; charset=utf-8»>;
  • Some mail portals cut out all the css, so it is necessary to impose using HTML3.2;
  • Others make great margins for the elements to fix it have to wrap a mail by <div id=«mailsub»>;
  • And others transforms indentation (\t) in the code into &nbsp; to fix it your code should not been formatted using tabs.
  • In the text version can not use html entities, because it is a text version, and not HTML. Also, in the text version can not use unicode characters that are not in the windows-1251, as some mail server transcodes the body of the message in this encoding;
  • Outlook 2007 at elements of type «div» makes the the field above. In the «table» with «cellpadding = 0 cellspacing = 0" is not such fields;
  • Outlook 2007 does not use background images. We really found one hack - you can specify a background-image for the body. Then Outlook will display the background. But the hack does not work if we use the background-position or the background-repeat. In the web client that background will not be shown, so to duplicate his instructions - in the style at body for Outlook, and in the background at the "root" table for web clients;
  • In the latest Pixar mailings we had to completely abandon the use of background images. because in this case there were problems with the positioning of images on top of the background and do the basic layout consists of a set of pictures inside the table. The text at the same time is also included in the pictures, links are arranged by areamap, but areamap can not be used because Gmail in these links can not be clicked. It is necessary to cut an image and store the table;
  • In Gmail, if the image that is unique in the cell of the table, there is a 3px bottom padding, it can be eliminated by specifying style = «display: block» for this image;

 

When I mention the name of any host that mail then refers to his web interface

 

HeidiSQL & HTTP Tunnel - it works!

Печать PDF
Рейтинг пользователей: / 6
ХудшийЛучший 

HeidiSQL&HTTP Tunnel

HeidiSQL is good and for someone simply irreplaceable tool for working with databases MySQL. This GUI has everything you need to work with the MySQL database. But HeidiSQL missing important functionality is the connection through the HTTP tunnel.

HeidiSQL is an easy-to-use interface and a "working-horse" for web-developers using the popular MySQL-Database. It allows you to manage and browse your databases and tables from an intuitive Windows® interface.

When you can't connect to the MySQL server directly, for example a firewall blocks the access from all network protocols, except HTTP protocol, you can use HTTP tunneling.

In this article I'll show you how to connect to remote MySQL server using HeidiSQL via HTTP Tunnel.

 

MySQL stored procedures

Печать PDF
Рейтинг пользователей: / 43
ХудшийЛучший 

For a long time to suffer with this issue. Literature on the Internet a lot. I had to ask around at different forums, deeper digging in the manual and explain to himself some weird moments. So, short of stored procedures in MySQL.

 

MySQL and character set. Migration (or degradation)

Печать PDF

Today I met with the transfer of data between two servers: MySQL 4.0 on FreeBSD (access via SSH) and MySQL 5.0 on Windows XP locally (base package XAMPP Web Servers).

In general, does not matter ... we all know that for any import and export data from one database to another, there are problems with Russian letters. You are probably faced with a bunch of articles on the subject, and they are all good, but today I went a little deeper.

The task was simple - to remove a couple of lines from one plate to the remote Web server. Put phpmyadmin and decide for 2-3 minutes ..And you can keep track of primary keys of the required records and a simple query on a remote server to remove them.

Too all that easy. I took a different route.

 


Страница 1 из 2

SSH file transfer Как скопировать файлы через SSH ? Далее ...

'mysql.proc' doesn't exist fixed problem

_

Table 'mysql.proc' doesn't exist. Fix error 1146 Table doesn't exist here...