CMS Logic
Moderator: General Moderators
CMS Logic
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
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
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
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.
I wouldn't create extra tables, I'd just use the explode() and implode() functions. And you could have unlimited amounts.
For example:
Now, to get the data, query the database and we'll say the query places the prices into $qprices and the PB into $qpb:
So now you have all your data for each of those two fields in a simple variable.
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)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;) )
}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
}Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact: