Query of Queries

programming
1

I’m a CF tured php dev’r. I alway thought that the query of queries functionality in CF was something that every server side script should have. So I made a simple script that allows your to run a query against an already made recordset (SELECT ONLY) I wanted to see if anyone had anything they wanted to add or had some recommendations on this script. Below is the script. The function takes 3 parameters (the sql statement you want to run, the recordset you want to run the query againt, and the name of the table) I typicall just use a guid for the table name, using the com_create_guid()

function QQ($sql, $rs, $table){

$table = com_create_guid();
$table = str_replace("{",'',$table);
$table = str_replace("}",'',$table);
$table = str_replace("-",'',$table);
$table = str_replace("/",'',$table);
$table = str_replace("\",'',$table);
$table = str_replace(".",'',$table);
$table = str_replace("+",'',$table);


$num_fields = mysql_num_fields($rs);
$num_records = mysql_num_rows($rs);
$tcs = 'CREATE TABLE '.$table.' (';
$insert_sql = 'INSERT INTO '.$table.'( ';
$drop_sql = 'DROP TABLE  '.$table;


//Loop through the fields
for($i=0;$i<$num_fields;$i++){
    //Get the name of the field
    $field_name = mysql_field_name($rs,$i);
    
    //Get the field name to my table creation statement
    $tcs = $tcs.$field_name.' varchar('.mysql_field_len($rs,$i).')';
    
    //Add the field name INSERT statement
    $insert_sql = $insert_sql.$field_name;
    
    //Add the comma
    if($i != ($num_fields-1)){
    $tcs = $tcs.',';
    $insert_sql = $insert_sql.', '; 
    }
}//end loop

$tcs = $tcs.")";
$insert_sql = $insert_sql.")";

//Craate the table in the database
mysql_query($tcs);


//Insert the current recordset into the database. Two loops, 1)control the reocrds, 2) control the fields
for($i=0;$i<$num_records;$i++){
        $add = 'VALUES(';
            for($a=0;$a<$num_fields;$a++){
                $add = $add."'".mysql_result($rs,$i,$a)."'";
                
                
                if($a != ($num_fields-1)){
                $add = $add.',';
                }
            }//end inner loop

$insert_tmp = $insert_sql.$add.")";

//Excecute the insertion of the recordset into the table
mysql_query($insert_tmp);

}//end outer loop




//Run the query provided by the user
$new_query = mysql_query($sql);
//echo "SELECT ERROR: ".mysql_error()."<BR>";

mysql_query($drop_sql);
//echo "DROP ERROR: ".mysql_error()."<BR>";

return $new_query;

}//end function