Need help with some DB queries
|
Posted
#83904
(In Topic #17391)
|
|
|---|---|
Community saint |
My plan is this I would like when a person uses a healthy item to do the following: Check the value of the item and if it has a cost associated with it determine the percentage of that cost from the maximum costing healthy item there is. I will uses that percentage result as the max percentage of the random health given to a person based on the persons current health. I have all that functionality worked out (I wrote a function for calculating the percents) so for example: Person eats an apple the bought for 30 and the most expensive healthy item is 150 so I will calculate the persons possible heal points as 20% of their current health (for argument sake lets say its 100) I will use the same percentage number as a the base percentage in my random number so when I determine the random health it will be between 4 and 20 points. Now here is my dilemma: The current use item query is this: Code
$healthy=$GLOBALS['SITE_DB']->query_value('w_itemdef','healthy',array('name'=>$item_name));
I would like to know how I can modify that to return the cost of the item with the healthy result. But the problem I see is all items that have a cost will also have an entry in w_items that has no cost because when an item is first made it is made without a cost. Then the next query I need to know how to write (to get the maximum costing item) to work with ocPortal would be this Code
SELECT max(b.cost) FROM _w_itemdef a LEFT JOIN w_items b on b.name = a.name WHERE a.healthy =1
I have been looking at the database functions and I think my second one would be as follows Code
$maxcost=$GLOBALS['SITE_DB']->query_value_null_ok_full('SELECT max(b.cost) FROM w_itemdef a LEFT JOIN w_items b on b.name = a.name WHERE a.healthy =1');
is this correct? and if so how do you suppose I handle the first situation? one thought I had was to to do a second query like so: Code
$itemcost=$GLOBALS['SITE_DB']->query_value_null_ok_full('SELECT max(b.cost) FROM _w_itemdef a LEFT JOIN w_items b on b.name = a.name WHERE a.healthy =1 AND a.name = '$item_name);
Any thoughts? 
Last edit: by Duck
|
|
|
|
Posted
|
|
|
Community saint |
I fixed it by replacing and saving again however it had converted stuff to entity codes.
Last edit: by Duck
|
|
|
|
|
Posted
|
|
ocStaff (admin) |
yes
Use the 'query' method instead. Instead of a non-result being NULL, it always returns an array of maps. So to check for a result you do: Code
if (array_key_exists(0,$result))
In your query you probably want to say max(b.cost) AS max_cost so you know what field name you can extract with. If I answered something that you think should be in the documentation, please take the initiative and add it to the community documentation. We really need people to help out here and build a well-organised large support resource. |
|
|
|
|
Posted
|
|
|
ocStaff (admin) |
I thought we'd got to the bottom of these problems. I don't know how to reproduce so if you can demonstrate it click-by-click, I'll take a look. If I answered something that you think should be in the documentation, please take the initiative and add it to the community documentation. We really need people to help out here and build a well-organised large support resource. |
|
|
|
|
Posted
|
|
|
ocStaff (admin) |
If I answered something that you think should be in the documentation, please take the initiative and add it to the community documentation. We really need people to help out here and build a well-organised large support resource. |
|
|
|
|
Posted
|
|
|
Community saint |
For a sec i thought maybe I could just check non-replicatable items but regular members can make those so that won't work. |
|
|
|
|
Posted
|
|
|
ocStaff (admin) |
All are good approximations when you don't have a privilege defined for what you're testing for. If I answered something that you think should be in the documentation, please take the initiative and add it to the community documentation. We really need people to help out here and build a well-organised large support resource. |
|
|
|
|
Posted
|
|
|
ocStaff (admin) |
If I answered something that you think should be in the documentation, please take the initiative and add it to the community documentation. We really need people to help out here and build a well-organised large support resource. |
|
|
|
1 guests and 0 members have just viewed this: None
Control functions: