Small Array Question and Query

[myfayt]

I am using the following array below and it works fine. However I would like it to only show the ore that is greater than 0.

 $furnquery = doquery("SELECT * FROM {{table}} WHERE name='$name'", 'furnace');

        if (mysql_num_rows($furnquery) == 0) {
         display("<p>There is no equipment by this name.<br /><br />You may now return to the <a href=\"furnace.php\">Furnace</a>.", "Error");
        }

        $frow = mysql_fetch_array($furnquery);
        
        $arr = array('Copperdust' => $frow['ore1'],
                  'Lavarock' => $frow['ore2'],
                  'Turquoise' => $frow['ore3'],
                  'Fire Opal' =>$frow['ore4'],
                  'Meteorite' => $frow['ore5'],
                  'Angels' => $frow['ore6'],
                  'Diamond' => $frow['ore7']);

              foreach ($arr as $key => $frow) {
              $page .= "You gained $frow $key<br />";
            } 

and it displays like this

You gained 12 Copperdust
You gained 3 Lavarock
You gained 0 Turquoise
You gained 0 Fire Opal
You gained 0 Meteorite
You gained 0 Angels
You gained 0 Diamond 

So I'd like it to only show the Copperdust and Lavarock since they are greater than 0 for this item.

My second question is how do I update only the values affected? My database is normalized and this is a standard update query for it.

if (mysql_num_rows($ore1query) == 1) {
         $updatequery = doquery("UPDATE {{table}} SET amount=amount+$orecount WHERE playerid='".$userrow['id']."' AND itemid='16' LIMIT 1", "useitems");
       
        }
        else {
        $query = doquery("INSERT INTO {{table}} SET id='', playerid='".$userrow['id']."', itemtype='3', amount='$orecount', itemid='16', itemname='Copperdust'", "useitems");
   
         } 

Thanks

[djm0219]

1. Alter your query to only return entries that have a value greater than zero (I guessed that the column with the value is named amount based on your second question):

$furnquery = doquery("SELECT * FROM {{table}} WHERE name='$name' AND amount > 0", 'furnace'); 

I'm not quite sure what you're asking in your second question.

[myfayt]

Doesn't seem to work, because the fields are ore1, ore2, ore3, ore4, ore5, ore6, ore7. So it needs to find the amounts from each field and display only the two with numbers for that item.

I guess I need some kind of TRUE/FALSE statement block to check each field somehow.

Let me try to be clearer on the second question.

if (mysql_num_rows($ore1query) == 1) {
         $updatequery = doquery("UPDATE {{table}} SET amount=amount+$orecount WHERE playerid='".$userrow['id']."' AND itemid='16' LIMIT 1", "useitems");
       
        }
        else {
        $query = doquery("INSERT INTO {{table}} SET id='', playerid='".$userrow['id']."', itemtype='3', amount='$orecount', itemid='16', itemname='Copperdust'", "useitems");
   
         } 

Itemid 16 would represent like Copperdust, it first checks to see if the user has any record of ever having any Copperdust on their account, if not it goes to the else statement and inserts a new record to their account with the amount. But if its true, it just updates it, adding the new ore to it.

So instead of doing this:

if ($frow['ore1'] > 0) {
//Add Ore
}
elseif ($frow['ore2'] > 0) {
//Add Ore
}
elseif ($frow['ore3'] > 0) {
//Add Ore
}
elseif ($frow['ore4'] > 0) {
//Add Ore
} 

There has to be a simpler way with an array or something. But instead of it saying

You gained 0 Copperdust
You gained 0 Lavarock
You gained 0 Turquoise
You gained 53 Fire Opal
you gained 10 Meteorite
You gained 0 Angels
You gained 0 Diamond

and having to do a dozen checks, there should be a way to only show the ore that is greater than 0 (Which will always only be 2 types only), and update their account with those two ores, without having to do another dozen checks to see which to add.

[Chris Hick]

NOTE: This is untested but should work.
How about putting an if statement in the foreach statement to check and see if $frow > 0, like this:

$arr = array('Copperdust' => $frow['ore1'], 
                  'Lavarock' => $frow['ore2'], 
                  'Turquoise' => $frow['ore3'], 
                  'Fire Opal' =>$frow['ore4'], 
                  'Meteorite' => $frow['ore5'], 
                  'Angels' => $frow['ore6'], 
                  'Diamond' => $frow['ore7']); 
 
foreach ($arr as $key => $frow) { 
     if ($frow > 0)  {
           $page .= "You gained $frow $key<br />"; 
     }
} 

[myfayt]

