Total newbie so apologies if I've made any blunders.

I have set up a form with a number of fields and PHP to go and fetch a unique number from a DB and insert it into one of the fields on a form. It then updates the the DB table to take that unique code out of circulation.

The trouble is this is the first time I've written anything in php and the solution I have set up is both clumsy and slow to load.

The issue I think is that it goes and gets all the values for the unique code then selects one from that list, whereas I think it should be possible to simply go get the next one.

My form is roughly like this:

First name <input type="text" id="FIRSTNAME_FIELD" name="FIRSTNAME_FIELD" value="" size="30" maxlength="64">

Last name <input type="text" id="LASTNAME_FIELD" name="FIRSTNAME_FIELD" value="" size="30" maxlength="64">

Email <input type="text" id="EMAIL_FIELD" name="FIRSTNAME_FIELD" value="" size="30" maxlength="64">

<input type="hidden" id="UNIQUE_CODE" name="UNIQUE_CODE_FIELD" value="<?php echo $yourcode; ?>" >

<input type="hidden" id="AFFILIATE" name="AFFILIATE_FIELD" value="97" >

NB: AFFILIATE is an integer used to define which UNIQUE_CODE is assigned to whom. I.e. codes 000001 thru 20,0000 belong to affiliate 1, 20,001 thru 50,000 to affiliate 2 etc.

My PHP reads like this:

<?php

$con = mysql_connect("DB_LOCATION.com","username","password");

if (!$con) { die('Could not connect: ' . mysql_error());

}mysql_select_db("DB_NAME", $con);

$sql = "SELECT * from table_name WHERE IN_USE='0' AND AFFILIATE="97"";

$result = mysql_query($sql,$con);

$rows = mysql_num_rows($result);

$counter = $rows-1;

$yourcode = mysql_result($result,$counter);

mysql_query("UPDATE table_name SET IN_USE='1' WHERE UNIQUE_CODE='$yourcode'"); mysql_close($con);?>

As you can see, far too much mucking about, can anyone suggest a better method?

    I may be misunderstanding but you do know you can set a column in the databse to auto increment?
    - so every time you add a new record it inserts a new unqiue id for that row
    all you do is set the column to auto increment (can be done via phpmyAdmin)

    then instead of updating you do an insert

    but I dont know what's in your table - if its pre populated with something else then you do need to do a select first

    you could do (pseudo code)

    $sql="SELECT MIN(UNIQUE_CODE) AS lowestavailable FROM table_name WHERE IN_USE='0' AND AFFILIATE='97' GROUP BY AFFILIATE";
    $result = mysql_query($sql,$con);
    $rows = mysql_fetch_array($result);
    $lowestavailable = $rows['lowestavailable '];

    then
    UPDATE table_name SET IN_USE='1' WHERE UNIQUE_CODE='$lowestavailable '"

    edit: I assume thats a typo in the double quotes within the sql statement??
    another edit: don't need rowcount in this method...

      Write a Reply...