Need help with mysql data format

[python01]

I had website build by someone else and he encoded the payment info in the following format:

payment_data  card_first_name + card_last_name + card_adres + card_city + card_province + card_postal_code + card_nomber + card_month + card_year +card_cvv2 OR email

 

When I query this information as a regular text data type I get the following:

 

John|Doe|222 Thames Way|City|ON|X0X0X0|4512121214147878|/|456

 

So each value is separated by vertical line, what do I need to do to have them separated as individual values?

[anatak]

use explode()
 

$string = 'John|Doe|222 Thames Way|City|ON|X0X0X0|4512121214147878|/|456';

$array = explode("|", $string);

 

http://jp2.php.net/manual/en/function.explode.php

[python01]

use explode()

 

$string = 'John|Doe|222 Thames Way|City|ON|X0X0X0|4512121214147878|/|456';

$array = explode("|", $string);

 

http://jp2.php.net/manual/en/function.explode.php

Thanks for the quick reply. Is there a name to this type of encoding of DB?

[boompa]

OMG, are you seriously storing people's credit card numbers??? AND THEIR CVV???

 

That is seriously, seriously wrong.

 

 

 

Is there a name to this type of encoding of DB?

Yes, it's called utter incompetence.

Edited March 15, 2014 by boompa

[therocker]

OMG, are you seriously storing people's credit card numbers??? AND THEIR CVV???

 

That is seriously, seriously wrong.

 

 

 

Yes, it's called utter incompetence.

If he wants to store people's credit cards and what not, it's up to him. He has to keep things confidential if he wants to store sensitive information like that.

What if he was making an online shop? What if he was storing client information so he can show them to his clients in a later time?

You can't just say "Hey, I deem this idea or this way wrong so I want you to stop because I'm working on one myself."

A forum is there to help others learn and code better with the codes that they have provided, it is NOT there to be critiqued.

If a person wants their codes critiqued, it would be in the title "CRITIQUE MY CODE". You remind me of someone who's on a dying forum on a different website. He basically called every single person's code crap even though he knows that the people have just signed up on their website.

And I wonder why the forum community on the website he uses is dying.

 

Now back to the OP, the technique is called Exploding and Imploding. When you explode, you're basically splitting the data into arrays. When you implode, you're combining the data back.

The "vertical lines" are actually called pipelines. A lot of suggestions would tell you to just use new rows and columns because storing data in one column isn't good.

You're better off having a table like

CREATE TABLE IF NOT EXISTS `credential` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `street` varchar(255) NOT NULL,
  `city` varchar(255) NOT NULL,
  `province` varchar(255) NOT NULL,
  `postal_code` int(11) NOT NULL,
  `card_nomber` text NOT NULL,
  `card_cvv2` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

INSERT INTO `credential` (`id`, `first_name`, `last_name`, `street`, `city`, `province`, `postal_code`, `card_nomber`, `card_cvv2`) VALUES
(1, 'John', 'Doe', '222 Thames Way', 'City', 'ON', 123456, '4512121214147878', 456);

This is much easier because you can specify which column or row you want to select.

Example: You have about 10 customers, each with different names. But you only want John Doe's, you can use SQL and do something like

$first_name = "John";
$last_name = "Doe";

$query = mysqli_query("SELECT first_name, last_name FROM credential WHERE first_name = '$first_name' AND last_name = '$last_name' LIMIT 1");