Removing blank entries

[greg]

How can I remove the blank entries? Tried trim() and doesn't work.

 

$string = '178', '1', '179', '', '180', '67', '63', '47', '46', '', '', '', '201';

 

Thanks

 

[QuickOldCar]

That's not a valid string.

 

Can explode and make it an array, either use array_map or trim in a loop, then implode back into a string

 

preg_replace seems the easiest

<?php
$string = "'178', '1', '179', '', '180', '67', '63', '47', '46', '', '', '', '201'";
$string = preg_replace("~\s'',+~", "", $string);
echo $string;
?>

Results:

'178', '1', '179', '180', '67', '63', '47', '46', '201'

 

This should work as well removing empties from an array

$array = array_map('trim', $array);

[Psycho]

My guess is that the string is likely generated from an array or DB query. If that is the case, then the empty values should be removed before it is converted to a string.

[greg]

@Psycho, yes, that's right. I also tried array_filter() without luck.

 

This is the complete code. Because not all $product_view_color have id, how can I run the query only IF NOT NULL?

		$countColors = 0;
		$ItemString = '';
		for($i=0; $i<$totalviewColors; $i++){
		$result9 = mysql_query("SELECT id, color_name AS name, color_code AS code, COUNT(*) FROM color_charts_colors WHERE color_name = '".trim($product_view_color[$i])."' ORDER BY color_chart_id DESC LIMIT 1");
		if(mysql_num_rows($result9)>0){
		   $rowscode = mysql_fetch_assoc($result9);
			 $item_colorsCODE[] = $rowscode;        
 			
			 if($i!=$totalviewColors-1){
			   $ItemString .= "'".$rowscode['id']."', ";
             } 
			 else {
			   $ItemString .= "'".$rowscode['id']."'";
             }
            $countColors++;
         }
       }
	 }

[Psycho]

First, never run queries in loops. There are very few reasons why you would need to do that. And, unless you know those reasons, always assume a loop is not the answer and ask for help if you don't know how to get the data you need without a loop.

 

Second, dealing with NULL and an empty string are two very different things when talking about database values. Without knowing if one or both are possible in the DB I can't provide the optimal code. But, what I have below should work.

 

Third, not sure what the intended use of the data is here. If you really only want to create a string to output to the page, then what I provided below will work.

 

Fourth, stop using the mysql_ functions. They are deprecated and will be removed in a future version of PHP. I suggest moving to PDO.

 

I'm not sure of the real objective is of your code. You are creating a string from the IDs, but what for? Are you using them somewhere else? plus, you are pulling three values in the query, so what are the other two used for? I expect there is a better way to rewrite the code, but I would have to know what the end objective is to provide a better solution. But, I'm more concerned with why you have records without an ID to begin with. Typically, the ID field is a required field. I'm also not understanding the relationship between $totalviewColors and $product_view_color. Something definitely seems out of whack here.

 

Here is my attempt at rewriting the code based on what was provided

 

<?php
 
//Trim the values in the product_view_color array
$product_view_color = array_map("trim", $product_view_color);
 
//Run ONE query to get all the results
$query = "SELECT id, color_name AS name, color_code AS code, COUNT(*)
          FROM color_charts_colors
          WHERE color_name IN (" . implode(',', $product_view_color) . ")
            AND id <> "" AND id NOT NULL
          GROUP BY color_name
          ORDER BY color_chart_id DESC";
$result = mysql_query($query);
 
//Populate results into an array, use ID as the index
$item_colorsCODE = array();
while ($row = mysql_fetch_assoc($result9))
{
    $item_colorsCODE[$row['id']] = $row;        
}
 
//Create a string to list the IDs (e.g. the array keys)
$ItemString = "'" . implode("', '", array_keys($item_colorsCODE)) . "'";
//Calculate the count of returned values
$countColors = count($item_colorsCODE);
 
?>

[greg]

Hi Psycho,

 

First - Understood

Second - In this case IS NULL, I mean does not exist in table.

Third - No, is not the output of the string.

I have a list of colors of each product which is $totalviewColors. The count is different for each product, for example:

