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

CloudSex13 · May 15, 2008

To MadTechie:

THAT WORKED.

But it only displayed one entry, for the White Tee.

What if AccountProductList = "1, 1, 3, 1, 71"

Could it display each one?

The Little Guy · May 15, 2008

function count_array($val,$array){
$i = 0;
if(!is_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']);
$listed = array();
foreach($prods as $prod){
     $p = trim($prod);
     $sql2 = mysql_query("SELECT * FROM Products WHERE ProductID = '$p'");
     $row2 = mysql_fetch_array($sql2);
 if(!in_array($p,$listed)){
	echo 'ProductName: '.$row2['ProductName'].' ( '.count_array($p,$prods).' )<br />';
	$listed[] = $p;
}
}

CloudSex13 · May 15, 2008

To TheLittleGuy:

THANK YOU AGAIN.

I've been trying to accomplish this for about a month.

Oh my god.

=)

I also made a little change to organize the results by ProductType and separate them into different HTML tables,

would you suggest doing it this way?

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

$sql = mysql_query("SELECT * FROM Account WHERE AccountID = '1'");
$row = mysql_fetch_array($sql);
$prods = explode(",",$row['AccountProductList']);
$listed = array();
foreach($prods as $prod){
     $p = trim($prod);
     $sql2 = mysql_query("SELECT * FROM Products WHERE ProductID = '$p' AND ProductType='Shirt'");
     $row2 = mysql_fetch_array($sql2);
if(!in_array($p,$listed)){
     echo "Shirts: ".$row2['ProductName']." (".count_array($p,$prods).") - $".$row2['ProductPrice']."<br>";
$listed[] = $p;
}
     $sql3 = mysql_query("SELECT * FROM Products WHERE ProductID = '$p' AND ProductType='Shoes'");
     $row3 = mysql_fetch_array($sql3);
if(!in_array($p,$listed)){
     echo "Shoes: ".$row3['ProductName']." (".count_array($p,$prods).") - $".$row3['ProductPrice']."<br>";
$listed[] = $p;
}
     $sql4 = mysql_query("SELECT * FROM Products WHERE ProductID = '$p' AND ProductType='Pants'");
     $row4 = mysql_fetch_array($sql4);
if(!in_array($p,$listed)){
     echo "Pants: ".$row4['ProductName']." (".count_array($p,$prods).") - $".$row4['ProductPrice']."<br>";
$listed[] = $p;
}
}

MadTechie · May 15, 2008

for some reason the IN doesn't like a varchar so i changed it to suite

<html>
<head>
<title>
Inventory
</title>
</head>
<body>
<div align=center>
<br><br><br>
<?php
$sql = mysql_query("SELECT * FROM Account WHERE AccountID = '{$_SESSION['id']}'");
$row = mysql_fetch_array($sql);
$APL = $row['AccountProductList'];
$AccountID=$row['AccountID'];
$AccountName=$row['AccountName'];
?>
<b>Account ID</b>: <?php echo($AccountID); ?> | 
<b>Account Name</b>: <?php echo($AccountName); ?>
<br><br>
<?php
$getaccountlist = mysql_query("SELECT * FROM Products WHERE ProductID IN ($APL) 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>

CloudSex13 · May 15, 2008

To TheLittleGuy:

I just tested the code. It came out like this:

Shirts: White Tee (3) - $13
Shirts: (1) - $

Wtf?

The Little Guy · May 15, 2008

function count_array($val,$array){
$i = 0;
if(!is_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']);
$listed = array();
foreach($prods as $prod){
     $p = trim($prod);
 if(!in_array($p,$listed)){
	$sql2 = mysql_query("SELECT * FROM Products WHERE ProductID = '$p'");
	$row2 = mysql_fetch_array($sql2);
	echo 'ProductName: '.$row2['ProductName'].' ( '.count_array($p,$prods).' )<br />';
	$listed[] = $p;
}
}

CloudSex13 · May 15, 2008

I tried that out, still no luck with the ProductType separation. Any other suggestions man?

MadTechie · May 15, 2008

who code?

mine or TheLittleGuy:

CloudSex13 · May 15, 2008

To MadTechie:

That was to TheLittleGuy.

To TheLittleGuy:

I tried this code here using while statements,

but it seems to loop forever. Any suggestions if so?

<?php

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

$sql = mysql_query("SELECT * FROM Account WHERE AccountID = '1'");
$row = mysql_fetch_array($sql);
$prods = explode(",",$row['AccountProductList']);
$listed = array();
foreach($prods as $prod){
     $p = trim($prod);
if(!in_array($p,$listed)){
$sql2 = mysql_query("SELECT * FROM Products WHERE ProductID = '$p' ORDER BY ProductName");
$row2 = mysql_fetch_array($sql2);
while ($row2['ProductType'] = "Shirt") {
$shirts = ''.$row2['ProductName'].' ('.count_array($p,$prods).')<br />';
$listed[] = $p;
}
while ($row2['ProductType'] = "Pants") {
$pants = ''.$row2['ProductName'].' ('.count_array($p,$prods).')<br />';
$listed[] = $p;
}
while ($row2['ProductType'] = "Shoes") {
$shoes = ''.$row2['ProductName'].' ('.count_array($p,$prods).')<br />';
$listed[] = $p;
}
}
}

?>

<table>
<tr>
<td>
<b>Shirts:</b>
</td>
</tr>
<tr>
<td>
<?php echo($shirts); ?>
</td>
</tr>
<tr>
<td>
<b>Pants:</b>
</td>
</tr>
<tr>
<td>
<?php echo($pants); ?>
</td>
</tr>
<tr>
<td>
<b>Shoes:</b>
</td>
</tr>
<tr>
<td>
<?php echo($shoes); ?>
</td>
</tr>
</table>

MadTechie · May 15, 2008

$row2['ProductType'] = "Shirt"

should be

$row2['ProductType'] == "Shirt"

on all 3 whiles

CloudSex13 · May 16, 2008

I love you.

CloudSex13 · May 16, 2008

I actually ran into another problem, but I tried to write a workaround but it did not work. Each section only displays one of each product type from AccountProductList. Is there any way for all of them to display? Here's the code I was working on and I'm surprised doesn't work:

$sql = mysql_query("SELECT * FROM Account WHERE AccountID = '1'");
$row = mysql_fetch_array($sql);
$prods = explode(",",$row['AccountProductList']);
$listed = array();
foreach($prods as $prod){
     $p = trim($prod);
if(!in_array($p,$listed)){
$sql2 = mysql_query("SELECT * FROM Products WHERE ProductID = '$p' ORDER BY ProductName");
$totalshirts = 1;
$totalpants = 1;
$totalshoes = 1;
while ($row2 = mysql_fetch_array($sql2)) {
if ($row2['ProductType'] == "Shirt") {
if ($totalshirts == 1) {
$shirts = ''.$row2['ProductName'].' ('.count_array($p,$prods).')<br />';
$listed[] = $p;
$totalshirts = 2; 
} else { 
$shirts = ''.$row2['ProductName'].' ('.count_array($p,$prods).')<br />';
$listed[] = $p;
$totalshirts = 1; 
}
} elseif ($row2['ProductType'] == "Pants") {
if ($totalpants == 1) {
$pants = ''.$row2['ProductName'].' ('.count_array($p,$prods).')<br />';
$listed[] = $p;
$totalpants = 2; 
} else { 
$pants = ''.$row2['ProductName'].' ('.count_array($p,$prods).')<br />';
$listed[] = $p;
$totalpants = 1; 
}
} elseif ($row2['ProductType'] == "Shoes") {
if ($totalshoes == 1) {
$shoes = ''.$row2['ProductName'].' ('.count_array($p,$prods).')<br />';
$listed[] = $p;
$totalshoes = 2; 
} else { 
$shoes = ''.$row2['ProductName'].' ('.count_array($p,$prods).')<br />';
$listed[] = $p;
$totalshoes = 1; 
}
}
}
}
}

?>

<table>
<tr>
<td>
<b>Shirts:</b>
</td>
</tr>
<tr>
<td>
<?php echo($shirts); ?>
</td>
</tr>
<tr>
<td>
<b>Pants:</b>
</td>
</tr>
<tr>
<td>
<?php echo($pants); ?>
</td>
</tr>
<tr>
<td>
<b>Shoes:</b>
</td>
</tr>
<tr>
<td>
<?php echo($shoes); ?>
</td>
</tr>
</table>

CloudSex13 · May 16, 2008

--

CloudSex13 · May 16, 2008

--

CloudSex13 · May 16, 2008

I still have one minor problem, the results won't organize by ProductName as they're intended too. Any further suggestions?

$sql = mysql_query("SELECT * FROM Account WHERE AccountID = '1'");
$row = mysql_fetch_array($sql);
$prods = explode(",",$row['AccountProductList']);
$listed = array();
foreach($prods as $prod){
     $p = trim($prod);
if(!in_array($p,$listed)){
$sql2 = mysql_query("SELECT * FROM Products WHERE ProductID = '$p' ORDER BY ProductName");
$totalshirts = 1;
$totalpants = 1;
$totalshoes = 1;
while ($row2 = mysql_fetch_array($sql2)) {
if ($row2['ProductType'] == "Shirt") {
if ($totalshirts == 1) {
$shirts .= ''.$row2['ProductName'].' ('.count_array($p,$prods).')<br />';
$listed[] = $p;
$totalshirts = 2; 
} else { 
$shirts .= ''.$row2['ProductName'].' ('.count_array($p,$prods).')<br />';
$listed[] = $p;
$totalshirts = 1; 
}
} elseif ($row2['ProductType'] == "Pants") {
if ($totalpants == 1) {
$pants .= ''.$row2['ProductName'].' ('.count_array($p,$prods).')<br />';
$listed[] = $p;
$totalpants = 2; 
} else { 
$pants .= ''.$row2['ProductName'].' ('.count_array($p,$prods).')<br />';
$listed[] = $p;
$totalpants = 1; 
}
} elseif ($row2['ProductType'] == "Shoes") {
if ($totalshoes == 1) {
$shoes .= ''.$row2['ProductName'].' ('.count_array($p,$prods).')<br />';
$listed[] = $p;
$totalshoes = 2; 
} else { 
$shoes .= ''.$row2['ProductName'].' ('.count_array($p,$prods).')<br />';
$listed[] = $p;
$totalshoes = 1; 
}
}
}
}
}

?>

<table>
<tr>
<td>
<b>Shirts:</b>
</td>
</tr>
<tr>
<td>
<?php echo($shirts); ?>
</td>
</tr>
<tr>
<td>
<b>Pants:</b>
</td>
</tr>
<tr>
<td>
<?php echo($pants); ?>
</td>
</tr>
<tr>
<td>
<b>Shoes:</b>
</td>
</tr>
<tr>
<td>
<?php echo($shoes); ?>
</td>
</tr>
</table>

CloudSex13 · May 16, 2008

I apologize for all the posts. The database was having problems and posted everything multiple times. I only meant to post the one reply above this reply.

CloudSex13 · May 16, 2008

Hmm... I'm still searching for a solution to the ORDER BY problem. Anyone else has any suggestions?

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

Join the conversation

Important Information