6 Replies - 2105 Views - Last Post: 16 June 2013 - 07:01 PM

#1 qtheninja  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 24-May 13

Not understanding syntax error: 1064 looked it over and seems fine

Posted 15 June 2013 - 07:46 PM

I may not be understanding how to do this but I am receiving a syntax error that makes no sense and I look it over and cannot find anything. As I am trying to learn code I may not be putting everything in best practice so you'll have to excuse me. At the very bottom I am providing the full code.

I am receiving "Database Error: unable to create record.1064You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'pccharacters.id = '4'' at line 4"

When it is not even close to line 4. It is also a second query. The goal is to transfer the character id to a many-to-many database once the character (and it's respective id) is created. I will be replacing the hard numbers in the mysql query to mysqli_insert_id($mysqli) as I believe that is how to pull the auto increment id.

Let me know if there is any further clarification needed.

$query1 = "INSERT playerskill
					SET
					skillid = '3'
					charid = '4'



My questions (are a few) but the main one is why am I receivign a syntax error? Could it be how I inner joined them?


$action = isset( $_POST['action'] ) ? $_POST['action'] : "";
if($action == "create"){
	//write the query for updating
	$query = "insert pccharacters
				set
					name = '".($_POST['name'])."',
					clan = '".$mysqli->real_escape_string($_POST['clan'])."',
					school = '".$mysqli->real_escape_string($_POST['school'])."',
					family = '".$mysqli->real_escape_string($_POST['family'])."',
					history = '".$mysqli->real_escape_string($_POST['history'])."',
					spells = '".$mysqli->real_escape_string($_POST['spells'])."',
					stamina = ".$mysqli->real_escape_string($_POST['stamina']).",
					earth = ".$mysqli->real_escape_string($_POST['earth']).",
					willpower = ".$mysqli->real_escape_string($_POST['willpower']).",
					Water = ".$mysqli->real_escape_string($_POST['water']).",
					strength = ".$mysqli->real_escape_string($_POST['strength']).",
					perception = ".$mysqli->real_escape_string($_POST['perception']).",
					air = ".$mysqli->real_escape_string($_POST['air']).",
					reflexes = ".$mysqli->real_escape_string($_POST['reflexes']).",
					awareness = ".$mysqli->real_escape_string($_POST['awareness']).",
					agility = ".$mysqli->real_escape_string($_POST['agility']).",
					void = ".$mysqli->real_escape_string($_POST['void']).",fire = ".$mysqli->real_escape_string($_POST['fire']).",
					intelligence = ".$mysqli->real_escape_string($_POST['intelligence']).",
honor = '".$mysqli->real_escape_string($_POST['honor'])."',
					hdecimals = '".$mysqli->real_escape_string($_POST['hdecimals'])."',
					glory = '".$mysqli->real_escape_string($_POST['glory'])."',
					gdecimals = '".$mysqli->real_escape_string($_POST['gdecimals'])."',
					koku = '".$mysqli->real_escape_string($_POST['koku'])."',
					bu = '".$mysqli->real_escape_string($_POST['bu'])."',
					zeni = '".$mysqli->real_escape_string($_POST['zeni'])."',
					gmapproved = '".($_POST['gmapproved'])."'
					";
					
					$query1 = "INSERT playerskill
					SET
					skillid = '3'
					charid = '4'
					";
					
//what happens when successful or not
	if( $mysqli->query($query) ) {
		echo $query ;
		echo "Character was approved.";
	}else{
		echo "Database Error: Unable to update record.";
		echo $mysqli->errno;
		echo mysqli_error($mysqli);
	}
}
if( $mysqli->query($query1)){
	echo $query1;
	echo "Skills were added.";
}else{
	echo "Database Error: unable to create record.";
	echo $mysqli->errno;
	echo mysqli_error($mysqli);
}
//second putting in the skills
$query1 = "select skid
			from skills";
$skillid = $row['skid'];

//regular query
$query = "select *
			from pccharacters
			where id='".$mysqli->real_escape_string($_REQUEST['id'])."'"
			;

$result = $mysqli->query( $query );
$row = $result->fetch_assoc();

$id = $row['id'];
$name = $row['name'];
$clan = $row['clan'];
$family = $row['family'];
$school = $row['school'];
$earth = $row['earth'];
$stamina = $row['stamina'];
$willpower = $row['willpower'];
$water = $row['Water'];
$strength = $row['strength'];
$perception = $row['perception'];
$air = $row['air'];
$reflexes = $row['reflexes'];
$awareness = $row['awareness'];
$fire = $row['fire'];
$intelligence = $row['intelligence'];
$agility = $row['agility'];
$void = $row['void'];
$honor = $row['honor'];
$hdecimals = $row['hdecimals'];
$glory = $row['glory'];
$gdecimals = $row['gdecimals'];
$koku = $row['koku'];
$bu = $row['bu'];
$zeni = $row['zeni'];
$history = $row['history'];
$spells = $row['spells'];
$gmapproved = $row['gmapproved'];

?>
<table width="700" border="1">
<form method="post" action="#">

  <tr>
    <td>Name </td>
    <td><label>
 
      <input name="name" type="text" id="name" />
    </label></td>
  </tr>
  <tr>
  	<td>Clan</td>
    <td>
    
    



<?php
$result = $mysqli->query("SELECT name FROM clan");
echo '<SELECT name = clan>';
      while($row1 = $result->fetch_assoc()) {
      $clan = $row1['name'];
echo "<option value=".$clan.">" . $clan .  "</option>";   
      }
      echo '</select>';
?>
 
    </td>
        </tr>
  <tr>
  	<td>School</td>
    <td>
    <label>
    <input name="school" type="text" id="school"/>
    </label>
    </td>
  </tr>
       <tr>
       	<td>Family</td>
        <td>
      <?php
$result = $mysqli->query("SELECT name FROM family");
echo '<SELECT name=family>';
      while($row1 = $result->fetch_assoc()) {
      $family = $row1['name'];
echo "<option value=".$family.">" . $family.  "</option>";   
      }
      echo '</select>';
?>
       </tr>
       
       <tr>
       	<td>
        XP
        </td>
       </tr>
       
       <tr>
       <td>
       Insight
       </td>
       </tr>
       
       <tr>
       <td>
       Earth
       </td>
       <td>
         <label>
       	
        <select name="earth">
<option value="1">1</option>
<option selected="selected" value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
        </label>
        </td>
        </tr>
        
        <tr>
        <td>
        Stamina
        </td>
        <td>
             <label>
       	
        <select name="stamina">
<option value="1">1</option>
<option selected="selected" value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
        </label>
        </td>
        </tr>
        
        <tr>
        <td>
        Willpower</td>
        <td>
       
              <label>
       	
        <select name="willpower">
<option value="1">1</option>
<option selected="selected" value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
        </label>
        </td>
        </tr>
        
         <tr>
    <td>Water </td>
    <td>
            <label>
       	
        <select name="water">
<option value="1">1</option>
<option selected="selected" value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
        </label>
        </td>
  </tr>
       
		<tr>
        <td>
        Strength
        </td>
        <td>
              <label>
       	
        <select name="strength">
<option value="1">1</option>
<option selected="selected" value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
        </label>
        </td>
        </tr>
        
        <tr>
        <td>
        Perception
        </td>
        <td>
              <label>
       	
        <select name="perception">
<option value="1">1</option>
<option selected="selected" value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
        </label>
        </td>
        </tr>
     
        <tr>
        <td>
        Air
        </td>
        <td>
              <label>
       	
        <select name="air">
<option value="1">1</option>
<option selected="selected" value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
        </label>
        </td>
        </tr>
        
        <tr>
        <td>
        Reflexes
        </td>
        <td>
              <label>
       	
        <select name="reflexes">
<option value="1">1</option>
<option selected="selected" value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
        </label>
        </td>
        </tr>
        
        <tr>

        <td>
        Awareness
        </td>
        <td>
              <label>
       	
        <select name="awareness">
<option value="1">1</option>
<option selected="selected" value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
        </label>
        </td>
        </tr>
        

      
        

       <tr>
    <td>Fire </td>
    <td>
            <label>
       	
        <select name="fire">
<option value="1">1</option>
<option selected="selected" value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
        </label>
        </td>
  </tr>
   	
      <tr>
        <td>
        Intelligence
        </td>
        <td>
              <label>
       	
        <select name="intelligence">
<option value="1">1</option>
<option selected="selected" value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
        </label>
        </td>
        </tr>
        
        <tr>
        <td>
        Agility
        </td>
        <td>
              <label>
       	
        <select name="agility">
<option value="1">1</option>
<option selected="selected" value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
        </label>
        </td>
        </tr>
        
        <tr>
        <td>
        Void
        </td>
        <td>
              <label>
       	
        <select name="void">
<option value="1">1</option>
<option selected="selected" value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
        </label>
        </td>
        </tr>
        
        <tr>
        <td>
        Skills
        </td>
        <option value="5">5</option>
    <td>
     <?php
$result = $mysqli->query("SELECT name, cost FROM skills");
echo '<SELECT name=skills>';
      while($row1 = $result->fetch_assoc()) {
      $skills = $row1['name'];
	  $skillid = $row1['skid'];
	  echo "<option value=".$skillid.">". $skills . "</option>";   
      }
      echo '</select>';
?>   
</td>
<td>
<label>
<select name="">
<option value="0">0</option>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
</select>
</label>
</td>  

   <td>
     <?php
$result = $mysqli->query("SELECT name, cost FROM skills");
echo '<SELECT name=skills>';
      while($row1 = $result->fetch_assoc()) {
      $skills = $row1['name'];
	  echo "<option value=".$skills.">". $skills . "</option>";   
      }
      echo '</select>';
?>   
</td>
<td>
<label>
<select name="">
<option value="0">0</option>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
</select>
</label>
</td>  
</tr>
<tr>
        <td>
        Skills
        </td>
        
    <td>
     <?php
$result = $mysqli->query("SELECT name, cost FROM skills");
echo '<SELECT name=skills>';
      while($row1 = $result->fetch_assoc()) {
      $skills = $row1['name'];
	  echo "<option value=".$skills.">". $skills . "</option>";   
      }
      echo '</select>';
?>   
</td>
<td>
<label>
<select name="">
<option value="0">0</option>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
</select>
</label>
</td>  

   <td>
     <?php
$result = $mysqli->query("SELECT name, cost FROM skills");
echo '<SELECT name=skills>';
      while($row1 = $result->fetch_assoc()) {
      $skills = $row1['name'];
	  echo "<option value=".$skills.">". $skills . "</option>";   
      }
      echo '</select>';
?>   
</td>
<td>
<label>
<select name="">
<option value="0">0</option>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
</select>
</label>
</td>  
</tr>
<tr>
        <td>
        Skills
        </td>
        
    <td>
     <?php
$result = $mysqli->query("SELECT name, cost FROM skills");
echo '<SELECT name=skills>';
      while($row1 = $result->fetch_assoc()) {
      $skills = $row1['name'];
	  echo "<option value=".$skills.">". $skills . "</option>";   
      }
      echo '</select>';
?>   
</td>
<td>
<label>
<select name="">
<option value="0">0</option>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
</select>
</label>
</td>  

   <td>
     <?php
$result = $mysqli->query("SELECT name, cost FROM skills");
echo '<SELECT name=skills>';
      while($row1 = $result->fetch_assoc()) {
      $skills = $row1['name'];
	  echo "<option value=".$skills.">". $skills . "</option>";   
      }
      echo '</select>';
?>   
</td>
<td>
<label>
<select name="">
<option value="0">0</option>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
</select>
</label>
</td>  
</tr>
<tr>
        <td>
        Skills
        </td>
        
    <td>
     <?php
$result = $mysqli->query("SELECT name, cost FROM skills");
echo '<SELECT name=skills>';
      while($row1 = $result->fetch_assoc()) {
      $skills = $row1['name'];
	  echo "<option value=".$skills.">". $skills . "</option>";   
      }
      echo '</select>';
?>   
</td>
<td>
<label>
<select name="">
<option value="0">0</option>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
</select>
</label>
</td>  

   <td>
     <?php
$result = $mysqli->query("SELECT name, cost FROM skills");
echo '<SELECT name=skills>';
      while($row1 = $result->fetch_assoc()) {
      $skills = $row1['name'];
	  echo "<option value=".$skills.">". $skills . "</option>";   
      }
      echo '</select>';
?>   
</td>
<td>
<label>
<select name="">
<option value="0">0</option>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
</select>
</label>
</td>  
</tr>
<tr>
        <td>
        Skills
        </td>
        
    <td>
     <?php
$result = $mysqli->query("SELECT name, cost FROM skills");
echo '<SELECT name=skills>';
      while($row1 = $result->fetch_assoc()) {
      $skills = $row1['name'];
	  echo "<option value=".$skills.">". $skills . "</option>";   
      }
      echo '</select>';
?>   
</td>
<td>
<label>
<select name="">
<option value="0">0</option>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
</select>
</label>
</td>  

   <td>
     <?php
$result = $mysqli->query("SELECT name, cost FROM skills");
echo '<SELECT name=skills>';
      while($row1 = $result->fetch_assoc()) {
      $skills = $row1['name'];
	  echo "<option value=".$skills.">". $skills . "</option>";   
      }
      echo '</select>';
?>   
</td>
<td>
<label>
<select name="">
<option value="0">0</option>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
</select>
</label>
</td>  
</tr>
        <tr>
        
        <td>
        Honor
       	</td>
        <td>
             <label>
       	
        <select name="honor">
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
<option value="6">6</option>
<option value="7">7</option>
<option value="8">8</option>
<option value="9">9</option>
<option value="10">10</option>
        </label>
        </td>
        <td>
              <label>
       	
        <select name="hdecimals">
<option value="0">0</option>        
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
<option value="6">6</option>
<option value="7">7</option>
<option value="8">8</option>
<option value="9">9</option>

        </label>
        </td>
        </tr>
        
        <tr>
        <td>
        Glory
        </td>
        <td>
       <label>
       	
        <select name="glory">
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
<option value="6">6</option>
<option value="7">7</option>
<option value="8">8</option>
<option value="9">9</option>
<option value="10">10</option>
        </label>
        </td>
        <td>
              <label>
       	
        <select name="gdecimals">
<option value="0">0</option>        
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
<option value="6">6</option>
<option value="7">7</option>
<option value="8">8</option>
<option value="9">9</option>

        </label>
        </td>
        </tr>
        
        <tr>
        <td>
        Status
        </td>
        </tr>
        
        <tr>
        <td>
        Taint
        </td>
        </tr>
        
        <tr>
        <td>
        Inititiative
        </td>
        </tr>
        
        <tr>
        <td>
        Wounds
        </td>
        </tr>
        
        <tr>
        <td>
        Wound levels
        </td>
        </tr>
        
        <tr>
        <td>
        Armor TN
        </td>
        </tr>
        
        <tr>
        <td>
        Armor
        </td>
        </tr>
        
        <tr>
        <td>
        Money
        </td>
        <td>Koku
            <label><input name="koku" type="text" id="bu"/></label></td>
        <td>Bu
           	<label><input name="bu" type="text" id="bu"/></label></td>
        <td>Zeni
            <label><input name="zeni" type="text" id="zeni"/></label></td>
       
    <tr>
    <td>
    Equipment
    </td>
    <td>
    <label>
    <input name="equipment" type="text" id="equipment"/>
    </label>
  	</td>
  	</tr>
  
		  <tr>
    <td>Advantages</td>
    <td>
     <?php
$result = $mysqli->query("SELECT name, cost FROM advantages");
echo '<SELECT MULTIPLE name=advantages>';
      while($row1 = $result->fetch_assoc()) {
      $advantages = $row1['name'];
	  $advantagescost = $row1['cost'];
echo "<option value=".$advantages.">". '-'. ' ' . $advantagescost . ' ' . $advantages  . "</option>";   
      }
      echo '</select>';
?>
        </td>
  </tr>  
    
    	  <tr>
    <td>Disadvantages</td>
    <td>
      <?php
$result = $mysqli->query("SELECT name,cost FROM disadvantages");
echo '<SELECT MULTIPLE name=disadvantages>';
      while($row1 = $result->fetch_assoc()) {
      $disadvantages = $row1['name'];
	  $disadvantagescost = $row1['cost'];
echo "<option value=".$disadvantages.">". '+' . ' ' . $disadvantagescost . ' ' . $disadvantages .  "</option>";   
      }
      echo '</select>';
?>
        </td>
  </tr>
   
  <tr>
    <td>Spells</td>
    <td>
    
    
      <label>
        <textarea rows="4" cols="50" input name="spells" type="text" id="spells" />
        
        </textarea>
        </label>
        </td>
  </tr>
 
  <tr>
  	<td>School Techniques</td>
    <td>
    <label>
    	<input name="schooltechniques" type="text" id="schooltechniques" />
        </label>
        </td>
        </tr>

  <tr>
    <td>History </td>
    <td>
   
    <p>
      <label>
      
      
        <textarea rows="10" cols="50" id="history" type="textarea" name="history"/>
        </textarea>
     </label>
     </p>
   
      </td>
  </tr>
 
 <input type="hidden" name="gmapproved" id="0" />
  <tr>
    <td>
      <label>
      <input type="submit" name="action" value="create" />
        </label>
    </form>    </td>
  </tr>
</table>



Is This A Good Question/Topic? 0
  • +

Replies To: Not understanding syntax error: 1064 looked it over and seems fine

#2 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3193
  • View blog
  • Posts: 10,696
  • Joined: 12-December 12

Re: Not understanding syntax error: 1064 looked it over and seems fine

Posted 15 June 2013 - 07:57 PM

You are confusing INSERT and UPDATE sql-statements.

This post has been edited by andrewsw: 15 June 2013 - 07:58 PM

Was This Post Helpful? 0
  • +
  • -

#3 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3193
  • View blog
  • Posts: 10,696
  • Joined: 12-December 12

Re: Not understanding syntax error: 1064 looked it over and seems fine

Posted 15 June 2013 - 08:03 PM

BTW If you perform all your real-escape processing separately, before creating the sql-statements, it will make them much easier to read. I would look into using array_map to do this on the $_POST array in one go if possible.

However, you should use prepared statements.

This post has been edited by andrewsw: 15 June 2013 - 08:06 PM

Was This Post Helpful? 0
  • +
  • -

#4 qtheninja  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 24-May 13

Re: Not understanding syntax error: 1064 looked it over and seems fine

Posted 15 June 2013 - 11:49 PM

Thanks for the tip and mixing the update/insert would explain it. Now it is pulling up only a '0' so I'll have to see how to fix that. I appreciate pointing out the issue and the tip on real escape.
Was This Post Helpful? 0
  • +
  • -

#5 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3480
  • View blog
  • Posts: 10,033
  • Joined: 08-June 10

Re: Not understanding syntax error: 1064 looked it over and seems fine

Posted 16 June 2013 - 12:34 AM

View Postqtheninja, on 16 June 2013 - 04:46 AM, said:

$query1 = "INSERT playerskill
					SET
					skillid = '3'
					charid = '4'


this is valid syntax, except that there is missing a comma.
Was This Post Helpful? 2
  • +
  • -

#6 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3193
  • View blog
  • Posts: 10,696
  • Joined: 12-December 12

Re: Not understanding syntax error: 1064 looked it over and seems fine

Posted 16 June 2013 - 03:57 AM

My apologies. Yes INSERT..SET is valid, I just hadn't encountered it. The comment "write the query for updating" perhaps also threw me :)
Was This Post Helpful? 0
  • +
  • -

#7 qtheninja  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 24-May 13

Re: Not understanding syntax error: 1064 looked it over and seems fine

Posted 16 June 2013 - 07:01 PM

That was my bad I started with a page that was for editing the information first and so I left the comment as it was because I wasn't thinking. Cardinal rule broken. I did change it to something closer to an array as the set I've read can be picky??
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1