Can this be sped up?

atokatim

All i need is to see if this code and be sped up because it is causing a server strain.

<?
include("_db.connection.php");
$song = "<song ";
$songs = "</songs>";
$folder=$_POST['folder'];

$sql = mysql_query("UPDATE members SET total_views=total_views + '1' WHERE member_id='$folder'");

$member_result = mysql_query("SELECT * FROM images WHERE mid = '$folder'");
while($file = mysql_fetch_object($member_result)){
$xmlstring1 = $xmlstring1 . $song . 'name="'.$file->songname.'" band="'.$file->bandname.'" file="'.$file->filename.'"/>';
}
$xmlstringfinal = $xmlstring1.$songs;
echo "&playurl=$xmlstringfinal&";
mysql_close($db);
?>

Thanks!
Tim

NogDog

I don't see anything obvious to change PHP-wise. If you haven't done so yet, the "mid" column of the "images" table should be indexed to help improve performance of the select query. The manual page for mysql_fetch_object() suggests that mysql_fetch_row() is slightly faster -- though it calls the difference "insignificant" -- but you could try using it and see if it makes any difference.

atokatim

i took out the "update" and that seemed to help out quite a bit... and i changed the code to read from mysql_fetch_row() that seemed to help a little to..... it still needs more. any ideas?

<?
include("_db.connection.php");
$song = "<song ";
$songs = "</songs>";
$folder=$_POST['folder'];
$xmlstring = "";
//$sql = mysql_query("UPDATE members SET total_views=total_views + '1' WHERE member_id='$folder'");

$member_result = mysql_query("SELECT * FROM images WHERE mid = '$folder'");
while($file = mysql_fetch_row($member_result)){
$url = $file[1];
$name = $file[7];
$band = $file[8];
$xmlstring = $xmlstring . $song . 'name="'.$name.'" band="'.$band.'" file="'.$url.'"/>';
}
$xmlstringfinal = $xmlstring.$songs;
echo "&playurl=$xmlstringfinal&";
mysql_close($db);
?>

Thanks!

bradgrafelman

Yeah... doing a "SELECT *" in MySQL is one of the worst type of SELECT queries you can perform. Try SELECT'ing only the columns you actually use. I also prefer to use [man]mysql_fetch_assoc[/man] and use associative arrays, just so you can easily see what column you're pulling data from, rather than guessing its numerical index (what if this changes later!).

Also, if you reinstate the UPDATE query, I would simply suggest removing the quotes around the 1 in the "total_views + '1'" statement, simply because you're trying to deal with numbers, and '1' is a string - just plain 1 is a number.

Weedpacket

bradgrafelman wrote:
Also, if you reinstate the UPDATE query, I would simply suggest removing the quotes around the 1 in the "total_views + '1'" statement, simply because you're trying to deal with numbers, and '1' is a string - just plain 1 is a number.

And if the member_id field is a number, ditto for that, too.

And while we're about it: do you ever check that $_POST['folder'] contains a legitimate value before putting it into the SQL query?

atokatim

the folder number will always be a number no matter what because it is sent by flash to the php, then the php sends back the information. I will have to try the mysql_fetch_assoc.

I also removed the update because i really did not need that.

Weedpacket

atokatim wrote:
the folder number will always be a number no matter what

So it doesn't need to be in quotes in the query either, then.

because it is sent by flash to the php

Ah, but how do you know that it is your Flash program that is submitting the form?

bradgrafelman

As Weedpacket is pointing out, you can't guarantee yourself that your flash program is the only thing that is submitting data to your script. As such, you definitely need to do some data validation/sanitization before you drop it in a MySQL query.

Since you're dealing with numbers, I would recommend passing the data through a function such as [man]intval/man first.