Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Need help with a query


bobg7

Recommended Posts

Trying to add this query to admin/invoice.php to display if the customer is a business address or not. I'm using the Customers Extra Fields contribution to get the information into the database.

 

The Query:

$Business = tep_db_query("select value from " . TABLE_CUSTOMERS_TO_EXTRA_FIELDS . " where value = '" . (int)$oID ."'");
 while ($result=tep_db_fetch_array($Business)) {
echo $result['Business'] . "\n";
}

 

The invoice page displays OK, but no output from the query.

 

The table is called:

customers_to_extra_fields

 

The columns are:

customers_id int(11)

fields_id int(11)

value text

 

Thanks in advance

Installed Contributions: CCGV, Close Popup, Dynamic Meta Tags, Easy Populate, Froogle Data Feeder, Google Position, Infobox Header Entire Row, Live Support for OSC, PayPal Seal with CC images, Report_m Sales, Shop by Price Revised, SQL Updater, Who's Online Enhancement, Footer, GNA EP Assistant and still going.

Link to comment
Share on other sites

Trying to add this query to admin/invoice.php to display if the customer is a business address or not. I'm using the Customers Extra Fields contribution to get the information into the database.

 

The Query:

$Business = tep_db_query("select value from " . TABLE_CUSTOMERS_TO_EXTRA_FIELDS . " where value = '" . (int)$oID ."'");
 while ($result=tep_db_fetch_array($Business)) {
echo $result['Business'] . "\n";
}

 

The invoice page displays OK, but no output from the query.

 

The table is called:

customers_to_extra_fields

 

The columns are:

customers_id int(11)

fields_id int(11)

value text

 

Thanks in advance

 

echo $result['value'] . "\n";

Treasurer MFC

Link to comment
Share on other sites

Thanks but it's still not showing, here is the entire admin/invoice.php perhaps I'm putting it in the wrong place or the wrong format.

 

<?php
/*
 $Id: invoice.php,v 1.6 2003/06/20 00:37:30 hpdl Exp $

 osCommerce, Open Source E-Commerce Solutions
 http://www.oscommerce.com

 Copyright (c) 2003 osCommerce

 Released under the GNU General Public License
*/

 require('includes/application_top.php');

 require(DIR_WS_CLASSES . 'currencies.php');
 $currencies = new currencies();

 $oID = tep_db_prepare_input($HTTP_GET_VARS['oID']);
 $orders_query = tep_db_query("select orders_id from " . TABLE_ORDERS . " where orders_id = '" . (int)$oID . "'");
 include(DIR_WS_CLASSES . 'order.php');
 $order = new order($oID);
?>
<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN">
<html <?php echo HTML_PARAMS; ?>>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>">
<title><?php echo TITLE; ?></title>
<link rel="stylesheet" type="text/css" href="includes/stylesheet.css">
</head>
<body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0" bgcolor="#FFFFFF">

<!-- body_text //-->
<table border="0" width="100%" cellspacing="0" cellpadding="2">
 <tr>
<td><table border="0" width="100%" cellspacing="0" cellpadding="0">
  <tr>
	<td class="pageHeading"><?php echo nl2br(STORE_NAME_ADDRESS); ?></td>
	<td class="pageHeading" align="right"><?php echo tep_image(DIR_WS_IMAGES . 'logo1.jpg', 'Color Your Grout', '204', '50'); ?></td>
  </tr>
</table></td>
 </tr>
 <tr>
<td><table width="100%" border="0" cellspacing="0" cellpadding="2">
  <tr>
	<td colspan="2"><?php echo tep_draw_separator(); ?></td>
  </tr>
  <tr>
	<td valign="top"><table width="100%" border="0" cellspacing="0" cellpadding="2">
	  <tr>
		<td class="main"><b><?php echo ENTRY_SOLD_TO; ?></b></td>
	  </tr>
				<tr>
		<td class="main"><?php echo tep_address_format($order->customer['format_id'], $order->customer, 1, '', '<br>'); ?></td>
	  </tr>
	  <tr>
		<td><?php echo tep_draw_separator('pixel_trans.gif', '1', '5'); ?></td>
	  </tr>
	  <tr>
		<td class="main"><?php echo $order->customer['telephone']; ?></td>
	  </tr>
				<tr>
				  <td class="main"> </td>
			</tr>
				<tr>
		<td class="main"><?php echo '<a href="mailto:' . $order->customer['email_address'] . '"><u>' . $order->customer['email_address'] . '</u></a>'; ?></td>
	  </tr>
	</table></td>
	<td valign="top"><table width="100%" border="0" cellspacing="0" cellpadding="2">
	  <tr>
		<td class="main"><b><?php echo ENTRY_SHIP_TO; ?></b></td>
	  </tr>
	  <tr>
		<td class="main"><?php echo tep_address_format($order->delivery['format_id'], $order->delivery, 1, '', '<br>'); ?></td>
	  </tr>
	</table></td>
  </tr>
