CMS Logic

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

CMS Logic

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Two options
  1. 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.
    Jim_Bo
    Forum Contributor
    Posts: 390
    Joined: Sat Oct 02, 2004 3:04 pm

    Post 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
    User avatar
    NightFox
    Forum Newbie
    Posts: 13
    Joined: Sat Mar 11, 2006 3:20 am

    Post 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.
    Jim_Bo
    Forum Contributor
    Posts: 390
    Joined: Sat Oct 02, 2004 3:04 pm

    Post 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
    User avatar
    s.dot
    Tranquility In Moderation
    Posts: 5001
    Joined: Sun Feb 06, 2005 7:18 pm
    Location: Indiana

    Post 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
    }
    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.
    User avatar
    feyd
    Neighborhood Spidermoddy
    Posts: 31559
    Joined: Mon Mar 29, 2004 3:24 pm
    Location: Bothell, Washington, USA

    Post by feyd »

    I suggested two tables, not three.
    Jim_Bo
    Forum Contributor
    Posts: 390
    Joined: Sat Oct 02, 2004 3:04 pm

    Post 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
    User avatar
    feyd
    Neighborhood Spidermoddy
    Posts: 31559
    Joined: Mon Mar 29, 2004 3:24 pm
    Location: Bothell, Washington, USA

    Post by feyd »

    Table A
    ID, name, description

    Table B
    ID, Table A ID, Price Break, Price
    User avatar
    NightFox
    Forum Newbie
    Posts: 13
    Joined: Sat Mar 11, 2006 3:20 am

    Post by NightFox »

    I still believe explode/implode is the way to go :)
    Jim_Bo
    Forum Contributor
    Posts: 390
    Joined: Sat Oct 02, 2004 3:04 pm

    Post 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
    User avatar
    feyd
    Neighborhood Spidermoddy
    Posts: 31559
    Joined: Mon Mar 29, 2004 3:24 pm
    Location: Bothell, Washington, USA

    Post 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.
    User avatar
    NightFox
    Forum Newbie
    Posts: 13
    Joined: Sat Mar 11, 2006 3:20 am

    Post 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.
    User avatar
    John Cartwright
    Site Admin
    Posts: 11470
    Joined: Tue Dec 23, 2003 2:10 am
    Location: Toronto
    Contact:

    Post 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..
    noginn
    Forum Newbie
    Posts: 4
    Joined: Fri Jul 15, 2005 3:58 pm

    Post by noginn »

    3 separate tables seems to be the logical choice. Would probably be faster.
    Post Reply