ODBC Syntax error? Hmm

PHP
1

Hey,

I’m getting this error:


Warning: odbc_fetch_array(): supplied argument is not a valid ODBC result resource in C:\\Users\\Public\\Desktop\\Server\\www\\web\\system_files\\sql_class\\_customer_purchase_history.class.php on line 34

Although the syntax looks correct. Could someone else lend a pair of eyes please? :slight_smile:


function get_prod_from_sql($New_ship = '', $columnSort = 'item_desc', $viewSort = 'ASC')
{
  global $cust_account, $shipping_session, $shipping_id;
  $conn = odbc_connect(PROPHET_DATA_SOURCE, PROPHET_USER, PROPHET_PASSW);
  if($shipping_session > 0 || $New_ship > 0 )
  {
    $prod_query = "SELECT * FROM jbi_web_cust_purch_hist
                         WHERE customer_id = '" .(int)$cust_account. "'
                         AND ship_to_id = '" .$shipping_session. "'
                         ORDER BY '".$columnSort."' '".$viewSort."'";
  }
  else
  {
    if ($New_ship == "Show_all")
    {
      $prod_query = "SELECT * FROM jbi_web_cust_purch_hist
                           WHERE customer_id = '" .(int)$cust_account. "'
                           ORDER BY '".$columnSort."' '".$viewSort."'";
    }
    else
    {
      $prod_query = "SELECT * FROM jbi_web_cust_purch_hist
                           WHERE customer_id ='" .(int)$cust_account. "'
                           AND ship_to_id ='".$shipping_id."'
                           ORDER BY '".$columnSort."' '".$viewSort."'";
    }
  }
  $prod_conn = odbc_exec($conn, $prod_query);
  while($favorites = odbc_fetch_array($prod_conn))
  {
    $part_number = $favorites['part_no'];
    $qty = (int)$favorites['qty_shipped'];
    $item_desc = $favorites['item_desc'];
    $ext_price = $favorites['ext_price'];
    $get_prod = db_query("SELECT products_id, short_code FROM " . TABLE_PRODUCTS . " WHERE part_number = '" . $part_number . "'");
    $prod_res = db_fetch_array($get_prod);
    $this->contents[$favorites['part_no']] = array('qty' => $qty, 'products_id' => $prod_res['products_id'], 'prod_name' => $prod_res['short_code'], 'ext_price' => $ext_price, 'products_name' =>$item_desc);//adding element to the array contents
  }
}

Thanks

2

First thing that stands out for me is that your quoting integers in your SQL, this is not necessary. :slight_smile:

1 Like
3

Change, the following code…


$prod_conn = odbc_exec($conn, $prod_query) or die(odbc_errormsg($conn));

Any pointers?

4

The reason I had quotes was because my IDE highlights that area and it’s easier for me to read :smiley:

Anyway, I tried the die function and this is what it outputs:


Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'ASC'., SQL state 37000 in SQLExecDirect in C:\\Users\\Public\\Desktop\\Server\\www\\web\\system_files\\sql_class\\_customer_purchase_history.class.php on line 36
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'ASC'.

Although, ASC is a proper SQL syntax, I don’t know why it’s showing that.

5

Try echo’ing out the compiled query before executing it, does it look ok? Are the values what you expect? Are they escaped properly?

Nearly there. :slight_smile:

6

ah ha!

It’s not escaping the single quotes.


SELECT * FROM jbi_web_cust_purch_hist WHERE customer_id = '542' ORDER BY 'item_desc' 'ASC'

the query should look just like that but without the single quotes for item_desc and ASC. What do you recommend?

7

This.

function get_prod_from_sql($New_ship = '', $columnSort = 'item_desc', $viewSort = 'ASC') 

