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

[Ray Masa]

Hi all,

Elliot you are a genius.  Yes, it worked beautifully.

Thanks for the help.

Ray


>From: Elliot Smith <elliot at townx.org>
>Reply-To: phpwm at mailman.lug.org.uk
>To: phpwm at mailman.lug.org.uk
>Subject: Re: [Phpwm] How to get a single string from different queries?
>Date: Wed, 19 Apr 2006 21:12:16 +0100
>
>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
>>
>>
>
>
>_______________________________________________
>Phpwm mailing list
>Phpwm at mailman.lug.org.uk
>https://mailman.lug.org.uk/mailman/listinfo/phpwm