[SOLVED] Count and Match Values in an Array to a Database Table

[CloudSex13]

I came up with a script (below) that matches values in a given array to items in a table in a database, displays all the items on the page (only once per item), and what I'm trying to do currently is count how many of the items are the same in the array and display it next to that item.

 

I'm not sure as to why this script will not count and display correctly the amounts or items, but I was wondering if anyone could give me some pointers or assistance to help me do so.

 

Thank you kindly if so.

 

$inventory = array("1,2,3,2,3,2,3,5,7,87677");

$items = explode(",", $inventory);

$listed = array();

foreach ($items as $item) {

  if (!in_array($item, $listed)) {

  $query = mysql_query("SELECT * FROM Items WHERE ItemID='$item' AND ItemType='Box' ORDER BY ItemName");

    $itemamount = array_count_values($items);

    while ($getquery = mysql_fetch_array($query)) {

    $itemname = $getquery['ItemName'];
    $itemid = $getquery['ItemID'];

foreach ($itemamount as $theitemname => $theitemamount) {

      $allitems .= "<div class=\"item\">".$itemname." (".$theitemamount.")</strong>";
      $listed[$theitemname] = $item;

    }

}

[Ken2k7]

1. $inventory is already of type array, so you shouldn't need to explode it.

2. Why is the element in your array a string? If you take out the quotes, the explode lines, and update the variables in the foreach loop, it should be fine.

[CloudSex13]

Thanks for the reply!

 

Actually, $inventory is a value from a field in a table on a database holding the following: "1,2,1,3,4,2,2,4,2," just like that. Updating my question now, I would have to keep explode there.

 

Would everything you suggested still be wrong, or?

[Ken2k7]

1. Read up on DB normalization. DB CSV values fail!

2. Don't put that string in an array. Let $inventory be "1,2,1,..." and then explode should be fine.

[CloudSex13]

I did read up on DB normalization. That cannot work for what I'm trying to accomplish. Thank you, though.

 

That string is not being put into an array. The foreach statement is the only thing not working... I can't seem to grasp my head around it.

[Ken2k7]

Either you don't know DB normalization or you are not comfortable with SQL. But it will work with whatever you want to accomplish.

 

$inventory = array("1,2,3,2,3,2,3,5,7,87677");

Well I see a string in an array. Can you take the array part out?

[CloudSex13]

Oh, no, ignore that.

 

$inventory is what the users Inventory is stored in the database. That field would have a value of "1,2,3,2,3,2,3,5,7,87677,"

 

As far as the database normalization goes, the article on it showed how to use it to link in a search. I have to store data that is consistent and maybe have a small portion removed or added in it.

[Ken2k7]

Well you can remove rows in a DB table. Same deal.

 

Anyways, what do you mean by ignore that. All I'm saying is if you remove the array() around that string, your foreach loop should be fine. I don't understand the problem here.

[CloudSex13]

I don't want rows removed in a table. It's a users inventory stored as one field in a table on a datase - how else could that be stored? I don't get it.

 

Here's the actual code:

 

$query = mysql_query("SELECT * FROM Users WHERE Username='$cookie' LIMIT 1");
$getquery = mysql_fetch_array($query);
$inventory = $getquery['Inventory'];

 

Does that make anymore sense, or? :/

[Ken2k7]

Okay forget the DB thing I mentioned.

 

Anyways, can you post what

var_dump($inventory);

outputs?

[CloudSex13]

Outputs this:

 

string(36) "3,3,3,1,1,1,1,1,2,2,2,2,2,2,2,1,2,3," 

[Ken2k7]

$query = mysql_query("SELECT * FROM Users WHERE Username='$cookie' LIMIT 1");
$getquery = mysql_fetch_array($query);
$inventory = $getquery['Inventory'];

$items = explode(",", $inventory);

$listed = array();

foreach ($items as $item) {

  if (!in_array($item, $listed)) {

    $query = mysql_query("SELECT * FROM Items WHERE ItemID='$item' AND ItemType='Box' ORDER BY ItemName");

    $itemamount = array_count_values($items);

    while ($getquery = mysql_fetch_array($query)) {

    $itemname = $getquery['ItemName'];
    $itemid = $getquery['ItemID'];

foreach ($itemamount as $theitemname => $theitemamount) {

      $allitems .= "<div class=\"item\">".$itemname." (".$theitemamount.")</strong>";
      $listed[$theitemname] = $item;

    }

}

[CloudSex13]

For some reason, that's giving an "Parse error:  syntax error, unexpected $end"

 

Hm?

[Ken2k7]

Oh, I just copy and pasted. Put two } at the end.

[CloudSex13]

Oh, gotcha.

 

That outputs something like the following:

 

SHIRT (2) SHIRT (6) SHIRT (8 ) SHIRT (1) PANTS (2) PANTS (6) PANTS (8 ) PANTS (1) SHIRT (2) SHIRT (6) SHIRT (8 )

 

And then it continues off screen -

 

I'm baffled at this point.

[Ken2k7]

LMAO! That is awesome.

 

Sorry, so what should the display look like? Also please post the updated code.

[CloudSex13]

lol, I know, right?

 

In comparison to what it's displaying now:

 

BOX1 (2) BOX1 (6) BOX1 (8 ) BOX1 (1) BOX2 (2) BOX2 (6) BOX2 (8 ) BOX2 (1)

 

I want it to display:

 

BOX1 (Actual amount of this box) BOX2 (Actual amount of this box)

 

I changed:

 

      $listed[$theitemname] = $item;

 

To:

 

      $listed[] = $item;

 

And now it only displays 4 BOX1's and 4BOX2's for a total of 8 outputs, where it should only be 2, one for each box, and the number of that box next to it instead of so much output it flows off the screen.

 

Updated code:

 

$query = mysql_query("SELECT * FROM Users WHERE Username='$cookie' LIMIT 1");
$getquery = mysql_fetch_array($query);
$inventory = $getquery['Inventory'];

$items = explode(",", $inventory);

$listed = array();

foreach ($items as $item) {

  if (!in_array($item, $listed)) {

    $query = mysql_query("SELECT * FROM Items WHERE ItemID='$item' AND ItemType='Box' ORDER BY ItemName");

    $itemamount = array_count_values($items);

    while ($getquery = mysql_fetch_array($query)) {

    $itemname = $getquery['ItemName'];
    $itemid = $getquery['ItemID'];

foreach ($itemamount as $theitemname => $theitemamount) {

      $allitems .= "<div class=\"item\">".$itemname." (".$theitemamount.")</strong>";
      $listed[] = $item;

    }

}

}

}

[Ken2k7]

I think there's a better way to get what you want. I gotta head home from work so I'll come up with something when I get home.

[CloudSex13]

No worries either way - I appreciate the dedicated assistance,  man.

 

[Ken2k7]

Not tested, but try this -

$query = mysql_query("SELECT Inventory FROM Users WHERE Username='$cookie' LIMIT 1");
$getquery = mysql_fetch_assoc($query);
$inventory = substr($getquery['Inventory'], 0, -1);

$inventory = explode(',',$inventory);
$inventory = array_count_values($inventory);

$item_names = array();

$query = mysql("SELECT ItemID, ItemName FROM Items WHERE ItemID IN (" . $inventory . ") AND ItemType='Box'");
while ($row = mysql_fetch_assoc($query)) {
     $item_names[$row['ItemID']] = $row['ItemName'];
}

foreach ($inventory as $key => $count) {
     echo '<div class="item">' . $item_names[$key] . ' (' . $count . ')</div>';
}

[CloudSex13]

That seems to output nothing.

 

Is there a way I could speak with you over MSN, Skype, or AOL about this?

[Ken2k7]

That seems to output nothing.

I could be messing up the SQL.

 

$query = mysql_query("SELECT Inventory FROM Users WHERE Username='$cookie' LIMIT 1");
$getquery = mysql_fetch_assoc($query);
$inventory = substr($getquery['Inventory'], 0, -1);

$inventory = explode(',',$inventory);
$inventory = array_count_values($inventory);

$item_names = array();

$sql = "SELECT ItemID, ItemName FROM Items WHERE ItemID IN (" . $inventory . ") AND ItemType='Box'";
$query = mysql_query($sql) or die(mysql_error() . ' @ ' . $sql);
while ($row = mysql_fetch_assoc($query)) {
     $item_names[$row['ItemID']] = $row['ItemName'];
}
var_dump($item_names);
var_dump($inventory);
foreach ($inventory as $key => $count) {
     echo '<div class="item">' . $item_names[$key] . ' (' . $count . ')</div>';
}

 

Any output?

 

Is there a way I could speak with you over MSN, Skype, or AOL about this?

Not currently. I'm not signed into any of those. Is this something that can't be said through the forums or PM?

[CloudSex13]

Yeah, I PMed you but you never replied back.

 

Error:

 

Unknown column 'Array' in 'where clause'

[Ken2k7]

I edited my post. You may have gotten to it a bit earlier. Can you copy and paste once more and tell me the error now?

[CloudSex13]

Same error keeps appearing, man:

 

Unknown column 'Array' in 'where clause'

 

D: