Trouble with array

[acsonline]

Hey,

 

I am trying to make and then read an array of items. Can someone help please?

 

I have a database field that holds an unlimited number of codes, and I need to get those codes....

 

at the moment I have a section of php

  $products= array($products);
  $array = array_values($products);
  print_r($array);

 

which generates

 

Array ( [0] => FRE0001,FRE0009,FRE0009,FRE0024,FRE0024 )

 

as the output...

 

What I need is to know how many of each code there are and the different codes....

 

i.e. 1 x FRE0001, 2 x FRE0009, 2 x FRE0024

 

Whats the best way to output the array and then count?

[acsonline]

I've now added this:

 

  $array = explode(",",$products);
  $products = array_count_values($array);
  print_r($products);

 

which now displays this:

 

Array ( [FRE0001] => 1 [FRE0009] => 2 [FRE0024] => 2 ) 

 

How do I get use the id? (FRE0001) etc....

[dreamwest]

Under foreach heading:

 

http://www.wizecho.com/nav=php&s=for_loops

[acsonline]

hey,

 

I've been looking at that but how do I get just the data out of the code, rather than the full array too?

 

I need to just get FRE0009 etc.... rather than the whole array code

[samoht]

you just echo what you need out of your foreach

 

 

[cyberRobot]

As the others have suggested, this should get you closer to what you want:

 

<?php
...
foreach($products as $key => $value) {
     echo "<div>$key ($value)</div>";
}
...
?>

 

Modify to fit your needs.

[acsonline]

