Jump to content

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

Hepp

By Hepp
in PHP Coding Help

 Share

Recommended Posts

Hepp Newbie

Hepp

Posted
Posted
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]
Hepp Newbie

Hepp

Posted
  • Author
Posted
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
mainewoods Newbie

mainewoods

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

Hepp Newbie

Hepp

Posted
  • Author
Posted
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]

... ???
Hepp Newbie

Hepp

Posted
  • Author
Posted
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?
Hepp Newbie

Hepp

Posted
  • Author
Posted
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
mainewoods Newbie

mainewoods

Posted
Posted
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]
mainewoods Newbie

mainewoods

Posted
Posted
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.
Hepp Newbie

Hepp

Posted
  • Author
Posted
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...  ???
mainewoods Newbie

mainewoods

Posted
Posted
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]
Hepp Newbie

Hepp

Posted
  • Author
Posted
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.  =)
Hepp Newbie

Hepp

Posted
  • Author
Posted
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.
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

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.