on 8/1/03 8:27 PM, bruce at bedouglas@xxxxxxxxxxxxx wrote:
> to give a better understanding of what you're trying to do...
>
> could you provide some sample tbl/row data, as well as some of the psuedo
> code you're dealing with...
OK. Here's the central bit of code from the page in question, as it
currently stands (decidedly unfinished), with comments explaining the issues
I'm having and how I was trying to do it. A couple more explanatory notes
follow.
// queries the keywords-artists join table, finds all keyword
instances for that artist ID
$query = "SELECT keyword_id FROM key_art_join WHERE artist_id =
'$artist_id'";
$keyword_id_list = pg_exec($db, $query);
// checks to see if any results where found
$rows = pg_numrows($keyword_id_list);
if ( $rows == 0) {
echo "<p>Sorry, no Processes & Techniques keywords were
found.</p>";
} else {
// queries keywords table for all rows whose keyword IDs
found in the previous query - this is one of the bits I'm having trouble
with
$key_query = "SELECT keyword_id, keyword, category FROM
keywords WHERE keyword_id = '$keyword_id_list' AND type = 'format' ORDER BY
category";
$keyword_info = pg_exec($db, $key_query);
// tries to turn the resultset into an array - not sure if
this is working at all
$keyword_array = pg_fetch_array ($keyword_info);
// does a second query of the keywords table looking for
just the categories, with duplicates eliminated, so that I can use this to
group the results
$cat_query = "SELECT DISTINCT category FROM keywords WHERE
keyword_id = '$keyword_id_list' AND type = 'format' ORDER BY category";
$cat_info = pg_exec($db, $cat_query);
// this was the beginning of my trying to get it to cycle
through the categories. I was going to nest another for loop in it printing
the keywords under that category, but I can't even get it to print the
category names
foreach ($cat_info as $cat) {
echo $cat;
}
// And this is about where I gave up
Notes:
The variable $artist_id was passed from the URL of the page (i.e.
artist_techniques.php?artist_id=111 or whatever), which works fine, and $db
is the database connection that was already established.
I think one of the problems is that I'm not entirely clear on the difference
between resultsets and arrays in PHP. I think in the foreach part above, I'm
trying to treat the resultset $cat_info as an array and it's not working.
Further up, I tried to explicitly turn $keyword_info into an array using the
pg_fetch_array() function but I don't think that worked either - maybe it
only works on one row at a time? The manual lists another function,
pg_fetch_all(), that will turn a whole resultset into a two-dimensional
array, which is probably what I need, but my host's stupid PHP installation
is only 4.1 so I can't use that one. Do you know if there's some kind of
workaround?
Some database info and sample data:
key_art_join is a two-column table, with both columns as smallint, which
lists keyword IDs and artist IDs, i.e.
128|100
131|100
100|105
109|105
110|105
where the first number is the keyword ID (referencing the keywords table)
and the second one is the artist ID (referencing the artists table).
The keywords table contains a serial smallint ID, and three varchar columns
for keyword, category and type. Keywords are specific descriptors of an
artist's work, category is the various categories the keywords are grouped
under (themes, techniques, etc.) on the artist info pages and type is either
format or content - the particular page I'm working on now only uses the
format keywords, but there will be a second page that uses the content ones.
Here's some sample data from the keywords table (order: keyword
ID|keyword|category|type):
100|activist|approaches|content
101|appropriated/found|approaches|content
102|autobiographical|approaches|content
125|abstract|subject/genre|content
126|architecture|subject/genre|content
153|gelatin silver (b+w) print|media|format
154|chromogenic (colour) print|media|format
173|installations|format/presentation|format
174|murals/large scale|format/presentation|format
175|multiple panels|format/presentation|format
What the end result is supposed to look like is something like this, on the
Techniques page (using a random assortment of keywords in this example):
MEDIA: gelatin silver (b+w) print, cyanotype, photosilkscreen
FORMAT/PRESENTATION: wall pieces, multiple panels
TECHNIQUE & PROCESSES: hand-tinting, liquid light, photocollage
Does this make it any clearer?
Thanks,
Lynna
> -----Original Message-----
> From: pgsql-php-owner@xxxxxxxxxxxxxx
> [mailto:pgsql-php-owner@xxxxxxxxxxxxxx]On Behalf Of Lynna Landstreet
> Sent: Friday, August 01, 2003 5:15 PM
> To: pgsql-php@xxxxxxxxxxxxxx
> Subject: [PHP] Resultset/array issues
>
>
> Hello all,
>
> I've spent the last several hours poring through the PHP manual trying to
> figure out how to do something for two pages in the site I'm working on.
> Word of warning, I'm not terribly experienced yet with any of this, so these
> questions may seem really dumb to a more experienced person...
>
> What I need to do is, first - for a given artist whose ID number is passed
> through the page URL, query a join table that links the artists with
> keywords describing their work to get all the rows with that artist ID, then
> query the keywords table to get the keywords corresponding with the keyword
> IDs found in the join table query.
>
> So that's the first problem: while it sounded straightforward when I first
> thought of it, I've realized I have no idea how to do a select based on
> looking for any values found in a preexisting resultset, rather than on just
> looking for one value. I tried using but that just got me an error message
> saying: "ERROR: pg_atoi: error in "Resource id #3": can't parse "Resource id
> #3"" I could go through the keyword_id resultset row by row and look for
> each one in a for statement, but then the results of each query would be a
> separate resultset and that wouldn't be very useful. If I just wanted to
> print each keyword on one line that would be fine, but I need to do a bit
> more with them, as I'll get to in part 2. So I need to learn how to bring up
> all the rows in the keywords table whose ids were found in the previous
> query I did in the join table. That's thing one.
>
> Thing two is that once I have all the data I need, I need to print it off
> sorted by category - not just ordered by category, that would be easy, but
> with each category as a subhead and then the keywords in that category
> listed beneath it. So for that, once I've got all the keyword and category
> data in a resultset, I need to extract the category values out into an
> array. But all the functions I can find that do things like that are
> designed to extract a row, not a column. Does anyone know if there's a way
> of doing this?
>
> BTW, just to make things more complicated, my stupid web host is still
> running PHP 4.1.2 (and PostgreSQL 7.2.1)! So I don't have access to the
> pg_fetch_all() function that would turn a resultset into a multidimensional
> array and thus probably make it easier to work with... :-(
>
> Any assistance would be *very* much appreciated!
--
Resource Centre Database Coordinator
Gallery 44
www.gallery44.org
