[LittleSoul]

So, against my pride, I'm finally asking for some help. This problem is depressing me as I can't progress my work.

I'm over halfway done making my online text-based role-playing game, and I've come to the crafting portion. At first I thought it'd be no problem. Compare resources on the ground to the required resources that make the desired item.
But, I've never had to compare so specifically before.

I've looked into doing this both in SQL and in PHP, SQL being preferred as I'm guessing it's probably quicker.
I've looked into array_diff, and array_intersect, I've done joins, left joins, right joins, all within difference re-evaluated contexts; but I'm just not getting what I need.

Here's what I've got in the database; right now I'm just trying to echo out what cooked food you can make ONLY if you have ALL the required resources on the ground to make it. I've gotten it to echo, but instead of only echoing if all the required resources are present, it echos all food that uses a resource at least once that if it is on the ground.

Table: edible_resources
Columns: edible_resource_id, edible_resource_name, hunger_points, degeneration_id

Table: edible_ground
Columns: id, resource, amount, location

Table: req_crafting_edible
Columns: req_crafting_edible_id, edible_resource_id, req_resource_amount, created_item_id

Table: items
Columns: item_id, item_name, degeneration_id, is_grounded, is_stackable, can_equip, can_edit

This is one thing I've tried, though I've gone through many; this one uses in_array.

//get all craftable items, and all edible resources on the ground
//iterate through each craftable item, gather it's required resources into an array
//to do that we can use a while loop for all the rows in the edible crafting table, then use a select statement
//with each craftable id which will give us multiple rows for each required item based on each created_item_id
//then put those rows into an array and use in array to see if those resource ids are on both the ground and in required resources
$get_ground = mysql_query("SELECT resource FROM edible_ground WHERE location = 2020");
$ground = mysql_fetch_array($get_ground);

