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

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

Yes that’s possible as well, although in a bigger picture where SQL contains ALL the data for affiliates / casinos / bonuses via web forms reverting back to htaccess redirects becomes less beneficial. Also Adding a simple update line into the above code will also allow you to collect click information per link.

As with any solution there are many different ways to achieve the end result, it all depends on what you want to do ” title=”” class=”bbcode_smiley” />

Would also be interesting on how .htacces liks such as that are indexed.

@allfreechips 215144 wrote:

Yes that’s possible as well, although in a bigger picture where SQL contains ALL the data for affiliates / casinos / bonuses

well it’s not necessarily a bigger picture, just a different one. [edit, ah okay i see what you mean, you have loads of info relating to each casino/room/offer in DB already i guess.. fair enough ” title=”” class=”bbcode_smiley” />]

@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.

Or you could use a program like pingQast ::: Ping and shorten URLs that does it all for you if you install a simple plugin, with that you can do split testing and geo-targetting and get complete analytics on your traffic ” title=”” class=”bbcode_smiley” />

@Rob472 216044 wrote:

The problem with a 301 redirect like this using htaccess is that you are still passing “link juice” to all these casinos .

Uh No, we add the rel=”nofollow” attribute to the link which is 301’d. saving us any php code or having to update the script later when some functions are deprecated in later versions of PHP.

just b/c we can write code to do stuff doesn’t mean it’s the most elegant solution fwiw.

but why add rel=”nofollow” to hundreds of links when you can add a single line to robots.txt to disallow /casino.php or /casino/

Also, with only a few links that may be ok but every request to every page on your server, apache has to parse every line and check for a match against the url. This can easily crash your http server with a moderate amount of traffic or at least slow it down enough to make users want to click the back button.

I am not saying what your doing is wrong. I am just suggesting more efficient ways to do it.

the robots.txt disallow directive has nothing to do with PR passing, it’s related to indexation, so as is your script is actually passing juice.

Depreciated code is valid as more people than you think will run php4 still these days and its still valid today. Although we should all be current not everyone is of course.

As with anything including cats, theres a hundred ways of doing stuff and the dicussion is what we are lookng for on these idea!

Thanks for your input Rob!