HTML Logo by World Wide Web Consortium (www.w3.org). Click to learn more about our commitment to accessibility and standards.

Moving forward with Composr

ocPortal has been relaunched as Composr CMS, which is now in beta. ocPortal 9 will be superseded by Composr 10.

Head over to compo.sr for our new site, and to our migration roadmap. Existing ocPortal member accounts have been mirrored.


Need help with some DB queries

Login / Search

 [ Join | More ]
 Add topic 
Posted
Rating:
#83904 (In Topic #17391)
Avatar

Community saint

Ok so for the ocWorld Modifications I am making I wanted to modify the use_item function a little to give it more dynamics.

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
Back to the top
 
Posted
Rating:
#83905
Avatar

Community saint

ok so there is a bug that when editing  regular text in the editor the text in my code boxes gets garbled after saving (I noticed this happened when previewing first instead of posting right away once too)

I fixed it by replacing and saving again however it had converted stuff to entity codes.


Last edit: by Duck
Back to the top
 
Posted
Rating:
#83920
Avatar

is this correct?

yes

how do you suppose I handle the first situation?

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))
{
}
And you use $result[0]['x'] where x is the name of the field you're extracting from the first 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.


Become a fan of ocPortal on Facebook or add me as a friend. Add me on on Twitter.
Was I helpful?
  • If not, please let us know how we can do better (please try and propose any bigger ideas in such a way that they are fundable and scalable).
  • If so, please let others know about ocPortal whenever you see the opportunity.
  • If my reply is too Vulcan or expressed too much in business-strategy terms, and not particularly personal, I apologise. As a company & project maintainer, time is very limited to me, so usually when I write a reply I try and make it generic advice to all readers. I'm also naturally a joined-up thinker, so I always express my thoughts in combined business and technical terms. I recognise not everyone likes that, don't let my Vulcan-thinking stop you enjoying ocPortal on fun personal projects.
  • If my response can inspire a community tutorial, that's a great way of giving back to the project as a user.
Back to the top
 
Posted
Rating:
#83921
Avatar

ok so there is a bug that when editing  regular text in the editor the text in my code boxes gets garbled after saving (I noticed this happened when previewing first instead of posting right away once too)

I fixed it by replacing and saving again however it had converted stuff to entity codes.

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.


Become a fan of ocPortal on Facebook or add me as a friend. Add me on on Twitter.
Was I helpful?
  • If not, please let us know how we can do better (please try and propose any bigger ideas in such a way that they are fundable and scalable).
  • If so, please let others know about ocPortal whenever you see the opportunity.
  • If my reply is too Vulcan or expressed too much in business-strategy terms, and not particularly personal, I apologise. As a company & project maintainer, time is very limited to me, so usually when I write a reply I try and make it generic advice to all readers. I'm also naturally a joined-up thinker, so I always express my thoughts in combined business and technical terms. I recognise not everyone likes that, don't let my Vulcan-thinking stop you enjoying ocPortal on fun personal projects.
  • If my response can inspire a community tutorial, that's a great way of giving back to the project as a user.
Back to the top
 
Posted
Rating:
#83927
Avatar

Nevermind, I can see it right away now - I'll get this fixed.


Become a fan of ocPortal on Facebook or add me as a friend. Add me on on Twitter.
Was I helpful?
  • If not, please let us know how we can do better (please try and propose any bigger ideas in such a way that they are fundable and scalable).
  • If so, please let others know about ocPortal whenever you see the opportunity.
  • If my reply is too Vulcan or expressed too much in business-strategy terms, and not particularly personal, I apologise. As a company & project maintainer, time is very limited to me, so usually when I write a reply I try and make it generic advice to all readers. I'm also naturally a joined-up thinker, so I always express my thoughts in combined business and technical terms. I recognise not everyone likes that, don't let my Vulcan-thinking stop you enjoying ocPortal on fun personal projects.
  • If my response can inspire a community tutorial, that's a great way of giving back to the project as a user.
Back to the top
 
Posted
Rating:
#83931
Avatar

Community saint

Ok I thought about this for a sec and realized people could probably cheat by making item copies cost huge amounts therefor I need to check if my max result is coming from admins. Does ocPortal have a function to return an array of admin id's?

For a sec i thought maybe I could just check non-replicatable items but regular members can make those so that won't work.
Back to the top
 
Posted
Rating:
#83932
Avatar

You probably want $GLOBALS['FORUM_DRIVER']->is_staff(get_member()) or $GLOBALS['FORUM_DRIVER']->is_super_admin(get_member()) or has_zone_access(get_member(),'adminzone')
All are good approximations when you don't have a privilege defined for what you're testing for.


Become a fan of ocPortal on Facebook or add me as a friend. Add me on on Twitter.
Was I helpful?
  • If not, please let us know how we can do better (please try and propose any bigger ideas in such a way that they are fundable and scalable).
  • If so, please let others know about ocPortal whenever you see the opportunity.
  • If my reply is too Vulcan or expressed too much in business-strategy terms, and not particularly personal, I apologise. As a company & project maintainer, time is very limited to me, so usually when I write a reply I try and make it generic advice to all readers. I'm also naturally a joined-up thinker, so I always express my thoughts in combined business and technical terms. I recognise not everyone likes that, don't let my Vulcan-thinking stop you enjoying ocPortal on fun personal projects.
  • If my response can inspire a community tutorial, that's a great way of giving back to the project as a user.
Back to the top
 
Posted
Rating:
#83937
Avatar

The 'code' tag problem should be fixed now. Please let me know if you have any further problems.


Become a fan of ocPortal on Facebook or add me as a friend. Add me on on Twitter.
Was I helpful?
  • If not, please let us know how we can do better (please try and propose any bigger ideas in such a way that they are fundable and scalable).
  • If so, please let others know about ocPortal whenever you see the opportunity.
  • If my reply is too Vulcan or expressed too much in business-strategy terms, and not particularly personal, I apologise. As a company & project maintainer, time is very limited to me, so usually when I write a reply I try and make it generic advice to all readers. I'm also naturally a joined-up thinker, so I always express my thoughts in combined business and technical terms. I recognise not everyone likes that, don't let my Vulcan-thinking stop you enjoying ocPortal on fun personal projects.
  • If my response can inspire a community tutorial, that's a great way of giving back to the project as a user.
Back to the top
 
1 guests and 0 members have just viewed this: None
Control functions:

Quick reply   Contract

Your name:
Your message: