Javascript mysql interface?

I am going back though a web-based document numbering system from few weeks ago. To sum it up, the user types in the project,class,base, and dash number (PPP-CCC-BBBB-DDD) then it is added to a mysql database. Now most doc numbers go in order according to revisions. IE: A document 1465-630-0001-000 becomes, after revision, 1465-630-0002-000.

The boss wants the system to automatically fill the input text box for the base number if it detects that the user is entering a revised doc. So if a user types in 1465 into the project field and 630 into the class field the system should autofill the base field with the next available number. In the previous example this would be 0002.

It needs to be able to search the database for the first two fields so that it can find the next a开发者_开发百科vailable one. Is there anyway to do this using javascript or something? SO was really helpful with my last javascript question pertaining to this system.

heres an bit of my code if it helps:

` ?>

<div id='preview'></div>
<form id='item' action="submit.php?item=1" method="post">
Enter Title:<input type="text" name="title" size="20"><BR>

Choose Project Code:
<SELECT NAME="project">
        <OPTION VALUE="">Project...
        <?
        $query  = "SELECT * FROM project ORDER BY project asc";
        $result = mysql_query($query);
        while($row = mysql_fetch_assoc($result))
            {
            $num = ($row['project']);
            $name = ($row['description']);
            ?>
            <OPTION VALUE="<?=$num?>" ><? echo"{$num}" . " | " . "{$name}";?>
            <?
            }
            ?>    
</SELECT><BR>

Choose Class Code:
<SELECT NAME="class">
            <OPTION VALUE="">Class...
        <?
        $query  = "SELECT * FROM class ORDER BY class asc";
        $result = mysql_query($query);
        while($row = mysql_fetch_assoc($result))
            {
            $num = ($row['class']);
            $name = ($row['description']);
            ?>
            <OPTION VALUE="<?=$num?>" ><? echo"{$num}" . " | " . "{$name}";?>
            <?
            }
            ?> 
</SELECT><BR>

Assigned Base Number:<input type="text" name="base" size="20"><BR>
Enter Dash Number:<input type="text" name="dash" size="20"><BR>
Enter Comments:<input type="text" name="comment" size="40"><BR>
<input type="hidden" name="item" value="1"/> `

Just a simple html/php input form with the project and class code list generated from a database pertaining to each.

Thanks for any help-Thomas

Update:
So, you're going to need to make an AJAX call (see example in my comment below) to some PHP script that will retrieve the base value you want and then returns that to the AJAX request. Once the request gets a response, you can use that data to fill in the value the way I originally said...

On a side note, since the example I gave you is a jQuery AJAX function, you should probably check out how to use jQuery to select elements on the page, instead of using straight JS.

E.g. for getting by ID and replacing value:

    $("#base").attr('value', valueFromAjaxCall);

How to change value with JS:
If you use PHP to get the base value you want to fill into the field, then you can fill the value in with:

    var baseField = document.getElementsByName("base")[0];
    baseField.value = <?=$baseValue?>;

The getElementsByName() call returns an array, which is why you have to index into the field you want. I would suggest giving your <input> an id so that you can use document.getElementById() instead. You would do something like:

    <input type="text" id="base" size="20">

and the JS to get the input element would be:

    var baseField = document.getElementById("base");

...therefore, no need to index, in case you named any fields with the same name.

**Not sure about the PHP syntax.

An ajax call on focus of the 3rd field firing back to the server the values of the first two fields?

first, you'll probably want to use jQuery since it has great support is easy to use and will feel familiar to someone used to PHP.

so include your jQuery javascript code that you can get from : http://jquery.com/

then, assume a form that looks like:

{form}
<input type=text id='major' name='major' value=''>
{Or a select, your choice}
<input type=text id='minor' name='minor'>
{or a select again}
<input type=text id='sequence' name='sequence' onFocus='getNextSequence()'>
...
{/form}

in your head, have your javascript:

 function getNextSequence(){  
    var major=$('#major').val();  
    var minor=$('#minor').val();  
    if(!major){  
        alert('Select a major version#');  
        $('#major').focus();  
        return(false);  
    }  
    if(!minor){  
        alert('Select a minor version#');  
        $('#minor').focus();  
        return(false);  
    }  
    $.getJSON('http://url.to.getnextNumber.php',
        {major:major,minor:minor},
        function(data){  
            if(!data.error){  
                $('sequence').val(data.nextSequence);  
            }else{  
                alert(data.error);  
            }  
        }
    });  
}  

the jQuery getJSON call will make a call back to your URL with two $_POST variables, major and minor. do your query, save the result as $result=array('nextSequence'=>$x,'error'=>'false'); and convert it to JSON with echo json_encode($result); don't include ANY headers or any other content in the output of that file, and jQuery will pull the correct value and insert it where it's supposed to bed