Storing/Retrieving Array

[TheBrandon]

Hello all,

 

I'm currently working on a project and it is the first time I have had to store/retrieve an array with PHP and MySQL. Basically its a website that has a list of discounts/coupons on it. People can register and submit their discounts and such. I am storing an array for who is eligible for the discount, as well as which categories the discount falls under.

 

I did some googling and there seems to be tons of thoughts on the best way to do this. Some say to use implode/explode, some say to serialize/unserialize, I was just wondering if there is a common, concrete way to do this?

 

I will need to be able to search the array for its contents. So if children are eligible for the discount, I need to be able to store that in a database, retrieve it later, and search it for only "Children" so I can display the discounts available only to children.

 

If someone could please advise the best way to do this and post some examples, it would be greatly appreciated. I learn best with examples that are explained properly.

 

 

[Psycho]

I have some advice - DON'T STORE ARRAYS. Once you store an array of values you lose a ton of functionality. You stated above you may need to search the array. However, by storing the array into a single field you lose the ability to easily search the records effectively. Youwould either need to use regular expressions in the query or you would have to query all the records and then iterrate through each one to search using PHP.

 

The better method is to store the multiple values in a separate table using a foreign key back tothe parent record. This is the whole point of a relatinal database.

 

For example, if you have a user with multiple coupons you would have a single entry in the USER table. Then in the COUPONS table you would have multiple records - one for each coupon - and each record would have a field to identify the user record they are associated with.

[AbraCadaver]

Wow, two in a row:  http://www.phpfreaks.com/forums/index.php/topic,307716.0.html

 

The way you describe it, you shouldn't be storing the array but you should probably use related tables.  Without knowing what you're doing exactly, here's a rough example:

 

table: coupons
id   name   discount

table: categories
id   name

table: coupons_categories
coupon_id   category_id

table:  coupons_users
user_id   coupon_id

 

Often times when people want to store an array in a database it is much better to store the array data as related data.

[TheBrandon]

Okay, so you're saying just make a database to contain the relations. Like an eligibility table that contains the eligibility option and the discount ID basically.

 

Here's my current database:

-- phpMyAdmin SQL Dump
-- version 2.9.1.1
-- http://www.phpmyadmin.net
-- 
-- Host: localhost
-- Generation Time: Aug 18, 2010 at 04:15 PM
-- Server version: 5.0.27
-- PHP Version: 5.2.0
-- 
-- Database: `discount`
-- 

-- --------------------------------------------------------

-- 
-- Table structure for table `categories`
-- 

DROP TABLE IF EXISTS `categories`;
CREATE TABLE `categories` (
  `id` smallint(4) unsigned NOT NULL auto_increment,
  `category_name` varchar(80) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `category_ads`
-- 

DROP TABLE IF EXISTS `category_ads`;
CREATE TABLE `category_ads` (
  `cat_id` smallint(4) NOT NULL,
  `vendor_id` smallint(4) unsigned NOT NULL,
  `filename` varchar(80) NOT NULL,
  `priority` varchar(80) NOT NULL,
  `url` varchar(80) NOT NULL,
  PRIMARY KEY  (`cat_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

-- 
-- Table structure for table `category_relations`
-- 

DROP TABLE IF EXISTS `category_relations`;
CREATE TABLE `category_relations` (
  `id` int(10) NOT NULL,
  `member_of` int(10) unsigned NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

-- 
-- Table structure for table `discounts`
-- 

DROP TABLE IF EXISTS `discounts`;
CREATE TABLE `discounts` (
  `id` smallint(5) NOT NULL auto_increment,
  `redeem` tinyint(3) NOT NULL,
  `discount_title` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL,
  `short_description` tinytext character set utf8 collate utf8_unicode_ci NOT NULL,
  `eligibility` blob NOT NULL,
  `url` varchar(100) character set utf8 collate utf8_unicode_ci default NULL,
  `coupon_code` varchar(75) character set utf8 collate utf8_unicode_ci default NULL,
  `start_date` datetime default NULL,
  `end_date` datetime default NULL,
  `long_description` text character set utf8 collate utf8_unicode_ci NOT NULL,
  `logo` varchar(255) character set utf8 collate utf8_unicode_ci default NULL,
  `business_name` varchar(75) character set utf8 collate utf8_unicode_ci NOT NULL,
  `store_location_street` varchar(75) character set utf8 collate utf8_unicode_ci default NULL,
  `store_location_city` varchar(75) character set utf8 collate utf8_unicode_ci default NULL,
  `store_location_state` char(2) character set utf8 collate utf8_unicode_ci default NULL,
  `store_location_zip` varchar(10) character set utf8 collate utf8_unicode_ci default NULL,
  `categories` blob NOT NULL,
  `level` tinyint(5) NOT NULL,
  `active` tinyint(1) NOT NULL,
  `user_id` varchar(2555) character set utf8 collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `level` (`level`,`active`),
  KEY `user_id` (`user_id`(333))
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `front_page_ads`
-- 

DROP TABLE IF EXISTS `front_page_ads`;
CREATE TABLE `front_page_ads` (
  `id` smallint(4) NOT NULL auto_increment,
  `vendor_id` smallint(4) unsigned NOT NULL,
  `filename` varchar(80) NOT NULL,
  `priority` smallint(4) NOT NULL,
  `url` varchar(80) NOT NULL,
  `ad_type` tinyint(5) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `users`
-- 

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` int( NOT NULL auto_increment,
  `username` varchar(11) NOT NULL,
  `password` varchar(32) NOT NULL,
  `level` int( NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;

 

I did the same thing with the category table.

 

I guess I was worried that eventually, I may have more eligibility options than I currently have, and I was hoping to make it as easy to maintain from a user standpoint but looking at that logic now, it would still be able to be managed by a user by simple inserting a new row.

 

Thanks for the help, guys.

 

So, just to explain it in words so I understand what it is I should do:

• Add a table for Eligibility options. Table should contain the text for the eligibility label (Children) and an ID
  
• Create a Discounts_To_Eligibility table that contains the ID for the Discount and the ID for the table
  
• Do joins based on the Discount ID to be able to pull specific eligibility options
  
• When a new discount is added, it is submitted as an array. (Eligibility option 1, 5, 7, 8, and 9). Loop over this submitted array and for every entry in the array, create a new row in the Discounts_To_Eligibility table
  

 

That's basically it right? Did I forget or miss anything?

[Psycho]

I'm not sure I totally understand your application or what the structure should be, but you explanation "seems" logical.

 

However:

• When a new discount is added, it is submitted as an array. (Eligibility option 1, 5, 7, 8, and 9). Loop over this submitted array and for every entry in the array, create a new row in the Discounts_To_Eligibility table
  

 

There is no need to "loop over" the array and run separate queries. You should never run queries within loops if not absolutely necessary. And in this case it definitely is not. Just use the array to create the single query statement. Here is an example (assuming the records just need foreign keys for the dicount and eligibility records.:

 

$discountID = $_POST['discount_id'];
$eligibilityAry = $_POST['eligibility_options'];

//Create an array of the records to be added
$valuesAry = array();
foreach($eligibilityAry as $eligOption)
{
    $valuesAry[] = "('{$discountID}', '{$eligOption}')";
}
$valuesStr = implode(', ', $valuesAry);

$query = "INSERT INTO `Discounts_To_Eligibility` VALUES " . $valuesStr;
$result = mysql_query($query);

 

The query would look something like this (added line breaks for clarity):

INSERT INTO `Discounts_To_Eligibility`
VALUES
  ('9', '5'), ('9', '16'), ('9', '23'), ('9', '33')