Coding Tips - MySQL

• Tip: Making The Connection
• Tip: Getting Only Numbers
• Tip: Custom Row Sorting

To establish a connection to MySQL with PHP, you should place the following script inside a folder "mysql/connect.php" and include it at the top of every page that you wish to access your database. Included in this folder, should also be an .htaccess file with the text "deny from all" included in it. This will help protect your connection details from unscrupulous people.

<?php
$connection=mysql_connect("localhost","user","password");
if (!$connection) {
echo "Could not connect to MySql server!";
exit;
}
$db=mysql_select_db("database",$connection);
if (!$db) {
echo "Could not select database";
exit;
}
?>

This code (preg_replace) will remove everything in a string except the numbers.

<?php
$sentence = 'I have 44 dollars.';
$numbers = preg_replace("/[^0-9]/","", $sentence);
echo $numbers;  //outputs 44
?>

The following code will change the order of a received row by changing the number in a custom column 'custom_order' (not the rows id) with the row either above or below it.

<?php
$action = $_GET['action'];
$id = preg_replace("/[^0-9]/","", $_GET['id']);

if($action == "up")
{
	//get my_order
	$result = mysql_query("SELECT custom_order FROM table WHERE id = '$id'");
	while($row = mysql_fetch_array($result))
	{
	$my_order = $row['custom_order'];
	$new_order = $my_order - 1;
	}

	//set replacements new order
	$result = mysql_query("SELECT id FROM table WHERE custom_order < '$my_order' ORDER BY custom_order DESC LIMIT 1");
	while($row = mysql_fetch_array($result))
	{
	mysql_query("UPDATE table SET custom_order = '$my_order' WHERE id = '$row[id]'");
	}

//set my order
mysql_query("UPDATE table SET custom_order = '$new_order' WHERE id = '$id'");
}

if($action == "down")
{
	//get my_order
	$result = mysql_query("SELECT custom_order FROM table WHERE id = '$id'");
	while($row = mysql_fetch_array($result))
	{
	$my_order = $row['custom_order'];
	$new_order = $my_order + 1;
	}

	//set replacements new order
	$result = mysql_query("SELECT id FROM table WHERE custom_order > '$my_order' ORDER BY custom_order ASC LIMIT 1");
	while($row = mysql_fetch_array($result))
	{
	mysql_query("UPDATE table SET custom_order = '$my_order' WHERE id = '$row[id]'");
	}

//set my order
mysql_query("UPDATE table SET custom_order = '$new_order' WHERE id = '$id'");
}
?>