What's wrong with my MySQL update code?

Question

I updated my code today from this:

$queue = "UPDATE hurlumhei SET barn = $barn, voksenuke = $voksenuke, voksenhelg =     $voksenhelg";

to this

$queue = "UPDATE hurlumhei 
             SET barn = $barn, 
                 voksenuke = $voksenuke, 
                 voksenhelg = $voksenhelg, 
                 klippekort = $klippekort, 
                 klippekortmega = $klippekortmega, 
                 parkering = $parkering, 
                 Kakao = $kakao, 
                 Te = $te,  
                 Kaffe = $kaffe,  
                 Solbærtoddy = $solbærtoddy,  
                 Powerrade = $powerrade,  
                 Brus_stor = $brus_stor,  
                 Brus_medium = $brus_medium,  
                 Brus_liten = $brus_liten,  
                 Bonaqua = $bonaqua,  
                 Iste = $iste,  
                 Sjokolademelk = $sjokolademelk,  
                 Juice = $juice,  
                 Friskus = $friskus,  
                 Slush = $slush,  
                 Pai = $pai,  
                 Calzone = $calzone,  
                 Lasagne = $lasagne,  
                 Buffalo_burger = $buffalo_burger,  
                 Bakt_potet = $bakt_potet,  
                 Pizza = $pizza,  
                 Panini = $panini,  
                 Toast = $toast,  
                 Inngang_pølse1 = $inngang_pølse1,  
                 Inngang_pølse2 = $inngang_pølse2,  
                 Inngang_calzone = $inngang_calzone,  
                 Frukttallerken = $frukttallerken,  
                 Kake = $kake,  
                 Muffins = $muffins,  
                 Popcorn = $popcorn,  
                 Baconchips = $baconchips,  
                 Potetgull = $potetgull,  
                 Baguette_reker = $baguette_reker,  
                 Baguette_kyllingbryst = $baguette_kyllingbryst,  
                 Baguette_ostskinke = $baguette_ostskinke,  
                 Salat_reker = $salat_reker,  
                 Salat_kyllingbryst = $salat_kyllingbryst,  
                 Salat_ostskinke = $salat_ostskinke";

Can anyone help me find the error ? For the record the columns in the database has capital first letters on the new ones, so that's not the error. Welcome to any suggestions, thanks

What error does your database return? Can you echo out the query and run it directly? What happens when you run the query? Does it update everything? Nothing? The wrong row? The right row, with the wrong data? Are you sanitizing your inputs? — andrewsi, Aug 22 '13 at 16:48
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 ' Kakao = 2, Te = 2, Kaffe = 2, Solbærtoddy = 2, Powerrade = 2, Brus_stor = 2, B' at line 1 It doesen't update anything now no — user2580094, Aug 22 '13 at 16:48
All your string variables need to be single-quoted. (also properly escaped) See [this question](http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks) — Michael Berkowski, Aug 22 '13 at 16:50
are your variables all numbers?? or do you have any text?? And are you sure all the variables are wright?? — Tiago, Aug 22 '13 at 16:50
The error will be the field before. Is `parkering` a string, maybe? — andrewsi, Aug 22 '13 at 16:50

Prix · Accepted Answer · 2013-08-22T18:03:51.533

You should consider using MySQLi and prepared statement for this, here is an example:

$con = mysqli_connect($db_host, $db_user, $db_pass, $db_name);
if ($con->connect_error)
    die('Connect Error (' . mysqli_connect_errno() . ') '. mysqli_connect_error());

// Here we prepare your query and make sure it is alright
// for each field we define it as "fieldname = ?"
$sql = "UPDATE hurlumhei 
         SET barn = ?, 
             voksenuke = ?, 
             voksenhelg = ?, 
             klippekort = ?";
if (!$stmt = $con->prepare($sql))
    die('Query failed: (' . $con->errno . ') ' . $con->error);

// Here we define the field types and the variable that will fill it 
// s stands for string, 
// i for integer, 
// d double and
// b for blob
// for each field you have, you will need 1 letter
// on this example I am using 4 of your fields and 
// considering them as strings so we need to have 4's like this 'ssss'
if (!$stmt->bind_param('ssss', $barn, $voksenuke, $voksenhelg, $klippekort))
    die('Binding parameters failed: (' . $stmt->errno . ') ' . $stmt->error);

// Now we finally execute the data to update it to the database
// and if it fails we will know
if (!$stmt->execute())
    die('Execute failed: (' . $stmt->errno . ') ' . $stmt->error);
else
   echo "Yay we updated something...";

As you can see on my above example I define what the variables for each column will be with ? and later on the bind_param I define what the variable is and where it will go into. You can read more about the field types of bind_param here.

You need to properly define the extension of your variables with {} along with placing it inside single quotes to avoid it failing due to spaces and others.

Also you're using column fields like Solbærtoddy I am not sure if MySQL accepts those kinds of letters.

$queue = "UPDATE hurlumhei 
             SET barn = '{$barn}', 
                 voksenuke = '{$voksenuke}', 
                 voksenhelg = '{$voksenhelg}', 
                 klippekort = '{$klippekort}', 
                 klippekortmega = '{$klippekortmega}', 
                 parkering = '{$parkering}', 
                 Kakao = '{$kakao}', 
                 Te = '{$te}', 
                 Kaffe = '{$kaffe}', 
                 Solbærtoddy = '{$solbærtoddy}', 
                 Powerrade = '{$powerrade}', 
                 Brus_stor = '{$brus_stor}', 
                 Brus_medium = '{$brus_medium}', 
                 Brus_liten = '{$brus_liten}', 
                 Bonaqua = '{$bonaqua}', 
                 Iste = '{$iste}', 
                 Sjokolademelk = '{$sjokolademelk}', 
                 Juice = '{$juice}', 
                 Friskus = '{$friskus}', 
                 Slush = '{$slush}', 
                 Pai = '{$pai}', 
                 Calzone = '{$calzone}', 
                 Lasagne = '{$lasagne}', 
                 Buffalo_burger = '{$buffalo_burger}', 
                 Bakt_potet = '{$bakt_potet}', 
                 Pizza = '{$pizza}', 
                 Panini = '{$panini}', 
                 Toast = '{$toast}', 
                 Inngang_pølse1 = '{$inngang_pølse1}', 
                 Inngang_pølse2 = '{$inngang_pølse2}', 
                 Inngang_calzone = '{$inngang_calzone}', 
                 Frukttallerken = '{$frukttallerken}', 
                 Kake = '{$kake}', 
                 Muffins = '{$muffins}', 
                 Popcorn = '{$popcorn}', 
                 Baconchips = '{$baconchips}', 
                 Potetgull = '{$potetgull}', 
                 Baguette_reker = '{$baguette_reker}', 
                 Baguette_kyllingbryst = '{$baguette_kyllingbryst}', 
                 Baguette_ostskinke = '{$baguette_ostskinke}', 
                 Salat_reker = '{$salat_reker}', 
                 Salat_kyllingbryst = '{$salat_kyllingbryst}', 
                 Salat_ostskinke = '{$salat_ostskinke}'";

You should as well use mysql_real_escape_string or prepared statements to avoid SQL Injection.

It was the special letters that was the issue, solved now. Thanks! :) — user2580094, Aug 22 '13 at 17:01

What's wrong with my MySQL update code?

1 Answers1