How to make it shorter and better?

[AlGorr]

Hello,,,

I don't know much about PHP+SQL but I have to find the better way to make my
query work and to be shorter and better.
All help will be appreciated.

//Check if IP exists in banIP
$res = mysql_query("SELECT ip FROM banIP WHERE ip =
'".$_SERVER['REMOTE_ADDR']."'");
$res = mysql_num_rows($res);
if($res != 0)
die;
mysql_free_result($res);

//Fetch Time
$timestamp = time();
$timeout = $timestamp - 900;
//Delete Users
$del = mysql_query("DELETE FROM caspionet WHERE timestamp<$timeout");

//Check if IP exists in caspionet
$res = mysql_query("SELECT ip FROM caspionet WHERE ip =
'".$_SERVER['REMOTE_ADDR']."'");
$res = mysql_num_rows($res);
if($res == 0){
//Insert User
$ins = mysql_query("INSERT INTO caspionet (timestamp, ip)
VALUES('$timestamp','".$_SERVER['REMOTE_ADDR']."')");
}
mysql_free_result($res);

//Fetch Users Online
$res = mysql_query("SELECT DISTINCT ip FROM caspionet");
while ($row = mysql_fetch_array($res, MYSQL_BOTH)){
print("IP:$row[0]");
}
mysql_free_result($res);

Thank you
A.K.

[Sandman]

In article <e2**********@news.tiscali.fr>, "AlGorr" <[e-mail]> wrote:

<snip>

It's pretty tight as it is.
# Setup
$ip = $_SERVER["REMOTE_ADDR"];
$timestamp = time()-900;

# Should we die?
$c = mysqlquery("select count(id) from banIP where ip = '$ip'");
list ($count) = mysql_fetch_assoc($c);
if ($count) die;

# Remove old stuff
mysql_query("delete from caspionet where timestamp < $timestamp");

# In Caspionet?
$c = mysqlquery("select count(id) from caspionet where ip = '$ip'");
list ($count) = mysql_fetch_assoc($c);
if (!$count){
mysql_query("insert into caspionet...");
}

# Who's online?
$ips = mysql_query("select distinct ip from caspionet");
while ($r = mysql_fetch_assoc($ips)){
print "IP: $r[ip]\n";
}


--
Sandman[.net]

[AlGorr]

Sandman thanks a lot !!!!!!!!!!!!!!!!!!!
That's much better! I will use your exemple.

Only look:
we have to ask 4 times the server for these 4 queries. It's a SQL question.
Is it possible to put for ex. 2 queries in one big and make work the server
more faster?

Regards
A.K.

[Jerry Stuckle]

AlGorr wrote:

Hello,,,

I don't know much about PHP+SQL but I have to find the better way to make my
query work and to be shorter and better.
All help will be appreciated.

//Check if IP exists in banIP
$res = mysql_query("SELECT ip FROM banIP WHERE ip =
'".$_SERVER['REMOTE_ADDR']."'");
$res = mysql_num_rows($res);
if($res != 0)
die;
mysql_free_result($res);

//Fetch Time
$timestamp = time();
$timeout = $timestamp - 900;
//Delete Users
$del = mysql_query("DELETE FROM caspionet WHERE timestamp<$timeout");

//Check if IP exists in caspionet
$res = mysql_query("SELECT ip FROM caspionet WHERE ip =
'".$_SERVER['REMOTE_ADDR']."'");
$res = mysql_num_rows($res);
if($res == 0){
//Insert User
$ins = mysql_query("INSERT INTO caspionet (timestamp, ip)
VALUES('$timestamp','".$_SERVER['REMOTE_ADDR']."')");
}
mysql_free_result($res);

//Fetch Users Online
$res = mysql_query("SELECT DISTINCT ip FROM caspionet");
while ($row = mysql_fetch_array($res, MYSQL_BOTH)){
print("IP:$row[0]");
}
mysql_free_result($res);

Thank you
A.K.

Please cross-post - don't multipost!

Answered in comp.databases.mysql.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[Sandman]

In article <e2**********@news.tiscali.fr>, "AlGorr" <[e-mail]> wrote:

Sandman thanks a lot !!!!!!!!!!!!!!!!!!!
That's much better! I will use your exemple.

Only look:
we have to ask 4 times the server for these 4 queries. It's a SQL question.
Is it possible to put for ex. 2 queries in one big and make work the server
more faster?

Nah, not more efficient. It's still four queries with four different
rules. They all need to be executed separately.

--
Sandman[.net]

[Colin McKinnon]

Sandman wrote:

In article <e2**********@news.tiscali.fr>, "AlGorr" <[e-mail]> wrote:

Sandman thanks a lot !!!!!!!!!!!!!!!!!!!
That's much better! I will use your exemple.

Only look:
we have to ask 4 times the server for these 4 queries. It's a SQL
question. Is it possible to put for ex. 2 queries in one big and make
work the server more faster?

Nah, not more efficient. It's still four queries with four different
rules. They all need to be executed separately.

Actually you could do strange things with cartesian products but it may be
messy depending on how the database is set up. Presumably if AlGorr needs
to look at it it's because there is a performance issue. First place to
start with performance issues in database queries is.....the database. Not
the application language.

C.

[Sandman]

In article <aJ*************@newsfe2-gui.ntli.net>,
Colin McKinnon
<co**********************@ntlworld.deletemeunlessU RaBot.com> wrote:

Sandman wrote:

In article <e2**********@news.tiscali.fr>, "AlGorr" <[e-mail]> wrote:

Sandman thanks a lot !!!!!!!!!!!!!!!!!!!
That's much better! I will use your exemple.

Only look:
we have to ask 4 times the server for these 4 queries. It's a SQL
question. Is it possible to put for ex. 2 queries in one big and make
work the server more faster?

Nah, not more efficient. It's still four queries with four different
rules. They all need to be executed separately.

Actually you could do strange things with cartesian products but it may be
messy depending on how the database is set up. Presumably if AlGorr needs
to look at it it's because there is a performance issue. First place to
start with performance issues in database queries is.....the database. Not
the application language.

Indeed - I didn't interprete "shorter and better" as being that,
though. BUt now that you say it...

Indexes is a good place to start for single-value lookups

--
Sandman[.net]