Increment numbers/Querying MySQL Help

[tubesnube]

Hi, I was wondering could anyone help me here. In my program, the user selects a document and fill's it out, submits it and receives an email with the information they filled out in. What I am trying to do now is that when the user selects document and submits it, a number will increment by 1 each time a document is selected.

In my database now I have a field ID, ID increments with each document selection and appears on screen so that is fine. But I have a problem with it when I try do submit a second document, I get an error. I think this has got to do with drop down menu's in my documents - for example drop down menu 1 contains: A, B and C. If I submit a document with A selected once it works, but when I try to select A and submit the document a second time I get an error.

My query is very long but here it is, any help appreciated:

            $select_query = "SELECT doc_id FROM tc_tool.forms WHERE  
                    doc_type =              '$_POST[doc_type]' AND 
                    doc_number =            '$_POST[doc_number]' AND        
                    revision =              '$_POST[revision]' AND
                    cdm_link =              '$_POST[cdm_link]' AND  
                    main_req_id =           '$_POST[main_req_id]' AND
                    mars_link =             '$_POST[mars_link]' AND
                    checklist_link =        '$_POST[checklist_link]' AND
                    link_internal_1_3 =     '$_POST[link_internal_1_3]' AND
                    impacted_products =     '$_POST[impacted_products]' AND
                    scope =                 '$_POST[scope]' AND
                    impacted_products_2 =   '$_POST[impacted_products_2]' AND
                    review_class =          '$_POST[review_class]' AND
                    full_simplified =       '$_POST[full_simplified]' AND
                    pref_earliest =         '$_POST[pref_earliest]' AND
                    pref_latest =           '$_POST[pref_latest]' AND
                    proj_name =             '$_POST[proj_name]' AND
                    auth_name =             '$_POST[auth_name]' AND
                    req_list =              '$_POST[req_list]' AND
                    optional_list =         '$_POST[optional_list]' AND
                    information_only =      '$_POST[information_only]' AND
                    chairperson =           '$_POST[chairperson]' AND
                    req_reviewers =         '$_POST[req_reviewers]' AND
                    review_duration =       '$_POST[review_duration]' AND
                    document_abstract =     '$_POST[document_abstract]'";       

                    $result_query = mysql_query($select_query, $connection);
                    if ( !$result_query )
                        die( "Error 1" );

                    echo mysql_num_rows( $result_query );

                    if ( mysql_num_rows( $result_query ) != 1 )
                        die( "Error 2" );

                        $row = mysql_fetch_assoc( $result_query );

                    echo $row[ doc_id ];    

[cereal]

Which error? Add or die(mysql_error()); to line 27, you will get some output about the error:

$result_query = mysql_query($select_query, $connection) or die(mysql_error());

bye.

[tubesnube]

I actually have it half working now thanks to the mysq_error. Updated it to look this like -

$result_query = mysql_query($select_query, $connection);
        if ( !$result_query )
            die( "Error 1 $query" . mysql_error() );

        echo mysql_num_rows( $result_query );

        /*while ( $row = mysql_fetch_assoc( $result_query ) )
        {
            echo '<tr>';
                echo '<td>' . $row[ ID ] . '</td>';
                echo '<td>' . $row[ Name ] . '</td>';
                echo '<td>' . $row[ Address ] . '</td>';
            echo '</tr>';
        } */

        $row = mysql_fetch_assoc( $result_query );


        echo $row[ 'doc_id' ];

Now my next problem - I don't no if I am explaining it correctly but I'll try.

Say for instance I select a document, select option A in the first drop down menu and submit it. I get a line back saying 00 (Which should show the ID) but the ID field still increments in the database!! It's really annoying, I can't understand why it's not displaying the number on the page but its still incrementing in the database.

Any idea's why this could be?

EDIT* It displays the number after the form is submitted when I select a different option from the drop down menu, but when I leave it as say option A for example it displays 00 after submitting it instead of displaying the number, but still increment's in the database.

[cereal]

I'm missing something, it's a counter? I don't see an update or insert query here, so the problem may be there. Can you show that code and an example output of the database table? If the count is done by adding a new row each time (insert) instead of an update query, then you need to change your select query with something like:

