Begärd sida

Titel: MySQL

Författare: Per Stenebo

Skapad: 2013-02-03 20:38:19

Ändrad: 2013-12-29 22:26:44

MySQL

MySQL-PHP tutorials: | w3schools | php.net | php-mysql-tutorial.com | tydal.nu |

MySQL .com | .se |
Docs | 4.1 |

MySQL-tutorials: | php.net/mysql |

Signed = Integer med både positivt och negativt värdespann.
Unsigned = Integer med enbart positiva värden.

In versions of MySQL up to and lincluding 4.1, BIT is a synonym for TINYINT(1).

Zerofill automatically adds leading zeros to the number. Keep in mind that all zerofill columns are automatically unsigned.

SELECT TRIM(LEADING '0' FROM myfield) FROM table

SQL-injektioner: | tizag.com | geiser.se | php.net |


Dataimport

Importera data med phpMyAdmin

MySQL manual mysqlimport: | 4.1 |

 

  • Infilen kan vara fältavgränsade textfiler, CSV.
  • Infilen skall ha lika många fält som måltabellen
  • Strängar i infilen får inte innehålla likadana tecken som används som avgränsare (tex " eller ,).
  • Om data skall uppdateras i måltabellen skall:
    -ett gemensamt fält ha egenskapen unik i måltabellen.
    -Rutan "Replace" vara ikryssad
  • Kommatecken som decimalavskiljare måste bytas ut mot punkt.
  • Om det blir problem med åäö, öppna excelfilen i OpenOffice calc och spara som CSV UTF-8.

Exempel:

"EmpID";"EmpNo";"Surname"
;"1002";"Lennartsson"

Data Migration

| Workbench | GUI-tools -download | Migration-toolkit -Doc | OBDC-connector |

Testserver: | sme73 | php_mysql |

Access -> MySQL | guide

Se även Migrering av Paletten till MySQL backend.

Paging

Guider: | designplace.org | plus2net.com | php-mysql-tutorial.com | phpjabbers.com |
 

Select

| MySQL manual: Select |

$sql = "SELECT *
FROM tblPricegroup  
WHERE OrderNo = '$set_pg_no' "; 

$result = mysql_query($sql) or die(mysql_error());
$numrows = mysql_num_rows($result);

$array = mysql_fetch_array($result); // Bra
$array = mysql_fetch_assoc($result); // Bättre

// Plocka ut Artikelnummer
$PGID_input = $array["PGID"];
$OrderNo_input = $array["OrderNo"]; 
$ArticleNo_input = $array["ArticleNo"]; 
$Active_input = $array["Active"];

// Free mysql resultset
mysql_free_result($result);

if ($numrows < 1) 

$errors[] = "Hittade inte prisgruppen med ordernummer $set_pg_no ";
}
else { $set_pg_cn = $ArticleNo_input; }

Räknare

$sql = "SELECT SQL_CALC_FOUND_ROWS a.*
            FROM tblItem AS a
            WHERE a.Itemnumber LIKE '$Itemnumber_input'
            ORDER BY a.Itemnumber ASC
            LIMIT $startrow, $showrows";

// Get total hit count (as if no LIMIT in query).
    $total = mysql_query("SELECT FOUND_ROWS()");
    $total = mysql_fetch_array($total);
    $total = $total['FOUND_ROWS()'];

 

Select row

$sql = "SELECT *
FROM tblItem
WHERE Itemnumber LIKE '$Itemnumber_input' ";

$result = mysql_query($sql) or die(mysql_error());

// Kör en loop som plockar alla poster rad för rad
while($row=mysql_fetch_array($result))
{
// Dela upp varje rad
$itemnumber=$row["Itemnumber"];
$itemname=$row["Itemname"];

echo "\t\n"; // Lägg in ny tabellrad
echo "\t\t$itemnumber\n"; // Visa posterna
echo "\t\t$itemname\n";
echo "\t\n";
}

mysql_free_result($result); // Free mysql resultset

 

Select like

$sql = "SELECT DISTINCT TypeName
FROM tblItem
WHERE Itemnumber LIKE '$Itemnumber_input'
AND Itemname LIKE '$Itemname_input'
AND Description LIKE '$Description_input'
AND LocalName LIKE '$LocalName_input'
AND BuarName LIKE '$BuarName_input'
ORDER BY TypeName ASC
LIMIT 0, 100"; // ASC - DESC



Select count

$sql = "SELECT COUNT(PGID) FROM tblPricegroup
WHERE PGID = '$PGID_input' ";

$result = mysql_query($sql) or die(mysql_error());
$array = mysql_fetch_array($result);
$count_pgid = $array['COUNT(PGID)'];
if ($count_pgid < 1)
{
action if no hits
}
else
{
action if at least one hit
}

Select join

$sql = "SELECT tblBridgeItemPrice.PGID
FROM tblBridgeItemPrice
INNER JOIN tblPricegroup
ON tblBridgeItemPrice.PGID = tblPricegroup.PGID
WHERE tblBridgeItemPrice.ItemID = '$itemid_select'
AND tblPricegroup.ResourceID = '$set_res_id'
ORDER BY tblBridgeItemPrice.Changed DESC
LIMIT 0 , 30 "; // ASC - DESC

 

Insert

$sql = "INSERT INTO prodx_a.tblPieceworktype (id, TypeName)
VALUES (NULL, 'Enskilt blandackord')";