{

  global $cust_account, $shipping_session, $shipping_id;

  $conn = odbc_connect(PROPHET_DATA_SOURCE, PROPHET_USER, PROPHET_PASSW);

  if($shipping_session > 0 || $New_ship > 0 ) 

  {

    $prod_query = "SELECT * FROM jbi_web_cust_purch_hist 

                         WHERE customer_id = '" .(int)$cust_account. "' 

                         AND ship_to_id = '" .$shipping_session. "' 

                         ORDER BY ".$columnSort." ".$viewSort;

  }

  else

  {

    if ($New_ship == "Show_all")

    {

      $prod_query = "SELECT * FROM jbi_web_cust_purch_hist 

                           WHERE customer_id = '" .(int)$cust_account. "' 

                           ORDER BY ".$columnSort." ".$viewSort;

    }

    else

    {

      $prod_query = "SELECT * FROM jbi_web_cust_purch_hist 

                           WHERE customer_id ='" .(int)$cust_account. "' 

                           AND ship_to_id ='".$shipping_id."' 

                           ORDER BY ".$columnSort." ".$viewSort;

    }

  }

  $prod_conn = odbc_exec($conn, $prod_query);

  while($favorites = odbc_fetch_array($prod_conn))

  {

    $part_number = $favorites['part_no'];

    $qty = (int)$favorites['qty_shipped'];

    $item_desc = $favorites['item_desc'];

    $ext_price = $favorites['ext_price'];

    $get_prod = db_query("SELECT products_id, short_code FROM " . TABLE_PRODUCTS . " WHERE part_number = '" . $part_number . "'");

    $prod_res = db_fetch_array($get_prod);

    $this->contents[$favorites['part_no']] = array('qty' => $qty, 'products_id' => $prod_res['products_id'], 'prod_name' => $prod_res['short_code'], 'ext_price' => $ext_price, 'products_name' =>$item_desc);//adding element to the array contents

  }

}

See, you didn’t need any help at all.

Just a kick. :stuck_out_tongue:

8

ah yes! thanks :smiley:

Running smooth like a baby’s butt!

9

Hi I’m back again :-\

I’m noticing when I use ASC or DESC that some the rows are not being put where they are suppose to be.

For example, if I say ORDER BY qty_shipped DESC then it will sort some of the rows in descending order and the rest will be put random order. It’s suppose to show from 1 all the way up into the 4000s.

I’m wondering what’s going on there? How can I fix that? The code is the same as above.

10

Check the data type on the column, pound-to-a-penny it’s not numeric. :wink:

11

I can’t really check the data type as I don’t have direct access to it. The network admin will not allow a peek into the DB. All I have are the column names :-\

I am noticing though that the Description column works perfect. It will arrange them A-Z or Z-A, but when it comes to numeric it screws all up.

12

Execute a show tables query, it should detail the column data types, if odbc compatible.


SHOW CREATE TABLE jbi_web_cust_purch_hist
13

It’s giving an error:



Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'jbi_web_cust_purch_hist'., SQL state 37000 in SQLExecDirect in C:\\Users\\Public



$queryTest = "SHOW CREATE TABLE jbi_web_cust_purch_hist";
$exec = odbc_exec($conn, $queryTest);
14

Maybe I’m over thinking this here,…

SELECT qty_shipped FROM table LIMIT 1

var_dump($row['qty_shipped'])

What does the var_dump output?

15

Another error :frowning:


Warning: odbc_fetch_array(): supplied argument is not a valid ODBC result resource in C:\\Users\\Public\\Desktop\\Server\\www\\web\\system_files\\sql_class\\_customer_purchase_history.class.php on line 38




$queryTest = "SELECT qty_shipped FROM jbi_web_cust_purch_hist LIMIT 1";
      $exec = odbc_exec($conn, $queryTest);
      while ($row = odbc_fetch_array($queryTest))
      {
        var_dump($row['qty_shipped']);
      }
16

Should it not be?

while ($row = odbc_fetch_arrayCOLOR=#007700)[/COLOR]

17

Yea sorry was typing too fast.

Anyway, it still gives the same error.

18

What does odbc_errormsg() say when you execute the query? Remove the ‘LIMIT 1’ also.

19 

Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '1'., SQL state 37000 in SQLExecDirect in C:\\Users\\Public\\Desktop\\Server\\www\\web\\system_files\\sql_class\\_customer_purchase_history.class.php on line 39
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '1'.

hmm…

20

Have you tried removing the LIMIT 1?