</table></td>
 </tr>
 <tr>
<td><?php echo tep_draw_separator('pixel_trans.gif', '1', '10'); ?></td>
 </tr>
 <tr>
<td><table border="0" cellspacing="0" cellpadding="2">
  <tr>
	<td class="main"><b><?php echo ENTRY_PAYMENT_METHOD; ?></b></td>
	<td class="main"><?php echo $order->info['payment_method']; ?></td>
  </tr>
</table></td>
  </tr>
 <tr>
<td><?php echo tep_draw_separator('pixel_trans.gif', '1', '10'); ?></td>
 </tr>
 <tr>
<td><table border="0" width="100%" cellspacing="0" cellpadding="2">
  <tr class="dataTableHeadingRow">
	<td class="dataTableHeadingContent" colspan="2"><?php echo TABLE_HEADING_PRODUCTS; ?></td>
	<td class="dataTableHeadingContent"><?php echo TABLE_HEADING_PRODUCTS_MODEL; ?></td>
	<td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_TAX; ?></td>
	<td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_PRICE_EXCLUDING_TAX; ?></td>
	<td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_PRICE_INCLUDING_TAX; ?></td>
	<td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_TOTAL_EXCLUDING_TAX; ?></td>
	<td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_TOTAL_INCLUDING_TAX; ?></td>
  </tr>
<?php
for ($i = 0, $n = sizeof($order->products); $i < $n; $i++) {
  echo '	  <tr class="dataTableRow">' . "\n" .
	   '		<td class="dataTableContent" valign="top" align="right">' . $order->products[$i]['qty'] . ' x</td>' . "\n" .
	   '		<td class="dataTableContent" valign="top">' . $order->products[$i]['name'];

  if (isset($order->products[$i]['attributes']) && (($k = sizeof($order->products[$i]['attributes'])) > 0)) {
	for ($j = 0; $j < $k; $j++) {
	  echo '<br><nobr><small> <i> - ' . $order->products[$i]['attributes'][$j]['option'] . ': ' . $order->products[$i]['attributes'][$j]['value'];
	  if ($order->products[$i]['attributes'][$j]['price'] != '0') echo ' (' . $order->products[$i]['attributes'][$j]['prefix'] . $currencies->format($order->products[$i]['attributes'][$j]['price'] * $order->products[$i]['qty'], true, $order->info['currency'], $order->info['currency_value']) . ')';
	  echo '</i></small></nobr>';
	}
  }

  echo '		</td>' . "\n" .
	   '		<td class="dataTableContent" valign="top">' . $order->products[$i]['model'] . '</td>' . "\n";
  echo '		<td class="dataTableContent" align="right" valign="top">' . tep_display_tax_value($order->products[$i]['tax']) . '%</td>' . "\n" .
	   '		<td class="dataTableContent" align="right" valign="top"><b>' . $currencies->format($order->products[$i]['final_price'], true, $order->info['currency'], $order->info['currency_value']) . '</b></td>' . "\n" .
	   '		<td class="dataTableContent" align="right" valign="top"><b>' . $currencies->format(tep_add_tax($order->products[$i]['final_price'], $order->products[$i]['tax']), true, $order->info['currency'], $order->info['currency_value']) . '</b></td>' . "\n" .
	   '		<td class="dataTableContent" align="right" valign="top"><b>' . $currencies->format($order->products[$i]['final_price'] * $order->products[$i]['qty'], true, $order->info['currency'], $order->info['currency_value']) . '</b></td>' . "\n" .
	   '		<td class="dataTableContent" align="right" valign="top"><b>' . $currencies->format(tep_add_tax($order->products[$i]['final_price'], $order->products[$i]['tax']) * $order->products[$i]['qty'], true, $order->info['currency'], $order->info['currency_value']) . '</b></td>' . "\n";
  echo '	  </tr>' . "\n";
  }
 $Business = tep_db_query("select value from " . TABLE_CUSTOMERS_TO_EXTRA_FIELDS . " where value = '" . (int)$oID ."'");
 while ($result=tep_db_fetch_array($Business)) {
echo $result['value'] . "\n";
}
?>
  <tr>
	<td align="right" colspan="8"><table border="0" cellspacing="0" cellpadding="2">
