Jump to content

Converting CSVs to database help

zeitgeber

By zeitgeber
in PHP Coding Help

 Share

Recommended Posts

zeitgeber Newbie

zeitgeber

Posted
Posted

Hey guys, first time poster here. I have some inherited code where there is an admin page, some includes, and a page on the website to view the database information. On the admin page are various fields to be able to upload excel files. There is one that has multiple categories that can be chosen from a dropdown menu. Once these are uploaded they are listed on the website under their respective categories.

 

I apparently have the uploading working for the most part; checking the mysql database shows me the populated data, except one of the columns has absolutely nothing. Of course, this is the name of the item. On the website page, I only want to show the name, so this means that nothing is showing up either.

 

I'm not sure what else to do. I'm definitely not a huge php developer and this seems slightly beyond me.

 

Here is the admin page with the uploading of the tab-delimited txt file

 

<?php
include("../inc/admin.inc.php");

$alert = "";
$dir = dirname(__FILE__);

function format_record_count($record_count)
{
	return "$record_count record".($record_count == 1 ? '' : 's')." imported";
}

function revert($filename, $revert_what, $import_func, $import_args=array())
{
	global $dir, $alert;

	$csv_file = revert_to_file($dir, $filename);
	if($csv_file)
	{
		$records = call_user_func_array($import_func, array_merge($import_args, array($csv_file)));
		$records_str = format_record_count($records);
		$alert = "Reverted $revert_what ($records_str)";
	}
	else
	{
		$alert = "Cannot revert $revert_what";
	}
}

function upload($post_param, $filename, $upload_what, $import_func, $import_args=array())
{
	global $dir, $alert;

	$csv_file = upload_csv($post_param, $dir, $filename);
	$records = call_user_func_array($import_func, array_merge($import_args, array($csv_file)));
	$records_str = format_record_count($records);
	$alert = "Updated $upload_what ($records_str)";
}

if($_REQUEST["revert_brands"] and $_POST["brand_category"])
{
	revert($_POST["brand_category"], "brand listings for {$_POST['brand_category']}", "import_brands_from_csv", array($_POST["brand_category"]));
}
if($_REQUEST["revert_brands2"] and $_POST["brand_category2"])
{
	revert($_POST["brand_category2"], "brand listings for {$_POST['brand_category2']}", "import_brands_from_csv2", array($_POST["brand_category2"]));
}elseif($_POST["upload_brands"] and $_POST["brand_category"] and is_uploaded_file($_FILES["brands_file"]["tmp_name"]))
{
	upload("brands_file", $_POST["brand_category"], "brand listing for {$_POST['brand_category']}", "import_brands_from_csv", array($_POST["brand_category"]));
}
elseif($_POST["upload_brands2"] and $_POST["brand_category2"] and is_uploaded_file($_FILES["brands_file2"]["tmp_name"]))
{
	upload("brands_file2", $_POST["brand_category2"], "brand listing for {$_POST['brand_category2']}", "import_brands_from_csv2", array($_POST["brand_category2"]));
}
<form method="POST" action="index.php" enctype="multipart/form-data">
			<fieldset>
				<legend>CSV Upload</legend>
				<p><label>Kind:</label> <select name="brand_category">
											<option <?=($_POST["brand_category"]=="Beer and Malt Alternative"?"selected":"");?> value="Beer and Malt Alternative">Beers & Malt Alternative</option>
											<option <?=($_POST["brand_category"]=="Bottled Waters"?"selected":"");?>>Bottled Waters</option>
											<option <?=($_POST["brand_category"]=="Juices, Teas and Sodas"?"selected":"");?>>Juices, Teas and Sodas</option>
											<option <?=($_POST["brand_category"]=="Mixers"?"selected":"");?>>Mixers</option>
											<option <?=($_POST["brand_category"]=="Premium Cigars"?"selected":"");?>>Premium Cigars</option>
											<option <?=($_POST["brand_category"]=="Riedel Crystal"?"selected":"");?>>Riedel Crystal</option>
											<option <?=($_POST["brand_category"]=="Spirits"?"selected":"");?>>Spirits</option>
											<option <?=($_POST["brand_category"]=="American Wines"?"selected":"");?> value="American Wines">Wines - American</option>
											<option <?=($_POST["brand_category"]=="Imported Wines"?"selected":"");?> value="Imported Wines">Wines - Imported</option>
										</select>
				</p>
				<p><span>&#8618; <input type="submit" name="revert_brands" value="Revert Last File" /></span><input type="file" name="brands_file"/> <input type="submit" name="upload_brands" value="Upload" /></p>
			</fieldset>
		</form>
		<br/>
            <h2>Southeast/Chattanooga Brands Portfolio</h2>
		<p>Use the form below to browse and upload an updated Brands Portfolio CSV file</p>
		<p>File Format: tab delimited</p>
		<p>Columns (1): Name</p>
		<form method="POST" action="index.php" enctype="multipart/form-data">
			<fieldset>
				<legend>CSV Upload</legend>
				<p><label>Kind:</label> <select name="brand_category2">
											<option <?=($_POST["brand_category2"]=="Beer and Malt Alternative"?"selected":"");?> value="Beer and Malt Alternative">Beers & Malt Alternative</option>
											<option <?=($_POST["brand_category2"]=="Bottled Waters"?"selected":"");?>>Bottled Waters</option>
											<option <?=($_POST["brand_category2"]=="Juices, Teas and Sodas"?"selected":"");?>>Juices, Teas and Sodas</option>
											<option <?=($_POST["brand_category2"]=="Mixers"?"selected":"");?>>Mixers</option>
											<option <?=($_POST["brand_category2"]=="Premium Cigars"?"selected":"");?>>Premium Cigars</option>
											<option <?=($_POST["brand_category2"]=="Riedel Crystal"?"selected":"");?>>Riedel Crystal</option>
											<option <?=($_POST["brand_category2"]=="Spirits"?"selected":"");?>>Spirits</option>
											<option <?=($_POST["brand_category2"]=="American Wines"?"selected":"");?> value="American Wines">Wines - American</option>
											<option <?=($_POST["brand_category2"]=="Imported Wines"?"selected":"");?> value="Imported Wines">Wines - Imported</option>
										</select>
				</p>
				<p><span>&#8618; <input type="submit" name="revert_brands2" value="Revert Last File" /></span><input type="file" name="brands_file2"/> <input type="submit" name="upload_brands2" value="Upload" /></p>
			</fieldset>
		</form>

 

