Limiting link insertion from MySQL

Happy Ho, Yo!

Hey, I've got a MySQL database with around 20k records running a site for me. What I'd like to do is use all those dynamic pages to push backlinks to my money pages.

The catch is, I don't want to push 20k links to 1 money page. I'd like to limit how many links go to any particular page. I've seen it done, but now I can't find it anywhere.

Any ideas?

I'm not sure exactly what you mean; a content pager perhaps? generating pages with X links only?

If you give me more details, I'll probably be a lot more helpful, but in the mean time, in case you didn't know, you can use an SQL query like this to select X records:

SELECT field1, field2, field2 FROM table_name WHERE condition LIMIT 0, X

Where X is the top numbers of records you would like to fetch from the database.

thanks thaorius!

Let me try again: (sorry I wasn't clear, I need more eggnog...)

I have a dynamic website that will generate around 20k pages from a mysql database. I want those 20k pages to show backlinks to a totally different website (money page).

the catch is, I don't want 20k backlinks. I don't need that many and G would prolly consider it spam.

ideally, I would like a way to have the dynamic site show varying backlinks in varying amounts:

moneypage 1 = 100 backlinks
moneypage 2 = 2000 backlinks
etc

but not necessarily all on the same pages. ie. if I had 40 money pages, I don't want 40 backlinks showing up on 1 dynamically generated page.

Now that I'm writing this out, I don't think that's even possible. i think the backlinks could be:

1. rotated
2. show up all at once

but not my ideal fantasy. but if it could, that would be awesome. What do you think?

edit: if this still makes no sense, check out:

and scroll down to "The Foundation" and you'll see what I'm trying to do.

After reading the post on Blue Hat SEO, which happens to be quite similar to an structure I have in mind this days, I would recommend a few things:

1) If possible, don't use a "database site", but instead, generate 20k/whatever static files and use that instead when possible. Your server will thank you.

2) When doing pure static files isn't viable, use both a disk and memory based cache.

2) For the backlinks, you could add a new table to your databases, let's call it tbl_blinks for the sake of this post. This table would have the following fields:

id - Backlink id
external_id - The id for each of your 20k pages
text - Text of the link
url - Where to?

Now, in order to add backlinks, you would just go about doing a query for a specific external_id; this ID would be that of the current page and the query would look like:

SELECT text, url FROM tbl_blinks WHERE external_id=X;

Now you have all the links for that page, so you go about caching the query result, and then you just display the links wherever you like.

As for adding the backlinks to your tables, assuming you are following the process the author describes in the post, you would have to determine the sites and pages you want to add the backlinks to, and then just insert them in the tbl_blinks table for those sites.

A word on performance: By using this approach, you get fixed links on each page, which I find nicer than the rotating ones. However, you would have a gigantic table of backlinks, which would make queries to the table slow, and that's why you should use a cache.

Now, if you want to just implement the backlinks on a rotated system, I would suggest you add a field to the table where your pages are, lets name it blink_count. Now add a new table and lets name it like the previous one, tbl_blinks. This table would be identical to the one with fixed links, but without the external_id field.

In such a way, in order to add links, you would just update the blink_count field for the page, and if necessary, add your new links to tbl_blinks. And to query the data, you would fetch the blink_count of the current page, then fetch the complete list of tbl_blinks (which should not be large), cache it, and randomly pick blink_count links from it. Do note that I'm fetching the whole tbl_blinks table here, you don't want to do that in all requests if it can be avoided, and I can't stress this enough.

I'm assuming you know how to program, at least to a reasonably basic level, and as such I'm not including any actual code snippet.

Now, does that sound like the structure you want to setup?

thanks given and +rep! I've read your post 4 times now trying to absorb everything you've written. a few questions, as i taught myself basic php/mysql just doing this project (ok, I lied, I used dreamweaver cs4, but I still had to learn some crap to make it all work..)

1. Now that I know how to make 20k dynamic pages, how do I go about making them static. Surely, I'm not handcoding 20k pages. There must be a way to use the database to make those pages automagically generate...

2. I understand what you're saying about caching - where can I learn more about how to do that? I can follow the concept, but have no idea how to do it. I'm willing to go out and learn, though.