<?php
 for ($i = 0, $n = sizeof($order->totals); $i < $n; $i++) {
echo '		  <tr>' . "\n" .
	 '			<td align="right" class="smallText">' . $order->totals[$i]['title'] . '</td>' . "\n" .
	 '			<td align="right" class="smallText">' . $order->totals[$i]['text'] . '</td>' . "\n" .
	 '		  </tr>' . "\n";
 }
?>
	</table></td>
  </tr>
	</table></td>
 </tr>
</table>
<!-- body_text_eof //-->

<br>

<script Language="Javascript">

/*
This script is written by Eric ([email protected])
For full source code, installation instructions,
100's more DHTML scripts, and Terms Of
Use, visit dynamicdrive.com
*/

function printit(){  
if (window.print) {
window.print();  
} else {
var WebBrowser = '<OBJECT ID="WebBrowser1" WIDTH=0 HEIGHT=0 CLASSID="CLSID:8856F961-340A-11D0-A96B-00C04FD705A2"></OBJECT>';
document.body.insertAdjacentHTML('beforeEnd', WebBrowser);
WebBrowser1.ExecWB(6, 2);//Use a 1 vs. a 2 for a prompting dialog box	WebBrowser1.outerHTML = "";  
}
}
</script>

<script Language="Javascript">  
var NS = (navigator.appName == "Netscape");
var VERSION = parseInt(navigator.appVersion);
if (VERSION > 3) {
document.write('<form><input type=button value="Print this Page" name="Print" onClick="printit()"></form>');		
}
</script>

</body>
</html>
<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>

Installed Contributions: CCGV, Close Popup, Dynamic Meta Tags, Easy Populate, Froogle Data Feeder, Google Position, Infobox Header Entire Row, Live Support for OSC, PayPal Seal with CC images, Report_m Sales, Shop by Price Revised, SQL Updater, Who's Online Enhancement, Footer, GNA EP Assistant and still going.

Link to comment
Share on other sites

Thanks but it's still not showing, here is the entire admin/invoice.php perhaps I'm putting it in the wrong place or the wrong format.

 

<?php
/*
 $Id: invoice.php,v 1.6 2003/06/20 00:37:30 hpdl Exp $

 osCommerce, Open Source E-Commerce Solutions
 http://www.oscommerce.com

 Copyright (c) 2003 osCommerce

 Released under the GNU General Public License
*/

 require('includes/application_top.php');

 require(DIR_WS_CLASSES . 'currencies.php');
 $currencies = new currencies();

 $oID = tep_db_prepare_input($HTTP_GET_VARS['oID']);
 $orders_query = tep_db_query("select orders_id from " . TABLE_ORDERS . " where orders_id = '" . (int)$oID . "'");
 include(DIR_WS_CLASSES . 'order.php');
 $order = new order($oID);
?>
<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN">
<html <?php echo HTML_PARAMS; ?>>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>">
<title><?php echo TITLE; ?></title>
<link rel="stylesheet" type="text/css" href="includes/stylesheet.css">
</head>
<body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0" bgcolor="#FFFFFF">

<!-- body_text //-->
<table border="0" width="100%" cellspacing="0" cellpadding="2">
 <tr>
<td><table border="0" width="100%" cellspacing="0" cellpadding="0">
  <tr>
	<td class="pageHeading"><?php echo nl2br(STORE_NAME_ADDRESS); ?></td>
	<td class="pageHeading" align="right"><?php echo tep_image(DIR_WS_IMAGES . 'logo1.jpg', 'Color Your Grout', '204', '50'); ?></td>
  </tr>
</table></td>
 </tr>
 <tr>
