Jump to content


Photo

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


  • Please log in to reply
21 replies to this topic

#1 Hepp

Hepp
  • Members
  • PipPipPip
  • Advanced Member
  • 33 posts

Posted 12 September 2006 - 02:12 PM

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:

<?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>


#2 ober

ober
  • Staff Alumni
  • Advanced Member
  • 5,337 posts
  • LocationEast Coast, USA

Posted 12 September 2006 - 02:19 PM

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.

Info: PHP Manual


#3 Hepp

Hepp
  • Members
  • PipPipPip
  • Advanced Member
  • 33 posts

Posted 12 September 2006 - 09:13 PM

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

#4 mainewoods

mainewoods
  • Members
  • PipPipPip
  • Advanced Member
  • 685 posts
  • LocationMaine

Posted 12 September 2006 - 09:31 PM

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/m...ount-values.php



#5 Hepp

Hepp
  • Members
  • PipPipPip
  • Advanced Member
  • 33 posts

Posted 12 September 2006 - 09:53 PM

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...

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

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

Blah blah blah blah...

<?php echo($totalamount) ?>

... ???

#6 mainewoods

mainewoods
  • Members
  • PipPipPip
  • Advanced Member
  • 685 posts
  • LocationMaine

Posted 12 September 2006 - 09:57 PM

you don't use the '=' with print_r like that, do:
print_r(array_count_values($itemslist));
and then me what prints out


#7 Hepp

Hepp
  • Members
  • PipPipPip
  • Advanced Member
  • 33 posts

Posted 12 September 2006 - 10:02 PM

It displays:

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

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?

#8 mainewoods

mainewoods
  • Members
  • PipPipPip
  • Advanced Member
  • 685 posts
  • LocationMaine

Posted 12 September 2006 - 10:08 PM

use:
$valuesarray = array_count_values($itemslist);
foreach ($valuesarray  as $key => $value) {
    //do something with each $key/$value here
}


#9 Hepp

Hepp
  • Members
  • PipPipPip
  • Advanced Member
  • 33 posts

Posted 12 September 2006 - 10:19 PM

I came up with this:

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";

}

}

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:

Necklace (3) Necklace (5) Necklace (7) Earrings (3) Earrings (5) Earrings (7) Glasses (3) Glasses (5) Glasses (7)

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

#10 mainewoods

mainewoods
  • Members
  • PipPipPip
  • Advanced Member
  • 685 posts
  • LocationMaine

Posted 12 September 2006 - 10:59 PM

you're welcome, try this:

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


#11 Hepp

Hepp
  • Members
  • PipPipPip
  • Advanced Member
  • 33 posts

Posted 13 September 2006 - 12:47 AM

Argh... I'm going to be honest...

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

Ugh... :-\

#12 Hepp

Hepp
  • Members
  • PipPipPip
  • Advanced Member
  • 33 posts

Posted 13 September 2006 - 01:06 PM

What PHP function would be used to do this?

#13 mainewoods

mainewoods
  • Members
  • PipPipPip
  • Advanced Member
  • 685 posts
  • LocationMaine

Posted 13 September 2006 - 03:43 PM

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.

#14 Hepp

Hepp
  • Members
  • PipPipPip
  • Advanced Member
  • 33 posts

Posted 13 September 2006 - 05:49 PM

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...  ???

#15 Hepp

Hepp
  • Members
  • PipPipPip
  • Advanced Member
  • 33 posts

Posted 14 September 2006 - 02:49 PM

Oh, and the "itemidlist" field is in the table "customers".

#16 mainewoods

mainewoods
  • Members
  • PipPipPip
  • Advanced Member
  • 685 posts
  • LocationMaine

Posted 14 September 2006 - 07:02 PM

Try this:
//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


#17 Hepp

Hepp
  • Members
  • PipPipPip
  • Advanced Member
  • 33 posts

Posted 14 September 2006 - 08:54 PM

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:

= 6 = 8 = 10
= 6 = 8 = 10
= 6 = 8 = 10

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:

<?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>

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

#18 sasa

sasa
  • Staff Alumni
  • Advanced Member
  • 2,804 posts
  • LocationHrvatska

Posted 14 September 2006 - 09:27 PM

change line
while (!$onename = mysql_fetch_array($itemnames)) {
to
while ($onename = mysql_fetch_array($itemnames)) {
remove !

#19 Hepp

Hepp
  • Members
  • PipPipPip
  • Advanced Member
  • 33 posts

Posted 15 September 2006 - 10:17 PM

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:

<?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>

Thank you again. I am very grateful.

#20 Hepp

Hepp
  • Members
  • PipPipPip
  • Advanced Member
  • 33 posts

Posted 17 September 2006 - 05:41 AM

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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users