Link to home
Start Free TrialLog in
Avatar of mememan
mememan

asked on

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.
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

>product_line.listing_no= _2

there seems to be a underscode in the $id value?
Avatar of mememan
mememan

ASKER

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
Avatar of mememan

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mememan

ASKER

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.