0

How would I best setup a query statement so that I can insert an autoincremented value that would be concatenated with a hypen.

For example the end result would be 0-01, 0-02, 0-03, .... this would help me when the value reaches 0-99 the next row will be 1-00. Hopefully that makes sense.

Thanks for any input.

Here was my Thought. 

$stmt = $connection->prepare("INSERT INTO merchandise (mfr,type,description,mer_sku,price,qty) VALUES (?,?,?,?,?,?)");
$stmt->bind_param("ssssssss", $mfr,$type,$desc, "MR".$mfr.$sku."-".$sku,$price,$qty);

EDITED SUGGESTED CODE

This is my current setup but it will not submit.

<?php 
if (isset($_POST['submit'])) {

    $mfr    = $_POST['mfr'];
    $type   = $_POST['type'];
    $desc   = $_POST['description'];
    $price  = $_POST['price'];
    $qty    = $_POST['qty'];

    $connection->query("SELECT m_id FROM merchandise");
    $result = $connection->fetch_assoc();
    $last_id = $result["m_id"];
    $next_id = $last_id + 1;
    $conc = number_format($next_id/100,2,'-','');
    $connection->query("INSERT INTO merchandise (mfr,type,description,mer_sku,price,qty) VALUES ('$mfr','$type','$desc','MR'.'$mfr'.'$conc','$price','$qty')");

}
?>
cpt-crunchy
  • 391
  • 4
  • 23
  • so what i am getting out of this is you want to create your own AI value. you would need to do a select query first to find out the last value – Travis Hallet Oct 30 '16 at 19:42
  • And what happens if you reach 999? – Shadow Oct 30 '16 at 19:42
  • @TravisHallet never do that – Drew Oct 30 '16 at 19:46
  • @shadow. This is a very unlikely situation as the highest value to be used is 799 – cpt-crunchy Oct 30 '16 at 19:47
  • In this case use simple auto increment, and format it when you print it out from php. – Shadow Oct 30 '16 at 19:57
  • Good to see you've got the prepared statements with placeholder values in place. It looks like you're trying to [create a sequence generator in MySQL](http://stackoverflow.com/questions/26578313/how-do-i-create-a-sequence-in-mysql) which is unusually annoying compared to other platforms like Postgres and SQL Server. You could create another table specifically for generating these values. – tadman Oct 30 '16 at 22:31

0 Answers0