Re: PHP loop to issue sql insert

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux