php mysql datafeed help

[agua]

OK - I'm having a go at building a site from a datafeed using php and mysql.

The datafeed came in one file with the following field headers:

id, sku, Brand, category, name, description, price, SalePrice, OnSale, buyurl, keywords, imageurl

So I built 1 mysql table using those values.

I am fairly new to programming myself (always using premade solutions) so I am working through this data with Kevin Yanks "building data websites" book.

What I am looking to do is have the categories displayed on the left and the content on the right (but theres another question - displaying sub-cats split with a "|"?)

Is it possible for me to produce a datafeed site from this one table, or should I split it as the book says?

The datafeed has 7500 entries so I was hoping on not doing any edits in the database not the feed I was also planning to make this a quick project but one which I can reuse for other datafeeds.

[LuckyShima]

Is it possible for me to produce a datafeed site from this one table, or should I split it as the book says?

It is not a large table. What do you mean the book says to split it?

[agua]

Well its says to separate categories from articles - Keep them in separate tables and cross reference (relationship database!!)

[Kate]

You can do it the way it is currently set up. In fact it would be easier than splitting the table.

[MarkB]

For a quick site, leave it as-is.

For an advanced one, create an extra category table (with fields 'id', 'categoryname'), and then just use the relevant category_id in the product table (where you have 'category' now). This way, you can expand the category table to include descriptions, meta tags, and be able to keep the management of that seperate from the product table.

I hope that makes sense!

[agua]

OK - I'm making progress - but I'm stuck on the categories

They are constructed like this:

Shoes|Mens|Casual|Comfort
Shoes|Kids|Girls|Infants
Shoes|Womens|Sandals|Dress
Accessories|Shoe Care|Shoes Trees

The code I have so far for showing the categories is

$cats = @mysql_query('SELECT id, category FROM spa');
if (!$cats) {
exit('<p>Error retrieving categories from database!<br />' .
'Error: ' . mysql_error() . '</p>');
}

while ($cat = mysql_fetch_array($cats)) {
$id = $cat['id'];
$category = htmlspecialchars($cat['category']);

echo "<li><a href='index.php?id=$id'>$category</a></li>";
}

But that just displays all of the categories - I wanted to have 2 main cats (Shoes and Acessories) and all the rest as levels of sub cats up to 4 deep.

I have read about using a separate cat table for this and having a catID and Parent ID column - would this be the best way here or is there a way I can use what I have already?

[MarkB]

I would explode those category listings out into:

id, categoryname, parentcategory_id

So 'Shoes' would have a parentcategory_id of '0'. 'Kids' would have a parentcategory_id equivalent of 'Shoes' id, and so on.

Then for the products, again just list their category_id of whatever the lowest level category is relevant (such as 'Infants').

Does that make sense? :-S

[Snowballer]

What I do is have parent and child categories, to an unlimited level. So each category has a parentID. Its also an easy way to build breadcrumbs etc.

I just created a new site actually: http://www.rc-car-planes.com

[Chris]

What I do is this.

1. Insert the enter datafeed into an import table. This is less stressful on the db server.

The SQL is something like this:

$sql = "LOAD DATA INFILE '/home/admin/aftp.linksynergy.com/$list[mid]_1193364_mp.txt' INTO TABLE linkshare.import_table FIELDS TERMINATED BY '|';";


Then, in my products table, I have 4 fields for categories (cat1, cat2, cat3, cat4). And my categories table is like catid, catname, parent.

I explode the field in my important table to get the 4 categories, then I insert them into the products table in textual format. I know, thats not fully normalized, but in this case where the products table is completely deleted and remade on a weekly basis (from the import table) I'm not really worried about normalization. Plus, normalization would require more queries, and when you're doing millions of products thats alot.

So anyways. I then build the category table from the products table.

SELECT distinct(cat1), cat2, cat3, cat4, mid FROM products2

(manipulation and formatting)

Insert blah blah

I have unique indexes setup in the DB to prevent any duplicates.

Then, on the frontend, category pages are referenced like so: domain.com/category/12897 but the code uses the textual name of the category (plus it's parents) and the merchant id (mid) to query the products DB for what products to list in that DB.


All told, this is really fricken complicated. Some of the most complex SQL queries I've ever wrote were for my datafeed system.

[agua]

nm... I'm confused now

I already have the mysql table made and data inserted so - where and when do I 'explode' the data?

Do I remake the table with the data exploded, or do I explode the data whilst it is in the table.

I'm just going off to read about the explode function

[Snowballer]

explode means spliting the string deliminating by a '|' right?

[r2d2]

explode means spliting the string deliminating by a '|' right?

Indeed is does, there's even an appropriately named function.

[Snowballer]

hehe, yeah in C# its just called Split which is an even better name since that is what PHP uses to describe explode's functionality "explode -- Split a string by string"

although explode is more exciting i'd say.