Page 1 of 1

For Each Loops

Posted: Wed Jul 14, 2010 2:00 pm
by tomnoble
Hi,

I have the following code which is causing me some issues:

Code: Select all

//Load in all the NID's
$result = mysql_query("SELECT nid FROM drupal_node WHERE type = 'product'"); //just get nid not *
$row = mysql_fetch_array( $result );
print_r($row);

foreach ($row as $info)
  {
	//Collect all information from the node_revisions table
	$revisions = mysql_query("SELECT title, body FROM 	drupal_node_revisions WHERE nid = '$info'"); 
	$rowrevise = mysql_fetch_array( $revisions );
	print_r($rowrevise);
        "<br />\n";

	//Collect all information from the products table
	$skucode = mysql_query("SELECT model FROM 	drupal_uc_products WHERE nid = '$info'");
	$sku = mysql_fetch_array( $skucode );
	print_r($sku);
        "<br />\n";

	//Collect all the prices from the prices table
	$prices = mysql_query("SELECT country_id, sell_price FROM 	drupal_uc_multigeog WHERE nid = '$info'");
	$price = mysql_fetch_array( $prices );
	print_r($price);
        "<br />\n";

	//Collect the taxonomy data 
	$taxid = mysql_query("SELECT tid FROM 	
	drupal_term_node WHERE nid = '$info'");
	$taxs = mysql_fetch_array( $taxid );
        print_r($taxs);
	foreach ($taxs as $taxs['tid'] => $id) 
	{
		$taxname = mysql_query("SELECT description FROM 	
		drupal_term_data WHERE tid = '$id'");
		$taxdesc = mysql_fetch_array( $taxname );
		$Taxonomy = $Taxonomy. "|" . $taxdesc[0];
	}
	if ( $Taxonomy[0] == "|" ) $Taxonomy = substr($Taxonomy, 1);
	echo $Taxonomy;
What I want to do is:

Select all the NID's from the drupal_node table, where the type = product.
For each NID found, I want to:
*Get the product title, and product body from the node_revisions table.
*Set tosave[2] to product title
*Set tosave[3] to product body
*Get the SKU code, from the uc_products table where the NID = NID read in for this loop
*set tosave[1] to SKU code read in
*Select all country_id and related sell_prices where the NID = NID read in for this loop
*Read in all the TID's from the term_node table, where the NID = NID read in for this loop
*For each TID, Select the Description from the term_data table
* $Taxonomy = $Taxonomy. "|" . <read in Description>;

Is anyone able to help me?

Kind Regards

Tom

Re: For Each Loops

Posted: Wed Jul 14, 2010 2:46 pm
by AbraCadaver
You're only fetching one row, then your looping through the columns in the one row. Here is a start:

Code: Select all

$result = mysql_query("SELECT nid FROM drupal_node WHERE type = 'product'"); //just get nid not *
//$row = mysql_fetch_array( $result );  // don't use this here, this just fetches one row
//print_r($row);

while($row = mysql_fetch_array( $result ))
  {
  $info = $row['nid'];
  //etc ...
  }

Re: For Each Loops

Posted: Wed Jul 14, 2010 3:30 pm
by AbraCadaver
You might be able to do this in just two queries. Obviously I can't test it and what you want is a little vague, but this may be something to work from:

Code: Select all

$result = mysql_query("
SELECT t1.nid, t2.title, t2.body, t3.model, t4.country_id, t4.sell_price
FROM drupal_node t1, drupal_node_revisions t2, drupal_uc_products t3, drupal_uc_multigeog t4
WHERE t1.type='products' AND t1.nid=t2.nid AND t1.nid=t3.nid AND t1.nid=t4.nid");

$i = 0;
while($row = mysql_fetch_array($result)) {
	$nid = $row['nid'];
	$tosave[$i][1] = $row['model'];
	$tosave[$i][2] = $row['body'];
	$tosave[$i][3] = $row['title'];
	// dunno what you want to do with country_id and sell_price
	
	$tax_result = mysql_query("
	SELECT t1.tid, t2.description
	FROM drupal_term_node t1, drupal_term_data
	WHERE t1.nid=$nid AND t1.tid=t2.tid");
	
	$description = array();

	while($tax_row = mysql_fetch_array($tax_result)) {
		$description[] = $tax_row['description'];
	}
	$taxonomy[$i] = implode('|', $description);

        $i++;
}

Re: For Each Loops

Posted: Wed Jul 14, 2010 5:03 pm
by tomnoble
Thanks for the code. Works a treat.

Unfortunately, I cant test or think of implementing a single statement query as my client wants to be able to edit it with no PHP knowledge, and it will just become too difficult. Should I have a use for that in the future though, I will make sure to post back to this thread.

The small piece of code I am now having an issue with is:

Code: Select all


if(substr('$line[1][$Position]', 0, 2) == '.|')
{
    $$line[1][$Position] = substr($line[1][$Position], 1);
} 
The purpose is to check the string in $line[1][$Position], check if the first 2 characters are ".|" and remove them if they are. Any ideas where I am going wrong. The error I am getting is: Undefined offset: 1 in ./Export Products.php on line 53

Thanks for help so far.

Kind Regards

Tom

EDIT: Probably best if I show you the entire code snippet, I have removed the DB connection stuff for obvious reasons.

Code: Select all

$Position= 0;
$line[1][$Position]=".|";
//Load in all the NID's
$result = mysql_query("SELECT nid FROM drupal_node WHERE type = 'product'"); //just get nid not *

while($row = mysql_fetch_array( $result ))
  {
  $info = $row['nid'];
  
	$revisions = mysql_query("SELECT title, body FROM 	drupal_node_revisions WHERE nid = '$info'"); 
	$rowrevise = mysql_fetch_array( $revisions );
	$line[2][$Position]= $rowrevise['title'];
	$line[3][$Position]= $rowrevise['body'];

	$skucode = mysql_query("SELECT model FROM 	drupal_uc_products WHERE nid = '$info'");
	$sku = mysql_fetch_array( $skucode );
	$line[0][$Position]= $sku['model'];

	$prices = mysql_query("SELECT country_id, sell_price FROM 	drupal_uc_multigeog WHERE nid = '$info'");
	
	while($cost = mysql_fetch_array( $prices ))
	  {
		If ($cost['country_id'] = 930)
		  $line[4][$Position]= $cost['sell_price'];
		If ($cost['country_id'] = 900)
		  $line[5][$Position]= $cost['sell_price'];
		If ($cost['country_id'] = 915)
		  $line[6][$Position]= $cost['sell_price'];
		If ($cost['country_id'] = 946)
		  $line[7][$Position]= $cost['sell_price'];
		If ($cost['country_id'] = 960)
		  $line[8][$Position]= $cost['sell_price'];  
	  }
	
	$taxid = mysql_query("SELECT tid FROM 	
	drupal_term_node WHERE nid = '$info'");

	while($tax = mysql_fetch_array( $taxid ))
	  {
		$id = $tax['tid'];
		$taxname = mysql_query("SELECT description FROM 	
		drupal_term_data WHERE tid = '$id'");
		$taxdesc = mysql_fetch_array( $taxname );
		$line[1][$Position]= ($line[1][$Position]) . "|" . ($taxdesc['description']);
	  }

if(substr('$line[1][$Position]', 0, 2) == '.|')
{
   $line[1][$Position] = substr($line[1][$Position], 1);
} 
$Position ++;

  }
Do I need to declare the array?
Is the array being populated with data?
My error seems to lie on line 53, but that isnt to say, I don't have an error further up the code.

Kind Regards

Tom