Syntax error or access violation: 1064 You have an error in your SQL syntax in alter table

Question

I am trying to alter the auto_increment by laravel stament function and it is giving the error.

The complete error message displaying is 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 '?' at line 1 (SQL: alter table bills AUTO_INCREMENT=9)

if($quantity[$i]>$qtny)
{
    Bills::where('id','=',$invoice_no)->delete();
    DB::enableQueryLog();
        DB::statement('alter table `bills` AUTO_INCREMENT=?',array('invoice_no'=>$invoice_no));
    dd(DB::getQueryLog());
    //return "<script>alert('Quantity is not available for product ".$item_name[$i]."');location.href='/create';</script>";
}

Answer 1

Variable placeholders are not allowed everywhere:

Within the statement, ? characters can be used as parameter markers to indicate where data values are to be bound to the query later when you execute it.

alter table requires a constant here, e.g. you could not use something like alter table bills AUTO_INCREMENT = (select max(id) from bills). Generally, everywhere you could do this, you are allowed to use placeholders (with some exceptions like limit, but those are then mentioned in the documentation).

So in this case you have to use the value directly:

DB::statement('alter table `bills` AUTO_INCREMENT = '.$invoice_no)

Answer 2

Variable Placeholder is not allowed here. So. I have done by this way,

DB::statement('alter table `bills` AUTO_INCREMENT = '.$invoice_no);

Answer 3

You can write the laravel query in two diffrent way you can tokenize variable

1) First method

if($quantity[$i]>$qtny)
{
    Bills::where('id','=',$invoice_no)->delete();
    DB::enableQueryLog();
        DB::statement('alter table `bills` AUTO_INCREMENT=:invoice_no',array('invoice_no'=>$invoice_no));
    dd(DB::getQueryLog());
    //return "<script>alert('Quantity is not available for product ".$item_name[$i]."');location.href='/create';</script>";
}

2) Second method

if($quantity[$i]>$qtny)
{
    Bills::where('id','=',$invoice_no)->delete();
    DB::enableQueryLog();
        DB::statement('alter table `bills` AUTO_INCREMENT=?',[$invoice_no]);
    dd(DB::getQueryLog());
    //return "<script>alert('Quantity is not available for product ".$item_name[$i]."');location.href='/create';</script>";
}

You can also write query below but your query will be wide open for sql injection.

DB::statement('alter tablebillsAUTO_INCREMENT = '.$invoice_no);