converting columns data to rows data

alirezaok

i
i have a table. this picture is a part of my table:
http://www.khodabakhsh.info/record.jpg

also this is sql of this table:

CREATE TABLE `formdata` (
  `id` int(11) NOT NULL auto_increment,
  `field_name` text collate utf8_persian_ci NOT NULL,
  `value` text collate utf8_persian_ci NOT NULL,
  `record` bigint(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci AUTO_INCREMENT=7 ;


INSERT INTO `formdata` VALUES (1, 'fname', 'jorj', 1);
INSERT INTO `formdata` VALUES (2, 'lname', 'bush', 1);
INSERT INTO `formdata` VALUES (3, 'fname', 'oliver', 2);
INSERT INTO `formdata` VALUES (4, 'lname', 'kinton', 2);
INSERT INTO `formdata` VALUES (5, 'fname', 'larry', 3);
INSERT INTO `formdata` VALUES (6, 'lname', 'bush', 3);

now i want to select data where value for eaxmple is 'bush' and converting columns data to rows data. result must like this:

record   fname   lname
1        jorj     bush
3        larry    bush

is it a way for doing this?

johanafm

Convert from column to row data? All I see is that you'd like to output rows with row data.

So, do you want plain output of

SELECT * FROM formdata WHERE lname like 'bush'

or are you really aiming for this kind of transformation of the data from the above query

1	Laura				1		2		3
2	George		=>		Laura		George		Barbara
3	Barbara

CoderDan

Well, If you can ascertain what fields you'll be storing beforehand, I'd reccomend using them as the field names, so you can have one mysql entry for 1 form submission
else you'll need to make some ugly queries to pull it out, which will require you knowing which form field names you want prior to running the query, or you can do it with multiple queries, or one query and then use php to grind the results afterwards.

CoderDan

johanafm;10923954 wrote:
Convert from column to row data? All I see is that you'd like to output rows with row data.

So, do you want plain output of
SELECT * FROM formdata WHERE lname like 'bush'
or are you really aiming for this kind of transformation of the data from the above query
1	Laura				1		2		3
2	George		=>		Laura		George		Barbara
3	Barbara

I think he wants to put in the filter of 'bush' and have it find the record identifiers for last name of bush, then match up the same record id's to get the first name (and presumably other fields in future) and return rows with ( record id, first name, last name )

alirezaok

excusme for my english language. CoderDan uderstands my goal right.

i need to some ugly queris.

i can find filed names with:

SELECT DISTINCT `field_name` FROM `formdata`

then i dont know what do i do with sql statment which have low process to reach that goal.

johanafm

CoderDan;10923956 wrote:
I think he wants to put in the filter of 'bush' and have it find the record identifiers for last name of bush, then match up the same record id's to get the first name (and presumably other fields in future) and return rows with ( record id, first name, last name )

I'm still not sure I follow. If he has a last name (e.g. originally retrieved by SELECT DISTINCT lname FROM formdata), in a post request from a select element or in a get request from clicking a link, then just grab it all by

SELECT * FROM formdata where lname like 'post data lname'

Or originally retrieve both id and lname

SELECT id, lname FROM formdata WHERE ...

and then...
<select>
	<option value="id from the above query">lname from the above query</option>

Either way, if lname is indexed, you might as well use that as the id.

Or if it's during the same request, just get all data as above, then use whatever you need whenever you need it.

CoderDan

he has 2 rows per record at present. and he wants to formulate them into one row, with field names as one value and value as another value, for each row.
ie with the data: ( 'field1' => 'field1name', 'field2' => 'field1value' ), ( 'field1' => 'field2name', field2' => 'field2value ) getting turned into=>
( 'field1name' => field1value', 'field2name', 'field2value' ) as the result of a mysql_fetch_assoc

tave

Why are you keeping the last name and first name in the same field ?
either way, i think this gets you what you want

select a.record as record, a.value as fname, b.value as lname
from formdata as a inner join formdata as b using(record)
where  a.value != b.value and b.value like "%bush%"

CoderDan

tave;10923988 wrote:
Why are you keeping the last name and first name in the same field ?
either way, i think this gets you what you want
select a.record as record, a.value as fname, b.value as lname
from formdata as a inner join formdata as b using(record)
where  a.value != b.value and b.value like "%bush%"

That works for his current data set, but there are cases where people have the same first and last name, or where some have a surname that others have as a first name, etc, also it's not very extensible as more fields are added, all of which is why i reccomend either changing the table structure, or using php to convert the data.

johanafm

I agree that the design is bad, but a.value != b.value could be changed to a.id != b.id to deal with that issue.
Also, at a quick glance it ought to be select DISTINCT a.record, or each entry for a record is matched with all of the others, yielding a result size per record with the same number of rows as there are records.

CoderDan

oh, I know there are ways of getting sets of data for a record, but I don't see how you'll get the field names out of the records to return an assoc array like he wants as mentioned in an earlier post of mine. Even if you manage to get a set of fields for a record id, how do you know which is first name, which is last name, and which are any other fields, etc. if "Marshal George' and 'George Marshal' both sign up, how can you get just one or the other?

johanafm

By not only pulling the fieldvalues, but also the fieldnames. Ah, and no. He needs more work after that. The query does nothing more than get the data together. He will have to parse it in php.

tave

In all honesty he really needs to redesign the database tables.
While it is possible to "work" with the table as is, it would be way simpler to just change the table.
For example, what if down the road he needs to acommodate for a person having multiple last names? or multiple first names? This is not a usual scenario, but what if the application requires him to do so ?

alirezaok

please be notice i can not and i dont have access to redesign table.

CoderDan

alirezaok;10924037 wrote:
please be notice i can not and i dont have access to redesign table.

Seems rather odd that such a structure was created to begin with. However. We need to use php to grind the data after it is recovered.

Not sure how to do this in 1 query off the top of my head, although it's probably possible, but as a breakdown:
SELECT record FROM formdata WHERE field_name = 'lname' AND value = 'Bush';
that gets you the record id(s), then you'd need
SELECT * FROM formdata WHERE record IN ( $record_id );
The thing is how to combine this into one query, which you'd want to order by record, then when you iterate through the results you could assign records into a php array.
If noone is quick on the query needed I can probably figure it out later, when I have more time.

johanafm

It's possible to get the assoc array by using one join per field_name value with minor modifications to the first posted query if there are no duplicate field_names per record

If only fname and lname are of interest:

SELECT DISTINCT a.record AS record, a.value AS fname, b.value AS lname
FROM formdata AS a
LEFT JOIN formdata AS b ON a.record = b.record AND a.field_name = 'fname' AND b.field_name = 'lname'
WHERE b.value LIKE "%bush%";

And it's not too hard to add more field_name values

SELECT a.record AS record, a.value AS fname, b.value AS lname, c.value AS mname, d.value AS phone
from formdata AS a
LEFT JOIN formdata AS b ON a.record = b.record AND a.field_name = 'fname' AND b.field_name = 'lname'
LEFT JOIN formdata AS c ON a.record = c.record AND c.field_name like 'mname'
LEFT JOIN formdata AS d ON a.record = d.record AND d.field_name like 'phone'
WHERE b.value LIKE "%bush%";

But if there are (can be) duplicates, there will be multiple rows per record and going over all the data to see what values differ and what values are the same will be inefficient and painful. So, in that case I'd recommend the following instead.

$qry = "SELECT record, field_name AS name, value
		FROM formdata
		WHERE record IN (SELECT record FROM formdata WHERE field_name = 'lname' AND value like '%bush%')
		ORDER BY record ASC";
$result = $db->query($qry);

if (!$result)
	exit;

$currentRecord = false;
$assoc = array();
$index = 0;
while ($row = $result->fetch_assoc()) {
	if ($row['record'] !== $currentRecord) {
		$currentRecord = $row['record'];
		++$index;
	}

if (isset($assoc[$index][$row['name']]))
	$assoc[$index][$row['name']] .= ' ' . $row['value'];
else
	$assoc[$index][$row['name']] = $row['value'];
}

alirezaok

thank you very much

in:

select a.record as record, a.value as fname, b.value as lname
from formdata as a inner join formdata as b using(record)
where  a.value != b.value and b.value like "%bush%"

SELECT DISTINCT a.record AS record, a.value AS fname, b.value AS lname
FROM formdata AS a
LEFT JOIN formdata AS b ON a.record = b.record AND a.field_name = 'fname' AND b.field_name = 'lname'
WHERE b.value LIKE "%bush%";

if i want to remove:

b.value LIKE "%bush%"

to show all records, the results of both queris are mistak.

may you help me?

CoderDan

johanafm's post above your last I think is the best solution for you, and should work with or without the filter subquery

alirezaok

yes but i need to remove that statement from thoes queris.

johanafm

Actually, that does indeed screw up those queries in ways I had not forseen, due to the left join. I went with the left join in case you have people with only an fname and no lname, or no phone number etc in case other field_names exist.

If all people do have both fname and lname, you could simply change the LEFT JOIN to INNER JOIN. Else, whenever you don't really want/need a where clause, instead of just removing it, replace it with b.value IS NOT NULL like this:

SELECT DISTINCT a.record AS record, a.value AS fname, b.value AS lname
FROM formdata AS a
LEFT JOIN formdata AS b ON a.record = b.record AND a.field_name = 'fname' AND b.field_name = 'lname'
WHERE b.value IS NOT NULL