Master Detail Record Set - 3 tables - DWX

[j milo taylor]

I have three tables

artist
work
artist_nodes

A dynamic link goes from master page to a details page displaying all the particular artist's works, plus a number of keywords (nodes) associated with that artist.

I have no problem in making the details page show all the artist's works, but when i try to display the results from the artist_nodes table as well i run into problems. ("Query was empty")

The code i am using is:

Artists Page

<?php include('Connections/immapp.php');

mysql_select_db($database_immapp, $immapp);
$query_rs_artist = "SELECT id_artist, 1stname_artist, 2ndname_artist FROM artist ORDER BY `2ndname_artist` ASC";
$rs_artist = mysql_query($query_rs_artist, $immapp) or die(mysql_error());
$row_rs_artist = mysql_fetch_assoc($rs_artist);
$totalRows_rs_artist = mysql_num_rows($rs_artist);
?>

and then

<?php do { ?>

<a href="artistdetail.php?recordID=<?php echo $row_rs_artist['id_artist']; ?>">

<?php echo $row_rs_artist['1stname_artist']; ?>
<?php echo $row_rs_artist['2ndname_artist']; ?></a>
<br>
<?php } while ($row_rs_artist = mysql_fetch_assoc($rs_artist)); ?>

And the details page....

<?php require_once('Connections/immapp.php'); ?>
<?php