<td><table width="100%" border="0" cellspacing="0" cellpadding="2">
  <tr>
	<td colspan="2"><?php echo tep_draw_separator(); ?></td>
  </tr>
  <tr>
	<td valign="top"><table width="100%" border="0" cellspacing="0" cellpadding="2">
	  <tr>
		<td class="main"><b><?php echo ENTRY_SOLD_TO; ?></b></td>
	  </tr>
				<tr>
		<td class="main"><?php echo tep_address_format($order->customer['format_id'], $order->customer, 1, '', '<br>'); ?></td>
	  </tr>
	  <tr>
		<td><?php echo tep_draw_separator('pixel_trans.gif', '1', '5'); ?></td>
	  </tr>
	  <tr>
		<td class="main"><?php echo $order->customer['telephone']; ?></td>
	  </tr>
				<tr>
				  <td class="main"> </td>
			</tr>
				<tr>
		<td class="main"><?php echo '<a href="mailto:' . $order->customer['email_address'] . '"><u>' . $order->customer['email_address'] . '</u></a>'; ?></td>
	  </tr>
	</table></td>
	<td valign="top"><table width="100%" border="0" cellspacing="0" cellpadding="2">
	  <tr>
		<td class="main"><b><?php echo ENTRY_SHIP_TO; ?></b></td>
	  </tr>
	  <tr>
		<td class="main"><?php echo tep_address_format($order->delivery['format_id'], $order->delivery, 1, '', '<br>'); ?></td>
	  </tr>
	</table></td>
  </tr>
</table></td>
 </tr>
 <tr>
<td><?php echo tep_draw_separator('pixel_trans.gif', '1', '10'); ?></td>
 </tr>
 <tr>
<td><table border="0" cellspacing="0" cellpadding="2">
  <tr>
	<td class="main"><b><?php echo ENTRY_PAYMENT_METHOD; ?></b></td>
	<td class="main"><?php echo $order->info['payment_method']; ?></td>
  </tr>
</table></td>
  </tr>
 <tr>
<td><?php echo tep_draw_separator('pixel_trans.gif', '1', '10'); ?></td>
 </tr>
 <tr>
<td><table border="0" width="100%" cellspacing="0" cellpadding="2">
  <tr class="dataTableHeadingRow">
	<td class="dataTableHeadingContent" colspan="2"><?php echo TABLE_HEADING_PRODUCTS; ?></td>
	<td class="dataTableHeadingContent"><?php echo TABLE_HEADING_PRODUCTS_MODEL; ?></td>
	<td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_TAX; ?></td>
	<td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_PRICE_EXCLUDING_TAX; ?></td>
	<td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_PRICE_INCLUDING_TAX; ?></td>
	<td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_TOTAL_EXCLUDING_TAX; ?></td>
	<td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_TOTAL_INCLUDING_TAX; ?></td>
  </tr>
<?php
for ($i = 0, $n = sizeof($order->products); $i < $n; $i++) {
  echo '	  <tr class="dataTableRow">' . "\n" .
	   '		<td class="dataTableContent" valign="top" align="right">' . $order->products[$i]['qty'] . ' x</td>' . "\n" .
	   '		<td class="dataTableContent" valign="top">' . $order->products[$i]['name'];

  if (isset($order->products[$i]['attributes']) && (($k = sizeof($order->products[$i]['attributes'])) > 0)) {
	for ($j = 0; $j < $k; $j++) {
	  echo '<br><nobr><small> <i> - ' . $order->products[$i]['attributes'][$j]['option'] . ': ' . $order->products[$i]['attributes'][$j]['value'];
	  if ($order->products[$i]['attributes'][$j]['price'] != '0') echo ' (' . $order->products[$i]['attributes'][$j]['prefix'] . $currencies->format($order->products[$i]['attributes'][$j]['price'] * $order->products[$i]['qty'], true, $order->info['currency'], $order->info['currency_value']) . ')';
	  echo '</i></small></nobr>';
	}
  }

  echo '		</td>' . "\n" .
	   '		<td class="dataTableContent" valign="top">' . $order->products[$i]['model'] . '</td>' . "\n";
  echo '		<td class="dataTableContent" align="right" valign="top">' . tep_display_tax_value($order->products[$i]['tax']) . '%</td>' . "\n" .
	   '		<td class="dataTableContent" align="right" valign="top"><b>' . $currencies->format($order->products[$i]['final_price'], true, $order->info['currency'], $order->info['currency_value']) . '</b></td>' . "\n" .
	   '		<td class="dataTableContent" align="right" valign="top"><b>' . $currencies->format(tep_add_tax($order->products[$i]['final_price'], $order->products[$i]['tax']), true, $order->info['currency'], $order->info['currency_value']) . '</b></td>' . "\n" .
	   '		<td class="dataTableContent" align="right" valign="top"><b>' . $currencies->format($order->products[$i]['final_price'] * $order->products[$i]['qty'], true, $order->info['currency'], $order->info['currency_value']) . '</b></td>' . "\n" .
	   '		<td class="dataTableContent" align="right" valign="top"><b>' . $currencies->format(tep_add_tax($order->products[$i]['final_price'], $order->products[$i]['tax']) * $order->products[$i]['qty'], true, $order->info['currency'], $order->info['currency_value']) . '</b></td>' . "\n";
  echo '	  </tr>' . "\n";
  }
 $Business = tep_db_query("select value from " . TABLE_CUSTOMERS_TO_EXTRA_FIELDS . " where value = '" . (int)$oID ."'");
 while ($result=tep_db_fetch_array($Business)) {
echo $result['value'] . "\n";
}
?>
  <tr>
	<td align="right" colspan="8"><table border="0" cellspacing="0" cellpadding="2">