Here is the admin.inc.php file:

<?php 
include("brands.inc.php");

function upload_csv($field_name, $to_dir, $root_name)
{
	$csv_file = "$to_dir/csv/{$root_name}.csv";

	if(file_exists($csv_file))
	{
		rename($csv_file, "{$csv_file}.old");
	}
	move_uploaded_file($_FILES[$field_name]["tmp_name"], $csv_file) or die("Failed to move to $csv_file");

	return $csv_file;
}

function revert_to_file($in_dir, $root_name)
{
	$fullpath = "$in_dir/csv/{$root_name}.csv";
	$oldpath = "{$fullpath}.old";

	if(file_exists($oldpath))
	{
		rename($oldpath, $fullpath);
		return $fullpath;
	}
	return false;
}

function import_brands_from_csv($category_name, $csv_file)
{
	$category_id = category_id($category_name);
	$categories = get_subcategory_ids($category_id);

	mysql_query("begin work");
	if($categories)
	{
		$categories_sql = "(".join(",", array_map(dbquote, $categories)).")";

		mysql_query("delete from brands where in_category in $categories_sql or in_category=$category_id") or mysql_fail();
		mysql_query("delete from categories where category_id in $categories_sql") or mysql_fail();
	}
	else
	{
		mysql_query("delete from brands where in_category=$category_id") or mysql_fail();
	}

	$records = 0;
	$cat_id = array();
	$csv_data = read_csv_file($csv_file, "\t");
	if(count($csv_data[0]) > 1)
	{
		foreach($csv_data[0] as $cat_name)
		{
			$cat_name = trim($cat_name);
			if ($cat_name == "") { continue; }
			mysql_query("insert into categories (category_name, parent_category) values (".dbquote($cat_name).",$category_id)") or mysql_fail();
			$cat_id[] = category_id($cat_name);
		}

		for($row=1; $row<count($csv_data); $row++)
		{
			for($col=0; $col<count($csv_data[0]); $col++)
			{
				$data = trim($csv_data[$row][$col]);

				if($data)
				{
					mysql_query("insert into brands (brand_name, in_category) values (".dbquote($data).",".$cat_id[$col].")") or mysql_fail();
					$records++;
				}
			}
		}
	}
	else
	{
		foreach(flatten($csv_data) as $item)
		{
			mysql_query("insert into brands (brand_name, in_category) values (".dbquote($item).",$category_id)");
		}

		$records = count($csv_data);
	}

	mysql_query("commit") or mysql_fail();

	return $records;
}

