[Phpwm] How to get a single string from different queries?
Ray Masa
raymasa at hotmail.com
Wed Apr 19 20:21:00 BST 2006
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 to give you all an idea, the table structure for this particular table
(in the open realty database), looks like this:
mysql> describe en_listingsdbelements;
+--------------------------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default |
Extra |
+--------------------------------+-------------+------+-----+---------+----------------+
| listingsdbelements_id | int(11) | | PRI | NULL |
auto_increment |
| listingsdbelements_field_name | varchar(80) | | MUL | |
|
| listingsdbelements_field_value | text | | MUL | |
|
| listingsdb_id | int(11) | | MUL | 0 |
|
| userdb_id | int(11) | | | 0 |
|
+--------------------------------+-------------+------+-----+---------+----------------+
And the select query would output this data (the following example only
shows one address):
mysql> select * from en_listingsdbelements where listingsdb_id = 7;
+-----------------------+-------------------------------+--------------------------------+---------------+-----------+
| listingsdbelements_id | listingsdbelements_field_name |
listingsdbelements_field_value | listingsdb_id | userdb_id |
+-----------------------+-------------------------------+--------------------------------+---------------+-----------+
| 1479 | mls |
| 7 | 1 |
| 1478 | status | Active
| 7 | 1 |
| 1477 | prop_tax |
| 7 | 1 |
| 1476 | lot_size |
| 7 | 1 |
| 1475 | sq_feet |
| 7 | 1 |
| 1474 | year_built |
| 7 | 1 |
| 1473 | floors | 2
| 7 | 1 |
| 1472 | baths | 2
| 7 | 1 |
| 1471 | beds | 3
| 7 | 1 |
| 1470 | full_desc |
| 7 | 1 |
| 1466 | zip | H3A 1Y1
| 7 | 1 |
| 1469 | price | 120000
| 7 | 1 |
| 1468 | neighborhood |
| 7 | 1 |
| 1467 | country |
| 7 | 1 |
| 1465 | state | Quebec
| 7 | 1 |
| 1463 | garage_size |
| 7 | 1 |
| 1464 | city | Montreal
| 7 | 1 |
| 1462 | address | 3459 Rue McTavish
| 7 | 1 |
| 1461 | or_owner | 1
| 7 | 1 |
| 1460 | mlsexport | no
| 7 | 1 |
+-----------------------+-------------------------------+--------------------------------+---------------+-----------+
Thanks again for all your 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 14:57:17 +0100
>
>Hello Ray,
>
>When you call mysql_query, what you get back is a result set (when doing a
>SELECT) or true/false (for other types of query). That's why you get
>"Resource id #3" echoed (that's how PHP converts the result set into a
>string). What your code is trying to do is join two result sets as if they
>were a string. What you need to do is get the values you want out of the
>result sets, then append them to each other. Something like:
>
><?php
>mysql_connect("host", "user", "pass") or die(mysql_error());
>mysql_select_db("realty") or die(mysql_error());
>
>$results = mysql_query("SELECT listingsdbelements_field_value FROM
>en_listingsdbelements WHERE listingsdbelements_field_name = 'address'") or
>die(mysql_error());
>
>$row = mysql_fetch_array( $results );
>$address = $row['listingsdbelements_field_value'];
>
>$results = mysql_query("SELECT listingsdbelements_field_value FROM
>en_listingsdbelements WHERE listingsdbelements_field_name = 'city'") or
>die(mysql_error());
>
>$row = mysql_fetch_array( $results );
>$city = $row['listingsdbelements_field_value'];
>
>$full_address=$city.', '.$address;
>echo $full_address;
>?>
>
>By the way, your database could be in need of some normalisation, by the
>looks of things. Any reason why you don't just have a table with 'city' and
>'address' fields?
>
>(You could probably also get both fields in a single query, as a previous
>post suggested, but I kept your structure.)
>
>Elliot
>
>
>
>
>
>Ray Masa wrote:
>
>>Hi all,
>>
>>I am trying to join several database queries into a single string, but
>>have not been able to do so, any suggestions?
>>
>>Here is the PHP code:
>>
>><?php
>>
>>mysql_connect("host", "user", "pass") or die(mysql_error());
>>mysql_select_db("realty") or die(mysql_error());
>>
>>$address = mysql_query("SELECT listingsdbelements_field_value FROM
>>en_listingsdbelements WHERE listingsdbelements_field_name = 'address'") or
>>die(mysql_error());
>>while($row1 = mysql_fetch_array( $address )) {
>>// check to see if query is being fetched
>>echo $row1['listingsdbelements_field_value']. "<br />";
>>}
>>
>>$city = mysql_query("SELECT listingsdbelements_field_value FROM
>>en_listingsdbelements WHERE listingsdbelements_field_name = 'city'") or
>>die(mysql_error());
>>while($row2 = mysql_fetch_array( $city )) {
>>// check to see if query is being fetched
>>echo $row2['listingsdbelements_field_value']. "<br />";
>>}
>>
>>// join the above query in one string
>>
>>$full_address=$city.', '.$address;
>>
>>
>>echo $full_address;
>>
>>?>
>>
>>
>>The above give me the following output:
>>
>>1200 Atwater Ave.
>>1240 Rue Drummond
>>Montreal
>>Westmount
>>H3G 1V7
>>H3A 1Y1
>>Resource id #3, Resource id #4
>>
>>
>>The individual queries are returning and echoing the results correctly,
>>but the last string ($full_address) is giving me error (Resource id #3,
>>Resource id #4).
>>
>>Any ideas on how to solve this?
>>
>>Thanks,
>>
>>Ray
>>
>>
>>
>>_______________________________________________
>>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
More information about the Phpwm
mailing list