$colname_rs_artist = "-1";
if (isset($_GET['recordID'])) {
$colname_rs_artist = (get_magic_quotes_gpc()) ? $_GET['recordID'] : addslashes($_GET['recordID']);
}
mysql_select_db($database_immapp, $immapp);
$query_rs_artist = sprintf("
SELECT *

FROM artist,work, artist_nodes

WHERE artist.id_artist = %s AND artist.id_artist = work.artistid_work ORDER BY year_work AND artist_nodes.artistID_artist_nodes= %s", $colname_rs_artist);

$rs_artist = mysql_query($query_rs_artist, $immapp) or die(mysql_error());
$row_rs_artist = mysql_fetch_assoc($rs_artist);
$totalRows_rs_artist = mysql_num_rows($rs_artist);
?>

i think the problem is in the syntax around ....

...AND artist_nodes.artistID_artist_nodes= %s

but i am not sure

thx
jmt

[cameraman]

Welcome to WebmasterWorld, j milo taylor!

Try putting your ORDER BY clause last. Also you must have error_reporting turned off - you need another argument for your sprintf, looks like you probably want to repeat $colname_rs_artist.

[j milo taylor]

cameraman,

thanks for the reply, that has helped a lot. 2 issues remain to be resolved

1 - each work is duplicated according to the number of artist_nodes associated with each artist

2 - how to do a repeat region for the artist_nodes (currently only one displays)

the new code (following your suggestions) is:

<?php require_once('Connections/immapp.php'); ?>
<?php

$colname_rs_artist = "-1";
if (isset($_GET['recordID'])) {
$colname_rs_artist = (get_magic_quotes_gpc()) ? $_GET['recordID'] : addslashes($_GET['recordID']);
}
mysql_select_db($database_immapp, $immapp);
$query_rs_artist = sprintf("
SELECT id_artist, 1stname_artist, 2ndname_artist, url_artist, country_artist, year_artist, workID_artist, abstract_artist, image_artist, id_work, name_work, worktype_work, exhibited_work, year_work, nodeID_artist_nodes, artistID_artist_nodes

FROM artist,work, artist_nodes

WHERE artist.id_artist = %s AND artist.id_artist = work.artistid_work AND artist_nodes.artistID_artist_nodes= %s ORDER BY year_work", $colname_rs_artist,$colname_rs_artist);
$rs_artist = mysql_query($query_rs_artist, $immapp) or die(mysql_error());
$row_rs_artist = mysql_fetch_assoc($rs_artist);
$totalRows_rs_artist = mysql_num_rows($rs_artist);
?>

Thanks for any help, it is much appreciated

all the best
JMT

[edited by: coopster at 3:22 pm (utc) on Mar. 19, 2008]
[edit reason] no personal urls please TOS [webmasterworld.com] [/edit]

[cameraman]

I don't understand what you're saying in 1, or whether that's something you want or don't want.
To repeat a region you'll remove artist_nodes from the query and do it separately, retrieving the records inside a control loop such as while.
while($row_rs_node = mysql_fetch_assoc($rs_node)) {
// display the data
}

[j milo taylor]

hi,

sorry for not being clear

point 1- "each work is duplicated according to the number of artist_nodes associated with each artist", is something i do not want

i've posted up a screengrab illustrating the problem

<snip>

thanks again for your time

all the best
milo

[edited by: eelixduppy at 10:04 pm (utc) on Mar. 19, 2008]
[edit reason] no URLs, please see charter [/edit]

[cameraman]

If you would, please look over the TOS [searchengineworld.com] and post the relevant part of your output in this thread.

[j milo taylor]

apologies, now i notice
no personal urls please TOS [/edit]
from previous post

it was an honest mistake, it wont happen again

embarrased,
mlio

[cameraman]

LOL that's ok, there's no need to be embarrassed - everyone's new before they're not.

Now show us the content!

[j milo taylor]

Hi there,

We learn from our misktakes I guess. ;-)

So here's an example of the problem as outlined in point 1

"each work is duplicated according to the number of artist_nodes associated with each artist", it's something i do not want..

i.e.

Selected Works
Workname Worktype Info Year
Ghost Shells unknown 0
Ghost Shells unknown 0
Ghost Shells unknown 0
Ghost Shells unknown 0
Blackout Installation 1997
Blackout Installation 1997
Blackout Installation 1997
Blackout Installation 1997
Artifical Lighting Spectrogram 1999
Artifical Lighting Spectrogram 1999
Artifical Lighting Spectrogram 1999
Artifical Lighting Spectrogram 1999
etc

so in this example each entry is copied 4 times, which is the number of nodes associated with this artist. the page should look like..

Selected Works
Workname Worktype Info Year
Ghost Shells unknown 0
Blackout Installation 1997
Artifical Lighting Spectrogram 1999
etc

any ideas?
regards
milo

[henry0]

Try SELECT DISTINCT
(add distinct after select)

[cameraman]

The solution for #2 will also solve #1.

If you change your first query to this (I switched back to * for brevity but your specific field names are better):
SELECT * FROM artist, work WHERE id_artist=$colname_rs_artist AND id_artist = artistid_work ORDER BY year_work

Then do a separate query with
SELECT * FROM artist_nodes WHERE artistID_artist_nodes=$colname_rs_artist

Notice that I dropped the table names from the field references (e.g. artist.id_artist) - if your field names in each table are unique to the table, and it appears that they are because the table name appears to be appended to the field name, then you don't have to reference the table names - you only need to do that in the event that you have to eliminate ambiguity in the field name.

Also notice that these don't need sprintf - since PHP can substitute variables directly into strings [which are surrounded with double-quotes], you don't really need the function in this case.

[j milo taylor]

dear cameraman,

thanks a lot, your advice really helped. i divided the records i needed into seperate recordsets, and that sorted it out.

is there bandwidth cost etc involved in using multiple record sets?

thanks again for your assistence, i'm now really excited about the project, a few things about php have clicked into place in my brain.

great!
milo

[cameraman]

clicked into place

Excellent!

There would be some overhead involved in setting up the query but I'm sure it's negligible. That doesn't change the fact that it has to be separate queries, so it's unavoidable.
If you're interested in seeing the execution time for a script, use the microtime() [us.php.net] function.

At the top of a script:
$start = microtime();

Then at the bottom:
$duration = microtime() - $start;
echo $duration;

You'll probably see an awfully small number.

[j milo taylor]

that's a handy little trick, many thanks. i think it will be useful later in on in this project. i'm sonifying a databse of sound artists, little bits if data like this microtime, will be good for that purpose.

i know it might sound a little strange, but that's art for you! ;-)