[SOLVED] Query failed. You have an error in your SQL syntax.

Deanznet · December 3, 2007

I keep getting

Query failed. You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ''Idk' at line 1

Not sure why

<?php
function dbQuery($sql)
{
   return mysql_query($sql) or die('Query failed. ' . mysql_error());
}

function dbAffectedRows()
{
   global $dbConn;

   return mysql_affected_rows($dbConn);
}

function dbFetchArray($result, $resultType = MYSQL_NUM)
{
   return mysql_fetch_array($result, $resultType);
}

function dbFetchAssoc($result)
{
   return mysql_fetch_assoc($result);
}

function dbFetchRow($result)
{
   return mysql_fetch_row($result);
}

function dbFreeResult($result)
{
   return mysql_free_result($result);
}

function dbNumRows($result)
{
   return mysql_num_rows($result);
}


//select the users money
$query = mysql_query("SELECT user_points FROM phpbb_users WHERE user_id='$user_id'")or die(mysql_error());
$user_points = mysql_fetch_assoc($query);
    
    
    //check if they submitted the form
    if (isset($_POST['submit'])) {
        
        if ($_POST['action'] == 'sell') {
            
            echo "<br>";
            
            //Loop through the selected items
            foreach($_POST['selected'] as $itemID){
                echo $itemID.'<br>';                
                                
                //get the items price
                $get_price = mysql_query("SELECT item_sellback FROM Items WHERE item_id='$itemID'")
                or die(mysql_error());
                $price = mysql_fetch_assoc($get_price);
                
         
//THIS IS WHERE THE MYSQL QUERY TO SELL THE ITEM SHOULD GO.

$sql = "SELECT item_name, item_quanity FROM Items WHERE item_name='$itemID";
   $result = dbQuery($sql);
if (dbNumRows($result) != 1) {
      // the product doesn't exist
      header('Location: store.php');
        // how many of this product we
      // have in stock
      $row = dbFetchAssoc($result);
      $currentStock = $row['item_quanity'];

if ($currentStock == 0) {
         // we no longer have this product in stock
         // show the error message
echo "Item Not In Stock<br>";
         exit;
      }
   } 


   if (dbNumRows($result) == 0) {

      // put the product in invetory
      $sql = "INSERT INTO user_item (item_name, item_owner_id,item_quanity)
VALUES ('$itemID', '$user_id'";        
    $result = dbQuery($sql);
   } else {
      // update product quantity in cart table
      $sql = "UPDATE user_item SET item_quanity = item_quanity + 1
              WHERE user_owner_id = '$user_id' AND item_name = $itemID";

      $result = dbQuery($sql); 
}
echo "Item Sold<br>";
                     
                    //they have enough, now do a query to add their money
                    $add = "UPDATE phpbb_users SET user_points=user_points-{$price['item_price']}
                    WHERE user_id='$user_id'";
                    $add_final = mysql_query($add)or die('ERROR: '.mysql_error().' with query<br>'.$add);
}
        }
    }
    
    $result = mysql_query("SELECT 
  i.item_name,i.item_desc,i.item_picture,i.item_sellback
  FROM 
    Items i 
  JOIN 
    user_item u 
    ON 
      i.item_id=u.item_id 
  WHERE 
    u.user_owner_id = $user_id; ")or die(mysql_error());
    $num_rows = mysql_num_rows($result);
    
    print "<br>There are $num_rows cards in your inventory <P>";
    print "<form action='{$_SERVER['PHP_SELF']}' method='post'>";
    print '<table width=500 height= 100  border=1>'."\n";
    
    while ($get_info = mysql_fetch_assoc($result)) {
        print "<tr>";
        
        print "\t<td><font face=arial size=1/>";
        
        print "<input type='checkbox' name='selected[]' value='{$get_info['item_name']}' />"
        .$get_info['item_name'].'<br><td><center>'.$get_info['item_price'].'<center>'.Boomies.'<br><td><center>Sell Back Price:<center>'.$get_info['item_sellback'].'<br><td><center>'.$get_info['item_desc']."</font></td>";
        print "</tr>";
    }
    
    echo '<select name="action">'
    .'<option value="sell">sell</option>'
    .'</select><p>';
    echo '<input type="submit" name="submit">';
    echo '</form>';
?>

revraz · December 3, 2007

Missing ending ' quote

$sql = "SELECT item_name, item_quanity FROM Items WHERE item_name='$itemID'"; <--

And here

$sql = "UPDATE user_item SET item_quanity = item_quanity + 1

WHERE user_owner_id = '$user_id' AND item_name = $itemID";

And a ) here

$sql = "INSERT INTO user_item (item_name, item_owner_id,item_quanity)

VALUES ('$itemID', '$user_id'";

stuffradio · December 3, 2007

I'm not sure which query it was referring to... but there is a spelling error:

$sql = "SELECT item_name, item_quanity FROM Items WHERE item_name='$itemID";

change to

$sql = "SELECT item_name, item_quantity FROM Items WHERE item_name='$itemID";

revraz · December 3, 2007

Looks like they all say that, so chances are he named the DB Column that way too.

I'm not sure which query it was referring to... but there is a spelling error:

$sql = "SELECT item_name, item_quanity FROM Items WHERE item_name='$itemID";

change to

$sql = "SELECT item_name, item_quantity FROM Items WHERE item_name='$itemID";

Deanznet · December 3, 2007

Ya i did spell it wrong but ill worry about that later

Now thos code gives me these 2 errors and wont do the other querys

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/content/K/i/c/KickGame/html/tradeadmin.php on line 135
Product Dosent Exist

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/content/K/i/c/KickGame/html/tradeadmin.php on line 120
Item Not In Stock

<?php
function dbQuery($sql)
{
   return mysql_query($sql) or die('Query failed. ' . mysql_error());
}

function dbAffectedRows()
{
   global $dbConn;

   return mysql_affected_rows($dbConn);
}

function dbFetchArray($result, $resultType = MYSQL_NUM)
{
   return mysql_fetch_array($result, $resultType);
}

function dbFetchAssoc($result)
{
   return mysql_fetch_assoc($result);
}

function dbFetchRow($result)
{
   return mysql_fetch_row($result);
}

function dbFreeResult($result)
{
   return mysql_free_result($result);
}

function dbNumRows($result)
{
   return mysql_num_rows($result);
}


//select the users money
$query = mysql_query("SELECT user_points FROM phpbb_users WHERE user_id='$user_id'")or die(mysql_error());
$user_points = mysql_fetch_assoc($query);
    
    
    //check if they submitted the form
    if (isset($_POST['submit'])) {
        
        if ($_POST['action'] == 'sell') {
            
            echo "<br>";
            
            //Loop through the selected items
            foreach($_POST['selected'] as $itemID){
                echo $itemID.'<br>';                
                                
                //get the items price
                $get_price = mysql_query("SELECT item_sellback FROM Items WHERE item_id='$itemID'")
                or die(mysql_error());
                $price = mysql_fetch_assoc($get_price);
                
         
//THIS IS WHERE THE MYSQL QUERY TO SELL THE ITEM SHOULD GO.

   $sql = "SELECT item_name, item_quanity
           FROM Items
           WHERE item_name='$itemID'";
   $result = dbQuery($sql);
if (dbNumRows($result) != 1) {
      // the product doesn't exist
echo "Product Dosent Exist<br>";
        // how many of this product we
      // have in stock
      $row = dbFetchAssoc($result);
      $currentStock = $row['item_quanity'];

if ($currentStock == 0) {
         // we no longer have this product in stock
         // show the error message
echo "Item Not In Stock<br>";
         exit;
      }
   } 


   if (dbNumRows($result) == 0) {

      // put the product in cart table
      $sql = "INSERT INTO user_item (item_name, item_owner_id)
VALUES ('$itemID', '$user_id'";        
    $result = dbQuery($sql);
   } else {
      // update product quantity in cart table
      $sql = "UPDATE user_item SET item_quanity = item_quanity + 1
              WHERE user_owner_id = '$user_id' AND item_name = $itemID";

      $result = dbQuery($sql); 
}
echo "Item Sold<br>";
                     
                    //they have enough, now do a query to add their money
                    $add = "UPDATE phpbb_users SET user_points=user_points-{$price['item_price']}
                    WHERE user_id='$user_id'";
                    $add_final = mysql_query($add)or die('ERROR: '.mysql_error().' with query<br>'.$add);
}
        }
    }
    
    $result = mysql_query("SELECT 
  i.item_name,i.item_desc,i.item_picture,i.item_sellback
  FROM 
    Items i 
  JOIN 
    user_item u 
    ON 
      i.item_id=u.item_id 
  WHERE 
    u.user_owner_id = $user_id; ")or die(mysql_error());
    $num_rows = mysql_num_rows($result);
    
    print "<br>There are $num_rows cards in your inventory <P>";
    print "<form action='{$_SERVER['PHP_SELF']}' method='post'>";
    print '<table width=500 height= 100  border=1>'."\n";
    
    while ($get_info = mysql_fetch_assoc($result)) {
        print "<tr>";
        
        print "\t<td><font face=arial size=1/>";
        
        print "<input type='checkbox' name='selected[]' value='{$get_info['item_name']}' />"
        .$get_info['item_name'].'<br><td><center>'.$get_info['item_price'].'<center>'.Boomies.'<br><td><center>Sell Back Price:<center>'.$get_info['item_sellback'].'<br><td><center>'.$get_info['item_desc']."</font></td>";
        print "</tr>";
    }
    
    echo '<select name="action">'
    .'<option value="sell">sell</option>'
    .'</select><p>';
    echo '<input type="submit" name="submit">';
    echo '</form>';
?>

Deanznet · December 3, 2007

Anyone

peranha · December 3, 2007

which lines are 135 and 120.

Deanznet · December 3, 2007

Line 135 return mysql_num_rows($result);

Line 120 return mysql_fetch_assoc($result);

paradigmapc · December 3, 2007

try putting your variables in your sql statements in between { }

you had

"SELECT user_points FROM phpbb_users WHERE user_id='$user_id'"

try

"SELECT user_points FROM phpbb_users WHERE user_id='{$user_id}'"

I don't think variables are printed between single quotes.

Deanznet · December 3, 2007

Still Dosent Work >:(

mmarif4u · December 3, 2007

$result = mysql_query("SELECT 
  i.item_name,i.item_desc,i.item_picture,i.item_sellback
  FROM 
    Items i 
  JOIN 
    user_item u 
    ON 
      i.item_id=u.item_id 
  WHERE 
    u.user_owner_id = $user_id; ")or die(mysql_error());

change to:

$result = mysql_query("SELECT 
  i.item_name,i.item_desc,i.item_picture,i.item_sellback
  FROM 
    Items i 
  JOIN 
    user_item u 
    ON 
      i.item_id=u.item_id 
  WHERE 
    u.user_owner_id = '$user_id'")or die(mysql_error());

Deanznet · December 3, 2007

Still dosent work.. It has something to do with this part i can tell you that.

Got ride of some functions i dont need..

<?php
function dbQuery($sql)
{
   return mysql_query($sql) or die('Query failed. ' . mysql_error());
}



function dbFetchAssoc($result)
{
   return mysql_fetch_assoc($result);
}



function dbNumRows($result)
{
   return mysql_num_rows($result);
}


//select the users money
$query = mysql_query("SELECT user_points FROM phpbb_users WHERE user_id='$user_id'")or die(mysql_error());
$user_points = mysql_fetch_assoc($query);
    
    
    //check if they submitted the form
    if (isset($_POST['submit'])) {
        
        if ($_POST['action'] == 'sell') {
            
            echo "<br>";
            
            //Loop through the selected items
            foreach($_POST['selected'] as $itemID){
                echo $itemID.'<br>';                
                                
                //get the items price
                $get_price = mysql_query("SELECT item_sellback FROM Items WHERE item_id='$itemID'")
                or die(mysql_error());
                $price = mysql_fetch_assoc($get_price);
                
         
//THIS IS WHERE THE MYSQL QUERY TO SELL THE ITEM SHOULD GO.

   $sql = "SELECT item_name, item_quanity FROM Items WHERE item_name='{$itemID}'";
   $result = dbQuery($sql);
if (dbNumRows($result) != 1) {
      // the product doesn't exist
echo "Product Dosent Exist<br>";
        // how many of this product we
      // have in stock
      $row = dbFetchAssoc($result);
      $currentStock = $row['item_quanity'];

if ($currentStock == 0) {
         // we no longer have this product in stock
         // show the error message
echo "Item Not In Stock<br>";
         exit;
      }
   } 


   if (dbNumRows($result) == 0) {

      // put the product in cart table
      $sql = "INSERT INTO user_item (item_name, item_owner_id)
VALUES ('$itemID', '$user_id'";        
    $result = dbQuery($sql);
   } else {
      // update product quantity in cart table
      $sql = "UPDATE user_item SET item_quanity = item_quanity + 1
              WHERE user_owner_id = '$user_id' AND item_name = $itemID";

      $result = dbQuery($sql); 
}
echo "Item Sold<br>";
                     
                    //they have enough, now do a query to add their money
                    $add = "UPDATE phpbb_users SET user_points=user_points-{$price['item_price']}
                    WHERE user_id='$user_id'";
                    $add_final = mysql_query($add)or die('ERROR: '.mysql_error().' with query<br>'.$add);

The error is telling me that my function are wrong

function dbFetchAssoc($result)
{
   return mysql_fetch_assoc($result);
}



function dbNumRows($result)
{
   return mysql_num_rows($result);
}

Deanznet · December 3, 2007

Got it working

Sign In

[SOLVED] Query failed. You have an error in your SQL syntax.

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information