Matching A Field's Multiple Comma Seperated Values to Another Field's Values

CloudSex13 · May 15, 2008

Hey.

So that title was confusing. I'll go a little more in depth:

I've been trying, over the past month, to do this.

Maybe it's because there's a PHP function I don't know,

or something of the sort, but it confuses the life out of me.

Everything in bold is the main focus.

Let's say we have two tables in a database:

Table 1 is called Products.

Table 2 is called Account.

Products has multiple fields in the table, including:

ProductID

ProductName

ProductType

ProductPrice

Account has multiple fields in the table, including:

AccountID

AccountName

AccountProductList

Now, let's say that the field AccountProductList has the type "text" and a current value of the following:

1, 43, 675, 348, 3, 69, 3459

Each one of those numbers represents a ProductID from the Products table.

Let's make an example Product row:

ProductID = 1

ProductName = "White Tee"

ProductType = "Shirts"

ProductPrice = "13.00"

Now, on a page called inventory.php, we want to have different separated sections organized by ProductType.

There would be sections called Shirts, Pants, Shoes, etc.

What I've been trying to accomplish is that when the Inventory link is clicked, it will go the the inventory.php page and the value of AccountProductList will match each individual number to the correct section (i.e. Shirts, Pants, Shoes) and display the results of only what's in that user's AccountProductList.

I'm a chill guy, and down to earth. I know my PHP, and I'm not asking for someone to code this for me. I'm simply asking for any help whatsoever on how to do this, cause I've been at a halt with my project for about a month now, and it's mind-bending.

For those of you who stop by to read this or even help, THANK YOU SO MUCH.

Have a nice day

MadTechie · May 15, 2008

unless i misread this,

what you can do is a simple sql statment using IN, for example

SELECT * FROM Products WHERE ID IN(1, 43, 675, 348, 3, 69, 3459)

then echo $row['ProductType'] ect

CloudSex13 · May 15, 2008

Well, the only problem with that code is that the items are specified in it. The items are always changing, being deleted - it all comes down to the field of AccountProductList. Is there a way the variable could be expressed using that IN statement?

MadTechie · May 15, 2008

yes just use the AccountProductList field

something like this

SELECT *.Products FROM Products, Account 
WHERE ID IN (Account.AccountProductList)

CloudSex13 · May 15, 2008

Thanks for the help again

I actually have the following code, but of course, it doesn't work.

Is your snippet placed in the correct spot?

<?php

include('connect.php');

$getaccountinfo = mysql_query("SELECT * FROM Account");

$snagaccount = mysql_fetch_array($getaccountinfo);

$AccountID = $snagaccount['AccountID'];
$AccountName = $snagaccount['AccountName'];
$AccountProductList = $snagaccount['AccountProductList'];

$getproducts = mysql_query("SELECT * FROM Products");

$snagproducts = mysql_fetch_array($getproducts);

$ProductID = $snagproducts['ProductID'];
$ProductName = $snagproducts['ProductName'];
$ProductType = $snagproducts['ProductType'];
$ProductPrice = $snagproducts['ProductPrice'];

$getaccountlist = mysql_query("SELECT *.Products FROM Products, Account WHERE ID IN (Account.AccountProductList)");

$row = mysql_fetch_array($getaccountlist);

$ItemID = $row['ItemID'];
$ItemName = $row['ItemName'];
$ItemType = $row['ItemType'];
$ItemPrice = $row['ItemPrice'];

?>
<html>

<head>

<title>
Inventory
</title>

</head>

<body>

<div align=center>

<br><br><br>
<b>Account ID</b>: <?php echo($AccountID); ?> | 
<b>Account Name</b>: <?php echo($AccountName); ?>
<br><br>

<table>
<tr>
<td>
Shirts: 
</td>
<td>
<?php echo($ItemName); ?>: <?php echo($ItemPrice); ?>
</td>
</tr>
<tr>
<td>
Pants:
</td>
<td>
<?php echo($ItemName); ?>: <?php echo($ItemPrice); ?>
</td>
</tr>
<tr>
<td>
Shoes:
</td>
<td>
<?php echo($ItemName); ?>: <?php echo($ItemPrice); ?>
</td>
</tr>
</table>

</div>

</body>

</html>

MadTechie · May 15, 2008

yes but theirs another problem, it will only return the first record

change

<?php
$getaccountlist = mysql_query("SELECT *.Products FROM Products, Account WHERE ID IN (Account.AccountProductList)");