That worked great, can you help me figure out the second part? I need to figure out how to extract those two into a query, without having to have all 7 in the query.

[Chris Hick]

Why not just add the query inside an if statement that is inside a foreach statement? That way the query will only run on the ones where the frow > 0. If frow is not greater than 0, then just use an else to run the other query.

$arr = array('Copperdust' => $frow['ore1'], 
                  'Lavarock' => $frow['ore2'], 
                  'Turquoise' => $frow['ore3'], 
                  'Fire Opal' =>$frow['ore4'], 
                  'Meteorite' => $frow['ore5'], 
                  'Angels' => $frow['ore6'], 
                  'Diamond' => $frow['ore7']); 
 
foreach ($arr as $key => $frow) { 
     if ($frow > 0)  {
         $updatequery = doquery("UPDATE {{table}} SET amount=amount+$orecount WHERE playerid='".$userrow['id']."' AND itemid='16' LIMIT 1", "useitems"); 
        
     } else { 
         $query = doquery("INSERT INTO {{table}} SET id='', playerid='".$userrow['id']."', itemtype='3', amount='$orecount', itemid='16', itemname='Copperdust'", "useitems"); 
    
       }  
} 

[myfayt]

So you mean like this?

foreach ($arr as $key => $frow) { 
     if ($frow > 0)  {
           $page .= "You gained $frow $key<br />"; 

 if (($key == 'Copperdust') && $frow > 0) {

 if (mysql_num_rows($ore1query) == 1) {
         $updatequery = doquery("UPDATE {{table}} SET amount=amount+$orecount WHERE playerid='".$userrow['id']."' AND itemid='16' LIMIT 1", "useitems");
       
        }
        else {
        $query = doquery("INSERT INTO {{table}} SET id='', playerid='".$userrow['id']."', itemtype='3', amount='$orecount', itemid='16', itemname='Copperdust'", "useitems");
   }

//NEXT

         }  

     }
} 

[Chris Hick]

Yeah, something similiar to that. Although, in your above code, you are already in the if statement of $frow being greater than 0 so you dont have to do it again in that nested if statemnt. Also, you could place your mysql_num_rows into the statement above it.

foreach ($arr as $key => $frow) {  
     if ($frow > 0)  { 
           $page .= "You gained $frow $key<br />";  

           if (($key == 'Copperdust') && (mysql_num_rows($ore1query) == 1)) { 
                  $updatequery = doquery("UPDATE {{table}} SET amount=amount+$orecount WHERE playerid='".$userrow['id']."' AND itemid='16' LIMIT 1", "useitems"); 
        
           } else { 
                  $query = doquery("INSERT INTO {{table}} SET id='', playerid='".$userrow['id']."', itemtype='3', amount='$orecount', itemid='16', itemname='Copperdust'", "useitems"); 
             } 

//NEXT 

         }   

} 

[myfayt]

Works perfect, one last problem, I need the piece of equipment to delete when successful. Which this line works

 $query = doquery("DELETE FROM {{table}} WHERE playerid='".$userrow['id']."' AND name='$name' LIMIT 1", "smithcreate"); 

However I had two of the same item and it deleted both of them. Any idea how I can make it only delete one of them?

[Chris Hick]

Your best bet for that is an update. Why? Because from a previous post, your table has a column for amount correct? The issue with that is when you delete from a table, you delete an entire row, which includes all that is in that amount column. So, what you should do is an update of subtracting one from the amount.

[myfayt]

Ah ha I think I have a plan. Is there a way to do this using mysql num rows? something like this?

if (mysql_num_rows($equip) > 1) {
//Delete or update 1 equipment
}
else {
//Delete row
} 

EDIT: by the way, that old topic about having (sets) didn't happen, decided against it because there were way too many factors involved.

[Chris Hick]

Nope, you can't do that. Because mysql_num_rows, only returns the amount of rows that meet your query's criteria, in this case just one row. You will need to query for your amount column then use that if else statement.

EDIT: Ahh, but it still does not matter. In your above queries, you clearly have a column in your table that is for amount. Which means, you can't use that myql_num_rows. You will have to do what I just said.

[myfayt]

I don't have an amount in that table. It does have a unique id which would work, but don't have a clue how to get that to work.

[Chris Hick]

How do you not have an amount in that table? How do you keep track of more than one of the same item? Unless, you have duplicate entries. If that is the case, then you could use the delete clause and mysql_num_rows.

[myfayt]

People can have multiple of the same item, and it will show multiple rows of it in their inventory, but the id is always unique. This is for weapon and armor, not regular items like ores, because all those have amounts