function import_brands_from_csv2($category_name, $csv_file)
{
	$category_id = category_id($category_name);
	$categories = get_subcategory_ids($category_id);

	mysql_query("begin work");
	if($categories)
	{
		$categories_sql = "(".join(",", array_map(dbquote, $categories)).")";

		mysql_query("delete from brands2 where in_category in $categories_sql or in_category=$category_id") or mysql_fail();
		mysql_query("delete from categories2 where category_id in $categories_sql") or mysql_fail();
	}
	else
	{
		mysql_query("delete from brands2 where in_category=$category_id") or mysql_fail();
	}

	$records = 0;
	$cat_id = array();
	$csv_data = read_csv_file($csv_file, "\t");
	if(count($csv_data[0]) > 1)
	{
		foreach($csv_data[0] as $cat_name)
		{
			$cat_name = trim($cat_name);
			if ($cat_name == "") { continue; }
			mysql_query("insert into categories2 (category_name, parent_category) values (".dbquote($cat_name).",$category_id)") or mysql_fail();
			$cat_id[] = category_id($cat_name);
		}

		for($row=1; $row<count($csv_data); $row++)
		{
			for($col=0; $col<count($csv_data[0]); $col++)
			{
				$data = trim($csv_data[$row][$col]);

				if($data)
				{
					mysql_query("insert into brands2 (brand_name2, in_category) values (".dbquote($data).",".$cat_id[$col].")") or mysql_fail();
					$records++;
				}
			}
		}
	}
	else
	{
		foreach(flatten($csv_data) as $item)
		{
			mysql_query("insert into brands2 (brand_name2, in_category) values (".dbquote($item).",$category_id)");
		}

		$records = count($csv_data);
	}

	mysql_query("commit") or mysql_fail();

	return $records;
}
?>

 

The master include file is just connecting to mysql, so I'm not including it here.

 

Here's the brand include:

<?php
include("master.inc.php");

function brands_in_category($category_id)
{
	return mysql_select_col("select brand_name from brands where in_category=".dbquote($category_id));
}

function get_subcategories($parent_id)
{
	$subcategories = array();
	$get_subcategories = mysql_query("select category_id, category_name from categories where parent_category=".dbquote($parent_id)) or mysql_fail();
	while($id_name = mysql_fetch_array($get_subcategories, MYSQL_NUM))
	{
		$id = $id_name[0];
		$subcategories[] = $id_name;
		$subcategories = array_merge($subcategories, get_subcategories($id));
	}
	return $subcategories;
}

function get_subcategory_ids($parent_id)
{
	$subcategories = array();
	$get_subcategories = mysql_query("select category_id from categories where parent_category=".dbquote($parent_id)) or mysql_fail();
	while(list($id) = mysql_fetch_array($get_subcategories, MYSQL_NUM))
	{
		$subcategories[] = $id;
		$subcategories = array_merge($subcategories, get_subcategories($id));
	}
	return $subcategories;
}

function category_id($category_name)
{
	list($category_id) = mysql_select_array("select category_id from categories where category_name=".dbquote($category_name));
	if(!$category_id)
	{
		die("Unable to find a category named '$category_name'");		
	}
	return $category_id;
}

function display_brands_in_category($category_name)
{
	$category_id = category_id($category_name);

	$cat_contents = brands_in_category($category_id);

	$subcategories = get_subcategories($category_id);
	$subcat_contents = array();
	if($subcategories)
	{
		foreach($subcategories as $id_name)
		{
			list($id, $name) = $id_name;
			$subcat_contents[$name] = brands_in_category($id);
		}
	}

	if($cat_contents)
	{
		sort($cat_contents);
            if(count($cat_contents) > 15)
            {
                $padded = count($cat_contents);
                if($padded % 2)
                    $padded++;
                    
                print '<table><tr>';
                print '<td valign="top"><ul>';
                foreach(array_slice($cat_contents, 0, $padded/2) as $brand_name)
		    {
			    print "<li>$brand_name</li>";
		    }
                print '</ul><td>';
                print '<td valign="top"><ul>';
                foreach(array_slice($cat_contents, $padded/2) as $brand_name)
		    {
			    print "<li>$brand_name</li>";
		    }
                print '</ul><td>';
                print '</table>';
            }
            else
            {
                print "<ul>";
		    foreach($cat_contents as $brand_name)
		    {
			    print "<li>$brand_name</li>";
		    }
		    print "</ul>";
            }	
	}
	if($subcategories)
	{
            echo '<table>';
            foreach($subcat_contents as $name => $contents)
		{
			print "<tr><td colspan=\"2\"><h3>$name</h3></td></tr>";
			if($contents)
			{
				sort($contents);
				$total = count($contents);

                    echo '<tr>';
                    if($total > 10)
                    {
                        if($total % 2)
                            $total++;

                        echo '<tr>';
                        echo '<td valign="top"><ul>';
                        foreach(array_slice($contents, 0, $total / 2) as $brand)
                        {
                            echo "<li>$brand</li>";
                        }
                        echo '</ul></td>';
                        echo '<td valign="top"><ul>';
                        foreach(array_slice($contents, $total / 2) as $brand)
                        {
                            echo "<li>$brand</li>";
                        }
                        echo '</ul></td>';
                        echo '</tr>';
                    }
                    else
                    {
                        echo '<td colspan="2"><ul>';
                        foreach($contents as $brand)
                        {
                            echo "<li>$brand</li>";
                        }
                        echo '</ul></td>';
                    }
			}
			else
			{
//					print "<h3>No brands in this subcategory.</h3>";
			}
                echo '</tr>';
		}
            echo '</table>';
	}
	if(!($cat_contents or $subcat_contents))
	{
		print "<h3>No brands in this category.</h3>";
	}
}

