Reply To: Php and SQL – Things we can do to make life better

@pokerseo 215135 wrote:

uh this is laborious and redundant, given that we can achieve the same result by using a .htaccess file to do redirects…

sample content of .htaccess file:

redirect 301 /jokerstars.com http://myaffillinkforjokerstars.com

The problem with a 301 redirect like this using htaccess is that you are still passing “link juice” to all these casinos and giving them tons of free backlinks. Those affiliate links also normally 301 redirect to their home page and that is how most of them get such high pr and start to outrank us. You could alternatively use a subdirectory link /casino/jokerstars then add a nofollow to all pages in /casino but once you start to get a big list of rewrites and/or redirects in your htaccess, it will start to have a tremendous load on your http server.

SQL setup –

Table `casinos`

id – Integer / 25 / auto Increment / Set as Key
name – VCHAR / 255
link – VCHAR /255

The MySQL command for that would be

CREATE TABLE `casinos` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(100),
`link` VARCHAR(255)
) ENGINE = MYISAM;[/CODE]

Integer 25 is not possible.

TINYINT – The signed range is –128 to 127. The unsigned range is 0 to 255.
SMALLINT – The signed range is –32768 to 32767. The unsigned range is 0 to 65535
MEDIUMINT – The signed range is –8388608 to 8388607. The unsigned range is 0 to 16777215
INT or INTEGER – The signed range is –2147483648 to 2147483647. The unsigned range is 0 to 4294967295
BIGINT – The signed range is –9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615

So the largest number you can use in an Integer field is 4294967295 or 10 characters length. The max length of a bigint is 20 so you will never have any type of integer with 25 characters in length.

then in casinolink.php

PHP Code:

1. $HTTP_GET_VARS has been deprecated since php 4.0.1 which was released almost 10 years ago. Use $_GET instead.

2. No need to mysql_real_escape_”STRING” an “INTEGER” value. A simple integer typecast is enough, which you do in the previous line anyways. So you can use inval($_GET[‘id’]) or simply (int) $_GET[‘id’];

3. There are very few circumstances where you should ever use @ for error suppression. I myself never use it as there are other ways to handle errors. If you want to hide errors from users in a production environment then turn off error reporting and log them to a file. Also, mysql_fetch_array() doesn’t throw any errors so there is no need to use @ anyways.

4. Although you have very few columns in each database record so you wont see a performance difference, you should know that mysql_fetch_array returns twice the data as mysql_fetch_assoc or mysql_fetch_row since it returns both a numeric and associative array when not using the second parameter result type. When I know I want an associative array I prefer to use mysql_fetch_assoc($result); or mysql_fetch_array($result, MYSQL_ASSOC); I actually use PDO or MySQLi mostly but I wont get into that here.

5. Using ‘http://pokerstars.com’,
‘bodog’ => ‘http://bodog.com’,
‘allslots’ => ‘http://allslots.com’,
//keep adding more here making sure you add a comma(,) at the end of each line
);

if(isset($_GET[‘id’]) && array_key_exists($_GET[‘id’], $links))
$link = $links[$_GET[‘id’]];
else
$link = ‘/’;

header(‘location: ‘ . $link);
exit();
[/PHP]

Sorry to bust up your thread but I figured this would all be helpful for the copy/paste happy php noobs.

PS – I am a professional developer and will be taking on a couple new clients in about 2 or 3 more weeks so if you need help with a project, keep me in mind.[CODE]CREATE TABLE `casinos` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(100),
`link` VARCHAR(255)
) ENGINE = MYISAM;[/CODE]

Integer 25 is not possible.

TINYINT – The signed range is –128 to 127. The unsigned range is 0 to 255.
SMALLINT – The signed range is –32768 to 32767. The unsigned range is 0 to 65535
MEDIUMINT – The signed range is –8388608 to 8388607. The unsigned range is 0 to 16777215
INT or INTEGER – The signed range is –2147483648 to 2147483647. The unsigned range is 0 to 4294967295
BIGINT – The signed range is –9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615

So the largest number you can use in an Integer field is 4294967295 or 10 characters length. The max length of a bigint is 20 so you will never have any type of integer with 25 characters in length.

then in casinolink.php

PHP Code:
include(“../configuration.php”); // Get SQL access
$id = ( isset($HTTP_GET_VARS) ) ? intval($HTTP_GET_VARS) : 0; / Pull in ID $
$id=mysql_real_escape_string($id); // Clean the string in case someone injects in URL
$get_rows=mysql_query(“select * from casinos where id=’$id'”);
$get_url=@mysql_fetch_array($get_rows);
$clickurl=$get_url[link]; // pull link to the casino for that ID

header(“Location: $clickurl”); // Redirect user to the casino
?>

1. $HTTP_GET_VARS has been deprecated since php 4.0.1 which was released almost 10 years ago. Use $_GET instead.

2. No need to mysql_real_escape_”STRING” an “INTEGER” value. A simple integer typecast is enough, which you do in the previous line anyways. So you can use inval($_GET) or simply (int) $_GET;

3. There are very few circumstances where you should ever use @ for error suppression. I myself never use it as there are other ways to handle errors. If you want to hide errors from users in a production environment then turn off error reporting and log them to a file. Also, mysql_fetch_array() doesn’t throw any errors so there is no need to use @ anyways.

4. Although you have very few columns in each database record so you wont see a performance difference, you should know that mysql_fetch_array returns twice the data as mysql_fetch_assoc or mysql_fetch_row since it returns both a numeric and associative array when not using the second parameter result type. When I know I want an associative array I prefer to use mysql_fetch_assoc($result); or mysql_fetch_array($result, MYSQL_ASSOC); I actually use PDO or MySQLi mostly but I wont get into that here.

5. Using $link = ‘/’; //Redirect user to homepage if they come without an id or the id doesn’t match our database

if(isset($_GET)) {

$id = (int) $_GET;
$result = mysql_query(“SELECT link FROM casinos WHERE id = $id LIMIT 0,1”);
if($row = mysql_fetch_row($result))
$link = $row[0];
}

header(‘location: ‘ . $link);
exit();
[/PHP]

Now instead of passing an id, you could pass the casino name instead so your query would look like

[PHP]
$name = mysql_real_escape_string($_GET);
$result = mysql_query(“SELECT link FROM casinos WHERE name = ‘$name’ LIMIT 0,1”);
[/PHP]

Then you can use mod rewrite to make those links look nice. Instead of having url’s like

/casino.php?id=1

You can have url’s like

/casino/pokerstars/

Also if you dont want to use a database for all this and create forms to create/read/update/delete (CRUD forms) you can do it all like this

[PHP]
$links = array(
‘pokerstars’ => ‘http://pokerstars.com’,
‘bodog’ => ‘http://bodog.com’,
‘allslots’ => ‘http://allslots.com’,
//keep adding more here making sure you add a comma(,) at the end of each line
);

if(isset($_GET) && array_key_exists($_GET, $links))
$link = $links[$_GET];
else
$link = ‘/’;

header(‘location: ‘ . $link);
exit();
[/PHP]

Sorry to bust up your thread but I figured this would all be helpful for the copy/paste happy php noobs.

PS – I am a professional developer and will be taking on a couple new clients in about 2 or 3 more weeks so if you need help with a project, keep me in mind.