• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 371
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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