$get_req = mysql_query("SELECT * FROM req_crafting_edible ORDER BY created_item_id");
while($required = mysql_fetch_array($get_req)){
  $get_each = mysql_query("SELECT edible_resource_id FROM req_crafting_edible WHERE created_item_id = $required[created_item_id]");
  while($each = mysql_fetch_array($get_each)){
    //now we have the required resources specific to each craftable item, so we put them in an array
    if (in_array($each['edible_resource_id'], $each) && in_array($ground['resource'], $ground)){
      $get_make = mysql_query("SELECT item_name FROM items JOIN req_crafting_edible ON items.item_id = req_crafting_edible.created_item_id
      WHERE edible_resource_id = $each[edible_resource_id]");
      while($can_make = mysql_fetch_array($get_make)){
        echo $can_make['item_name'].'<br>';
      }
    }else{
      echo 'There are no matches';
    }
  }
}

You can kind of see what I'm going for here, but.. I think my logic only looks good on paper. When I use it there's a gigantic list of those items and their names being repeated (sort of). I suspect placement of my while loops (it gets confusing), and the items that are being listed shouldnt be. Some items are listed that don't even use the items on the ground, like mushroom soup. They aren't even repeating in a set way. The repeats show different instances of some foods almost every time. Which. Again. Probably dumb placement of the while loops.

Part of the output:

Quote

Mushroom Soup
Charred Mushroom
Gumbo
Fish Soup
Seafood Soup
Charred Fish
Grilled Fish
Baked Fish
Sashimi
Sushi
Udon
Fish Soup
Seafood Soup
Charred Fish
Grilled Fish
Baked Fish
Sashimi
Sushi
Udon
Seafood Soup
Charred Scallops
Grilled Scallops
Boiled Scallops
Baked Scallops
Udon
Seafood Soup
Charred Shrimp
Grilled Shrimp
Boiled Shrimp
Baked Shrimp
Udon

Can anyone help me out here pretty please in_array feels like the closest I've gotten to my goal, and that's saying something because it's a mess as it stands.

I'd be grateful for any giant brains out there.
(:

[modi123_1]

Quote

So, against my pride, I'm finally asking for some help. This problem is depressing me as I can't progress my work.
...

I'd be grateful for any giant brains out there.

Let's be a tad less creepy and odd.


AS for your issue - you need to get on board with group bys, counts, etc.


The setup.. I have an item table of unique item ids and item names. Pretty straight forward, right?

table #item (lid int, sval varchar(30))

1	Apple
2	Noodle
3	Jerky
4	Sunflower Seeds
5	Dirt

Then I have a table of my recipes... a recipe id, a name, and this is important - the number of types of items that go into making it.
table #recipe (lid int, sval varchar(30), ingred_count int)

1	Pho	2
2	Poison Tart	2
3	An Apple	1
4	Brownies	3

Then I have a relationship table that matches up recipe ids to item ids, and how many of those items I need to make this recipe.
table #recipe_items (lid int, recipid int, itemid int, itemcount int)

1	1	3	2
2	1	2	4
3	2	1	1
4	2	3	1
5	3	1	1
6	4	1	1
7	4	4	30
8	4	5	2

Example - for Pho it appears I need two Jerky bits and four Noodle bits.
For An Apple I just one Apple.
For Brownies I need one Apple, thirty Sunflower Seeds, and two Dirt clods.

Not so bad so far, right?

In this quick example I have the user's inventory stored in a table... this can be where ever.. but for this example it's the inventory.

table #user_inventory(lid int, itemid int, itemcount int)

3	1	Apple	20
1	2	Noodle	50

now this means I have twenty apples, and fifty Noodles. What I am looking for is a list of recipes for the items I want. So.. that means I need to compare what I have in my inventory to items in recipe table.. compare the quantity needed versus what I have on hand, and make sure I have the right number of unique items for that recipe.


Working inside out.

select z.sval '-- 9 display that recipe info
from #recipe z
join (

'-- !!!!start here
select recipid, count(*) valid_items '-- 1. get the recipe id and the count of valid items 
from #recipe_items b '-- 2. from the recipe table
join #user_inventory a on a.itemid = b.itemid  '-- 3. when joined by the user's inventory
where a.itemcount >= b.itemcount '-- 4. and having a stock pile that meets or exceeds the required numbers from the recipe
group by recipid  '-- 5. group on the recipe id so we get the number of distinct items in that recipe.


      ) y  '-- 6 join off that query like it was a table
on y.recipid = z.lid '-- 7 on the recipe ids that match up
where z.ingred_count = y.valid_items '-- 8  only if we have the right count of distinct items

Given the inventory can you guess what comes up? An Apple.

If I have that same inventory and add five Jerky

3	1	Apple	20
1	2	Noodle	50
2	3	Jerky	5

The query tells me I can make:
Pho
Poison Tart
An Apple


If I have only one Jerky

3	1	Apple	20
1	2	Noodle	50
2	3	Jerky	1

I can only make:
Poison Tart
An Apple


Easy peasy, right?

Oh and if that exact thing doesn't work in your DB environ - treat this like pseudocode.

[LittleSoul]

modi123_1, on 27 September 2012 - 01:56 PM, said:

Quote

So, against my pride, I'm finally asking for some help. This problem is depressing me as I can't progress my work.
...

I'd be grateful for any giant brains out there.

Let's be a tad less creepy and odd.

Thanks for the criticism and confidence boost.
You could have just been polite and said nothing if those were your thoughts.

Anyways, thank you for the reply; I'm still open to other answers however, if anyone has any.

I've never seen '-- used to comment out before, and I've also never seen # used before table names.
Is that new, out of curiosity?

[modi123_1]

Quote

Thanks for the criticism and confidence boost.
You could have just been polite and said nothing if those were your thoughts.

Bah.. then when would we have time for this awkward side conversation? Some days it is the little interactions that make the world go round!

Quote

Anyways, thank you for the reply; I'm still open to other answers however, if anyone has any.

What - was the answer not complete enough? Did you even try to incorporate it? Too complex? Man.. all that hard work creating temp tables and a bad ass sql statement to pseudo fit into your sketch of what you are doing.. for nothing!

Quote

I've never seen '-- used to comment out before, and I've also never seen # used before table names.
Is that new, out of curiosity?

As I said - results may vary on the system you are using. Just to highlight that is a comment and not some piece of code.. as for the hashes - temp tables man! Temp tables!

[LittleSoul]

It was complete, I just don't know if it works because I've been working on switching out all the pseudo code with my actual database information; and its confusing. My database already has all the data in it, so it's not like I can willy nilly add columns or delete them without going through hundreds of rows to add in new values.

Once I finally get all the information squared away I'll be happy to say if it works or not; in the mean time I'm leaving the door open for anyone else.

I do appreciate the work you did, thank you.
It's just not easy to implement it into my own environment, so I have no idea if it works or not yet.

[e_i_pi]

First, any time you need meta-data from the DB, use the DB. Don't use arrays. There are very very few times where you need to loop through a result set as an array, executing queries for each node, where you can't do it by DB.

As for your problem, sometimes it's easiest approach the situation one step at a time. Let's give that a try.

First, let's get a list of all possible items, ordered by their name:

SELECT
	items.item_name
FROM items
ORDER BY items.item_name

Simple stuff. Not very handy, but simple enough. Now, let's get a list of all craftable items. To do this, we're going to have to join in the req_crafting_edible table:

SELECT
	items.item_name
FROM items
INNER JOIN req_crafting_edible
	ON req_crafting_edible.created_item_id = items.id
ORDER BY items.item_name

The problem here is, we're going to get multiple rows per item that has has multiple "ingredients". We can solve this by using either a SELECT DISTINCT or a GROUP BY clause. GROUP BY is generally better practice, as you are declaring which columns will have their distinction recognised, and it also doesn't "hide" your intentions by using a blanket grouping method (which SELECT DISTINCT does):

SELECT
	items.item_name
FROM items
INNER JOIN req_crafting_edible
	ON req_crafting_edible.created_item_id = items.id
GROUP BY items.item_name
ORDER BY items.item_name

So there's our list of all craftable items. Still not entirely useful, as we need to find which items we have the ingredients for. So now we'll join in the list of ingredients:

SELECT
	items.item_name
FROM items
INNER JOIN req_crafting_edible
	ON req_crafting_edible.created_item_id = items.id
INNER JOIN edible_resources
	ON edible_resources.edible_resource_id = req_crafting_edible.edible_resource_id
GROUP BY items.item_name
ORDER BY items.item_name

This should give us exactly the same list as before. The only instance under which it won't give us the same list is if every ingredient for a crafted item is 'NULL'. Again, this query is not too helpful. Here is where we get the meat and potatoes of the query in place, no pun intended. We now need to join in the items on the ground, using an INNER JOIN to filter out results which don't match:

SELECT
	items.item_name
FROM items
INNER JOIN req_crafting_edible
	ON req_crafting_edible.created_item_id = items.id
INNER JOIN edible_resources
	ON edible_resources.edible_resource_id = req_crafting_edible.edible_resource_id
INNER JOIN edible_ground
	ON edible_ground.resource = edible_resources.edible_resource_id
	AND edible_ground.amount >= req_crafting_edible.req_resource_amount
WHERE edible_ground.location = 2020
GROUP BY items.item_name
ORDER BY items.item_name

That query should get us the list of items that we can make given the items that are on the ground at location 2020. If it doesn't, maybe I've misunderstood your schema.

This isn't the end of it though. We now have a redundant JOIN in our query that we can remove, that is the JOIN to the edible_resources table. It is only acting as a bridge between req_crafting_edible.edible_resource_id and edible_ground.resource. Let's go ahead and remove that JOIN, and amend the predicate on the edible_ground table:

SELECT
	items.item_name
FROM items
INNER JOIN req_crafting_edible
	ON req_crafting_edible.created_item_id = items.id
INNER JOIN edible_ground
	ON edible_ground.resource = req_crafting_edible.edible_resource_id
	AND edible_ground.amount >= req_crafting_edible.req_resource_amount
WHERE edible_ground.location = 2020
GROUP BY items.item_name
ORDER BY items.item_name

Finally, this should be the query we want. This query may take some time to run, depending on the size of your tables. If it is taking too much time, we can enhance the running times by placing targetted indexes on the tables:

items(id)
req_crafting_table(created_item_id,edible_resource_id,req_resource_amount)
edible_ground(resource,amount)
edible_ground(location)

Depending on the size of the relevant tables, the indexes may increase performance time a substantial amount. The final two indexes on edible_ground may be able to be combined, but my gut instinct says they could run faster being separate. You would have to run benchmark tests yourself to get a good indication. The index on items(id) is likely going to do nothing, unless you're on MSSQL, in which case adding that index while including the column item_name will improve your query times marginally.

This post has been edited by e_i_pi: 27 September 2012 - 07:53 PM

[LittleSoul]

Thank you -SO- much. That's exactly what I was looking for. I haven't ever really used GROUP BY, and I didn't know you could add AND after an ON. Thank you so much. I'll be referencing this post again soon so I can keep in mind what you've showed me. I appreciate you breaking it down for me too with added advice, rather than just handing me a query.

You've saved me so many more headaches, and I learned something in the process. Thank you for your help.