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\publi c_html\inc ludes\cart _functions .php on line 39
Warning: odbc_fetch_row(): supplied argument is not a valid ODBC result resource in c:\LocalUser\fashuti\publi c_html\inc ludes\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\publi c_html\inc ludes\cart _functions .php on line 39
Warning: odbc_fetch_row(): supplied argument is not a valid ODBC result resource in c:\LocalUser\fashuti\publi c_html\inc ludes\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\publi c_html\inc ludes\cart _functions .php on line 39
Warning: odbc_fetch_row(): supplied argument is not a valid ODBC result resource in c:\LocalUser\fashuti\publi c_html\inc ludes\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\publi c_html\inc ludes\cart _functions .php on line 39
Warning: odbc_fetch_row(): supplied argument is not a valid ODBC result resource in c:\LocalUser\fashuti\publi c_html\inc ludes\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.
$conn=odbc_connect('danum_
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&
$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_
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\publi
Warning: odbc_fetch_row(): supplied argument is not a valid ODBC result resource in c:\LocalUser\fashuti\publi
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\publi
Warning: odbc_fetch_row(): supplied argument is not a valid ODBC result resource in c:\LocalUser\fashuti\publi
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\publi
Warning: odbc_fetch_row(): supplied argument is not a valid ODBC result resource in c:\LocalUser\fashuti\publi
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\publi
Warning: odbc_fetch_row(): supplied argument is not a valid ODBC result resource in c:\LocalUser\fashuti\publi
I have tried every combination and cannot see how to get around this problem would be grateful to anyone who can help.
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
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\publi c_html\car t.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
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='.
the thing is that the sql works fine it gives the desired output but i obviously need a webpage without errors
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
there seems to be a underscode in the $id value?