Accessing array data from mysql database and php

Question

I have two mysql tables with information:

One table stores page information like its name and location as well as an array column to store what widgets to show per page stored in array format: 1,2,3,4

The other table is the actual widget table with the widget info and the id as its identifier ex: 1,2,3,4

I need to:

Get array data from the page table
Loop through that array and display content from the widget table.

Ive been working with foreach statements:

The url for each page follows this format index.php?page=thispage&route=thisroute thus the $_GET['page']

$page = $_GET['page'];
$order = "SELECT * FROM pages where name='$page'";
  $result = mysql_query($order);
    while($row = mysql_fetch_array($result)){
            $widget = array();
            $widget .= $row[3];
        }

        print_r($widget);

        foreach($widget as $w) {
                    $order = "SELECT * FROM menu where id='$w'";
                    $result = mysql_query($order);
                    while($row = mysql_fetch_array($result)){
                    print $row[1];
                    }
            }

UPDATE: Figured out a solution that works for me.

The reason behind keeping the comma separated values is because i could have one widget per page or 15 widgets per page and each widget can be added on the fly. And from the administration side i want to be able to select which widgets each page can have.

When keeping the data in comma separated values where all the data is in ONE column "1,2,3,4".

Ill just stick to exploding the data into an array and looping through each value of the array:

$page = $_GET['page'];
$order = "SELECT * FROM pages where name='$page'"; //select widgets to show on page
  $result = mysql_query($order);
    while($row = mysql_fetch_array($result)){
            $widget = $row[3]; 
            $string = preg_replace('/\,$/', '', $widget); //remove trailing comma
            $array = explode(', ', $string);

            foreach($array as $value) //loop over values
                {
                    $order = "SELECT * FROM widget where id='$value'";
                    $result = mysql_query($order);
                    while($row = mysql_fetch_array($result)){
                        print $row[1]; //print data: this will be formated to the specific divs set up.
                }

            }
    }

There are three rules for database design: normalise, normalise, normalise — Mark Baker, Feb 18 '13 at 16:50
Show what you've attempted so far, and we'll try to help fix that. but we're not going to do your job for you. — Marc B, Feb 18 '13 at 16:51
I suggest you design the database schema properly or use [PHP's `serialize()` function](http://www.php.net/manual/en/function.serialize.php). — EM-Creations, Feb 18 '13 at 16:51
That's not a smart way to design your database. Furthermore, what have you tried already? Don't expect us to dish out complete solutions, without showing some effort. — Decent Dabbler, Feb 18 '13 at 16:51
Basically, you don't want to store comma-separated values in a field, if you need those individual values as arguments for joining with other data. You'd rather want to store them in a separate table, where each row represents an [`m:n`-relationship](http://stackoverflow.com/q/3397349/165154) (in this case the relationship between a page and a widget). There's a lot more to normalization, but this part was particularly relevant to your question. — Decent Dabbler, Feb 18 '13 at 17:04

score 0 · Accepted Answer · answered Feb 18 '13 at 16:58

Get array data from the page table: SELECT array_column_name FROM table_store_page WHERE id=your_id (use this sql to fetch the array in)
Execute the SQL statement in php maybe using mysql_query

Anyway, STORING array/comma-separated values in Database is NEVER RECOMMENDED. You can try normalizing your Database.

Accessing array data from mysql database and php

1 Answers1