convert sql to sqli pull id and reinsert into table using php

Question

I've been searching and trying for the last few hours with no luck. Trying to do this. I have a web Purchase order system. It uses mysql which is obviously outdated. It's a simple code, I pull the ID of the last ponumber, +1 to it, and reinsert it. I also display the new number on the form. This allows my system to display the PO number before submitting, and ensures noone else gets the same number twice. Here is my old code. There is a connection section in the header, but it's the same as the conn below which is all i havent added.

$row = mysql_fetch_array($result);
$ID = $row['ID'] +1 ;
?>
<?php
$con = mysql_connect("localhost","username","password");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("purchaseorder", $con);

$sql="INSERT INTO ponumber (ID)
VALUES
('$ID')";
if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}

?> 
<form action="submit.php" method="post" name="form" id="form" accept-    charset="utf-8">
<div class="form-all">
<ul class="form-section">
  <li id="cid_1" class="form-input-wide">
    <div class="form-header-group">
      <h2 id="header_1" class="form-collapse-table">
        Jillian Builders Purchase Order #:</h2><center><font size="9"> 
 <?php echo $ID ?>
      </h1></center></font></div>

This works just fine, other than an annoying sql is deprecated warning, which im trying to change. I cannot for the life of me figure out how to get the ID number using mysqli. I have tried numerous ways with fetch assoc, with array's, but for some reason I can't make it work. Im sure there is a better way to do this than what im doing. I'm open to suggestions. In the form. The employee needs to give the PO number to a vendor. Once he pushes submit on the form it submits the number in. So i need the number before he submits the data to the database so he can give it. Which is why i used +1. I get the old ID +1 to it to show the number in the FORM ONLY. It's not relevant to the actual DB. When he submits the PO it submits the number into it, and everything lines up. All i need to do it find a way to pull the ID off.

This is what i've tried so far to no avail

<?php 
$data = mysqli_query($conn,"SELECT ID FROM ponumber ORDER BY ID DESC LIMIT 1");
while ($row = mysqli_fetch_assoc ($data)) {
extract ($row);
echo "
ID=$ID +1 
";
}

Hope this helps clarify where i'm at Thanks in advance.

You can save yourself some pain by skipping `mysqli` and jumping straight to `PDO` — Kraang Prime, Dec 31 '16 at 01:35
1. You should **never** use `$row['ID'] +1` to get the id of the next row. There is auto-increment for that. 2. Put the mysqli code you tried and say where exactly you have problems. — Dekel, Dec 31 '16 at 01:36
for `row['ID'] +1` there is [insert_id](http://php.net/manual/de/mysqli.insert-id.php), because your code doesn't _"ensure(s) noone else gets the same number twice"_ — Jeff, Dec 31 '16 at 01:39
fair enough with the insert_id but there are no examples (nor can i get it to work) with using it the way i need to. I need to have the ID displayed on the page before I submit the actual po. I pull the number first, add 1, and then insert it back in. All the explamples ive read with insert_id require me to do an insert query first. This may not be true, its just what ive found. Any thoughts how to make the insert_id work then — Lloyd Diggins, Dec 31 '16 at 01:45
and skipping sqli and going straight to PDO while sounds fun, ive got pages of code to change over then which Im not to keen on doing — Lloyd Diggins, Dec 31 '16 at 01:46
Just modify your `ID` column to `auto_increment` as @Dekel recommends, and then you don't need to do `SELECT`/increase/`INSERT`. When you add a new row, it will automatically increase http://stackoverflow.com/questions/5035836/how-to-add-auto-increment-to-an-existing-column — Sean, Dec 31 '16 at 01:55
in relation to updating from mysql to mysqli, use [How can I prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). for example, this answer - http://stackoverflow.com/a/60442/689579 - shows how to do a mysqli connection/query. — Sean, Dec 31 '16 at 01:58
if you really (and I recommend not to need that) need to know an ID before you insert it, you can use microtime, as that will be unique for the next (I don't know..) 100 years. - but still not for absolutely sure — Jeff, Dec 31 '16 at 02:02
*"cannot for the life of me figure out how to get the ID number anymore using mysqli."* - `mysqli_`? you're not trying to mix in `mysqli_` with `mysql_` are you? Plus, if you need to get a specific P.O. number, you should be using a `WHERE` clause. I think the question's unclear. — Funk Forty Niner, Dec 31 '16 at 02:18
if you're trying to insert the same number, make sure there isn't a unique constraint anywhere; that might be failing you. Were there any errors thrown back? — Funk Forty Niner, Dec 31 '16 at 02:21
btw, you're trying to execute the first query but connecting "after"; most likely why your code is failing — Funk Forty Niner, Dec 31 '16 at 02:22
You were mixing APIs in your other one http://stackoverflow.com/q/39461383/1415724 - hope you're not doing the same thing here with code you're not showing us. — Funk Forty Niner, Dec 31 '16 at 02:24
Fred all im trying to do is pull the ID number off a query. and no im not mixing the two. Im just at a loss at how else to do this. — Lloyd Diggins, Dec 31 '16 at 02:27
I'll edit to show you my entire code..its only a few more lines really. And Im not mixing anything here , this code ive displayed works fine, but its the old sql way. im trying to learn how to do it the new mysqli but running into issues. — Lloyd Diggins, Dec 31 '16 at 02:29

Ahmad Asjad · Accepted Answer · 2016-12-31T03:14:03.723

First of all I'll need to tell you that your this code -( according to my understaning, if you are not using two databases)

$row = mysql_fetch_array($result);
$ID = $row['ID'] +1 ;

should be after this code:

<?php
$con = mysql_connect("localhost","username","password");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("purchaseorder", $con);

Please never do $row['ID'] +1 ;

//just insert 
$sql="INSERT INTO ponumber (ID)
VALUES
(NULL)";

And the ID column of ponumber should be auto_incremented Then get the last insert id(auto incremented)

$po_id = mysqli_insert_id($con);

in your form

<?php echo $po_id ?>

and one hidden input like

<input type="hidden" name="po_id" value="<?php echo $po_id; ?>"/>

And after submitting the form you should update the form with these values. And if you don't want the table to be empty (if user skips the full process of filling the form), just add a created_at column. After that you have updated the table, check if any row created one day ago exists, just delete it. For checking that if a row is empty or not add a flag column and play with it when going to update or delete the row -(means if a column named 'rec_inserted' is false, then this row should be deleted after 1 day. And this column should be set to true when going to update the record first time.

Thanks, this works, and is a lot more organized then the mess i had. I appreciate the time to go over that in such detail as well. For my own knowledge Is using the $row['ID'] +1 ; a bad idea because of possible injection or some other reason? — Lloyd Diggins, Dec 31 '16 at 03:14

convert sql to sqli pull id and reinsert into table using php

1 Answers1