"select count(doc_id) as total_doc_id from tc_tool.forms group by doc_id"

where you have to add your WHERE conditions. Hope it helps.

[tubesnube]

Sorry cereal should have posted it, here is my query:

            $select_query = "SELECT doc_id FROM tc_tool.forms WHERE 
    doc_type =              '$_POST[doc_type]' AND 
    doc_number =                    '$_POST[doc_number]' AND        
    revision =                              '$_POST[revision]' AND
    cdm_link =                          '$_POST[cdm_link]' AND  
    main_req_id =                       '$_POST[main_req_id]' AND
    mars_link =                             '$_POST[mars_link]' AND
    checklist_link =                    '$_POST[checklist_link]' AND
    link_internal_1_3 =             '$_POST[link_internal_1_3]' AND
    impacted_products =         '$_POST[impacted_products]' AND
    scope =                                 '$_POST[scope]' AND
    impacted_products_2 =   '$_POST[impacted_products_2]' AND
    review_class =                  '$_POST[review_class]' AND
    full_or_simplified =            '$_POST[full_simplified]' AND
    earliest_date =                     '$_POST[pref_earliest]' AND
    latest_date =                       '$_POST[pref_latest]' AND
    proj_name =                         '$_POST[proj_name]' AND
    author_name =                   '$_POST[auth_name]' AND
    req_list =                              '$_POST[req_list]' AND
    optional_list =                     '$_POST[optional_list]' AND
    information_only =              '$_POST[information_only]' AND
    chairperson =                       '$_POST[chairperson]' AND
    reviewers_required =            '$_POST[req_reviewers]' AND
    review_duration =               '$_POST[review_duration]' AND
    document_abstract =         '$_POST[document_abstract]'";       

    $result_query = mysql_query($select_query, $connection);
    if ( !$result_query )
        die( "Error 1 $query" . mysql_error() );

    echo mysql_num_rows( $result_query );

    if ( mysql_num_rows( $result_query ) != 1 )
        die( "Error 2" . mysql_error() );

That's my query. As for database output, there is none. It is incrementing in the database itself but not actually outputting the number if you get me?

Sorry it isn't a counter either no, I have ID set as primary key in my database which is auto_increment, so everytime I submit a document the auto_increment goes up..

[tubesnube]

Would anyone have any idea what I could do please?!

[cereal]

I still don't understand what you want to achieve, the last query you posted is the same of the first post.
doc_id is primary key in tc_tool.forms or is a foreign key to another table? Can you run this query and paste the output?

explain tc_tool.forms;

[tubesnube]

Here is what I get when I ran that query -

http://i42.tinypic.com/bjyol.jpg

doc_id is primary key in tc_tools.forms yeah. I'll try explain this best I can -

User selects a form, fills in information and submits it. That information is stored in the database.

I have the above working but what I am trying to do now is that when the user submits the form to the database, a number is returned that is associated with that form.
Thats the reason I have doc_id as primary key and auto_increment so that everytime a form is submitted a doc_id is associated with that form is generated. That is also working but what is not working is the output of the doc_id, it is not displaying the number correctly when the form is submitted but it is incrementing in the database.

IE when the form is submitted it goes to a screen saying "Form successfully submitted, form number: (form number should be here)". It just says 0 instead of the doc_id that was generated with it in the database even though I have

$result_query = mysql_query($select_query, $connection);
        if ( !$result_query )
            die( "Error 1 $query" . mysql_error() );

        echo mysql_num_rows( $result_query );

        $row = mysql_fetch_assoc( $result_query );

        echo $row[ 'doc_id' ];

[cereal]

Ok, now is clear, nothing seems to be wrong to me, have you tried mysql_insert_id()? http://php.net/manual/en/function.mysql-insert-id.php
You can run this right after the form submits data to the database:

mysql_query('INSERT ...');
$id = mysql_insert_id();

This way you don't need to bug the database another time with a select query.
Also if anybody else has an idea..

[tubesnube]

