Jump to content

Archived

This topic is now archived and is closed to further replies.

Hepp

Items Won't Display How Many There Are In A Field In A Database Need Help Please

Recommended Posts

Hey,

I am trying to create a page that goes into my database, selects something called an "itemidlist", put it into an array, and display how many of each of the different items there are for each person.

Basically, in a table in the database called customers, there is the field of "itemidlist". The field vaules in this could look like "1,2,1,2,2,3,5,2,4,7,8,4,23,256,98,46" and etc. I want this to take thos numbers, and go into a databse called "items". In there, it matches the "itemid", or the values in "itemidlist" and displays the "itemname", that field being also in the table "items". It displays the name and everything fine, but I can't get the code to display how many of every different item it has, and the total amount of the items altogether.

Ex:

An item is called "Necklace". The "itemid" of "Necklace" is "1".
Another item is called "Earring". The "itemid" of "Earring" is "2".
Another item is called "Glasses". The "itemid" of "Glasses" is "3".

In "itemidlist", it contains the data "1,3,1,3,1,2,1,2,3"

It takes the code, matches the "itemid" with the "itemname" and displays it correctly. It does not though tell in parenthesis on the side how many of "Necklace", "Earring", and "Glasses" there are.

It should say:

Nacklace (4)
Earring (2)
Glasses (3)

But it does not.

Can anyone out there, please for the love of anything, find out how to fix this? I've been trying to get it to work for two weeks now, and I seemingly cannot.

Thank you so very much for your help and even reading this,
Hepp

The code is below:

[code]
<?php

include('config.php');


$customerquery = mysql_query("SELECT * FROM customers WHERE customername='".$_COOKIE['customername']."' LIMIT 1");

$snagcustomer = mysql_fetch_array($playerquery);

$customername = ($snagplayer['customername']);


$itemidlistquery = mysql_query("SELECT itemidlist FROM players WHERE customername='".$_COOKIE['customername']."' LIMIT 1");

$itemidlistrow = mysql_fetch_array($itemidlistquery);

$itemidlist = ($itemidlistrow['itemidlist']);


$itemslist = explode(",",$itemidlist);

$inventorystring = "";


$itemidquery = mysql_query("SELECT itemid FROM items");

$itemidrow = mysql_fetch_array($itemidquery);


foreach($itemslist as $a=>$b) {

$inventorystring .= "itemid='$b' OR ";

}

$totalamount = ($a + $b);

$inventorystring .= rtrim($inventorystring, " OR ");

$itemsquery = mysql_query("SELECT * FROM items WHERE $inventorystring ORDER BY itemname");

while ($itemsrow = mysql_fetch_array($itemsquery)) {

$page .= "
<tr>
<td width=100% align=center>
<b>".$itemsrow['itemname']."</b> (". $totalamount .")
</td>
</tr>\n";

}

?>
<link href=styles.css rel=stylesheet type=text/css>
<html>
<head>
</head>
<body>
<center>
<form action=inventory.php method=post>
<table width=60% align=center>
<tr>
<td align=center width=100% colspan=2 cellpadding=3>
<b>Inventory</b>
</td>
</tr>
<tr>
<td width=100% align=center>
<br>
You have <font color=lime><b><?php echo($totalamount); ?></b></font> items.
</td>
</tr>
</table>

<?php echo($page); ?>
</table>
</form>

<p>
</center>
</body>
</html>
[/code]

Share this post


Link to post
Share on other sites
You need to use the COUNT SQL function to grab the number of these items.  You'll have to run an extra query, but that'll get the information you're looking for.

Share this post


Link to post
Share on other sites
Thank you for the reply, Ober.

I went to Google and researched up the COUNT SQL function, and it told me that that would be used to display the amount of rows in the query of the database table.

This data, 1,2,3,1,23,1,23,594,54394 etc. is contained within one field in the table of the database, as you know.

So, I am not exactly understanding what you mean when you said that...  ???

Thank you so very much though,
-Hepp

Share this post


Link to post
Share on other sites
Use: explode ( string separator, string string [, int limit] ) to convert your field into an array, then use the: array_count_values ( array input ) function to count how many there are of each.
http://us2.php.net/manual/en/function.array-count-values.php

Share this post


Link to post
Share on other sites
Okay, so I tried doing what you suggested, and I failed miserably. I managed to come up with this weak coding... I pity myself as a coder...

[code]
$itemslist2 = explode(",",$itemidlist);

$totalamount = print_r(array_count_values($itemslist));

Blah blah blah blah...

<?php echo($totalamount) ?>
[/code]

... ???

Share this post


Link to post
Share on other sites
you don't use the '=' with print_r like that, do:
[code]print_r(array_count_values($itemslist));[/code]
and then me what prints out

Share this post


Link to post
Share on other sites
It displays:

[code]
You have Array ( [1] => 3 [2] => 5 [3] => 7 ) 1 items.
[/code]