<?php
 for ($i = 0, $n = sizeof($order->totals); $i < $n; $i++) {
echo '		  <tr>' . "\n" .
	 '			<td align="right" class="smallText">' . $order->totals[$i]['title'] . '</td>' . "\n" .
	 '			<td align="right" class="smallText">' . $order->totals[$i]['text'] . '</td>' . "\n" .
	 '		  </tr>' . "\n";
 }
?>
	</table></td>
  </tr>
	</table></td>
 </tr>
</table>
<!-- body_text_eof //-->

<br>

<script Language="Javascript">

/*
This script is written by Eric ([email protected])
For full source code, installation instructions,
100's more DHTML scripts, and Terms Of
Use, visit dynamicdrive.com
*/

function printit(){  
if (window.print) {
window.print();  
} else {
var WebBrowser = '<OBJECT ID="WebBrowser1" WIDTH=0 HEIGHT=0 CLASSID="CLSID:8856F961-340A-11D0-A96B-00C04FD705A2"></OBJECT>';
document.body.insertAdjacentHTML('beforeEnd', WebBrowser);
WebBrowser1.ExecWB(6, 2);//Use a 1 vs. a 2 for a prompting dialog box	WebBrowser1.outerHTML = "";  
}
}
</script>

<script Language="Javascript">  
var NS = (navigator.appName == "Netscape");
var VERSION = parseInt(navigator.appVersion);
if (VERSION > 3) {
document.write('<form><input type=button value="Print this Page" name="Print" onClick="printit()"></form>');		
}
</script>

</body>
</html>
<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>

 

but the query does not make much sense.

it says select x where x = y

 

so if found x is always y

Treasurer MFC

Link to comment
Share on other sites

Sorry, lost me on that

Installed Contributions: CCGV, Close Popup, Dynamic Meta Tags, Easy Populate, Froogle Data Feeder, Google Position, Infobox Header Entire Row, Live Support for OSC, PayPal Seal with CC images, Report_m Sales, Shop by Price Revised, SQL Updater, Who's Online Enhancement, Footer, GNA EP Assistant and still going.

Link to comment
Share on other sites

boxtel, thanks but that didn't work.

 

And I have no idea what "it says select x where x = y so if found x is always y" means.

 

Any other ideas anyone?

 

Thanks.........

Installed Contributions: CCGV, Close Popup, Dynamic Meta Tags, Easy Populate, Froogle Data Feeder, Google Position, Infobox Header Entire Row, Live Support for OSC, PayPal Seal with CC images, Report_m Sales, Shop by Price Revised, SQL Updater, Who's Online Enhancement, Footer, GNA EP Assistant and still going.

Link to comment
Share on other sites

boxtel, thanks but that didn't work.

 

And I have no idea what "it says select x where x = y so if found x is always y" means.

 

Any other ideas anyone?

 

Thanks.........

 

your query:

 

$Business = tep_db_query("select value from " . TABLE_CUSTOMERS_TO_EXTRA_FIELDS . " where value = '" . (int)$oID ."'");

 

basically states, find all rows where the column "value" equals $oID and return the column "value".

 

as in select x where x = y

 

which means if found, x is always y

 

in a real world example :

 

select value from table where value = 100

 

will either return nothing or 100

Treasurer MFC

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...