1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

MYSQL Query

Discussion in 'Databases' started by Filip Boga, Sep 14, 2019.

0
  1. #1
    Hi brothers
    Im newbie in databases.
    im trying to insert one query in database but keeps showing error

    INSERT INTO `Options` (` Name`, `Surname`,` Grade`, `Address`,` City`) VALUES
(‘Adi’, ‘Test’, ‘ 2 ‘, ”, NULL),
(‘Andy’ , ‘User’, ‘ 100500 ‘, ”, NULL);
    Code (markup):
    what i have done wrong?
     
    Last edited by a moderator: Sep 14, 2019
    Filip Boga, Sep 14, 2019 IP
  2. deathshadow

    deathshadow Acclaimed Member

    Messages:
    9,732
    Likes Received:
    1,998
    Best Answers:
    253
    Trophy Points:
    515
    #2
    You're trying to insert two rows at once. It doesn't work that way. That should be two SEPARATE queries.

    Which is where bind/execute in languages like PHP also becomes handy.

    The presence of "styled quotes" probably isn't helping. You've got ‘ and ’ mixed. Really though that's why I avoid doing anything that requires backtick quotes in my queries altogether.

    I also suggest you avoid mixing upper and lower case in your fieldnames / table names. Some backup software can screw that up if you end up on winblows.

    So something like:

    
/* assuming $db is a connected PDO object */

$stmt = $db->prepare('
	INSERT INTO options (
		name, surname, grade, address, city
	) VALUES (
		?, ?, ?, ?, ?
	)
');
$stmt->execute(['Adi', 'Test', 2, '', null]);
$stmt->execute(['Andy', 'User', 100500, '', null]);
    Code (markup):
    Assuming you're working in a language designed to do queries "properly" it should look something like that. It's called -- or was twenty-five years ago -- POEM. Prepare Once, Execute Mostly. There's some new-fangled name for it I can never remember.
     
    Last edited: Sep 14, 2019
    deathshadow, Sep 14, 2019 IP
    sarahk likes this.
  3. sarahk

    sarahk iTamer Staff

    Messages:
    28,500
    Likes Received:
    4,460
    Best Answers:
    123
    Trophy Points:
    665
    #3
    Because you're using MySQL you should be using something like the pdo library and following @deathshadow's advice.

    Technically your query is still valid but it's insecure and therefore you don't do it that way. The most glaring problem I could see was spaces between the ` and the column name.
     
    sarahk, Sep 14, 2019 IP
  4. Filip Boga

    Filip Boga Peon

    Messages:
    2
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #4
    Hi Guys
    Thank you for reply
    This is the structure
     

    Attached Files:

    Filip Boga, Sep 16, 2019 IP
  5. deathshadow

    deathshadow Acclaimed Member

    Messages:
    9,732
    Likes Received:
    1,998
    Best Answers:
    253
    Trophy Points:
    515
    #5
    Not sure what the structure has to do with it. What server side language are you controlling your SQL from?
     
    deathshadow, Sep 16, 2019 IP
  6. sarahk

    sarahk iTamer Staff

    Messages:
    28,500
    Likes Received:
    4,460
    Best Answers:
    123
    Trophy Points:
    665
    #6
    That "key" with "mul" for name and surname... is that a multi column primary key?
    Always have an id for your primary
    name + surname is flawed because
    • you will have people with the same name
    • people change their surname on marriage
    • people change their first names - William becomes Bill, people switch to using their middle names
    I appreciate that concerns about name + surname are based on anglo culture and I know little about Algeria but I am aware of the Muslim tendency to call boys Mohammed something - the spelling seems to have many variations but it increases the probability of duplication.
     
    sarahk, Sep 16, 2019 IP
  7. JEET

    JEET Notable Member

    Messages:
    3,825
    Likes Received:
    502
    Best Answers:
    19
    Trophy Points:
    265
    #7
    Try this please:

    INSERT INTO `Options`
    ( Name, Surname, Grade, Address, City) VALUES
    (‘Adi’, ‘Test’, ‘2‘, '', ''),
    (‘Andy’, ‘User’, ‘100500‘, '', NULL);


    In the query you posted, the column before NULL is a double quote sign. It should be 2 single quotes.
    What is the error you are getting anyways?
     
    JEET, Nov 11, 2019 IP