Help simplifying a php script to lower server resources

B

brunoais

New Member
Messages
115
Reaction score
0
Points
0
Ok I have this php text:
There is already coding that start the db connection.
There are already some variables defined

Ok This code is completely functional but it uses a lot the mysql_query().
I'd like to ask for your assistance to help me reduce the server load without lowering the functionality
If I could reduce to one single query it would be wonderful!
(I accept any suggestion that works!)

PHP: 
// create a new cURL resource
$ch = curl_init();

// set URL and other appropriate options
curl_setopt($ch, CURLOPT_URL, SERVER_URL."/map.sql");
curl_setopt($ch, CURLOPT_HEADER, 0);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1); 


$result= curl_exec($ch);
$ally=explode(';
', $result);


// close cURL resource, and free up system resources and accelerate the process
curl_close($ch);

?>
<INPUT TYPE="image" SRC="img/ok.gif">
<br><br>
<?=text('dba_3')?>
<?
//apaga a tabela para adicionar os dados
mysql_query("TRUNCATE x_world") OR die(mysql_error());

foreach( $ally as $ally ) {

$allyy=explode("'", $ally);

$n=count($allyy);

$aid_1=str_replace(",","",$allyy[$n-3]);

$aid =explode(';',Wings);

$i = 0;
do {
if ($aid[$i]==$aid_1)
{
//echo $ally;
mysql_query($ally)OR die(mysql_error());
}
$i++;
} while ($i <((count($aid))));
}
?>
<INPUT TYPE="image" SRC="img/ok.gif">
<br><br>
<?=text('dba_4')?>...
<?
$sql='update '.PREFIX.'dorf v, x_world x
SET v.size = x.population, v.name = x.village
where v.x = x.x and v.y = x.y';
mysql_query($sql)OR die(mysql_error());
?>
<INPUT TYPE="image" SRC="img/ok.gif">
<br><br>
<?=text('dba_5')?>...
<?
$sql='truncate table '.PREFIX.'xtmp_uid';
mysql_query($sql)OR die("<br> ".mysql_error());

$sql='insert into '.PREFIX.'xtmp_uid
select distinct uid, player, tid, aid, alliance
from x_world';
mysql_query($sql)OR die("<br> ".mysql_error());

$sql='truncate table '.PREFIX.'xtmp_aid';
mysql_query($sql)OR die("<br> ".mysql_error());

$sql='insert into '.PREFIX.'xtmp_aid
select distinct aid, alliance
from x_world';
mysql_query($sql)OR die("<br> ".mysql_error());
?>
<INPUT TYPE="image" SRC="img/ok.gif">
<br><br>
<?=text('dba_6')?>...
<?
$sql='update '.PREFIX.'xallys a, '.PREFIX.'xtmp_aid t
SET a.alliance = t.alliance
where a.aid = t.aid';
mysql_query($sql)OR die("<br> ".mysql_error());
?>
<?
$sql='UPDATE '.PREFIX.'spieler s LEFT OUTER JOIN '.PREFIX.'xtmp_uid u ON s.uid = u.uid
SET s.aid = u.aid';
mysql_query($sql)OR die("<br> ".mysql_error());
?>
<INPUT TYPE="image" SRC="img/ok.gif">
<br><br>
<?=text('dba_7')?>

</div>

</body>
</html>
 
quantum1

quantum1

New Member
Messages
68
Reaction score
0
Points
0
Well....I don't think you can combine the above into one query since you are using update statements on different tables. The server load issue is unclear since you are looping using a variable called $ally and then executing sql based on $ally. Since the value(s) of $ally are not shown above it is hard to comment on that part of your source.
 
B

brunoais

New Member
Messages
115
Reaction score
0
Points
0
Last edited:
xPlozion

xPlozion

New Member
Messages
868
Reaction score
1
Points
0
you can use multiple tables in one query by using joins, but you cannot combine a truncate and insert into one command. each of those must have their own query.

possible join examples are:

PHP: 
mysql_query("SELECT u.username, p.signature, p.avatar FROM username AS u LEFT JOIN profile AS p ON u.uid=p.uid WHERE u.uid='".mysql_escape($_GET['id'])."' LIMIT 1");

you can also use inner join, right join, as well as left join above. they all work the same way, it's just the data they return differ.

http://www.w3schools.com/sql/sql_join.asp
http://answers.yahoo.com/question/index?qid=20070427082608AA7DwSh
 
You must log in or register to reply here.
Top