[Phpwm] How to get a single string from different queries?

[Elliot Smith]

Hi,

The issue Ray has is that there is no simple way to group the data: 
there isn't one row per record (as there would be in a sensible 
database). Instead you might have 20 "records" (properties), each 
represented by 20 rows, yielding a 400 row table. The issue is (as I 
understand it) to group each set of 20 rows into a single "virtual" row 
which can then be processed. (Don't get me started on that database 
structure, I'll cry.)

One way you might do this would be (in an unnecessarily verbose form for 
clarity's sake):

<?php
// get the distinct IDs
$results = mysql_query("SELECT DISTINCT(listingsdb_id) AS id FROM 
en_listingsdbelements");

while ($row = mysql_fetch_assoc($results)) {
// for each ID, fetch all the associated rows in the database table
$id = $row['id'];
$sql = "SELECT field_name, field_value FROM en_listingsdbelements WHERE 
listingsdb_id = $id";
$results2 = mysql_query($sql);

// associative array for holding the rows: one element of the array = 
one row from the second result set
$fields_raw = array();

// process the rows for this listing ID, flattening them out into an 
associative array
while($row2 = mysql_fetch_assoc($results2)) {
$field_name = $row2['field_name'];
$field_value = $row2['field_value'];
$fields_raw[$field_name] = $field_value;
}

// munge the fields together to get the string we actually want
$str = $fields_raw['city'] . ", " . $fields_raw['address'] . ", " . 
$fields_raw['zip'];
echo $str;
}
?>

(By the way, I am doing this without testing, as I haven't got the 
database table in front of me populated with data, so excuse typos.)

This is hopelessly inefficient, as it does one query for each ID in the 
table. There may be a more efficient way of doing this using subqueries 
(perhaps); I toyed with the GROUP_CONCAT function as a solution, but I 
don't think it works, as you can't guarantee the field values you are 
concatenating will be in the right order. What the code above does is 
basically turns a crazy database table into a sensible "pretend" table, 
and may be the only approach if using MySQL 4.

Another thing you could do is a stored procedure for efficiency, I suppose.

Elliot




Phil Beynon wrote:

>>Hi all,
>>
>>Thank you for the responses.  I agree, the database structure is a bit
>>convoluted (I wish it was more of a standard structure). I am
>>using an open
>>source project (http://www.open-realty.org/) and that is the way their
>>database is structured.
>>
>>I tried Elliot’s solution and it works perfectly, except it returns only
>>record set in the database (for all four fields – address, city,
>>state and
>>zip).  How do I do a while loop, so that it will get results for all the
>>records in the database.
>>
>>    
>>
>
>Just a chunk of code which you can use as required, this will place all
>returned elements of the query into an array called $rawdata;
>
>$i=1; // just a counter variable
>while($row = mysql_fetch_array($result,MYSQL_ASSOC)){$rawdata[$i] = $row;
>$i++;}// raw data to array
>print_r($rawdata); // show $rawdata
>
>$i will also tell you how many element sets are in the array, saves doing a
>count() in the query.
>
>Regards,
>
>Phil Beynon
>
>** Infolink Electronic Systems Ltd. http://www.infolinkelectronics.co.uk
>** Professional Web Design & Cobalt Hosting Solutions
>** Contact: Sales at infolinkelectronics.co.uk
>** Tel / Fax 0121 458 4894 (office) 0121 441 3558 (home) 07801 548464
>(mobile)
>
>
>
>_______________________________________________
>Phpwm mailing list
>Phpwm at mailman.lug.org.uk
>https://mailman.lug.org.uk/mailman/listinfo/phpwm
>  
>