Nice script for reference

[tronicsmasta]

Just thought this might be a useful script for someone... I needed it to build a categories list of over 10,000 videos with over 900 categories...

 

It builds a unique category listing from one db of videos, builds an array, trims each element of white spaces, sorts it ascending, and inserts it into another table...

 

I am sure there is some redundant code lol

 

Would like some feed back

 

<?php

//connect to the db
require_once('dbconnect.php');

//clear table
$sql_truncate = "TRUNCATE TABLE categories;";
$result = mysql_query($sql_truncate) or die(mysql_error());

//reset auto increment to 0 again
$sql_alter = "ALTER TABLE categories AUTO_INCREMENT = 0;";
$result = mysql_query($sql_alter) or die(mysql_error());

//select categories from orginal table
$sql = "SELECT DISTINCT category FROM videos ORDER BY category ASC;";

//build array
$cat_array = array();

//populate array based on sql query of unique categories
if ($result = mysql_query($sql)) {
    //count the results
        $numrows = mysql_num_rows($result);

        //add a nice header with row count
        $output .= "<h4>Categories ($numrows)</h4>";

        //run query and build an array and add to your array... possible redundant
	while ($row = mysql_fetch_assoc($result)) { 
	     $cat_array[] = $row['cat']; 
	}
}
//create new temporary array for next foreach...
$cat_array_trim = array();

//this foreach loop trims the white spaces from beginning and end of each element of the array
//this must be done before sorting
foreach ($cat_array as &$cat) {

//add the new trimmed element to the new temporary array
$cat_array_trim[] = trim($cat);
   }

//reset the original array and sort it at the same time...
$cat_array = asort($cat_array_trim);

//now for each array element, add it to the database
foreach ($cat_array_trim as &$cat) {	
//addslashes takes this "This is my friend's cat!" and makes it mysql safe "This is my friend\'s cat!"
$cat = addslashes($cat);

//insert query
$sql_insert = "INSERT INTO categories (`id`, `category`, `type`) VALUES (NULL, '$cat', '');";
$result = mysql_query($sql_insert) or die(mysql_error());

//output results just to double check your handy work
$output .= "Insert $cat into the db!<br />";	
   }

//this shows your handy work... good job!		
echo $output;

//close the db
require_once('dbclose.php');

?>

 

[sasa]

you can do all job wit SQL

look http://dev.mysql.com/doc/refman/5.1/en/insert-select.html and http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_trim