Product A --> Blue, Red, Green, White --> $totalviewColors= 4
Product B --> Red, Black, Gray --> $totalviewColors= 3

Now, I have color_charts_colors with an structure like this:
 

ID | color name    |     color code
1  |    Blue            |               #0000FE
2  |    Red            |              #9E2337
3  |    Green        |               #008A59
4  |    Gray          |              #56595C
5  |    White         |              #FFFFFF   

As you can see, Black is not in this table. That is the NULL. Why is NULL? Because Black is in the list of colors for Product B, but there is no reference in the table color_charts_colors for this color name. so $countColors will be this:

Product A --> $countColors= 4
Product B --> $countColors= 2

Do you understand now the structure?

What I want is to take the color codes of the color names existing in the table and output columns using the color code as a background. Have you seem these small squares showing the color of products on some websites? That is what I looking for.

I tested your code and got this error:
Parse error: syntax error, unexpected '" AND id NOT NULL ' (T_CONSTANT_ENCAPSED_STRING)

Thank you!

[Psycho]

I did not test the code as I don't have your DB (or enough information) to properly test it. But, the logic was sound based upon what I understood at the time. I expect the person using the code to find/fix any minor typos.

 

As to your explanation, what you are doing doesn't make sense to me. You were running a loop as follows

for($i=0; $i<$totalviewColors; $i++){

But, then were defining the color string value using

$product_view_color[$i]

But, in your example above, you stated that $totalviewColors for Product B would be 3 (with Black being used as a spacer). But, that product doesn't use the first color: Blue. Based on your original code, the first iteration of the loop would be using Blue, then with Red & Green. It woudl be completely out of sync with the actual colors you should be using. I think the whole logic here is problematic. There should be no reason to use Black as a NULL value anyway. Where is $totalviewColors even defined? And, you shouldn't be doing lookups based on a description value (such as color name) when those values have an ID.

 

Based on your explanation, I *think* what you are really trying to accomplish is to pull the data specific to a product. So, you SHOULD have a table that defines the applicable colors for a product. Therefore, you should be able to create a single query that pulls the data you need by only providing the product ID as the variable data. Again, without a full understanding of the DB structure (which may be faulty) and the intended result I can't provide the right solution.

 

Based on your explanation above, I would expect to have three tables similar to this

color_charts_colors
-----------------------------
ColorID | color_name  | color_code
  1     |    Blue     |  #0000FE
  2     |    Red      |  #9E2337
  3     |    Green    |  #008A59
  4     |    Gray     |  #56595C
  5     |    White    |  #FFFFFF
 
 
products
-----------------------------
ProductID | Product_name 
  1       |  Product A
  2       |  Product B
 
 
products_color
-----------------------------
ID | ProductID  | ColorID
1  |  1         |  1
2  |  1         |  2
3  |  1         |  3
4  |  1         |  4
5  |  2         |  2
6  |  2         |  3

Then you can get all the color data related to a product using a query such as this

 

SELECT ColorID, color_name, color_code
FROM color_charts_colors ccc
JOIN products_color pc
  ON ccc.ColorID = pc.ColorID
WHERE pc.ProductID = $productID

Edited April 6, 2015 by Psycho

[greg]

That approach is not easy. A database with 13,000 products and more than 40 pages already pulling the color names are something to consider if new code is in place. The colors are stored in a field, in the table products, one row per product, separated by commas and some products may have no colors at all. Until now we only pulled names and wont run in trouble. We are now looking to add a visual color in the listing.

 

This is not to pull the data for a specific product but for a category of product where the list is 40-200 products.

 

$totalviewColors is defined in the listing query and the reason is because I don't want to run a code for products with no colors at all.

 

How the color names and color codes in $item_colorsCODE[] are pushed out of the loop?

[Psycho]

I'm not sure where you come to the conclusion that a properly normalized database is not easy or flexible. I really don't understand what you are working with or what you are trying to achieve. I've already invested a lot of time in this thread and don't seem to be getting closer to a resolution. I'll bow out now and hopefully someone else can provide the assistance you need.