3.

2) For the backlinks, you could add a new table to your databases, let's call it tbl_blinks for the sake of this post. This table would have the following fields:

id - Backlink id
external_id - The id for each of your 20k pages
text - Text of the link
url - Where to?

Now, in order to add backlinks, you would just go about doing a query for a specific external_id; this ID would be that of the current page and the query would look like:

SELECT text, url FROM tbl_blinks WHERE external_id=X;

Now you have all the links for that page, so you go about caching the query result, and then you just display the links wherever you like.

Click to expand...

I follow you here - so to limit the amount of links, basically, I would just limit the amount of external ID's I insert the links on, yes?

Also, how do you actually insert the links once you've chosen the external ID's? I think I missed that part...

Thank you so much for such a detailed explanation, it's really appreciated!

1) An easy way would be to write an small script that goes through a list containing the URL every page on your website (I'm sure you can generate a list from the database); then fetches the page via HTTP, like any browser would do, and saves the files locally. Say one URL is mysite.com/category/bazingaa/content011, your script would save the page to category/bazingaa/content011, within a directory of your choosing.

Later on, you just upload the whole directory with pages, and point your browser to it instead of the dynamic site. You'll also have to copy the graphics and CSS sheets though.

Note: If you go for this approach, don't delete the old site or database from the server, if you make changes, you would need to regenerate the pages.

It would probably be more practical to use a cache instead of purely static files, since it will save you some time. It will cost you some performance though.

2) I see you are using PHP, so I'm going to recommend you take a look at the Zend framework, it has quite a large library of common use classes, and you don't need to use the whole framework in order to take advantage of them, you can just import what you need.

Particularly, take a look at:
http://framework.zend.com/manual/en/zend.cache.html
http://framework.zend.com/manual/en/zend.cache.backends.html#zend.cache.backends.file
http://framework.zend.com/manual/en/zend.cache.backends.html#zend.cache.backends.memcached

The file based cache will not require anything special and can be used on just about any server, which is handy if you don't yet run on a dedicated server. It will however cost you a disk seek and read for all files not internally cached by the operative system, and those don't come cheap, performance wise.

Memcached is basically a *nix daemon that sits on your server doing nothing and stores all your pages in memory, making it considerably fast, at the cost of RAM.

I would say you use both. Use memcached for your most requested pages and set a maximum amount of RAM for memcached to use. Then, when you need to fetch something from the cache, you first ask memcached, if memcached doesn't have what you are looking for, ask the file cache, if you still haven't found what you are looking for, you dynamically generate the content, and store it on both memcached and the file caches.

Now, all these techniques and caches are great if you are making the script that your sites run on, but that won't be the case most of them, and for that, I would suggest you use a "bridge", so to speak.

If you are using Apache as your webserver, you can set on your web site's root directory, inside the .htaccess file, that you want all request to be redirected to a particular script. The script will be given the requested URL, which can then be used to do the aforementioned operation with memcached and the file based cache. In the dynamic generation, you would just let the script running on the website generate the page by, usually, including it's main page (usually the index.php file) with a simple require()/include() call.

The above approach will give you fine grained control on how to handle your caches to the last detail, but you don't usually need that. Because of this, there are also other less "hands on" approaches to web site caching; namely, SQUID (http://www.squid-cache.org/), which is an HTTP cached proxy server. I can't give you detailed information on how to use it's cache features as I only use it as my proxy server of choice. I'll have to refer you to the official manual on this one.

There are also some Apache modules for caching and other stuff that I've never needed to use, but it might be worth checking them out. A quick google search will give you some names .

I follow you here - so to limit the amount of links, basically, I would just limit the amount of external ID's I insert the links on, yes?

Click to expand...

Yes, exactly .

And you insert them with:

INSERT INTO tbl_blinks(external_id,text,url) VALUES('the chosen external id', 'Click Here', 'http://www.hello.com/i/am/a/link');

P.S.: The ';' at the end of the SQL queries will give you an error if you pass it at the end of an SQL query string passed to mysql_query(). So, remove them if you are using the standard mysql extension.

If you have any more questions, just ask .