CODEWITHSUNDEEP
phpmysqlsql
Ethan
Ethan

Reputation: 13

PHP / Mysql increment by one from previous row

I am looking to take the ID that auto increments already from the latest row in a table and increment it by one and append it on to a returned result from that same table. So if the row id is 5 and the tabledata result is product5, I need it changed to product6 in the php result. So when the data is resubmitted it is in-line with the new row ID.

EDIT: I need it displayed and incremented before the sql insert because it's also emailed on data submit for ordering. The database insert is just to retain the order record.

Current code display results:

$conn=mysql_connect(" "," "," ");
mysql_select_db("database",$conn);
$sql="SELECT * FROM table WHERE ID=(SELECT max(ID) FROM table)";
$rs=mysql_query($sql,$conn) or die(mysql_error());
$result=mysql_fetch_array($rs);

echo '<table>
<tr>
<td>Data: '.$result["tabledata"].'</td>
</tr>
</table>';
?>

Upvotes: 0

Views: 1442

Answers (2)

LSerni
LSerni

Reputation: 57453

So if I understand, you have two fields, not one:

id    tabledata
1     PO-01
5     product5

is a Purchase order field so it stays in line with the previous just one number different (Example PO-01 to PO-02)

First thing that comes to mind is not to write that number in two places anyway. You could have

id    tabledata
1     PO-%02d
5     product%d

and then wherever you had the row data, you could use

sprintf($row['tabledata'], $row['id'])

to get the "human readable" version of tabledata. Then to get the "next" ID you could just do

sprintf($row['tabledata'], $row['id'] + 1)

Otherwise, you need to extract the number from the text field. This requires that you know in advance its format (e.g. is it %d or %02d or...?).

If you know it is just the number, with variable length, as in your first example (product5 to product6), you do

$table['tabledata'] = preg_replace('#\\d+$#', $table['id'] + 1);

The above will replace the last sequence of numeric digits (here, 5; it could be 1701 for example) with 6. Or you can capture the number with preg_match and much the same expression, only in parentheses, increment it, and store it back.

If it is a fixed-length sequence of numbers as in your second example (PO-01 to PO-02), you just use substr:

$size = 2; // Two-digit number

$num  = substr($table['tabledata'], -$size);
$prd  = substr($table['tabledata'], 0, -$size);

$table['tabledata'] = $prd . sprintf("%0{$size}d", $num + 1);

An even more complicated solution would be to merge the two versions into a "decoding" function that would count leading zeroes and digit lengths and use this to determine the format being used. This however would have problems in some cases (e.g. maybe the next number out from AB-99 is not AB-100 but AC-00 or AC-01), so all in all I think this is best left to someone with the knowledge of the specific domain.

Upvotes: 0

Riz
Riz

Reputation: 1131

What if you modify the query to select value after incrementing it. i.e.

SELECT ID+1 AS ID, /*all other fields of the table */ FROM table WHERE ID=(SELECT max(ID) FROM table)

Upvotes: 1

Related Questions