[PHP] Multiple database seeking
Hello, I am trying to make a something like a cooking recipe with some calculations. My problem is too much database querying. Is it really a problem??? Let's suppose there is a table with items and prices: item1price1 item2price2 item3price3 ... Then I have to calculate some new indegrient price, which is a result of calculating other prices: (ex: $new_price1 = 2*$price1 + $price2 ) To get wanted prices I used standard query: $result = mysql_query (SELECT price from price_list where id = '01'); $row = mysql_fetch_array($result); $price1 = $row[price]; $result = mysql_query (SELECT price from price_list where id = '02'); $row = mysql_fetch_array($result); $price2 = $row[price]; //...and so on for about 30 indegrients. And on the end: $new_price1 = 2*$price1 + $price2; print $new_price1; -- But, is there a quicker way to do this? Am I doing maybe the wholistic mistake in process? Or this is just OK? Please, help cooking the soup ;) regards, Alen
Re: [PHP] Multiple database seeking
Allen,
Have you a table of recipes, like so:
recipe1 item1
recipe1 item2
...
recipe1 itemn
recipe2 item1
You could then fetch all the items for a given recipe, and loop through the
returned results (an array), concatenating the prices.
Alternately, you store all your items in an array for each recipe and loop
through the array ...
Here's an example of what I'm using, where the prompts for a page are
stored in a table. This lets me handle different languages very easily:
//all the connection stuff is up above ...
$cLLangCode = GB;
$cPageName = user_bid;
$arr_lbl = array();
$sql = select cLblName,cLblText from prompt
where cLangCode = '$cLangCode' and cPageName =
'$cPageName';
$result = mysql_query( $sql, $db )
or die( Unable to fetch prompts for page $cPageName. );
while( $row = mysql_fetch_array( $result ) )
{
$arr_lbl[$row[cLblName]] = $row[cLblText];
}
In the $arr_lbl assignment, following the while(), you could execute
queries for individual item costs, and add them up. Essentially nest the
same code inside the while, except that it's executing for one ingredient
at a time.
If you will have to a lot of this, seriously consider PostgreSQL (or
similar db) as it supports subqueries, and this situation cries out for
their use. This single line would give you the sum of all your ingredients:
Select sum(price) from items where item in (select item from
recipe where recipe_id = choc_chip)
Database querying isn't a problem, as it's all server side and really fast,
no traffic across the wire. If you have a lot of complex queries, really
consider something other than MySQL.
Cheers - Miles Thompson
http://www.cqagroup.ca
At 03:31 PM 12/3/2001 +0100, =?iso-8859-2?Q?Alen_Nonkovi=E8?= wrote:
Hello,
I am trying to make a something like a cooking recipe with some
calculations. My problem is too much database querying. Is it really a
problem???
Let's suppose there is a table with items and prices:
item1price1
item2price2
item3price3
...
Then I have to calculate some new indegrient price, which is a result of
calculating other prices:
(ex: $new_price1 = 2*$price1 + $price2 )
To get wanted prices I used standard query:
$result = mysql_query (SELECT price from price_list where id = '01');
$row = mysql_fetch_array($result);
$price1 = $row[price];
$result = mysql_query (SELECT price from price_list where id = '02');
$row = mysql_fetch_array($result);
$price2 = $row[price];
//...and so on for about 30 indegrients.
And on the end:
$new_price1 = 2*$price1 + $price2;
print $new_price1;
--
But, is there a quicker way to do this? Am I doing maybe the wholistic
mistake in process?
Or this is just OK?
Please, help cooking the soup ;)
regards,
Alen
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] Multiple database seeking
Use smth like this -
$need_prices = '01,02';
$prices = new Array();
$sql = select * from price_list where id in ($need_prices)
$result = mysql_query ($sql);
{//cycle while $result is not empty - for every record
$row = mysql_fetch_array($result);
$prices[$row[id]] = $row[price];
}
After this you'll get array of prices $prices - indexes are id value. so u
can write
$new_price1 = 2*$prices[1] + $prices[2]
Zliy Pes, http://www.zliypes.com.ua
- Original Message -
From: Alen Nonkovi? [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, December 03, 2001 4:31 PM
Subject: [PHP] Multiple database seeking
Hello,
I am trying to make a something like a cooking recipe with some
calculations. My problem is too much database querying. Is it really a
problem???
Let's suppose there is a table with items and prices:
item1price1
item2price2
item3price3
...
Then I have to calculate some new indegrient price, which is a result of
calculating other prices:
(ex: $new_price1 = 2*$price1 + $price2 )
To get wanted prices I used standard query:
$result = mysql_query (SELECT price from price_list where id = '01');
$row = mysql_fetch_array($result);
$price1 = $row[price];
$result = mysql_query (SELECT price from price_list where id = '02');
$row = mysql_fetch_array($result);
$price2 = $row[price];
//...and so on for about 30 indegrients.
And on the end:
$new_price1 = 2*$price1 + $price2;
print $new_price1;
--
But, is there a quicker way to do this? Am I doing maybe the wholistic
mistake in process?
Or this is just OK?
Please, help cooking the soup ;)
regards,
Alen
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP] Multiple database seeking
Something like this for the SQL SELECT ( t1.price + t2.price ) as total FROM table t1, table t2 WHERE t1.id=1 AND t2.id=5; Jason Lotito [EMAIL PROTECTED] www.NewbieNetwork.net -Original Message- From: Alen Nonkoviè [mailto:[EMAIL PROTECTED]] Sent: Monday, December 03, 2001 9:31 AM To: [EMAIL PROTECTED] Subject: [PHP] Multiple database seeking Hello, I am trying to make a something like a cooking recipe with some calculations. My problem is too much database querying. Is it really a problem??? Let's suppose there is a table with items and prices: item1price1 item2price2 item3price3 ... Then I have to calculate some new indegrient price, which is a result of calculating other prices: (ex: $new_price1 = 2*$price1 + $price2 ) To get wanted prices I used standard query: $result = mysql_query (SELECT price from price_list where id = '01'); $row = mysql_fetch_array($result); $price1 = $row[price]; $result = mysql_query (SELECT price from price_list where id = '02'); $row = mysql_fetch_array($result); $price2 = $row[price]; //...and so on for about 30 indegrients. And on the end: $new_price1 = 2*$price1 + $price2; print $new_price1; -- But, is there a quicker way to do this? Am I doing maybe the wholistic mistake in process? Or this is just OK? Please, help cooking the soup ;) regards, Alen -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
