Page 1 of 1
CMS Logic
Posted: Fri Mar 10, 2006 11:40 pm
by Jim_Bo
Hi,
Im trying to come up with the best way to store data in a databse for products. I will be using a CMS system to add/edit/delete the records.
The products will contain the the following fields
| id | name | description | PB | price |
Now the issue is that the PB & price fields could contain upto 3 records per item ie:
PB = PB6, PB9, PB14
Price = 12.00, 14.00, 19.00
so when called out using a loop I want it to display the name, description then list the PB's & prices one under another for that record, then move onto the next record etc etc
I was thinking maybe storing them each within the corosponding field using something like "," to seperate them, then upon calling the records out explode those 2 fields using "explode(',',...."
But im thinking that when loop through the items when calling them back this will mess up the structure?
What would be a pluasble way to go about this situation?
Thanks
Posted: Fri Mar 10, 2006 11:55 pm
by feyd
Two options
- Separate table for PB (price break?) and price
Pros
More flexible; allows for any number of price breaks
Cons
Slightly more complex query structuring
- Separate fields.
Pros
More simple; queries and coding can, overall, be more simple
Cons
Limiting; Finite number of price breaks
Requires "special case" coding to know which field to update in the table.
Posted: Sat Mar 11, 2006 2:36 am
by Jim_Bo
Hmmz,
I guess 3 seperate tables + joins it is .. altho 3 tables seems extreme for such a small project.
Does that mean that using a seperator and explode wont do the job at all?
Thanks
Posted: Sat Mar 11, 2006 3:50 am
by NightFox
I wouldn't create extra tables, I'd just use the explode() and implode() functions. And you could have unlimited amounts.
For example:
Code: Select all
$prices[] = "15.99"; (sets $price[0] = 15.99)
$prices[] = "12.99"; (sets $price[1] = 12.99)
$prices[] = "9.99"; (sets $price[2] = 9.99)
$insert_prices = implode("|",$prices); //$insert_prices now = "15.99|12.99|9.99"
$pb[] = 14;
$pb[] = 1;
$pb[] = 401;
$insert_pb = implode("|",$pb); //$insert_pb now = "14|1|401"
$query = dbquery("INSERT INTO XXTABLEXX blah blah blah"); //(using PHP-Fusion defined db query function)
Now, to get the data, query the database and we'll say the query places the prices into $qprices and the PB into $qpb:
Code: Select all
$prices = explode("|",$qprices);
$s_pb = explode("|",$qbp);
foreach ($s_pb as $key => $value) {
$pb[$key] = "PB".$value; //automatically adds the PB prefix to the numbers to keep your database smaller (every bit helps;) )
}
So now you have all your data for each of those two fields in a simple variable.
Posted: Sat Mar 11, 2006 5:10 am
by Jim_Bo
Hi,
Yer thats what I was thinking, Just wasnt sure if using explode within the query loop would keep the structure of each item_id when echoing them out of the db .. ?
cheers
Posted: Sat Mar 11, 2006 5:51 am
by s.dot
explode won't mess with any database value, it simply breaks the string up into pieces
Code: Select all
$result = mysql_query("SELECT `str` FROM `table` LIMIT 10");
echo mysql_result($result,0); // should pring something like a|b|c|d|e that it pulled from the database
while($array = mysql_fetch_assoc($result)){
$pieces = explode("|",$array['str']);
echo $pieces[0]; // a
echo $pieces[1]; // b
echo $pieces[2]; // c
}
Posted: Sat Mar 11, 2006 8:53 am
by feyd
I suggested two tables, not three.
Posted: Sat Mar 11, 2006 2:10 pm
by Jim_Bo
Hi,
Im not sure how 2 tables will work as 2 fields will have up to 3 values ..
PB and price
So you say hold both values in the second table?
Thanks
Posted: Sat Mar 11, 2006 2:18 pm
by feyd
Table A
ID, name, description
Table B
ID, Table A ID, Price Break, Price
Posted: Sat Mar 11, 2006 6:21 pm
by NightFox
I still believe explode/implode is the way to go

Posted: Sat Mar 11, 2006 7:20 pm
by Jim_Bo
Hi,
the way i see it, feyds way will still need to be imploded and exploded to acheive the job as well?
Thanks
Posted: Sat Mar 11, 2006 7:27 pm
by feyd
Not at all. Each price break-price combo has their own record in Table B. Table B references the Table A record they apply to. This allows any number of price breaks, be it one or 500. It's a one-to-many relationship between Table A and Table B.
Posted: Sun Mar 12, 2006 1:23 am
by NightFox
ya... my philosophy is, the less bulk in your db, the better. That's why I'd prefer ex/implode functions.
And it will also be useful when editing the db manually because all the data will be in one row, not two tables with multiple rows.
Posted: Sun Mar 12, 2006 9:08 am
by John Cartwright
Databases are designed to handle large amount of data, somestimes millions of rows of data so a properly designed database is much more portable, logical, and is likely faster than having a pull your data (unformatted) and do the formatting yourself..
Posted: Sun Mar 12, 2006 9:13 am
by noginn
3 separate tables seems to be the logical choice. Would probably be faster.