questions of efficiency

[knome]

Sorry if this doesn't belong in MySQL Help, and sorry for including php, but my problem isn't with any of my code, rather its with the efficiency of the process I am using.

 

The goal of my entire program is to take a "report" made of a large string consisting of a few hundred words separated by spaces, and to sort through those words and put the ones of relevance into their own columns in my database. (each report gets its own row)

 

Currently, I am approaching the problem thus:

1) Create row by inserting the entire report into its own column 'raw_report'

2) this creates an auto incremented column 'report_id' which i want to use from this point on to input data

3) this is where in my opinion it looks inefficient, I need to get that 'report_id' so I can use it to input the other data, so I select it by comparing the report to the 'raw_report' column. Now perhaps I'm overreacting to something that wont matter in the gist of things, but it seems to me that comparing a string with thousands of characters to other strings with thousands of characters will get increasingly more inefficient when more reports are added to the database. There must be a better way to get report_id without making this comparison.

4) using report_id input other data.

 

I realize that I could sort through the report, storing everything in temporary php variables, and then upload it all at once thus eliminating the need for comparisons altogether, but that would require that I completely change the structure of my code, and it would create hours of work. So ya... It would be perfect if some one could explain a better way of executing the enclosed code, but I'm open to other suggestions, even if its that I should "get over my OCD".

 

   
//store raw_report as a string, words seperated by spaces
$insert = mysql_query("
            INSERT INTO reports (raw_report)
            VALUES ('$pRawReport')
            ")or die (mysql_error());
    
//retrieve and set report_id
$temp = mysql_fetch_array(mysql_query("
            SELECT report_id
            FROM reports
            WHERE raw_report = '$pRawReport'
            "))or die (mysql_error());
$reportID = $temp[0];

 

[knome]

forgot. mysql version:5.1.56, although, I don't know how relevant it will be to my problem.

[mikosiko]

you don't need the second SELECT... you just need

LAST_INSERT_ID()

 

[knome]

thank you sir. that worked!