Jump to content

Switch Proxies Every 5 min.


Lukeidiot

Recommended Posts

Hello fine people of phpfreaks,

 

I am trying to devise a way to only use 1 proxy for 5 minutes, once 5 minutes has passed, I need to mark that proxy as used, until all available proxies are used, then it should reset all and start over.

 

It should work like this:

 

-> proxy1 selected, use for 5 minutes, then go to next proxy...

-> proxy2 selected, use for 5 minutes, then go to next proxy...

-> proxy3 selected, use for 5 minutes, then go to next proxy...

-> //end of proxies

-> all proxies used, reset all, and reuse.

 

Here is my table called "proxies"

 

b30fecfab7f3d7f3832e92f735b3665f.png

 

Any suggestions?

Link to comment
Share on other sites

add a column with datetime, and name it "date_started" with an index on it.

 

When the time now - INTERVAL 5 MINUTE > date_started, mark it used, and go on to the next.

 

run a select COUNT(id) on proxies where used != 1 when you mark one used.  If count comes back zero, update table proxies set used = 0; then start over

 

(I intentionally didn't give the exact proper SQL syntax, so you have a little work to do)

Edited by jonsjava
Link to comment
Share on other sites

add a column with datetime, and name it "date_started" with an index on it.

 

When the time now - INTERVAL 5 MINUTE > date_started, mark it used, and go on to the next.

 

run a select COUNT(id) on proxies where used != 1 when you mark one used.  If count comes back zero, update table proxies set used = 0; then start over

 

(I intentionally didn't give the exact proper SQL syntax, so you have a little work to do)

 

Thank you Jon, I will update you on my progress.

Link to comment
Share on other sites

add a column with datetime, and name it "date_started" with an index on it.

 

When the time now - INTERVAL 5 MINUTE > date_started, mark it used, and go on to the next.

 

run a select COUNT(id) on proxies where used != 1 when you mark one used.  If count comes back zero, update table proxies set used = 0; then start over

 

(I intentionally didn't give the exact proper SQL syntax, so you have a little work to do)

 

Like this?

 

502e55d7e046fb81a5851606e0b64e8d.png

 

Table:

35a3cabfdce23f8c56e7f2c1827abd08.png

Edited by Lukeidiot
Link to comment
Share on other sites

not quite.

 

NOW() is what you want, so it would be:

 

 

SELECT proxy  FROM `proxies` WHERE date_started > NOW() - INTERVAL 5 MINUTE and used=0;

 

This will find any proxy who has a start time more recent than 5 minutes before the current time (i.e., it's still current)

 

make sure to count the results before using them, and if they are zero, rinse and repeate.

Link to comment
Share on other sites

not quite.

 

NOW() is what you want, so it would be:

SELECT proxy  FROM `proxies` WHERE date_started > NOW() - INTERVAL 5 MINUTE and used=0;

This will find any proxy who has a start time more recent than 5 minutes before the current time (i.e., it's still current)

 

make sure to count the results before using them, and if they are zero, rinse and repeate.

 

When and how often do I set the date_started? I cant seem to comprehend how to not overwrite it each time.

Link to comment
Share on other sites

When a proxy expires, you would do this:

 

 

UPDATE proxies SET used=1 WHERE proxy='CURRENT PROXY IP GOES HERE'; SELECT proxy FROM proxies WHERE used=0 LIMIT 1 ORDER BY id ASC;

 

If that comes up with zero results, you run this:

 

UPDATE proxies SET used=0; SELECT proxy FROM proxies WHERE used=0 LIMIT 1 ORDER BY id ASC;
Link to comment
Share on other sites

 

When a proxy expires, you would do this:

UPDATE proxies SET used=1 WHERE proxy='CURRENT PROXY IP GOES HERE'; SELECT proxy FROM proxies WHERE used=0 LIMIT 1 ORDER BY id ASC;

If that comes up with zero results, you run this:

UPDATE proxies SET used=0; SELECT proxy FROM proxies WHERE used=0 LIMIT 1 ORDER BY id ASC;

 

I understand that mostly, but what happens when I check for date_started when its set to default, or 0? Once everything resets, it may break the comparisons.

Link to comment
Share on other sites

This was bashed together, not tested, and not made in good coding standards.  My IDE screamed at me the whole time, but I put my headphones on while it wept.

 

function getProxy(){
    /* Database connection stuff goes here */
    $base_sql = "SELECT proxy  FROM `proxies` WHERE date_started > NOW() - INTERVAL 5 MINUTE and used=0;";
    $get_expired_sql = "SELECT id  FROM `proxies` WHERE USED=0 ORDER BY id ASC limit 1;";
    $expired_sql = "UPDATE proxies SET used=1 WHERE id='$id'; SELECT proxy FROM proxies WHERE used=0 LIMIT 1 ORDER BY id ASC;";
    $reset_proxy_sql = "UPDATE proxies SET used=0; SELECT proxy FROM proxies WHERE used=0 LIMIT 1 ORDER BY id ASC;";
    /* First, lets see if we have an active proxy */
    $res = mysql_query($base_sql);
    if (mysql_num_rows($res) == 0){
        // Well, that one is expired. Lets see which one is next
        $res = mysql_query($get_expired_sql);
        $row = mysql_fetch_assoc($res);
        $id = $row['id'];
        $res = mysql_query($expired_sql);
        $row = mysql_fetch_assoc($res);
        if (mysql_num_rows($row) == 0){
            // Well, we're out of current proxies. time to reset
            $res = mysql_query($reset_proxy_sql);
            $row = mysql_fetch_assoc($res);
            $proxy = $row['proxy'];
            mysql_query("UPDATE proxies set date_started=NOW() where proxy='$proxy';");
        }
        else{
            $proxy = $row['proxy'];
            mysql_query("UPDATE proxies set date_started=NOW() where proxy='$proxy';");
        }
    }
    else{
        $row = mysql_fetch_assoc($res);
        $proxy = $row['proxy'];
    }
    return $proxy;
}

 

** EDIT: forgot to return something...*sigh*

***EDIT 2: Man, I need to spend more than 5 minutes on code. I forgot to update the proxies to tell it the start time.

Edited by jonsjava
Link to comment
Share on other sites

Forgot to mention: this does not switch the proxy every 5 minutes. It merely says that if a proxy has a start time greater than 5 minutes, switch it.  If this isn't called within that 5 minute window, it will do no good. It might be better to use cron for this.

 

So you are saying it does switch, IF its called within the 5 minute period?

 

052607f45c3a64f514a4d622f90d1142.png

Link to comment
Share on other sites

 

I need to mark that proxy as used, until all available proxies are used, then it should reset all and start over.

 

No need to mark anything to used or not, just create a second table which map all rows from the main table. Because it is 3:00 AM and I have to go to work in 5 hours my example will be simple and fast.

 

Create two tables - proxy and proxyMap.

CREATE TABLE `proxy` (
  `proxy_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `proxy_ip` varchar(45) NOT NULL,
  PRIMARY KEY (`proxy_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

// insert values
INSERT INTO `proxy` VALUES (1,'123.123.123.1'),(6,'123.123.123.2'),(3,'123.123.123.3'),(4,'123.123.123.4'),(5,'123.123.123.5');

// result of select statement
mysql> select * from proxy;
+----------+---------------+
| proxy_id | proxy_ip      |
+----------+---------------+
|        1 | 123.123.123.1 |
|        2 | 123.123.123.2 |
|        3 | 123.123.123.3 |
|        4 | 123.123.123.4 |
|        5 | 123.123.123.5 |
+----------+---------------+
5 rows in set (0.00 sec)


// create proxyMap tbl

CREATE TABLE `proxyMap` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `proxy_id` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

// select proxyMap
mysql> select * from proxyMap;
Empty set (0.00 sec)

Then do an update to proxyMap:

UPDATE test.proxyMap 
SET 
    proxyMap.proxy_id = case proxyMap.proxy_id
        WHEN
            (SELECT 
                    COUNT(proxy.proxy_id)
                FROM
                    test.proxy
                WHERE
                    1)
        THEN
            1
        ELSE proxyMap.proxy_id + 1
    END

mysql> select * from proxyMap;
+----+----------+
| id | proxy_id |
+----+----------+
|  1 |        1 |
+----+----------+
1 row in set (0.00 sec)

So, there are few important things here I need to mention!

 

1) Never delete rows between 1 and 5 doing a gap.

2) If some ip address is not longer available just update the rows with new one or replace it with one from the table.  

3) You can insert as many rows as you want but in increment order.

 

Then, join the tables on their proxy_id!

SELECT 
    p.proxy_id, p.proxy_ip
FROM
    test.proxy p
        INNER JOIN
    test.proxyMap m USING (proxy_id)
WHERE m.id = 1

// Result after second update
+----------+---------------+
| proxy_id | proxy_ip      |
+----------+---------------+
|        3 | 123.123.123.3 |
+----------+---------------+
1 row in set (0.00 sec)

To run this update statement on schedule you have (at least) 2 options.

1) Use a cron job

2) Use  - https://dev.mysql.com/doc/refman/5.1/en/events.html

 

For more information tomorrow :)

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.