Table query is not working. Need some help please.

**southshorepizza** · 17 Aug 2014, 03:56 AM

I am trying to create a unique module. In doing so I created a table called zen_employees. This table looks like this and has three columns.
employee_id | employee_first | employee_last.
1 | first1 | last1
2 | first2 | last2

I want to query TABLE_EMPLOYEES and then create a drop down list of the employees first names. I have tried using the wiki and I get error messages. I have tried searching for it. I get error messages. The only thing I have tried that hasn't given me an error message is

Code:

<?php
require('includes/application_top.php');
?>
<!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>
<select name="dropdown">
<?php
 $result1= mysql_query("SELECT * FROM TABLE_EMPLOYEES");
 while($row = mysql_fetch_array($result1)){
      echo '<option value="' . $row['employee_id'] . '">' . $row['employee_first'] . '</option>';
 }
?>
</select>

With this I don't get anything in my drop down box.

Any help would be appreciated.

**RodG** · 17 Aug 2014, 02:13 PM

Originally Posted by southshorepizza

With this I don't get anything in my drop down box.

Any help would be appreciated.

Try this for the SQL portion of your code:

Code:

$result = $db->Execute("SELECT * FROM TABLE_EMPLOYEES");
while (!$result->EOF) {
  echo '<option value="' . $result->fields['employee_id'] . '">' . $result->fields['employee_first'] . '</option>';
$result->MoveNext(); 
  }

