CODEWITHSUNDEEP
phpmysql
tyo
tyo

Reputation: 83

How to select multiple values from one MySQL table column with PHP

I have a MySQL table name data_car. The table data looks like this:

id   name      car
1    john      BMW, Toyota 
2    terry     Nixan, Toyota

My question how to select specific data from car. For example I have a combobox title car containing Toyota, BMW, Nissan, and I select Toyota so "name" person containing the value Toyota will show in my program.

The output I want is as follows:

name: john      car:toyota    payment:$1000

But unfortunately when I select toyota in the combobox the result is always BMW, and always shows up like this:

name : john    car:toyota,BMW   payment:$1000

My question is can I just select the toyota value from that column?

Upvotes: 0

Views: 1665

Answers (4)

Babitha
Babitha

Reputation: 420

MySQL 8 update:

SELECT name, REGEXP_SUBSTR(car, 'Toyota') as carname FROM data_car

Upvotes: 0

jimmy8ball
jimmy8ball

Reputation: 756

If you wanted to do this as a select, and then join on to your costs table then you can split the string using SQL, you then put an outer reference and join onto costs. This way you could avoid recreating tables, although as stated above this would be a preferred method.

eg:

 SELECT id, name,
 PARSENAME(REPLACE(Split.a.value(',', 'VARCHAR(max)'),'-','.'),1) 'car' 
 FROM  
 (
 SELECT id, name,
 CAST ('<M>' + REPLACE([car], ',', '</M><M>') + '</M>' AS XML) AS Data 
 FROM   [table] 
 ) AS A 
 CROSS APPLY Data.nodes ('/M') AS Split(a)

Upvotes: 0

alanlittle
alanlittle

Reputation: 470

It would be better if you re-design your database, something like this:

CREATE TABLE drivers (
  driverid      int(11)       UNSIGNED NOT NULL auto_increment,
  driver        varchar(64),
  PRIMARY KEY   (driverid)
);

CREATE TABLE cars (
  carid         int(11)       UNSIGNED NOT NULL auto_increment,
  car           varchar(16),
  PRIMARY KEY   (carid)
);

CREATE TABLE driver_car (
  driverid      int(11),
  carid         int(11)
);

Upvotes: 0

Rayann Nayran
Rayann Nayran

Reputation: 1135

You could perform it with PHP like this:

foreach ($values as $key => $value) {
    $cars = explode(',', $value['car']);
    foreach ($cars as $car) {
        switch ($car) {
            case 'toyota':
                $values[$key]['car'] = $car;
                break;

            default:
                # code...
                break;
        }
    }
}

I recommend you remodeling your database, transforming this data in a many-to-many relationship.

See also:

Upvotes: 1

Related Questions