PHP/MYSQL Help!!

[kaumilpatel]

Hey guys,

I'm having a problem. I don't know how to get this working. I have a database called "accounts", where there are accounts with URL's. I want to send traffic to one url, lets say for example:

http://www.url.com/trafficinput.php

How can I make it so that it distributes traffic amongst the members in the database upto a number that is specified in their "credits" row. Once it meets the specified number, the URL is removed from the rotation.

Could you please help? It would be much appreciated!

Thank you.

[MrAlaska]

It seems to me that you would increment a counter for the URL every time you directed somebody to that URL, then compare that to the 'credits' row and remove it. Perhaps I am missing something, here.

[kaumilpatel]

I have the account_status row also. So that maybe we could have it set the account_status = out_of_credits or something like that which removes it from the list.

How can we do this?

[MrAlaska]

In the SQL statement that collects the URL's, add a condition to ignore the ones you don't want.

i.e.
select (blah, blah)
from (blah, blah)
where (blah, blah)
and account_status <> 'out_of_credits'

[examancer]

not sure if this is exactally what you are looking for, but if you use 4 table columns like these:

id - (auto increment integer)
credits - (integer)
shown - (integer)
url - (text)

you could add more columns for additional account info, like the path to an image if its an ad rotation system, or another text field for a link discription, etc...

then, every time you call up a random ad like this:

SELECT id, url FROM accounts WHERE credits > shown ORDER BY rand() LIMIT 1

(limit is the number of urls you want to pull from the database)

then, after you pull the urls, take the id number and use it to increment each url you pulled from the database:

UPDATE accounts SET shown=shown+1 WHERE id=$id

(where $id is the variable where the id number is stored)

HTH

[kaumilpatel]

Still finding it difficult to get this done! 🙁 Could someone write me a template? For a trafficinput.php file?

database name: blazehits_com
table name: accounts
user: test1
pass: test2

aid: account id
url: where the url is located
credits: number of times to show
account_status: approved, denied, suspended, etc.

thanks a bunch!

[MrAlaska]

You need to add a counter column and update it similar to the column 'shown' in the hypothetical table described by Examancer. To only pull back 'approved' accounts, you would make that a condition of your SQL statement:

$sql="select aid, user, url, credits, shown
    from accounts
    where shown<=credits
    and account_status = 'approved'
    ";
$result=mysql_query($sql)
	or die ("problem with SQL query<p>$sql<P>".
	mysql_errno().": ".mysql_error());
while($row=mysql_fetch_array($result, MYSQL_ASSOC)){
    if($row[shown]>=$row[credits]){
          $ssql="update accounts set
            account_status = 'out_of_credits'
            where aid = $row[aid]
            ";
        mysql_query($ssql);
    }
    $ssql="update accounts
        set shown = shown+1
        where aid = $row[aid]
        ";
    mysql_query($ssql);
    //use php to generate the links, HTML, etc. here
    echo "<a href='$row[url]'>$row[user]</a><br>";
}

EDIT: This code contains all the bug fixes mentioned in the following posts. The full code including the MySql connections is on page two of this thread. This code and the code posted on page 2 is functional by c&p, at least on my system.

[kaumilpatel]

Great, so that checks to see if the account is out of credits, if not then it adds 1 more till it comes to lets say the credit of 5000. That's the checking part, but how do I make it goto the URL automatically from the database after it checks everything and adds the visit.

thanks for this help guys, i really appreciate it

[MrAlaska]

if 'user' is the name is the name you want to show in the link, add that to the select statement then use something like this in the area that is commented out:

echo "<a href='$request[url]'>$request[user]</a><br>";

Or

$i++;
echo "<a href='$request[url]'>Link no $i</a><br>";

or whatever

[kaumilpatel]

I'm going to copy and paste this, and edit it a bit, let's see if it works.

[kaumilpatel]

after connecting to the database, and all... i get this error:

Parse error: parse error, unexpected T_DOUBLE_ARROW in /home/blazehit/blazehits-www/temp/trafficinput.php on line 23

Here is what the script looks like:

<?

$hostname = "localhost";
$username = "TEST1";
$password = "BLAT1";
$dbName = "blazehits_com";

MYSQL_CONNECT($hostname, $username, $password) OR DIE("Unable to connect to database");

@mysql_select_db( "$dbName") or die( "Unable to select database");

//(example only, untested syntax)
$sql="select aid, user, url, credits, shown
from accounts
where shown<=credits
and account_status = 'approved'
";
while($result=mysql_query($sql, MYSQL_ASSO)){
if($result[shown]=>$result[credits]){
$ssql="update table accounts set
account_status = 'out_of_credits'
where aid = '$result[aid]'
";
mysql_query($ssql);
}else{
$ssql="update table accounts
set shown = shown+1
where aid = '$result[aid]'
";
mysql_query($ssql);
}
//use php to generate the links, HTML, etc. here
echo "<a href='$request[url]'>$request[user]</a><br>";
}

mysql_close();

?>

[MrAlaska]

I had a the equal and greater signs reversed:
if($result[shown]>=$result[credits]){
It should now be ok, at least as far as the PHP.

My phone went off, and I have whiskey to drink and photo albums to marvel at.

Good luck, talk with ya later

[kaumilpatel]

Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in /home/blazehit/blazehits-www/temp/trafficinput.php on line 22

thats the error now 🙁

[abda53]

whats on line 22?

[kaumilpatel]

line 22: while($result=mysql_query($sql, MYSQL_ASSO)){

[abda53]

hmm.. must not be a valid MySQL-link resource

[kaumilpatel]

!?!?

[kaumilpatel]

anyone?

[MrAlaska]

Oops, sorry. I left out a minor, yet crucial step. I edited it once again, give the latest version a try and see what happens.

[kaumilpatel]

It now gives me the following error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/blazehit/blazehits-www/temp/trafficinput.php on line 23