That array is indeed correct. There are 3 "1's", and etc.

YAY! IT'S GETTING THERE!

Now how could I serperate them and display them differently in one coding while loop...? Or something?

Share this post


Link to post
Share on other sites
use:
[code]$valuesarray = array_count_values($itemslist);
foreach ($valuesarray  as $key => $value) {
    //do something with each $key/$value here
}[/code]

Share this post


Link to post
Share on other sites
I came up with this:

[code]
while ($itemsrow = mysql_fetch_array($itemsquery)) {

$valuesarray = array_count_values($itemslist2);

foreach ($valuesarray as $key => $value) {

$totalvalue = $value;

$page .= "
<tr>
<td width=100% align=center>
<b>".$itemsrow['itemname']."</b> (". $value .")
</td>
</tr>\n";

}

}
[/code]

I'm going to be honest, and I know how peoiple hate it, but I'm totally clueless here on what next to do. I got it to display this:

[code]
Necklace (3) Necklace (5) Necklace (7) Earrings (3) Earrings (5) Earrings (7) Glasses (3) Glasses (5) Glasses (7)
[/code]

I think I just need some if statements, but I'd like to make sure about that. Would you suggest anything more?

THANK YOU SO MUCH MAINEWOODS FOR ALL YOUR HELP  ;D

Share this post


Link to post
Share on other sites
you're welcome, try this:

[code]while ($itemsrow = mysql_fetch_array($itemsquery)) {
    $itemslist2 = explode($itemsrow['**yourdbfieldname**']);
    $valuesarray = array_count_values($itemslist2);
    foreach ($valuesarray as $key => $value) {
        //write some code to get the translation of
        //the $key code from your database
        echo $thewordtranslationofkey . '= ' . $value . '<br>';
    } //ends foreach
} //ends the while[/code]

Share this post


Link to post
Share on other sites
Argh... I'm going to be honest...

I don't know how to make $key display the value for every different itemid.

Ugh... :-\

Share this post


Link to post
Share on other sites
do you have a separate table that has the word translations of the numbers in the field: "1,2,1,2,2,3,5,2,4,7,8,4,23,256,98,46"? If so,  and if the number of items in that table is small (<300?) you should just read the entire table into an array at the beginning of the page and use that for your translations further down the page.  If you do it that way, it should run way faster than executing a large amount of sql statements within the loop to get the translation.

Share this post


Link to post
Share on other sites
I do have a seperate table called "items", that has the field "itemid" and "itemname" to communicate with "itemidlist" to display the name of the item in the variable "$page". I can't get my head to think of how I could tell the code to display how many times that itemid number is in "itemidlist" for the itemname. I don't know how to get it to do that in that while() loop...  ???

Share this post


Link to post
Share on other sites
Try this:
[code]//open db code here
$getnames = "SELECT * FROM items"; //gets all the rows
$itemnames = mysql_query($getnames );
while (!$onename = mysql_fetch_array($itemnames )) {
    $transname[$onename['itemid']] = $onename['itemname']; //building the $transname array
}
//the $transname array should contain all the values in the items table
//it uses the primary key of each record as the array key as well

while ($itemsrow = mysql_fetch_array($itemsquery)) {
    $itemslist2 = explode($itemsrow['customers']);
    $valuesarray = array_count_values($itemslist2);
    foreach ($valuesarray as $key => $value) {
        echo $transname[$key] . '= ' . $value . ' '; //prints one summary item
    } //ends foreach
    echo '<br>'; new line for new table row
} //ends the while[/code]

Share this post


Link to post
Share on other sites
Okay, so I tried that, and it didn't exactly work. I had to edit it a bit too to get it to at least say what it does now.

It displays the following at the top of the page now:

[code]
= 6 = 8 = 10
= 6 = 8 = 10
= 6 = 8 = 10
[/code]

The numbers are correct, and I know it's almost there... but I just can't think of this.

Here is the coding for the entire page itself:

[code]
<?php

include('config.php');

$itemidlistquery = mysql_query("SELECT itemidlist FROM customers WHERE username='".$_COOKIE['username']."' LIMIT 1");

$itemidlistrow = mysql_fetch_array($itemidlistquery);

$itemidlist = ($itemidlistrow['itemidlist']);

$itemidlist2 = ($itemidlistrow['itemidlist']);


$itemslist = explode(",",$itemidlist);

$inventorystring = "";


$itemidquery = mysql_query("SELECT itemid FROM items");

$itemidrow = mysql_fetch_array($itemidquery);


foreach($itemslist as $a=>$b) {

$inventorystring .= "itemid='$b' OR ";

}

$inventorystring .= rtrim($inventorystring, " OR ");

$itemsquery = mysql_query("SELECT * FROM items WHERE $inventorystring ORDER BY itemname");

//NEW CODING