Also check that your constant TABLE_EMPLOYEES is defined as 'zen_employees' (if in doubt, change the query to "SELECT * FROM 'zen_employees' "

Cheers
RodG

**lat9** · 17 Aug 2014, 02:46 PM

Teeny change to RodG's code suggestion:

Code:

$result = $db->Execute("SELECT * FROM " . TABLE_EMPLOYEES);
while (!$result->EOF) {
  echo '<option value="' . $result->fields['employee_id'] . '">' . $result->fields['employee_first'] . '</option>';
$result->MoveNext(); 
  }

**southshorepizza** · 17 Aug 2014, 03:13 PM

Originally Posted by lat9

Teeny change to RodG's code suggestion:

Code:

$result = $db->Execute("SELECT * FROM " . TABLE_EMPLOYEES);
while (!$result->EOF) {
  echo '<option value="' . $result->fields['employee_id'] . '">' . $result->fields['employee_first'] . '</option>';
$result->MoveNext(); 
  }

I now have

Code:

<select name="dropdown">
<?php
$result = $db->Execute("SELECT * FROM " . TABLE_EMPLOYEES);
while (!$result->EOF) {
  echo '<option value="' . $result->fields['employee_id'] . '">' . $result->fields['employee_first'] . '</option>';
$result->MoveNext(); 
  }

?>
</select>

This prevents the rest of my page from loading AND I still don't get anything in the drop down list.
Here is the phpmyadmin showing the table name and the column names.

Click image for larger version.

Name: myadmin.jpg
Views: 90
Size: 32.6 KB
ID: 14407

Here is what the page looks like with my broken code. When the two changes suggested I loose all the order details below the drop down. It's like the page just stops loading.

Thank you for your help in solving this.

Click image for larger version.

Name: broken.jpg
Views: 80
Size: 42.5 KB
ID: 14408

**gjh42** · 17 Aug 2014, 04:03 PM

If the page stops loading at some point, it is most likely a PHP error, which will show up as a myDebugxxx file in either your /cache/ folder or /logs/ folder, depending on your Zen Cart version. Find the latest debug log files and post them here so we can see what the reported error is. Also, more information on your site may be helpful. Post the information requested above the "reply" textbox.

**mc12345678** · 17 Aug 2014, 04:19 PM

Originally Posted by southshorepizza

I now have

Code:

<select name="dropdown">
<?php
$result = $db->Execute("SELECT * FROM " . TABLE_EMPLOYEES);
while (!$result->EOF) {
  echo '<option value="' . $result->fields['employee_id'] . '">' . $result->fields['employee_first'] . '</option>';
$result->MoveNext(); 
  }

?>
</select>

This prevents the rest of my page from loading AND I still don't get anything in the drop down list.
Here is the phpmyadmin showing the table name and the column names.

Here is what the page looks like with my broken code. When the two changes suggested I loose all the order details below the drop down. It's like the page just stops loading.

Thank you for your help in solving this.

Error logs? In older Zc (<1.5.1 in the cache directory, newer 1.5.1 and above.)

**southshorepizza** · 17 Aug 2014, 04:27 PM

Code:

[17-Aug-2014 11:21:34] PHP Fatal error:  1146:Table 'w57dsjmm_zncr1.TABLE_EMPLOYEES' doesn't exist :: SELECT * FROM TABLE_EMPLOYEES in /home4/w57dsjmm/public_html/order/includes/classes/db/mysql/query_factory.php on line 120

I added the table in myphpadmin. Do I have to register it in a zen-cart file somewhere?

I also tried changing the table name from TABLE_EMPLOYEES to ZEN_EMPLOYEES It created this log

Code:

[17-Aug-2014 11:24:47] PHP Fatal error:  1146:Table 'w57dsjmm_zncr1.ZEN_EMPLOYEES' doesn't exist :: SELECT * FROM ZEN_EMPLOYEES in /home4/w57dsjmm/public_html/order/includes/classes/db/mysql/query_factory.php on line 120

**southshorepizza** · 17 Aug 2014, 04:28 PM

Code:

<?php
/**
 * @package admin
 * @copyright Copyright 2003-2013 Zen Cart Development Team
 * @copyright Portions Copyright 2003 osCommerce
 * @license http://www.zen-cart.com/license/2_0.txt GNU Public License V2.0
 * @version GIT: $Id: Author: DrByte  Wed Nov 6 21:04:33 2013 -0500 Modified in v1.5.2 $
 */

  require('includes/application_top.php');

  // unset variable which is sometimes tainted by bad plugins like magneticOne tools
  if (isset($module)) unset($module);

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

  if (isset($_GET['oID'])) $_GET['oID'] = (int)$_GET['oID'];

  include(DIR_WS_CLASSES . 'order.php');

?>
<!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/dispatch.css">
<link rel="stylesheet" type="text/css" href="includes/cssjsmenuhover.css" media="all" id="hoverJS">
<script language="javascript" src="includes/menu.js"></script>
<script language="javascript" src="includes/general.js"></script>
<script type="text/javascript">
  <!--
  function init()
  {
    cssjsmenu('navbar');
    if (document.getElementById)
    {
      var kill = document.getElementById('hoverJS');
      kill.disabled = true;
    }
  }
  // -->
</script>
<script language="javascript" type="text/javascript"><!--
function couponpopupWindow(url) {
  window.open(url,'popupWindow','toolbar=no,location=no,directories=no,status=no,menubar=no,scrollbars=yes,resizable=yes,copyhistory=no,width=450,height=280,screenX=150,screenY=150,top=150,left=150')
}
//--></script>
</head>
<body onLoad="init()">
<!-- header //-->
<div class="header-area">
<?php
  require(DIR_WS_INCLUDES . 'header.php');
?>
</div>
<!-- header_eof //-->

<!-- body //-->
<table border="0" width="100%" cellspacing="2" cellpadding="2">
<!-- body_text //-->
<?php
  if (($action == 'edit') && ($order_exists == true)) {
    $order = new order($oID);
    if ($order->info['payment_module_code']) {
      if (file_exists(DIR_FS_CATALOG_MODULES . 'payment/' . $order->info['payment_module_code'] . '.php')) {
        require(DIR_FS_CATALOG_MODULES . 'payment/' . $order->info['payment_module_code'] . '.php');
        require(DIR_FS_CATALOG_LANGUAGES . $_SESSION['language'] . '/modules/payment/' . $order->info['payment_module_code'] . '.php');
        $module = new $order->info['payment_module_code'];

      }
    }
?>
        </table></td>
            </tr>
<?php
      if (is_object($module) && method_exists($module, 'admin_notification')) {
?>
<?php
}
?>
        </table></td>
      </tr>
        </table></td>
      </tr>
<?php
  } else {
?>
      <tr>
        <td><table border="0" width="100%" cellspacing="0" cellpadding="0">

          <p>Dispatch Screen</p>
<select name="dropdown">
<?php
$result = $db->Execute("SELECT * FROM " . ZEN_EMPLOYEES);
while (!$result->EOF) {
  echo '<option value="' . $result->fields['employee_id'] . '">' . $result->fields['employee_first'] . '</option>';
$result->MoveNext(); 
  }
?>
</select>

            <td valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">
              <tr class="dataTableHeadingRow">
<?php
// Sort Listing
          switch ($_GET['list_order']) {
              case "id-asc":
              $disp_order = "c.customers_id";
              break;
              
              default:
              $disp_order = "c.customers_id DESC";
          }
?>
                <td class="dataTableHeadingContent" align="center"><?php echo "Dispatch"; ?></td>
                <td class="dataTableHeadingContent" align="center"><?php echo TABLE_HEADING_ORDERS_ID; ?></td>
                <td class="dataTableHeadingContent" align="center"><?php echo "Address"; ?></td>
                <td class="dataTableHeadingContent" align="center"><?php echo "Order Age"; ?></td>
                <td class="dataTableHeadingContent" align="center"><?php echo TABLE_HEADING_ORDER_TOTAL; ?></td>
              </tr>

<?php
// Only one or the other search
// create search_orders_products filter
  $search = '';
  $new_table = '';
  $new_fields = '';
  if (isset($_GET['search_orders_products']) && zen_not_null($_GET['search_orders_products'])) {
    $new_fields = '';
    $search_distinct = ' distinct ';
    $new_table = " left join " . TABLE_ORDERS_PRODUCTS . " op on (op.orders_id = o.orders_id) ";
    $keywords = zen_db_input(zen_db_prepare_input($_GET['search_orders_products']));
    $search = " and (op.products_model like '%" . $keywords . "%' or op.products_name like '" . $keywords . "%')";
    if (substr(strtoupper($_GET['search_orders_products']), 0, 3) == 'ID:') {
      $keywords = TRIM(substr($_GET['search_orders_products'], 3));
      $search = " and op.products_id ='" . (int)$keywords . "'";
    }
  } else {
?>
<?php
// create search filter
  $search = '';
  if (isset($_GET['search']) && zen_not_null($_GET['search'])) {
    $search_distinct = ' ';
    $keywords = zen_db_input(zen_db_prepare_input($_GET['search']));
    $search = " and (o.customers_city like '%" . $keywords . "%' or o.customers_postcode like '%" . $keywords . "%' or o.date_purchased like '%" . $keywords . "%' or o.billing_name like '%" . $keywords . "%' or o.billing_company like '%" . $keywords . "%' or o.billing_street_address like '%" . $keywords . "%' or o.delivery_city like '%" . $keywords . "%' or o.delivery_postcode like '%" . $keywords . "%' or o.delivery_name like '%" . $keywords . "%' or o.delivery_company like '%" . $keywords . "%' or o.delivery_street_address like '%" . $keywords . "%' or o.billing_city like '%" . $keywords . "%' or o.billing_postcode like '%" . $keywords . "%' or o.customers_email_address like '%" . $keywords . "%' or o.customers_name like '%" . $keywords . "%' or o.customers_company like '%" . $keywords . "%' or o.customers_street_address  like '%" . $keywords . "%' or o.customers_telephone like '%" . $keywords . "%' or o.ip_address  like '%" . $keywords . "%')";
    $new_table = '';
//    $new_fields = ", o.customers_company, o.customers_email_address, o.customers_street_address, o.delivery_company, o.delivery_name, o.delivery_street_address, o.billing_company, o.billing_name, o.billing_street_address, o.payment_module_code, o.shipping_module_code, o.ip_address ";
  }
} // eof: search orders or orders_products
    $new_fields = ", o.customers_company, o.customers_email_address, o.customers_street_address, o.delivery_company, o.delivery_name, o.delivery_street_address, o.billing_company, o.billing_name, o.billing_street_address, o.payment_module_code, o.shipping_module_code, o.ip_address ";
?>
<?php
    if (isset($_GET['cID'])) {
      $cID = zen_db_prepare_input($_GET['cID']);
      $orders_query_raw =   "select o.orders_id, o.customers_id, o.customers_name, o.payment_method, o.shipping_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total" .
                            $new_fields . "
                            from (" . TABLE_ORDERS_STATUS . " s, " .
                            TABLE_ORDERS . " o " .
                            $new_table . ")
                            left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id and ot.class = 'ot_total') " . "
                            where o.customers_id = '" . (int)$cID . "' and o.orders_status = s.orders_status_id and s.language_id = '" . (int)$_SESSION['languages_id'] . "' order by orders_id DESC";

//echo '<BR><BR>I SEE A: ' . $orders_query_raw . '<BR><BR>';

    } elseif ($_GET['status'] != '') {
      $status = zen_db_prepare_input($_GET['status']);
      $orders_query_raw = "select o.orders_id, o.customers_id, o.customers_name, o.payment_method, o.shipping_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total" .
                          $new_fields . "
                          from (" . TABLE_ORDERS_STATUS . " s, " .
                          TABLE_ORDERS . " o " .
                          $new_table . ")
                          left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id and ot.class = 'ot_total') " . "
                          where o.orders_status = s.orders_status_id and s.language_id = '" . (int)$_SESSION['languages_id'] . "' and s.orders_status_id = '" . (int)$status . "'  " .
                          $search . " order by o.orders_id DESC";

//echo '<BR><BR>I SEE B: ' . $orders_query_raw . '<BR><BR>';

    } else {
      $orders_query_raw = "select " . $search_distinct . " o.orders_id, o.customers_id, o.customers_name, o.payment_method, o.shipping_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total" .
                          $new_fields . "
                          from (" . TABLE_ORDERS_STATUS . " s, " .
                          TABLE_ORDERS . " o " .
                          $new_table . ")
                          left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id and ot.class = 'ot_total') " . "
                          where (o.orders_status = s.orders_status_id and s.language_id = '" . (int)$_SESSION['languages_id'] . "')  " .
                          $search . " order by o.orders_id DESC";

//echo '<BR><BR>I SEE C: ' . $orders_query_raw . '<BR><BR>';

    }

// Split Page
// reset page when page is unknown
if (($_GET['page'] == '' or $_GET['page'] <= 1) and $_GET['oID'] != '') {
  $check_page = $db->Execute($orders_query_raw);
  $check_count=1;
  if ($check_page->RecordCount() > MAX_DISPLAY_SEARCH_RESULTS_ORDERS) {
    while (!$check_page->EOF) {
      if ($check_page->fields['orders_id'] == $_GET['oID']) {
        break;
      }
      $check_count++;
      $check_page->MoveNext();
    }
    $_GET['page'] = round((($check_count/MAX_DISPLAY_SEARCH_RESULTS_ORDERS)+(fmod_round($check_count,MAX_DISPLAY_SEARCH_RESULTS_ORDERS) !=0 ? .5 : 0)),0);
  } else {
    $_GET['page'] = 1;
  }
}

//    $orders_query_numrows = '';
    $orders_split = new splitPageResults($_GET['page'], MAX_DISPLAY_SEARCH_RESULTS_ORDERS, $orders_query_raw, $orders_query_numrows);
    $orders = $db->Execute($orders_query_raw);
    while (!$orders->EOF) {
    if ((!isset($_GET['oID']) || (isset($_GET['oID']) && ($_GET['oID'] == $orders->fields['orders_id']))) && !isset($oInfo)) {
        $oInfo = new objectInfo($orders->fields);
      }

      
?>

                <td class="dataTableContent" align="center"><input type="checkbox" name="otd" value="$orders->fields['orders_id']" /></td>
                <td class="dataTableContent" align="center"><?php echo $show_difference . $orders->fields['orders_id']; ?></td>
                <td class="dataTableContent" align="center"><?php echo $orders->fields['delivery_street_address']; ?></td>
                <td class="dataTableContent" align="center"><?php
$purchase_time = strtotime ($orders->fields['date_purchased']);
$difference = time () - $purchase_time;
$difference_in_minutes = ceil ($difference / 60);  //-Round up to the next minute
echo gmdate("H:i:s", $difference); 
?></td>
                <td class="dataTableContent" align="center"><?php echo strip_tags($orders->fields['order_total']); ?></td>
              </tr>
<?php
      $orders->MoveNext();
    }
?>
              <tr>
                <td colspan="5"><table border="0" width="100%" cellspacing="0" cellpadding="2">
                  <tr>
                    <td class="smallText" valign="top"><?php echo $orders_split->display_count($orders_query_numrows, MAX_DISPLAY_SEARCH_RESULTS_ORDERS, $_GET['page'], TEXT_DISPLAY_NUMBER_OF_ORDERS); ?></td>
                    <td class="smallText" align="right"><?php echo $orders_split->display_links($orders_query_numrows, MAX_DISPLAY_SEARCH_RESULTS_ORDERS, MAX_DISPLAY_PAGE_LINKS, $_GET['page'], zen_get_all_get_params(array('page', 'oID', 'action'))); ?></td>
                  </tr>
                </table></td>
              </tr>
            </table></td>
          </tr>
        </table></td>
      </tr>
<?php
  }
?>
    </table></td>
<!-- body_text_eof //-->
  </tr>
</table>
<!-- body_eof //-->

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

Thread: Table query is not working. Need some help please.

Thread Tools

Search Thread

Display

Hybrid View

Table query is not working. Need some help please.

Re: Table query is not working. Need some help please.

Re: Table query is not working. Need some help please.

Re: Table query is not working. Need some help please.

Re: Table query is not working. Need some help please.

Re: Table query is not working. Need some help please.

Re: Table query is not working. Need some help please.

entire page. It seems to work fine until I run this query though

Similar Threads

v151 Need some help building a looping SQL query to fill an array.

v152 Coupons not working when payment module is on. Need help Please

Need Some Help Joining Category Name to Search Query

Template not working...Need help please!

Posting Permissions