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

[Phil Beynon]

> 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
>

He _might_ get away with selecting ids and building an "where in(list of
ids)" against the other table, but I entirely agree  its a dreadful
structure, problem is if he alters it then other parts of his app will
probably die.
Since my servers are still Raq4s the idea of hitting them with that many
queries is scary - you get a knack for efficient coding when you have an AMD
450Mhz shared amongst that many sites! :-)


Regards,

Phil Beynon
Sales director

** http://www.diygear.com THE Online DIY Toolstore For DIY & Business
** 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)