Thanks man, that works!! Thanks a million! It prints now when the form is submitted.

Can I trouble you with one more thing if you don't mind? What I have working is when the form is submitted, person X receives a email with the form filled out in it. What I am trying to do now is to get the doc_id to appear in the received email, is this possible? Here is the received email:

http://i42.tinypic.com/2dqvh2b.jpg

What I have for my other information to be taken from the form and submitted into the email is this way:

message .= "<tr><td><strong>Document Number: </strong> </td><td>" . strip_tags($_POST['doc_number']) . "</td></tr>";

But how would that work with taking the doc_id from the database and putting it into the email?!

[cereal]

Good.

If the email is sent from the same script then just use the same $id variable as in my previous post. But try also to run your previous select query directly from phpmyadmin (or mysql shell) wiht same conditions and check if you get a result, because it's strange that you get an empty set. Also I suggest you to trim and escape data before running a query, something like this can be useful:

    function sanitize_data($data)
    {
        $data = array_map('trim',$data);
        $data = array_map('strip_tags',$data);
        $data = array_map('htmlspecialchars',$data);
        $data = array_map('mysql_real_escape_string',$data);
        return $data;
    }

    $post = sanitize_data($_POST);

and then, for example, write echo $post['doc_number']; instead of echo $_POST['doc_number'];.

[tubesnube]

Yeah the email is sent from the same script. What do you mean about try to run my previous select query directly from phpmyadmin? The reason it was an empty set was because I was deleting each new entry thats all, but I don't no if that makes any difference?

I put that function sanitize_data before my insert query too is that ok?

I don't really no how to incorporate the doc_id into the email part of the script. Here is a snippet of the email code, should I put it into a $message variable?

$subject = 'TC OSS RC Review Requested';

    $headers = "From: " . strip_tags($_POST['req-email']) . "\r\n";
    $headers .= "Reply-To: " . strip_tags($_POST['req-email']) . "\r\n";
    $headers .= "CC: david.f.flynn@ericsson\r\n";
    $headers .= "MIME-Version: 1.0\r\n";
    $headers .= "Content-Type: text/html; charset=ISO-8859-1\r\n";
    $header .= "Content-Transfer-Encoding: base64\r\n\r\n";

    $mail_body = "<html xmlns='http://www.w3.org/1999/xhtml'><head><meta http-equiv='Content-Type content='text/html; charset=utf-8' />";
    $mail_body .= "<title>Document</title></head>\n<body>\n";

    $message = "chunk_split(base64_encode($message))";

    $message = '<html><body>';
    $message = '<h2><u>This is a call to TC OSS RC Review</u></h2>';

    $message .= '<tr><td><a href = "http://159.107.173.47/webpages/TC_Tool/Tool/MOM_oss_rc.php">Click here for a link to MOM</a></td></tr><br /><br />';

    $message .= '<table rules = "all" style = "border-color: #333333" cellpadding = "4"; cplor = "#012561";>';
    $message .= "<tr style = 'background: #eee;'><td><strong>Document Title: </strong> </td><td>" . strip_tags($_POST['doc_title']) . "</td></tr>";

EDIT - I dont actually use echo $post['doc_number']; instead of echo $_POST['doc_number'];. anywhere, I just have '$_POST[doc_title]', does this make a difference?

[cereal]

The reason it was an empty set was because I was deleting each new entry thats all, but I don't no if that makes any difference?

Ah! Sure it makes a difference because you showed only the select part of the query, neither the insert nor the delete queries, this explains why you could not get the doc_id.

I put that function sanitize_data before my insert query too is that ok?

sure, you have to.

I don't really no how to incorporate the doc_id into the email part of the script. Here is a snippet of the email code, should I put it into a $message variable?

Yes, just add something like: $message .= "$post[doc_id]"; along with your HTML and it's not a problem if you don't use $post['doc_number'] mine was just an example.

A question: if you delete the entry from the database right after the insert, even if you send a number to the client, he cannot use it to retrieve this data from the website and if he writes back with that number as reference, you don't have any data to match.. what's the sense of this? Just to get an incrementing number? o_o'