Re: PHP loop to issue sql insert

On 15/11/2010, at 12:47 PM, Rick Dwyer wrote: > Hello List. > > I have a sql command that counts, groups and sorts data from a table. I need to insert the > results of that sql command into different table. > > My sql SELECT looks like this: > > select count(*) as count, searchkeywords from searchtable group by searchkeywords order by > count desc; > > and returns records like this: > > 578 green > 254 blue > 253 red > 253 yellow > 118 orange > .... etc. > > My PHP looks like this so far: > > $sql = "select count(*) as count, searchkeywords from searchtable group by searchkeywords > order by count desc"; > $result = @mysql_query($sql,$connection) or die("Couldn't execute checkcat > query"); > $num = mysql_num_rows($result); > echo $num; > > > The echo is of course returning the total number of records.... not data as listed above. > > I know a WHILE statement is to be used here, but can't get it to work. > > How do I loop through the above found data, inserting each value as like this: > > insert into mytable (count, color) values ("578", "green"); > insert into mytable (count, color) values ("254", "blue"); > ...etc > > > > Thanks, > > > --Rick Personally I'll get MySQL to do it for me using: insert into mytable (count, color) select count(*) as count, searchkeywords from searchtable group by searchkeywords order by count desc (see http://dev.mysql.com/doc/refman/5.1/en/insert-select.html for more information on INSERT ... SELECT) Otherwise, you'll want to use mysql_fetch_assoc($result). Examples and information can be found at http://php.net/mysql_fetch_assoc --- Simon Welsh Admin of http://simon.geek.nz/ Who said Microsoft never created a bug-free program? The blue screen never, ever crashes! http://www.thinkgeek.com/brain/gimme.cgi?wid=81d520e5e