Switch Proxies Every 5 min.

[Lukeidiot]

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"

 

 

Any suggestions?

[jonsjava]

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 February 24, 2014 by jonsjava

[Lukeidiot]

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.

[Lukeidiot]

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?

 

 

Table:

Edited February 24, 2014 by Lukeidiot

[jonsjava]

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.

[Lukeidiot]

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.

[jonsjava]

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;

[Lukeidiot]

 

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.

[jonsjava]

That is why you select after reset, without the date factor.  you find the one that is next, set the date_started, and go at it all over again.

[Lukeidiot]

That is why you select after reset, without the date factor.  you find the one that is next, set the date_started, and go at it all over again.

 

That is the part I cant understand

 

 

I cant seem to think when to add it as started

[jonsjava]

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 February 24, 2014 by jonsjava

[jonsjava]

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.

[Lukeidiot]

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?

 

[jonsjava]

Correct, or at the next time it is called, if the current proxy was activated greater than 5 minutes ago.

[Lukeidiot]

Correct, or at the next time it is called, if the current proxy was activated greater than 5 minutes ago.

 

Hmm, I cant seem to get it to work, its only returning blank.

 

Edited February 24, 2014 by Lukeidiot

[jonsjava]

Did you call the function?

 

$proxy = getProxy();
echo $proxy;

[Lukeidiot]

Did you call the function?

 

$proxy = getProxy();
echo $proxy;

Yes sir; 

 

[jonsjava]

Again, this wasn't tested code. Have you checked the error logs, or turned error_reporting to E_ALL for debug?

[jonsjava]

I am exhausted after hiking half the day, and I have work in 6 hours. If you can wait, just PM me, and I'll help from there in the morning. If not, I'm sure other great PHP guys/gals here can help out in sorting out my code.

[jazzman1]

 

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