Re: PHP loop to issue sql insert

On Nov 14, 2010, at 8:15 PM, Simon J Welsh wrote:

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)

Wow... I have never heard of the combination of INSERT... SELECT before.... worked perfectly. Thanks. --Rick