Forums

PHP

This topic is locked

Select of another Select

Posted 08 Dec 2001 19:11:19
1
has voted
08 Dec 2001 19:11:19 benja min posted:
I need to do a SELECT from a returned value of another SELECT, but i only have the options URLget, Form variable, cookie, session variable, phpvariable, entered value .. and not from an Object. How can i do it ?
can someone help me ?

Replies

Replied 08 Dec 2001 21:56:14
08 Dec 2001 21:56:14 Tim Green replied:
Use PHP Variable and enter your value like this:-

$RecordsetName->Fields("ColumnName"

Note though, that this will only work if the first select returns one result. If multiple results are returned for the first recordset then only the first will be used.

However, all this said and done, it is possible that you are not using the most efficient method of doing this.

A much more efficient method would be to use a JOIN. In MySQL you would do it like this :-

SELECT customers.name AS THE_NAME, customers.address AS ADDR, customers.city AS THE_CITY, countrylist.country AS THE_COUNTRY FROM customers, countrylist
WHERE countrylist.city = customers.city

This might look a little difficult to understand. I will explain it in stages.

customers.name AS THE_NAME - Selects the name column from the customers table, and calls the column THE_NAME (essential for using it in UltraDEV)

This is the same for all similarly named sections.

FROM customers, countrylist - Use both the customers table and the countrylist table.

WHERE countrylist.city = customers.city - countrylist.city is a field in the countrylist table that holds the names of cities. This section purely compares these values, so that only the right THE_COUNTRY value is determined.

Hope this helps.


Tim Green

Extension & PHP TalkZone Manager
<font size=1>-------------------------------------------
<i>Please read the Forum FAQ before posting
a question to this TalkZone.</i>
-------------------------------------------
www.UDzone.com : A dynamic Dreamweaver,
Ultradev and Fireworks site for developers
by developers.
-------------------------------------------</font id=size1>
Replied 08 Dec 2001 22:00:17
08 Dec 2001 22:00:17 benja min replied:
Does Ultradev understand sintaxis like that ?

"SELECT customers.name AS THE_NAME, customers.address AS ADDR, customers.city AS THE_CITY, countrylist.country AS THE_COUNTRY FROM customers, countrylist
WHERE countrylist.city = customers.city"

Replied 08 Dec 2001 22:03:14
08 Dec 2001 22:03:14 Tim Green replied:
Indeed it does, just click the 'Advanced' button in the Recordset window.

Strictly speaking though, UltraDev doesn't really recognize the SQL, it just sends it to your Database. So if your database recognizes it, then so will UltraDev.

I use this technique on a daily basis, so I can speak from experience that it works.

Tim Green

Extension & PHP TalkZone Manager
<font size=1>-------------------------------------------
<i>Please read the Forum FAQ before posting
a question to this TalkZone.</i>
-------------------------------------------
www.UDzone.com : A dynamic Dreamweaver,
Ultradev and Fireworks site for developers
by developers.
-------------------------------------------</font id=size1>
Replied 08 Dec 2001 22:14:55
08 Dec 2001 22:14:55 benja min replied:
What i need to do is for multiple results. I have 2 tables

Table Collection
-id_user
-id_figure

Table Figure
-id_figure
-figure_name

First i need to know what figures has a unique user (lets say 'john', id_user=1) in his collection.
like
ID_user Figure
1 3
1 5
1 15
3 4
5 6
.
.

And then when i have the Id's of the figures, get the name of the figures.

id_figure name_figure
1 figure_1
2 figure_2
.
.


And display the name of the figures on a HTML Table.

John has figure_1, Figure_2.....


Can it be done with Ultradev ?

Replied 08 Dec 2001 22:24:47
08 Dec 2001 22:24:47 Tim Green replied:
The SQL I gave you will produce multiple results, if there are multiple matches.

Tim Green

Extension & PHP TalkZone Manager
<font size=1>-------------------------------------------
<i>Please read the Forum FAQ before posting
a question to this TalkZone.</i>
-------------------------------------------
www.UDzone.com : A dynamic Dreamweaver,
Ultradev and Fireworks site for developers
by developers.
-------------------------------------------</font id=size1>

Edited by - rawveg on 08 Dec 2001 22:25:59
Replied 08 Dec 2001 22:45:30
08 Dec 2001 22:45:30 benja min replied:
i made it work....lalala
thanks <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>

Reply to this topic