Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Regular Coder
    Join Date
    Nov 2011
    Posts
    250
    Thanks
    8
    Thanked 4 Times in 4 Posts

    SQL syntax error help

    hello, i'm relatively new to php and mysql. i have some code that is displaying the below error.

    Could not insert data because 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 ''name', 'Weapons'' at line 1
    the code that is giving this error is below

    <?php
    include("config.php");

    // connect to the mysql server
    $link = mysql_connect($server, $db_user, $db_pass)
    or die ("Could not connect to mysql because ".mysql_error());

    // select the database
    mysql_select_db($database)
    or die ("Could not select database because ".mysql_error());

    $insert = mysql_query("insert into $table values ('NULL', '".$_POST['email']."', '".$_POST['password']."', '".$_POST['name']."', '".$_POST['Gender']."', '".$_POST['Age']."', '".$_POST['Race']."', '".$_POST['SpecialtyOne']."', '".$_POST['Feet']."', '".$_POST['Weight']."', '".$_POST['Eyes']."', '".$_POST['Hair']."', '".$_POST['Strength']."', '".$_POST['Stamina']."', '".$_POST['Perception']."', '".$_POST['Agility']."', '".$_POST['Intelligence']."', '".$_POST['Tech']."', '".$_POST['Luck']."', '".$_POST['Appearance']."', '".$_POST['Background']."', '".$_POST['Weapons']."', '".$_POST['Armour']."', '".$_POST['Items']."', '".$_POST['Enhancements']."', '".$_POST['Skills']."')ON DUPLICATE KEY UPDATE '".$_POST['name']."', '".$_POST['Weapons']."'")
    or die("Could not insert data because ".mysql_error());

    ?>

  • #2
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,347
    Thanks
    23
    Thanked 618 Times in 617 Posts
    Save yourself a headache and turn those $_POSTs into strings before doing the query.
    $email = $_POST['email'];

    saves all that . and ' and " and it becomes readable.

  • #3
    Regular Coder
    Join Date
    Nov 2011
    Posts
    250
    Thanks
    8
    Thanked 4 Times in 4 Posts
    <?php
    include("config.php");

    // connect to the mysql server
    $link = mysql_connect($server, $db_user, $db_pass)
    or die ("Could not connect to mysql because ".mysql_error());

    // select the database
    mysql_select_db($database)
    or die ("Could not select database because ".mysql_error());

    $insert = mysql_query("insert into $table values ('NULL', $email =$_POST['email']; $password= $_POST['password']; $name= $_POST['name']; $Gender= $_POST['Gender']; $Age= $_POST['Age']; $Race= $_POST['Race']; $SpecialtyOne= $_POST['SpecialtyOne']; $Feet= $_POST['Feet']; $Weight= $_POST['Weight']; $Eyes= $_POST['Eyes']; $Hair= $_POST['Hair']; $Strength= $_POST['Strength']; $Stamina= $_POST['Stamina']; $Perception= $_POST['Perception']; $Agility= $_POST['Agility']; $Intelligence= $_POST['Intelligence']; $Tech= $_POST['Tech']; $Luck= $_POST['Luck']; $Appearance= $_POST['Appearance']; $Background= $_POST['Background']; $Weapons= $_POST['Weapons']; $Armour= $_POST['Armour']; $Items = $_POST['Items']; $Enhancements= $_POST['Enhancements']; $Skills= $_POST['Skills']."')ON DUPLICATE KEY UPDATE ; $name= $_POST['name']; $Weapons= $_POST['Weapons'];'")
    or die("Could not insert data because ".mysql_error());

    ?>
    you mean like that?

  • #4
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,347
    Thanks
    23
    Thanked 618 Times in 617 Posts
    No, like this:
    Code:
    <?php
    
    $email =$_POST['email'];
    $password= $_POST['password'];
    $name= $_POST['name'];
    $Gender= $_POST['Gender'];
    $Age= $_POST['Age'];
    $Race= $_POST['Race'];
    $SpecialtyOne= $_POST['SpecialtyOne'];
    $Feet= $_POST['Feet'];
    $Weight= $_POST['Weight'];
    $Eyes= $_POST['Eyes'];
    $Hair= $_POST['Hair'];
    $Strength= $_POST['Strength'];
    $Stamina= $_POST['Stamina'];
    $Perception= $_POST['Perception'];
    $Agility= $_POST['Agility'];
    $Intelligence= $_POST['Intelligence'];
    $Tech= $_POST['Tech'];
    $Luck= $_POST['Luck'];
    $Appearance= $_POST['Appearance'];
    $Background= $_POST['Background'];
    $Weapons= $_POST['Weapons'];
    $Armour= $_POST['Armour'];
    $Items = $_POST['Items'];
    $Enhancements= $_POST['Enhancements'];
    $Skills= $_POST['Skills'];
    
    $insert = mysql_query("insert into $table values ('NULL', $email, $password, $name, $Gender, $Age, $Race, $SpecialtyOne, $Feet, $Weight, $Eyes, $Hair, $Strength, $Stamina, $Perception, $Agility, $Intelligence, $Tech, $Luck, $Appearance, $Background, $Weapons, $Armour, $Items, $Enhancements, $Skills") or die("Could not insert data because ".mysql_error());
    
    ?>
    I did not put the ON DUPLICATE KEY UPDATE in there because it is not correct and it's the source of your error. You need to put a column = value as the update.

    ON DUPLICATE KEY UPDATE deals with unique index or primary key and I don't think that's what you want.
    Last edited by sunfighter; 11-06-2011 at 08:13 PM.

  • #5
    Regular Coder
    Join Date
    Nov 2011
    Posts
    250
    Thanks
    8
    Thanked 4 Times in 4 Posts
    thanks i appreciate it. as i said i'm very new php and only got to where i was by luck and google. is there any chance i could be cheeky and ask you to put the correct on duplicate key update in? i understand if you dont have the time but thank you once again

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,184
    Thanks
    80
    Thanked 4,451 Times in 4,416 Posts
    So if there *IS* a duplicate, then *WHAT* fields do you want to update????

    And what is your PRIMARY KEY in that table?

    My gut feeling is that you probably don't really want to use ON DUPLICATE KEY. If the use really typed in all of that information, then he/she is probably *not* registered in your system and you are getting a false duplicate.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    Regular Coder
    Join Date
    Nov 2011
    Posts
    250
    Thanks
    8
    Thanked 4 Times in 4 Posts
    that form submits for them to 'buy' something from a 'shop' for their character. it only needs to update Weapons but they need to enter a user name so it knows who to add it to. i'f i'm doing this wrong please feel free to correct me

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,184
    Thanks
    80
    Thanked 4,451 Times in 4,416 Posts
    But that form, according to your code, is requiring them to enter name, email, password, gender, race, etc., etc.

    That makes sense for a registration form, but not for each and every time they come to the "shop" to buy something.

    Heck, it doesn't even make sense that they would enter all the info when they login next week if they register this week.

    You should have the registration process separate from the login process and both of them separate from the buying process.

    And also, once they are logged in, you should be maintaining that login info via one or more $_SESSION values. You shouldn't depend on the validated login info coming from the browser.

    So I can't say for sure you are doing it wrong, but it sure feels that way to me.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #9
    Regular Coder
    Join Date
    Nov 2011
    Posts
    250
    Thanks
    8
    Thanked 4 Times in 4 Posts
    the registration process is seperate, the webpage itself doesnt require you to fill all that information in. i removed it but when i tried to use it i got an incorrect column count error so i put it in the php but not the webpage itself

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,184
    Thanks
    80
    Thanked 4,451 Times in 4,416 Posts
    So then why would you *EVER* want to do an INSERT just because somebody is buying something?????

    The whole premise makes no sense.

    Since you seem to be using only one table in your database (in my opinion, a really rotten design--what happens when you want to add, say, "Charisma" as a buyable property?) then I would assume that once a person is registered you would NEVER want to insert that person's name and other information again. So you should *only* be doing updates.

    If this is an update for a weapon, then just do the update for a weapon and do nothing else.

    And why is the table name a PHP variable??? Please don't tell me you have more than one table with all these same fields sitting around.

    Anyway:
    Code:
    $sql = "UPDATE theOneAndOnlyTable "
         . " SET Weapons = '" . mysql_real_escape_string($_POST["Weapons"]) .  "' " _
         . " WHERE Name = '" . $_SESSION["UserName"] . "'";
    ...
    And never the INSERT should meet, except when registering a new user.

    Curiosity: How do you keep track of multiple weapons? Please don't tell me you have them in a single list in that one field. But I know that's what you are going to say.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #11
    Regular Coder
    Join Date
    Nov 2011
    Posts
    250
    Thanks
    8
    Thanked 4 Times in 4 Posts
    yes i do have a single list in that field, as i said though i am very new to using php with mysql. if i'm doing it wrong tell me, i want to learn and that wont happen if i dont know where i've gone wrong.

    i only have one table, it is a php variable cos thats the code i found to do it and didnt know i needed to change it. once a person is registered then no none of their innformation needs to be entered again just new items/weapons etc

  • #12
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,347
    Thanks
    23
    Thanked 618 Times in 617 Posts

  • #13
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,184
    Thanks
    80
    Thanked 4,451 Times in 4,416 Posts
    Well, here's the DB design I would have, were I you:

    Code:
    CREATE TABLE users (
        userid INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(30),
        name VARCHAR(50),
        gender CHAR(1),  /* M or F */
        age INT,
        email VARCHAR(100)
    );
    
    CREATE TABLE properties (
        propertyID INT AUTO_INCREMENT PRIMARY KEY,
        propertyName VARCHAR( 100 )
    );
    
    -- propertyName would have values such as "Weapon","Strength",etc.
    
    CREATE TABLE userProperties (
        userid INT REFERENCES users(userid),
        propertyID INT REFERENCES properties(propertyID),
        quantity INT,
        power INT
    );
    So you register a user and they get put in the users table.

    And you have a list of the kinds of properties their character can have in the properties table.

    And then, for each property that they gain (or lose) you create or update an entry in the userProperties table.

    You store their userid as a PHP session value, so you don't have to keep passing it around.

    Say they purchase a spear:
    Code:
    $sql = "INSERT INTO userProperties (userid, propertyid) "
         . "VALUES(" . $_SESSION["userid"] . "," . $_POST["propertyid"] . ")";
    In the <form> where they choose what they are purchasing, you can show the propertyName but hide the propertyID (e.g., as a <input type="hidden"> or maybe in a <select> list you have <option value="17">Spear</option>).

    This is called "NORMALIZATION" and, in the long run, it will make the maintenance and querying of the DB a *LOT* easier to do. It may seem like more work, when you are setting it up, but it is more than worth the effort.

    Note that you never have more than one property per record (though the quantity of identical items could be more than one). It's perfectly okay to have, say, 3 spears (that is, you have a record that links a given user to a spear, and you have that record 3 times...though better would be to just change the quantity in a single record...but either will work).
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Foster (11-07-2011)

  • #14
    Regular Coder
    Join Date
    Nov 2011
    Posts
    250
    Thanks
    8
    Thanked 4 Times in 4 Posts
    it's not only that it seems like more work it's confused the hell out of me. i've used php and html before but this is just beyond me

  • #15
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,184
    Thanks
    80
    Thanked 4,451 Times in 4,416 Posts
    Just go read up a bit on "Normalization" as it applies to relational databases.

    Mind you, you don't HAVE to do as I have suggested. But what you have at this point is not so much a database as just the equivalent of a text file where you are stuffing information. Which means that then *ALL* the work of figuring out how to handle adding, changing, deleting, finding information has to be done in your PHP code. You can do that, but it will make the PHP code many times bigger than it needs to be and much more complex. Whereas spending a week or two learning good database design principles will pay off in much better performance, much less coding, and far fewer bugs.

    Here's one place to start:
    http://www.w3schools.com/sql/default.asp

    Mind you, it doesn't teach database *DESIGN*. Just how to maneuver data.

    Master that stuff first then we can talk about design.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •