• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 365
  • Last Modified:

ODBC connection - sql query error with autonumber

I am currently making a shopping cart and have come across a problem using a variable to relate to a database autonumber:

$conn=odbc_connect('danum_fashu','','');
foreach ($contents as $id=>$qty)
      {
      $sql ="SELECT * FROM products, product_line WHERE product_line.listing_no= $id AND product_line.product_id = products.product_id";
      $rs=odbc_exec($conn,$sql);
      
      while (odbc_fetch_row($rs))

listing_no is an autonumber and $id is a variable data type which comes from an action=add link on a seperate page

echo '<a href="cart.php?action=add&id='.$list.'">Add to Cart</a>';            
                  $cart = $_SESSION['cart'];
                  if ($cart) {
                        $cart .= ','.$_GET['id'];
                  } else {
                        $cart = $_GET['id'];
                  }
                  $_SESSION['cart'] = $cart;

i have tried lots of different combinations and get various errors for each see below for outcomes of different attempts:

1)
$conn=odbc_connect('danum_fashu','','');
foreach ($contents as $id=>$qty)
      {
      $sql ="SELECT * FROM products, product_line WHERE product_line.listing_no= $id AND product_line.product_id = products.product_id";
      $rs=odbc_exec($conn,$sql);
      
      while (odbc_fetch_row($rs))

this works correctly for the first product addition but then if you add another it gives the following warning:

Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Syntax error in query expression 'product_line.listing_no= _2 AND product_line.product_id = products.product_id'., SQL state 37000 in SQLExecDirect in c:\LocalUser\fashuti\public_html\includes\cart_functions.php on line 39

Warning: odbc_fetch_row(): supplied argument is not a valid ODBC result resource in c:\LocalUser\fashuti\public_html\includes\cart_functions.php on line 41


2)

{
      $sql ="SELECT * FROM products, product_line WHERE product_line.listing_no= '". $id . "' AND product_line.product_id = products.product_id";
      $rs=odbc_exec($conn,$sql);
      
      while (odbc_fetch_row($rs))
            {


Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression., SQL state 22005 in SQLExecDirect in c:\LocalUser\fashuti\public_html\includes\cart_functions.php on line 39

Warning: odbc_fetch_row(): supplied argument is not a valid ODBC result resource in c:\LocalUser\fashuti\public_html\includes\cart_functions.php on line 41

Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression., SQL state 22005 in SQLExecDirect in c:\LocalUser\fashuti\public_html\includes\cart_functions.php on line 39

Warning: odbc_fetch_row(): supplied argument is not a valid ODBC result resource in c:\LocalUser\fashuti\public_html\includes\cart_functions.php on line 41

3)

foreach ($contents as $id=>$qty)
      {
      $sql ='SELECT * FROM products, product_line WHERE product_line.listing_no= '. $id . ' AND product_line.product_id = products.product_id';
      $rs=odbc_exec($conn,$sql);
      
      while (odbc_fetch_row($rs))
            {

Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Syntax error in query expression 'product_line.listing_no= _2 AND product_line.product_id = products.product_id'., SQL state 37000 in SQLExecDirect in c:\LocalUser\fashuti\public_html\includes\cart_functions.php on line 39

Warning: odbc_fetch_row(): supplied argument is not a valid ODBC result resource in c:\LocalUser\fashuti\public_html\includes\cart_functions.php on line 41


I have tried every combination and cannot see how to get around this problem would be grateful to anyone who can help.
0
mememan
Asked:
mememan
  • 3
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>product_line.listing_no= _2

there seems to be a underscode in the $id value?
0
 
mememanAuthor Commented:
yes but there definitely isnt i have echoed it out of the sql statement and it is the number on its own there is no underscore added anywhere in the code
0
 
mememanAuthor Commented:
I have now found the underscore and fixed that problem and now im getting a different error with the select statement

Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'product_line.product_id = products.product_id AND product_line.listing_no='., SQL state 37000 in SQLExecDirect in c:\LocalUser\fashuti\public_html\cart.php on line 40

the thing is that the sql works fine it gives the desired output but i obviously need a webpage without errors
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this now looks like the value for the listing_no that you pass is empty.
0
 
mememanAuthor Commented:
but its not empty and if it wasnt empty and if it was empty it wouldnt be able to display the record but it does. Its really strange, so what i need to do is to find a way to make the warnings invisible or turn them off.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now