INSERT INTO prodx_a.tblPieceworktype (id, TypeName)
VALUES (NULL , 'Gruppblandackord');

Delete

$sql = "DELETE FROM tblPricegroup
WHERE PGID = '$PGID_input' ";

// Send query and check if successful
if (mysql_query($sql))
{
$info[] = "Query success!";
}
else
{
$info[] = "Query failure!";
$error[] = mysql_error();
}

Update

| MySQL manual: Update |

$sql = "UPDATE tblPricegroup
SET OrderNo = '$OrderNo_input', ArticleNo = '$ArticleNo_input', CommonName = '$CommonName_input', Price = '$Price_input', MachineMin = '$MachineMin_input', Type = '$Type_input', Timecode = '$Timecode_input', ResourceID = '$ResourceID_input', ManpowerStd = '$ManpowerStd_input', Active = '$Active_input'
WHERE PGID = '$PGID_input' ";

$sql = "UPDATE tblItem SET BuarName = 'IKEA' WHERE BuarName = 'IKEA (Common Private Label)' ";

$sql = "UPDATE tblItem SET LocalName = Itemname WHERE LocalName = '' ";
if (mysql_query($sql)) {
$info[] = "Query success! $sql ";
}
else {
$info[] = "Query failure! $sql ";
$error[] = mysql_error();
}

UPDATE tblItem
SET LocalName = Itemname
WHERE (LocalName = ''
OR LocalName IS NULL)

Ändra del av text:

UPDATE tablename SET fieldname = REPLACE(fieldname, 'oldtext', 'newtext')

Beräkningar

update yourtable set column4 = (column1 * column2 * column3) / 100;

UPDATE tblTimestudy SET ManMin = MachineMin * Manpower

 

Update join

if (isset($_POST["btnUpdate"]))
{
$sql = "UPDATE tblEmployee
JOIN tblDepartment
ON tblEmployee.DepName = tblDepartment.DepMon
SET tblEmployee.DepID = tblDepartment.DepID
WHERE tblEmployee.DepID <> tblDepartment.DepID ";

if (mysql_query($sql))
{
$info[] = "Uppdateringen lyckades!";
}
else
{
$info[] = "Uppdateringen misslyckades! ";
$error[] = mysql_error();
}
$info[] = "sql: $sql ";
}

Group By

| mysql manual | Tizrag | databasejournal.com |

// --- Query to sum Plannumber per Ordernumber
$sql = "SELECT OrderNo, SUM(PlanNr)
FROM tblDispatch
WHERE ResourceID = '$set_res_id'
AND Shift = '$set_shift_id'
AND PlanDate = '$set_plandate'
AND Sortnr BETWEEN '$sortnr_start'
AND '$sortnr_end'
GROUP BY OrderNo
ORDER BY OrderNo ASC
LIMIT 0 , 100 ";
$result = mysql_query($sql) or die(mysql_error());
// Print out result
while($row = mysql_fetch_array($result))
{
echo $row['OrderNo']. " - ". $row['SUM(PlanNr)']. " st | ";
}

Subquery

| MySQL; manual |

$sql = "SELECT a.*, tblPieceworktype.TypeName, tblTimecode.Name, tblResource.Name AS Resource_Name
FROM tblPricegroup AS a
LEFT JOIN tblPieceworktype ON a.Type = tblPieceworktype.id
LEFT JOIN tblTimecode ON a.Timecode = tblTimecode.Code
LEFT JOIN tblResource ON a.ResourceID = tblResource.ResourceID
WHERE Price IN (SELECT Price FROM tblPricegroup WHERE OrderNo = '$OrderNo_search')";

Samma med ytterligare argument:

$sql = "SELECT a.*, tblPieceworktype.TypeName, tblTimecode.Name, tblResource.Name AS Resource_Name
FROM tblPricegroup AS a
LEFT JOIN tblPieceworktype ON a.Type = tblPieceworktype.id
LEFT JOIN tblTimecode ON a.Timecode = tblTimecode.Code
LEFT JOIN tblResource ON a.ResourceID = tblResource.ResourceID
WHERE Price IN (SELECT Price FROM tblPricegroup WHERE OrderNo = '$OrderNo_search' AND Active = 1)
AND a.ResourceID IN (SELECT ResourceID FROM tblPricegroup WHERE OrderNo = '$OrderNo_search')";

Search

| MySQL; manual | Tutorials; | PHP Freaks | devarticles |

 

Remote access

Contribs: | wiki |

config show mysqld

/sbin/e-smith/db configuration show > dbconfig.txt

Standard:

mysqld=service
LocalNetworkingOnly=yes
status=enabled

Databasanvändare i MYSQL måste tillåtas ansluta utifrån (%).

mysql -h 192.168.0.22 -u prodxuser -p

Teckenkodning

Nyckelord: | mysql iso-8859-1 to utf-8 | mysql latin1 to utf-8 |

Hjälp: | mysql unicode | dev.mysql charset-conversion |

Startade 091116 med att göra om prodX till UTF-8.
Gav upp 091118 eftersom åäö från databasen fortfarande visades fel på sidorna.

Filer

Ladda upp/ner filer med PHP och MySQL

| Tutorial 1 |

PHP-MySQL klass

| Tony Marston tutorial |

Kommentarer till sidan MySQL

Nytt meddelande

Det finns inga kommentarer till denna sidan.