$row = mysql_fetch_array($getaccountlist);

$ItemID = $row['ItemID'];
$ItemName = $row['ItemName'];
$ItemType = $row['ItemType'];
$ItemPrice = $row['ItemPrice'];
?>

to

$getaccountlist = mysql_query("SELECT *.Products FROM Products, Account WHERE ID IN (Account.AccountProductList)");

echo "ItemID - ItemName - ItemType - ItemPrice";
while($row = mysql_fetch_array($getaccountlist)
{
$ItemID = $row['ItemID'];
$ItemName = $row['ItemName'];
$ItemType = $row['ItemType'];
$ItemPrice = $row['ItemPrice'];

echo "$ItemID - $ItemName - $ItemType - $ItemPrice";
?>

your need to build this into the html part a (mine is just basic) to check the correct data is being returned

CloudSex13 · May 15, 2008

Feel free to yell at me, but I'm still lost. Like, what do you mean build it into the HTML part?

Here's the updated code if it helps you help me if so?

Sorry man.

???

<?php

include('connect.php');

$getaccountinfo = mysql_query("SELECT * FROM Account");

$snagaccount = mysql_fetch_array($getaccountinfo);

$AccountID = $snagaccount['AccountID'];
$AccountName = $snagaccount['AccountName'];
$AccountProductList = $snagaccount['AccountProductList'];

$getproducts = mysql_query("SELECT * FROM Products");

$snagproducts = mysql_fetch_array($getproducts);

$ProductID = $snagproducts['ProductID'];
$ProductName = $snagproducts['ProductName'];
$ProductType = $snagproducts['ProductType'];
$ProductPrice = $snagproducts['ProductPrice'];

$getaccountlist = mysql_query("SELECT *.Products FROM Products, Account WHERE ID IN (Account.AccountProductList)");

echo "ItemID - ItemName - ItemType - ItemPrice";

while($row = mysql_fetch_array($getaccountlist)) {
$ItemID = $row['ItemID'];
$ItemName = $row['ItemName'];
$ItemType = $row['ItemType'];
$ItemPrice = $row['ItemPrice'];

}

?>
<html>

<head>

<title>
Inventory
</title>

</head>

<body>

<div align=center>

<br><br><br>
<b>Account ID</b>: <?php echo($AccountID); ?> | 
<b>Account Name</b>: <?php echo($AccountName); ?>
<br><br>

<?php echo "$ItemID - $ItemName - $ItemType - $ItemPrice"; ?>

<table>
<tr>
<td>
Shirts: 
</td>
<td>
<?php echo($ItemName); ?>: <?php echo($ItemPrice); ?>
</td>
</tr>
<tr>
<td>
Pants:
</td>
<td>
<?php echo($ItemName); ?>: <?php echo($ItemPrice); ?>
</td>
</tr>
<tr>
<td>
Shoes:
</td>
<td>
<?php echo($ItemName); ?>: <?php echo($ItemPrice); ?>
</td>
</tr>
</table>

</div>

</body>

</html>

CloudSex13 · May 15, 2008

No but like, I'm not trying to be a jerk and get you to code it for me, I'm just being honest - I'm lost.

lol, sorry man.

MadTechie · May 15, 2008

Okay first off, did you get a list, with correct data ?

if not then we need to fix that first..

if thats working then we need to make it look nice.

for example into a table not just raw text (as mine is)

CloudSex13 · May 15, 2008

A list with correct data?

Like, the rows in a table?

MadTechie · May 15, 2008

the output should of looked something like this

ItemID - ItemName - ItemType - ItemPrice
111 - abc1 - thing1 - $5.99
112 - abc1 - thing2 - $5.99
113 - abc1 - thing3 - $5.99
etc

CloudSex13 · May 15, 2008

Well, nothing is appearing.

I actually updated the coding so it's easier to read and output.

I also noticed in the WHERE ID IN statement doesn't like, specify the different ProductType.

Is it possible to do two WHERE statements so there are different ProductType sections?

By the way, thanks so much for taking your time to help me out. You're a cool dude.

<?php

include('connect.php');

$getaccountinfo = mysql_query("SELECT * FROM Account");

$snagaccount = mysql_fetch_array($getaccountinfo);

$AccountID = $snagaccount['AccountID'];
$AccountName = $snagaccount['AccountName'];
$AccountProductList = $snagaccount['AccountProductList'];

?>
<html>

<head>

<title>
Inventory
</title>

</head>

<body>

<div align=center>

<br><br><br>
<b>Account ID</b>: <?php echo($AccountID); ?> | 
<b>Account Name</b>: <?php echo($AccountName); ?>
<br><br>

<?php

$getaccountlist = mysql_query("SELECT *.Products FROM Products, Account WHERE ID IN (Account.AccountProductList)");

echo "
<table>
<tr>
<td align=center colspan=4>
<b>Shirts</b>
</td>
</tr>
<tr>
<td>
Product ID
</td>
<td>
Product Name
</td>
<td>
Product Type
</td>
<td>
Product Price
</td>
</tr>";

while($row = mysql_fetch_array($getaccountlist)) {
$UserProductID = $row['ProductID'];
$UserProductName = $row['ProductName'];
$UserProductType = $row['ProductType'];
$UserProductPrice = $row['ProductPrice'];

}

echo "
<tr>
<td>
".$UserProductID."
</td>
<td>
".$UserProductName."
</td>
<td>
".$UserProductType."
</td>
<td>
".$UserProductPrice."
</td>
</tr>
</table>";

?>

</div>

</body>

</html>

MadTechie · May 15, 2008

Heres an update

Note the change to the SQL statement "ORDER BY ProductType"

<?php

include('connect.php');

$getaccountinfo = mysql_query("SELECT * FROM Account");

$snagaccount = mysql_fetch_array($getaccountinfo);

$AccountID = $snagaccount['AccountID'];
$AccountName = $snagaccount['AccountName'];
$AccountProductList = $snagaccount['AccountProductList'];

?>
<html>

<head>

<title>
Inventory
</title>

</head>

<body>

<div align=center>

<br><br><br>
<b>Account ID</b>: <?php echo($AccountID); ?> | 
<b>Account Name</b>: <?php echo($AccountName); ?>
<br><br>

<?php

$getaccountlist = mysql_query("SELECT *.Products FROM Products, Account WHERE ID IN (Account.AccountProductList) ORDER BY ProductType");

echo "
<table>
<tr>
<td align=center colspan=4>
<b>Shirts</b>
</td>
</tr>
<tr>
<td>
Product ID
</td>
<td>
Product Name
</td>
<td>
Product Type
</td>
<td>
Product Price
</td>
</tr>";

$type = "";
while($row = mysql_fetch_array($getaccountlist))
{
$UserProductID = $row['ProductID'];
$UserProductName = $row['ProductName'];
$UserProductType = $row['ProductType'];
$UserProductPrice = $row['ProductPrice'];
//this If create headers for each type
if($type != $UserProductType)
{
	echo "<tr><td colspan=\"4\">$UserProductType</td></tr>";
	$type = $UserProductType;
}

echo "
<tr>
<td>
".$UserProductID."
</td>
<td>
".$UserProductName."
</td>
<td>
".$UserProductType."
</td>
<td>
".$UserProductPrice."
</td>
</tr>";

}


echo "</table>";

?>

</div>

</body>

</html>

The Little Guy · May 15, 2008

If this was me, this is how I would do it:

session_start();
$sql = mysql_query("SELECT * FROM Account WHERE AccountID = '{$_SESSION['id']}'");
$row = mysql_fetch_array($sql);
$prods = explode(",",$row['AccountProductList']);
foreach($prods as $prod){
     $p = trim($prod);
     $sql2 = mysql_query("SELECT * FROM Products WHERE ProductID = '$p'");
     $row2 = mysql_fetch_array($sql2);
     echo 'ProductName: '.$row2['ProductName'].'<br />';
     echo 'ProductType: '.$row2['ProductType'].'<br />';
     echo 'ProductPrice: '.$row2['ProductPrice'].'<br />';
}

CloudSex13 · May 15, 2008

To MadTechie:

Oh, I know the ORDER BY command. I mean like, different tables for each ProductType.

Like, Shirts would have its own separate table with only ProductType="Shirt", Jeans would have its own separate table and etc.

The updated code still doesn't display anything ??? ?

As an overview, like, a certain user with the AccountProductList field in the Account table has, in example, the number 1 in it as well as many other numbers.

1 and the many other numbers would then match to the ProductID in the Products table.

1 would then acquire the ProductName and other field values for that corresponding AccountProductList ProductID and organize itself into the proper separate HTML table, per say, ProductType="Shirt", then it'd go into the Shirts table and ouput all the information for that ProductID.

lol, is that all still possible with WHERE ID IN and everything?

That may be the problem?

MadTechie · May 15, 2008

yes but unneeded the update will break them up, well kinda.. your see what i mean,

in thelast update i forgot to update the SQL

so change

$getaccountlist = mysql_query("SELECT *.Products FROM Products, Account WHERE ID IN (Account.AccountProductList) ORDER BY ProductType");

to

$getaccountlist = mysql_query("SELECT *.Products FROM Products, Account WHERE Products.ProductID IN (Account.AccountProductList)  ORDER BY ProductType");

CloudSex13 · May 15, 2008

To TheLittleGuy:

THAT WORKED.

THANK YOU.

I do have to more questions with that new coding idea though, if you don't mind?

Is there a way to separate them by ProductType into different HTML tables (i.e. Shirts, Jeans, Shoes)?

And also, in example, let's say AccountProductList = "1, 1, 3, 1"

Right now, it displays each output as lined up in AccountProductList, which is cool.

But...

Let's say ProductID 1's ProductName is "White Tee".

Is there a way to make it look like:

White Tee (3)

Blue Shoes (1)

Like, it will add up the amount of the same ProductIDs in AccountProductList and organize it like:

Shirts:

White Tee (3)

Pants:

None.

Shoes:

Blue Shoes (1)

Would your coding idea be able to accomplish this?

I'm really impressed, like, I'm actually not familiar with the foreach command... yet.

lol, wow. Thanks again man, this is ILL. I'm like, stoked lol.

CloudSex13 · May 15, 2008

To MadTechie:

Ha, I should have caught that.

But odd enough again, that solution still leaves the variables blank...

The Little Guy · May 15, 2008

What does the number represent?

MadTechie · May 15, 2008

strange your not getting any errors

try this

$getaccountlist = mysql_query("SELECT *.Products FROM Products, Account WHERE Products.ProductID IN (Account.AccountProductList) ORDER BY ProductType") or die(mysql_error());

see if we can get an error to help

CloudSex13 · May 15, 2008

To TheLittleGuy:

The number in parenthesis represents the number of times the ProductID appears in AccountProductList.

So if AccountProductList has a value of "1, 1, 3, 1, 71", and ProductID 1's ProductName was "White Tee", then the output would be:

White Tee (3)

It would like add the number of times the same ProductID is in the AccountProductList field and display it next to each item, so ProductID 3 would look like:

Blue Shoes (1)

And ProductID 71 would look like:

Necklace (1)

Yeah. lol. =)

CloudSex13 · May 15, 2008

To MadTechie:

Thank you again for your help.

The error that appeared is the following:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.Products FROM Products, Account WHERE Products.ProductID IN (Account.AccountPro' at line 1

Hmm...

MadTechie · May 15, 2008

LMAO..

i don't believe i missed that,

okay lets see if theirs a non-dumb bug

$getaccountlist = mysql_query("SELECT Products.* FROM Products, Account WHERE Products.ProductID IN (Account.AccountProductList)  ORDER BY ProductType") or die(mysql_error());

The Little Guy · May 15, 2008

function count_array($val,$array){
$i = 0;
if(!is_array($array)){
	return 'Second value is not an array.';
}else{
	foreach($array as $v){
		if($val == $v){
			$i++;
		}
	}
	return $i;
}
}

session_start();
$sql = mysql_query("SELECT * FROM Account WHERE AccountID = '{$_SESSION['id']}'");
$row = mysql_fetch_array($sql);
$prods = explode(",",$row['AccountProductList']);
foreach($prods as $prod){
     $p = trim($prod);
     $sql2 = mysql_query("SELECT * FROM Products WHERE ProductID = '$p'");
     $row2 = mysql_fetch_array($sql2);

     echo 'ProductName: '.$row2['ProductName'].' ( '.count_array($p,$prods).' )<br />';
     echo 'ProductType: '.$row2['ProductType'].'<br />';
     echo 'ProductPrice: '.$row2['ProductPrice'].'<br />';
}

CloudSex13 · May 15, 2008

To TheLittleGuy:

THAT WORKED.

But it opened up another can of worms...

The output looks something like this:

White Tee (3) - $13

Snazzy Kicks (1) - $54

White Tee (3) - $13

Is there any way to make each product only display once now?

Like White Tee still displays three times even though the number is there and works.

Does that defeat the purpose of the foreach command?

Sign In

Matching A Field's Multiple Comma Seperated Values to Another Field's Values

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information