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
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
