How do you do this?

alanzhao

Hi All,

I have a CSV file with a column of data of category breadcrumb delimited by comma. What I want to do is query the database and find out the category ID.

The table structure is like this:

id
parent_id
category_name

sample data:

CSV:
Shoes,Hiking,Nike

MySQL
id, parent_id, category_name
1, 0, Shoes
2, 1, Hiking
3, 2, Nike

If you've done something like this before please share.

Thanks!

Shrike

Unless there is some hidden meaning here, use [MAN]fgetcsv[/MAN] to parse the CSV, then run a sql query for each category name. If the input file is especially huge you could write one long query using the sql IN() construct.

alanzhao

Shrike;10918809 wrote:
Unless there is some hidden meaning here, use [MAN]fgetcsv[/MAN] to parse the CSV, then run a sql query for each category name. If the input file is especially huge you could write one long query using the sql IN() construct.

I am hoping to have a solution using just a query. How do you do this using sql IN() since the category relation is hierarchical?

Thanks!

Shrike

Is the hierarchy even relevant here? You just wanted to turn a category name into an ID. Of course I am assuming the category name is unique. If it's not then you have a problem right away since if you have a category with the same name at different points in the tree you will get ambiguous results.

alanzhao

In my case, the full breadcrumb will be the unique identifier. I assume there must be some sort of recursive methods either in PHP or MySQL can achieve this.

bradgrafelman

Well, you could do something like:

$data = 'Shoes,Hiking,Nike'; // no need for fgetcsv; just file() to read each line

$data = explode(',', $data);
$cat = array();
$query = "SELECT id, category_name FROM myTable WHERE category_name IN ('" . implode("','", $data) . "')";
$exec = mysql_query($query);

while($row = mysql_fetch_assoc($exec))
    $cat[$row['category_name']] = $row['id'];

// now cat looks like: array('Shoes' => 1, etc.)
// you can build a breadcrumb by looping through $data and grabbing the
// appropriate cat. id via $cat[$data[$i]] (if using a for($i=0,...) loop, for example)