$getnames = "SELECT * FROM items"; //gets all the rows
$itemnames = mysql_query($getnames);
while (!$onename = mysql_fetch_array($itemnames)) {
    $transname[$onename['itemid']] = $onename['itemname']; //building the $transname array
}
//the $transname array should contain all the values in the items table
//it uses the primary key of each record as the array key as well


while ($itemsrow = mysql_fetch_array($itemsquery)) {

$itemslist2 = explode(",",$itemidlist2);

$valuesarray = array_count_values($itemslist2);


foreach ($valuesarray as $key => $value) {

echo $transname[$key] . ' = ' . $value . ' ';

}


echo '<br>';

}

?>
<!--

//OLD CODING

while ($itemsrow = mysql_fetch_array($itemsquery)) {

$itemslist3 = explode(",",$itemidlist2);

$valuesarray = array_count_values($itemslist3);


foreach ($valuesarray as $key => $value) {

        //write some code to get the translation of
        //the $key code from your database

//get $key to euqal $itemid and them for that $itemid to equal that $values

$itemid = ($itemsrow['itemid']);

$whatevs = ($count_values[$value]);

}

$page .= "
<tr>
<td width=100% align=center>
<b>".$itemsrow['itemname']."</b> (". $whatevs .")
</td>
</tr>\n";

}
-->
<link href=styles.css rel=stylesheet type=text/css>

<html>

<head>

</head>

<body>

<center>

<form action=inventory.php method=post>
<table width=60% align=center>
<tr>
<td align=center width=100% colspan=2 cellpadding=3>
<b>Inventory</b>
</td>
</tr>
</table>

<?php echo($page); ?>
</table>
</form>
<p>

</center>

</body>

</html>
[/code]

MaineWoods, again, thank you so much for sticking with me and helping me. You are a PHP God. Thank you so much.  =)

Share this post


Link to post
Share on other sites
change line [code]while (!$onename = mysql_fetch_array($itemnames)) {[/code]to
[code]while ($onename = mysql_fetch_array($itemnames)) {[/code]remove [color=red]![/color]

Share this post


Link to post
Share on other sites
OMG! IT WORKED! THANK YOU SASA AND MAINEWOODS!

But... that led to yet another problem.

I can't find a way, no matter what, to get the items displayed to order by "itemname". Instead, they're ordering in the order they were purchased and inputed into the database.

I also was wondering if there is a way that you can add all the values together of all the items in the array to get it to display a variable called "$totalamount"...?

I can't figure out how to do both, but seriously, thank you MaineWoods and Sasa for all the help so far  ;D

Here is my current coding below if it helps:

[code]
<?php

include('config.php');

$itemidlistquery = mysql_query("SELECT itemidlist FROM customers WHERE username='".$_COOKIE['username']."' LIMIT 1");

$itemidlistrow = mysql_fetch_array($itemidlistquery);
$itemidlist = ($itemidlistrow['itemidlist']);

$itemslist = explode(",",$itemidlist);
$inventorystring = "";
foreach($itemslist as $a => $b)
{
$inventorystring .= "itemid='$b' OR ";
}
$inventorystring .= rtrim($inventorystring, " OR ");
$itemsquery = mysql_query("SELECT * FROM items WHERE $inventorystring ORDER BY itemname LIMIT 1");
while ($itemsrow = mysql_fetch_array($itemsquery))
{
$itemnames = mysql_query("SELECT * FROM items ORDER BY itemname");
while ($onename = mysql_fetch_array($itemnames))
{
$transname[$onename['itemid']] = $onename['itemname'];
}
$itemidlist2 = ($itemidlistrow['itemidlist']);
$itemslist2 = explode(",",$itemidlist2);
$valuesarray = array_count_values($itemslist2);
foreach ($valuesarray as $name => $amount)
{
$page .= "
<tr>
<td width=100% align=center>
<b>".$transname[$name]."</b> (". $amount .")
</td>
</tr>\n";
}
$page .= "
<br>\n";
}
?>
<link href=styles.css rel=stylesheet type=text/css>

<html>

<head>

</head>

<body>

<center>

<form action=inventory.php method=post>
<table width=60% align=center>
<tr>
<td align=center width=100% colspan=2 cellpadding=3>
<b>Inventory</b>
</td>
</tr>
<tr>
<td width=100% align=center>
<br>
You have <font color=lime><b><?php echo($totalamount); ?></b></font> items.
</td>
</tr>
</table>

<?php echo($page); ?>
</table>
</form>
<p>

</center>

</body>

</html>
[/code]

Thank you again. I am very grateful.

Share this post


Link to post
Share on other sites
Cause there's going to be more values than just the three, there's going to be billions at most. I just can't see how it could add up, or order by the itemname.

Share this post


Link to post
Share on other sites
Yeah, if anyone knows how to do that and could help, that would be the best. Thanks =)

Share this post


Link to post
Share on other sites
my suggestin is thet you change database structur
change field itemidlist to new table thet contain fields userid and itemid

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.