To execute sql query

By riwaj on 11 Apr 2008 at 01:37 UTC

I couldn't generate a custom view to meet my requirement, so I opted to write query myself but I had problems in making own module. Is there something magical that can execute my sql query and generate a result page.

Comments

Magic

cayenne commented 11 April 2008 at 04:52

Little bit of magic

First, ya gotta learn how to create a module. There's tutorials for that.
Then, write the code.

Here is the part of code that, for me, queries the database for products sold in e-commerce and display a list of 'em.

Check it out. THe basic way to do it is to create a string that represents your sql query ("$querylist"), execute it ($queryResult = db_query($query);) and then parse the result ($links = db_fetch_object($queryResult)))

Hope this gets you started.



function saleslist_all() {


$val = $_GET['productid'];

$page_content = 'Here is a list of all products being administered<br/>';

  // content variable that will be returned for display
		 
		 $querylist = "SELECT node.nid, node.title  ".
			"FROM ".
			" {node} Join  {ec_product} on node.nid = ec_product.nid ".
      "ORDER BY nid";	
	$queryResult =  db_query($querylist);
  while ($links = db_fetch_object($queryResult)) {
	$page_content .= '<a href = "saleslist?productid='.$links->nid.'"> '.$links->title.'</a><br/>';
	
	
	};
	if(is_numeric($val))  $seekprod = $val;
	
  $page_content .= '<h3>List of Sales with ID='.$seekprod.'</h3><table  border = 1>';

	
	
		 $query = "SELECT ec_transaction_product.txnid, ec_transaction_product.title, ec_transaction_product.nid, ".
      "ec_transaction_product.qty, ec_transaction.mail,  users.uid, users.name,  profile2.value AS fname, profile1.value AS lname  ".
			
			"FROM ".
			
			" {ec_transaction} LEFT JOIN {ec_transaction_product} on ec_transaction.txnid =ec_transaction_product.txnid ".
					"LEFT JOIN {users} on ec_transaction.uid=users.uid ".
										
		            		"LEFT JOIN {profile_values}  profile2 on profile2.uid=users.uid and profile2.fid = 2 ".		
										      		"LEFT JOIN {profile_values}  profile1 on profile1.uid=users.uid and profile1.fid=1  ";
			
			if(is_numeric($val))  		 $query .= 	'WHERE nid = '.$val;
														
   		 $query .= "   ORDER BY nid,txnid";	
			

  // get the links (no range limit here)
	
  $queryResult =  db_query($query);
  while ($links = db_fetch_object($queryResult)) {
	

    $page_content .= '<tr><td>'.$links->txnid. '</td><td> '.     l($links->title, 'node/'.$links->nid).   '</td><td>';
		  
			//email and user address
			 $page_content .= '<td>'. $links->mail.   '</td><td>'.arg(1).  l($links->name, 'user/'.$links->uid). '</td><td>'.
		
		//Profile information here
			 $links->fname.' '.$links->lname.'</td>';
			 // now add the quantity with a link to the order
			$page_content .= '<td><a href="admin/store/transaction/product/edit/'. $links->txnid.'" >'.$links->qty .'</a></td>';
			$page_content .= '</tr>';
  }

		$page_content .= '</table>';
  return $page_content;

}