On Dec 13, 2007 10:14 AM, tedd <tedd.sperling@xxxxxxxxx> wrote: > At 12:20 PM +0100 12/13/07, Zoltán Németh wrote: > >2007. 12. 12, szerda keltezéssel 20.13-kor tedd ezt írta: > > > I would like to create a temporary table to perform searches. > >> > >> From my main table, I need to exclude records that have certain > >> fields that are null or empty; and then sort the final result. > > > >why do you need a temp table for that? > >select * from blah where not isnull(checkfield) and checkfield <> '' > >order by someotherfield > > Zoltán: > > Ok, here's the problem. > > I have a table with over 5000 records. > > There is no index (not my dB) and the records are not complete. > > There is a numeric product_id field, but in the dB this is not in sequence. > > Some records have a product_id, but no product_name. > > I need to travel the dB showing each item in > order (product _id) and excluding those products > that have no product_name. > > That sounds simple enough, but currently for each > step the entire table gets sorted (unless I'm > doing it wrong). > > I was thinking that I could: > > 1. Create a temporary table. > 2. Sort the table once. > 3. Remove the records that have no product_name > 4. And then just travel the temporary table for the duration of the script. > 5. Drop the table when done with it. > > Now, what's wrong with my thinking? > > Cheers, > > tedd > > > > > > > > -- > ------- > http://sperling.com http://ancientstones.com http://earthstones.com > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > <? // Put DB and config includes here. $sql = "SELECT * FROM prod_table WHERE product_name != '' ORDER BY product_id"; $result = mysql_query($sql) or die(mysql_error()); while($row = mysql_fetch_array($result)) { $ssql = "INSERT INTO tmp_table(product_id,product_name,val,val2) "; $ssql .= "VALUES('".$row['product_id']."','".$row['product_name']."','$row['val']."','".$row['val2']."')"; mysql_query($ssql); } mysql_query("DROP TABLE prod_table"); mysql_query("RENAME TABLE tmp_table TO prod_table") or die(mysql_error()); ?> -- Daniel P. Brown [Phone Numbers Go Here!] [They're Hidden From View!] If at first you don't succeed, stick to what you know best so that you can make enough money to pay someone else to do it for you. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
