converting arrays

[mez]

hi all. ive had a quick search but need a bit of a guide any help is good…

i have a string which will have all different lengths depending on the array it same from.
say the string is called $arr_exp and for this example its value is “1,3,6,10”

$tp = 0.00; print_r (explode(',',$arr_exp));

how would i go about doing the following…
for each element in the array/string
convert the number to a int and then perform a query on that number, take the result (a price) and add it to the variable $tp

what im trying to do is get the total costs…
each of the elements in the array is a id from a different table and has a price linked with it.
so just go through the array and perform a query on each of the elements … get the total price and … then i’ll do other things with it.

[peg110]

I think the simplest is to go through the array (Multiple ways to do this) to create your query or even run your query using the $arr_exp itself (an avoid looping through an array, then avoid looping through a summing routine)

$sqlQuery = "SELECT SUM(price) FROM table WHERE id in (" . $arr_exp  . ");" ;

That way the result will RETURN the summed value right from the server and there is less Looping to deal with.

[mez]

i understand what is going on in the bit of code you provided, but i dont understand how the server will do the maths itself.
?
where/how would i tell it to perform the maths and each time add the $tp
?

thanks

would it be better for me to show the full script ?

[peg110]

the SUM function is a basic function of pretty much EVERY SQL server (MySQL, MS SQL Server, DB2, Oracle, etc…)

These are AGGREGATE functions and will only return the single value (unless you use other grouping functions)

It’s better (and generally more efficient) to allow the SQL server to do the math if possible. To illustrate WHY Let’s assume that you have a table of data. with 10 records which have (for example) the Quantity of your product. You want to get the total quantity of items you have.

You could do a query which returns the 10 line items and then sum them up.

Pseudo-Code

<?
if (!$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')) {
    echo 'Could not connect to mysql';
    exit;
}

if (!mysql_select_db('mysql_dbname', $link)) {
    echo 'Could not select database';
    exit;
}

$sql = "SELECT quantity FROM table"
$result = mysql_query($sql, $link);

if (!$result) {
    echo "DB Error, could not query the databasen";
    echo 'MySQL Error: ' . mysql_error();
    exit;
}
$tp = 0;
while ($row = mysql_fetch_assoc($result)) {
    $tp = $tp + $row['quantity'] 
}

mysql_free_result($result);
echo $tp;
?>

The above would be able to get the sum of the quantity of records. The problem is that it brought the value of 10 records back. Let’s say that each value represented ONE byte. That would be 10 bytes. Not at problem, right?

Well now let’s expand this to a let’s say a MILLION records (which is not inconceivable). If EACH quantity were only ONE Byte, that would be a Million Bytes that get passed back (and still needs summing).

The summing needs to be done either way whether you do it on the SQL server or in your script. The difference would then be seen in the transfer of data (i.e. a million bytes of data compared to the summed results which once summed may be 2 bytes (a double) of data).

Again, since both script and database are probably on the same server, it’s probably NOT that big of an issue, but this may not always be the case, not to mention you should always try to code it as efficiently as possible.

I hope this helps to clarify things.