Ok, thanks guys, getting closer now

 

  $array = explode(",",$products);
  $products = array_count_values($array);
  print_r(array_values($array));
  print_r(array_keys($products));
    foreach ($products as $value => $key){
    echo $products . "<br>";

 

but I just get

 

array

array

array

 

instead of the code - plus how can I find out the quantity in the array too?

[cyberRobot]

The data is in the $value and $key variables

[acsonline]

Ah ha,

 

Excellent thank you!!!

 

I've got it reading the array now, however I'm now strugling to connect to the wordpress system...

 

This is my code:

 

<?php
if (!empty($_SERVER['SCRIPT_FILENAME']) && 'comments.php' == basename($_SERVER['SCRIPT_FILENAME']))
    die ('Please do not load this page directly. Thanks!');
    
if (is_page()) { } else {

    $key="Linked Products";
    $idcode= get_post_meta($post->ID, $key, true);
       	$array = explode(",",$idcode);
  	$products = array_count_values($array);

    foreach ($products as $key => $value) {
    $productid = $wpdb->get_results( "SELECT product_id FROM wordpdem_wpsc_productmeta WHERE meta_value = '$key'" );

}
}
?>

I tried to echo $productid but it just says array.....

I need it to tell me the product_id from the database...

 

Any ideas

[samoht]

Are you saying that you have an array with product names and quantities and now you want to look up what their product id's are in the db?

 

I am confused about your process and what you are trying to accomplish. I would not however use multiple queries to get the data you're looking for.

You could use one query to get all the product_id's from the db then with your foreach loop check if the product name with that product_id is in your array and if so echo out the product_id

[acsonline]

Hey,

 

I have one DB with 2 tables, one table has the product details, and the other has codes - So I need to reference the table wordpdem_wpsc_productmeta and get the product_id where meta_value = "FRE0009" etc... then check to get the name and price of FRE0009 from the table wordpdem_wpsc_product_list.

 

The output I need from thsi whole query is - Ref (FRE0009), Name, price and Quantity (How many times FRE0009 is in the first list)

 

So Far, I have got the reference using the code:

 

$idcode= get_post_meta($post->ID, $key, true);
       	$array = explode(",",$idcode);
  	$products = array_count_values($array);

 

So now I need to count the different times the codes are outputted, then check the other DB for Name and Price...

[samoht]

I assume then that the codes and the number of times it is output comes from a page where users are ordering products?

Does this page have the product names and ID's or just the codes?

[acsonline]

basically there is an online store in use, which for some reason splits the product code to one table and the name and price to a different table.

 

we then have a section of posts - and we add product references to the pages, i.e. relevant products...

 

on the post page, we have text then a section that says: "To do this you will need...." and then the list of products (and a link to buy now button)

 

when we create the post we have custom fields which we input the relevant items in to... FRE0001,FRE0009 etc, and it we need 2 of something we simply put it in twice... FRE0001,FRE0009,FRE0009 etc....

 

So we need to check that field - find out what is required...

 

    $key="Linked Products";
    $idcode= get_post_meta($post->ID, $key, true);
       	$array = explode(",",$idcode);
  	$products = array_count_values($array);

at present this would display FRE0009 - 2, FRE0001 etc etc...

 

so now we know the codes, we can query the DB for the product id, and then check the table for the name and price...

 

 

[acsonline]

I ran an echo on my sql to check the code and it appears to be working correctly.

 

SELECT product_id FROM wordpdem_wpsc_productmeta WHERE meta_value = 'FRE0001'

SELECT product_id FROM wordpdem_wpsc_productmeta WHERE meta_value = 'FRE0009'

SELECT product_id FROM wordpdem_wpsc_productmeta WHERE meta_value = 'FRE0024'

 

this is the code now

 

<?php
if (!empty($_SERVER['SCRIPT_FILENAME']) && 'comments.php' == basename($_SERVER['SCRIPT_FILENAME']))
    die ('Please do not load this page directly. Thanks!');
    
if (is_page()) { } else {

    $key="Linked Products";
    $idcode= get_post_meta($post->ID, $key, true);
       	$array = explode(",",$idcode);
  	$products = array_count_values($array);

    foreach ($products as $key => $value) {
    $sql = "SELECT product_id FROM wordpdem_wpsc_productmeta WHERE meta_value = '$key'";
    $productid = $wpdb->get_results($sql);
    
    echo $sql . "<br>";
    }

//   foreach ($productid as $prod_id) {
//   echo $prod_id;
//   $product = $wpdb->get_results( "SELECT name, price FROM wordpdem_wpsc_product_list WHERE id = '$productid'" );
//   }

    $pid = '376';
?>
       <span class="art-button-wrapper">
       <span class="l"> </span>
       <span class="r"> </span>
       <?php wpsc_add_to_cart_button($pid); ?>
       </form></span>
<?php } ?>

 

Any ideas how I can get the next stage? I've commented out the code that fails... how can I get the data?

 

Thank You

[acsonline]

hmmm,

 

Ok, further to that, I found this page:

 

http://wordpress.org/support/topic/how-to-query-database

 

which shows a query:

 

$pageposts = $wpdb->get_results($sql, ARRAY_N);

while($row = mysql_fetch_array($pageposts)) {

 

echo $row['fieldname'];

 

}

 

So I changed to:

 

    foreach ($products as $key => $value) {
    $sql = "SELECT product_id FROM wordpdem_wpsc_productmeta WHERE meta_value = '$key'";
    $productid = $wpdb->get_results($sql, ARRAY_N);
    
    	echo $sql . "<br>"; //THIS ECHO CORRECTLY
    }
    	while($row = mysql_fetch_array($productid)) {
		echo $row['product_id']; //NOW GET AN ERROR Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in XXXXXX/comments.php on line 18
	} 

 

Any ideas why this error: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in XXXXXX/comments.php on line 18 comes up?

[dreamwest]

$productid has no value aka its empty

 

You should use to prevent errors

 

if($productid){

while($row = mysql_fetch_array($productid)) {
		echo $row['product_id']; //NOW GET AN ERROR Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in XXXXXX/comments.php on line 18
	} 
}else{

echo  'No data';  //remove after testing
}

 

 

[samoht]

Well, you are doing a double loop. foreach is a loop and while is a loop.

 

You will want to do one query outside of your loop and then loop through the results and that is it.

This is what was said on the page that you linked "$wpdb->get_results returns an array so you wanna loop the results out:"

 

We need to work on getting the right query so that we can then setup the loop for your exact needs

 

[samoht]

if you are working with a WP plugin tell me which one and I will experiment on my own.

 

We should have something that looks like:

<?php
	$sql = 'SELECT DISTINCT ' . ENTRY_TABLE . '.*

			FROM ' . ENTRY_TABLE . ' ' . implode(' ', $join) . ' ' .

			implode(' ', $where) . ' ' . 

			'ORDER BY `product_id`;


	$results = $wpdb->get_results($sql);

foreach($results as $result)
{
           echo $result['produt_id'];
}

 

[acsonline]

Hey,

 

Its WP E-Commerce, so if I used something like:

 

<?php
  $where = 'WHERE id = $productid';

  $sql = 'SELECT DISTINCT ' . ENTRY_TABLE . '.*
  FROM ' . ENTRY_TABLE . ' ' . implode(' ', $join) . ' ' .
  implode(' ', $where) . ' ' . 
  'ORDER BY `product_id`;

$results = $wpdb->get_results($sql);

foreach($results as $result)
{
           echo $result['product_id'];
} ?>

However what do I do for $join - and I'm assuming that i replace ENTRY_TABLE with wordpdem_wpsc_product_list

[samoht]

OK,

 

is the FRE0001 the 'sku' in the productsmeta table? (because I could not find a product code )

 

I noticed their code looks like:

$product_id = $wpdb->get_var("SELECT `product_id` FROM `".WPSC_TABLE_PRODUCTMETA."` WHERE `meta_key` IN ( 'url_name' ) AND `meta_value` IN ( '{$wp_query->query_vars['product_url_name']}' ) ORDER BY `id` DESC LIMIT 1");
		$full_product_name = $wpdb->get_var("SELECT `name` FROM `".WPSC_TABLE_PRODUCT_LIST."` WHERE `id`='{$product_id}' LIMIT 1");

When they have to retrieve the product name and the values they have are in the productmeta table.

I'll take a look further and see what I come up with. If I can recreate the situation your in it would be helpful.

Are you writing this code on a custom template for your theme?? (can you chow me the whole page?)

[acsonline]

Hi,

 

It is indeed the SKU,

 

I'm using a totally independent code, hand written - basically this is a food shop and there is a recipes section. On that post it needs to show the food items needed to make that recipes....

 

So in the posts section, I have custom fields installed, which allows me to enter the food items needed...

 

i.e: FRE0001,FRE0009,FRE0009,FRE0024 - Notice there are 2 x FRE0009 so on the shopping list it would show 1 x PRODUCT 1 @ 29p, 2 x PRODUCT 9 @5.60 and 1 x PRODUCT 24 @69p

 

I hope this helps clarify it a little better

[samoht]

Yes, I think am starting to understand.

 

Are you saying that you are manually adding the sku's that you need for a recipe as a custom field "Ref" in your post - and then looking for a way to query the db for the product_id's associated with the sku's? 

[acsonline]

Bingo!!

 

All I need to know is the product ID, product name and price, so that I can display them....  Then I can create a buy now button using the shortcode and the product id (Then I need to work out a way to add quantity too....)

[samoht]

OK,

 

You can try this for now:

<?php

if (is_page()) { } else {

if(!empty($wpdb->prefix)) {
  $wp_table_prefix = $wpdb->prefix;
} else if(!empty($table_prefix)) {
  $wp_table_prefix = $table_prefix;
}

// Define the database table names
define('WPSC_TABLE_PRODUCT_LIST', "{$wp_table_prefix}wpsc_product_list");
define('WPSC_TABLE_PRODUCTMETA', "{$wp_table_prefix}wpsc_productmeta");


    $cf="Linked Products";
    $sku = get_post_meta($post->ID, $cf, true);
    $array = explode(",",$sku);
$products = array_count_values($array);


foreach($products as $key => $value) {
	$product = $wpdb->get_row("
	SELECT `meta.product_id`, list.name as name, list.price as price 
	FROM `".WPSC_TABLE_PRODUCTMETA." as meta` INNER JOIN `".WPSC_TABLE_PRODUCT_LIST." as list` ON (list.id = meta.product_id)
	WHERE `meta_key` IN ( 'sku' ) AND `meta_value` IN ( '{$key}' ) 
	ORDER BY `id` DESC ");
	echo $value .'x'. $product['name'] .'@'. $product['price'] .'<br />';
}
}

 

This is not the best solution - but it should work.

 

if you have trouble with the query - try putting it all on one line like:

		$product = $wpdb->get_row("SELECT `meta.product_id`, `list.name` as name, `list.price` as price FROM `".WPSC_TABLE_PRODUCTMETA."` as meta INNER JOIN `".WPSC_TABLE_PRODUCT_LIST."` as list ON (list.id = meta.product_id) WHERE `meta_key` IN ( 'sku' ) AND `meta_value` IN ( '{$key}' ) ORDER BY `id` DESC ");

[samoht]

Sorry but here is the correct query

$product = $wpdb->get_row("SELECT meta.product_id, list.name AS name, list.price AS price FROM ".WPSC_TABLE_PRODUCTMETA." AS meta INNER JOIN ".WPSC_TABLE_PRODUCT_LIST." AS list ON ( list.id = meta.product_id ) WHERE `meta_key` IN ( 'sku' ) AND `meta_value` IN ( '{$key}' ) ORDER BY list.id DESC");

 

I was having problems with the `  hate those things