2

I'm trying to run the following code which is in innoDB server to update value of a table using basic query form. but it's kept showing error.

if(isset($_POST['col_opt']) &&
    isset($_POST['val1']) &&
    isset($_POST['where_opt']) &&
    isset($_POST['val2']) 
){
    $col_name = $_POST['col_opt'];
    $nvalue = $_POST['val1'];
    $cond = $_POST['where_opt'];
    $condval = $_POST['val2'];

    DB::update("UPDATE animes SET ? = ? Where ? = ?",
        [$col_name, $nvalue, $cond, $condval]);

The sql is getting value of all variables. But it still showing error:

SQLSTATE[42000]: Syntax error or access violation: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '? = ? Where ? = ?' at line 1 (SQL: UPDATE animes SET genre_id = 2 Where anm_id = 4)

it's working when column names are manually given.

Shouldn't it also work in case of variables?

Majid Alaeinia
  • 962
  • 2
  • 11
  • 27
Nowshin
  • 81
  • 2
  • 10
  • did you try with `DB::raw` instead `DB::update`? – porloscerros Ψ Jun 15 '19 at 03:54
  • yes, i've tried. No errors are showing .But the database table is not updated. – Nowshin Jun 15 '19 at 04:05
  • See [this](https://stackoverflow.com/a/182353/7644018) – Paul T. Jun 15 '19 at 04:17
  • 2
    @nowshintasnim You cannot substitute columns in DB::update() as it uses parameterized query means query(column names) and the parameter is sent separately and [?, ?] cannot be used for sending column name. But you may use concatenation to generate the query string dynamically `DB::update("UPDATE animes SET ".$col_name." = ? Where ".$cond." = ?",[$nvalue, $condval])` but be sure to sanitize the variables to prevent sql injection – Vinay Jun 15 '19 at 04:41
  • @Viney thnks. it's working. – Nowshin Jun 15 '19 at 06:09
  • @nowshintasnim Why would you use raw sql queries on laravel ? – user7747472 Jun 15 '19 at 10:59
  • @user7747472 it's for an assignment. we were told to use larval and raw sql queries. – Nowshin Jun 15 '19 at 11:23

1 Answers1

0

Try using this query,

DB::update("UPDATE animes SET ? = '%?%' Where ? = '%?%' ",
        [$col_name, $nvalue, $cond, $condval]);

OR

DB::table('animes')
            ->where($cond, $condval)
            ->update([$col_name =>DB::raw($value)]);
Murtaza Bharmal
  • 472
  • 4
  • 16