function brands_in_category2($category_id)
{
	return mysql_select_col("select brand_name2 from brands2 where in_category=".dbquote($category_id));
}

function display_brands_in_category2($category_name)
{
	$category_id = category_id($category_name);

	$cat_contents = brands_in_category2($category_id);

	$subcategories = get_subcategories($category_id);
	$subcat_contents = array();
	if($subcategories)
	{
		foreach($subcategories as $id_name)
		{
			list($id, $name) = $id_name;
			$subcat_contents[$name] = brands_in_category2($id);
		}
	}

	if($cat_contents)
	{
		sort($cat_contents);
            if(count($cat_contents) > 15)
            {
                $padded = count($cat_contents);
                if($padded % 2)
                    $padded++;
                    
                print '<table><tr>';
                print '<td valign="top"><ul>';
                foreach(array_slice($cat_contents, 0, $padded/2) as $brand_name2)
		    {
			    print "<li>$brand_name2</li>";
		    }
                print '</ul><td>';
                print '<td valign="top"><ul>';
                foreach(array_slice($cat_contents, $padded/2) as $brand_name2)
		    {
			    print "<li>$brand_name2</li>";
		    }
                print '</ul><td>';
                print '</table>';
            }
            else
            {
                print "<ul>";
		    foreach($cat_contents as $brand_name2)
		    {
			    print "<li>$brand_name2</li>";
		    }
		    print "</ul>";
            }	
	}
	if($subcategories)
	{
            echo '<table>';
            foreach($subcat_contents as $name => $contents)
		{
			print "<tr><td colspan=\"2\"><h3>$name</h3></td></tr>";
			if($contents)
			{
				sort($contents);
				$total = count($contents);

                    echo '<tr>';
                    if($total > 10)
                    {
                        if($total % 2)
                            $total++;

                        echo '<tr>';
                        echo '<td valign="top"><ul>';
                        foreach(array_slice($contents, 0, $total / 2) as $brand)
                        {
                            echo "<li>$brand</li>";
                        }
                        echo '</ul></td>';
                        echo '<td valign="top"><ul>';
                        foreach(array_slice($contents, $total / 2) as $brand)
                        {
                            echo "<li>$brand</li>";
                        }
                        echo '</ul></td>';
                        echo '</tr>';
                    }
                    else
                    {
                        echo '<td colspan="2"><ul>';
                        foreach($contents as $brand)
                        {
                            echo "<li>$brand</li>";
                        }
                        echo '</ul></td>';
                    }
			}
			else
			{
//					print "<h3>No brands in this subcategory.</h3>";
			}
                echo '</tr>';
		}
            echo '</table>';
	}
	if(!($cat_contents or $subcat_contents))
	{
		print "<h3>No brands in this category.</h3>";
	}
}
?>

 

To call all this up on the website, all I use is this:

<?php display_brands_in_category2("Spirits"); ?>

 

I don't get a "No brands in this category" like I should if there weren't any. I'm thinking the problem lies within the function import_brands_from_csv2, but I'm not sure. Oh, I should also add that the original brands stuff is working, but everything having to do with table brands2 is not. This is my attempt at duplication. If anyone could help, that'd be great! I'm getting pretty desperate. Thanks!

Link to comment
Share on other sites
Psycho Prolific Member

Psycho

Posted
Posted

Two things jump out at me:

 

First, on all of your INSERT queries you are not enclosing the value of the title in quotes (you are exiting the quoted sting to append the value).

mysql_query("insert into categories (category_name, parent_category)
             values (".dbquote($cat_name).",$category_id)") or mysql_fail();

 

Should look something like this

mysql_query("insert into categories (category_name, parent_category)
             values ('".dbquote($cat_name)."',$category_id)") or mysql_fail();

 

Also, where is the function dbquote()? It may have a flaw in it.

Link to comment
Share on other sites
zeitgeber Newbie

zeitgeber

Posted
  • Author
Posted

I am a lamer apparently...the master inc has some extra functions that I forgot about.

 

function dbquote($val)
{
  	if (get_magic_quotes_gpc())
	{
    	$val = stripslashes($val);
  	}
  	if (!is_numeric($val))
	{
    	$val = "'".mysql_real_escape_string($val)."'";
  	}
  	return $val;
}

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.