[RESOLVED] Query despair

[Tezread]

Ive been working on this sooo long im about to give up. Im trying to carry out a query that involves 4 tables in all

I have tables

qualityadmin
qualityadmin_id (PK)
quality_name
address1

structure here http://www.mindseyemidlands.co.uk/qualityadmin.gif

activity
activity_id (PK)
activity

structure here http://www.mindseyemidlands.co.uk/activity.gif

activityorg
activityorg_id (PK)
actvity_id
qualityadmin_id

activityuser
activityuser_id
username
activity_id

A qualityadmin can do many activities and to show this the qualityadmin_id & activity_id is recorded in the activityorg table:

http://www.mindseyemidlands.co.uk/activityorg.gif

In a form a user can choose many activities and his/her choices are stored in activityuser

http://www.mindseyemidlands.co.uk/activityuser.gif

My query takes what activities the user chose from the form and compares each activity_id in activityuser with the activities the qualityadmin does in activityorg. The outcome is that the user knows the quality_name's that carry out the activities they have chosen in the form.

How can I do this query.

here is my effort to date- this produces a list of numbers all saying '1' which is wrong because it is supposed to show the list of 'quality_name's.'

if (isset($_GET['activity_checkbox'])) { 

$sql = "SELECT activityuser.activity_id, activityorg.activity_id, qualityadmin.qualityadmin_id
FROM activityuser, activityorg, qualityadmin
WHERE activityuser.activity_id = activityorg.activity_id"; 
$result = mysql_query($sql) or die(mysql_error()); 
echo $result;
} 

[masonbarge]

Without going through the query line by line, it looks okay just at a glance.

I think your problem is "echo $result". "$result" is a resource. Off the top of my head:

	$row = mysql_fetch_array ($result, MYSQL_NUM);
foreach ($row as $key => $value) {
echo "<p>$row[0], $row[1], $row[2]</p>";
}

[Tezread]

thankyou for your prompt reply masonbarge. i tried what you said but I get an error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /disk1/home3/mindseye/public_html/notts_quality/info_resource/infolinks_choicesv4_formhandler.php on line 132

Warning: Invalid argument supplied for foreach() in /disk1/home3/mindseye/public_html/notts_quality/info_resource/infolinks_choicesv4_formhandler.php on line 133

I was thinking maybe a LEFT JOIN be a work around. Trouble is i'm confused as to what table is dependent on what. I had a look at this:

http://dev.mysql.com/doc/refman/5.1/en/left-join-optimization.html

Essentially I need to be showing the quality_name from the qualityadmin table at the end of the query.
After all - in the activityorg table is a record of qualityadmin_ids with any number of activity_id's. The reason I think a LEFT JOIN is needed is that im selecting records from three tables.

[Tezread]

I still haven't resolved this. Im getting results now but they are incorrect.

<?php
	if (isset($_POST['submit'])) { 

$sql = "SELECT activityuser.activity_id, activityorg.activity_id, 
activityorg.qualityadmin_id, qualityadmin.quality_name
FROM activityuser, activityorg, qualityadmin
WHERE activityuser.activity_id = activityorg.activity_id";

if(!$sql) die("SQL ERROR: ".mysql_error()); 
$result = mysql_query($sql) or die(mysql_error()); 

} 

$row = mysql_fetch_array ($result, MYSQL_NUM);
foreach ($row as $key => $value) 
{
echo "<p>$row[0], $row[1], $row[2], $row[3]</p>";
}

this brings up the following (click the link and choose some activities)

http://www.mindseyemidlands.co.uk/notts_quality/info_resource/infolinks_choicesv4.php

you will notice that the result is repeated when in fact their should be different quality_names listed and no matter what activities you pick the results are the same when this should not be the case.

For example- if you pick the activity 'advocacy' whose id is '2' and childcare' whose id is '6' then the result should be that the qualityadmin's with an id of 37,38,53,62,68,73,77 and 78 should show (where 'id 37' is 'age concern' and id 38 is 'national care standards etc)

[Tezread]

Since my last post I went back to the drawing board to review my database design and aims. My database design has been adapted now and I have tables:

qualityadmin
qualityadmin_id
quality_name
address1
etc

activity
activity_id
activity

activityrelations
relationid
relatedactivityid
relatedqualityadminid

here is my formhandler. im trying to do one loop for each qualityadmin then a second loop inside it for each activity the qualiyadmin has

// create short variable names 
$activitysearch=$HTTP_POST_VARS['activitysearch']; 

if (!$activitysearch) { 

echo 'You have not selected anything. Please go back and try again.'; 
exit; 
} 

$activitysearch = addslashes($activitysearch); 

include ("connect.php");

/* The user has picked a activity_id */ 

/* Grab all the qualityadmin's with that activity */ 
$sql1 = 
  "SELECT * FROM qualityadmin
  WHERE qualityadmin.qualityadmin_id = activityrelations.relatedqualityadminid AND 
  AND activityrelations.relationid = $activitysearch"; 
$set1 = mysql_query($sql1); 

/* Process each qualityadmin */ 
while($row1 = mysql_fetch_array($set1)) 
{ 
  echo $row1['quality_name']; 

  /* Now get all the activities for a given qualityadmin */ 
  $relatedqualityadminid = $row1['relatedqualityadminid']; 
  $sql2 = 
    "SELECT * FROM activity
    WHERE activity.activity_id = activityrelations.relatedactivityid 
    AND activityrelations.relatedqualityadminid = $relatedqualityadminid";
  $set2 = mysql_query(sql2); 

  /* Process each activity */ 
  while($row2 = mysql_fetch_array($set2)) 
  { 
    echo $row2['activity']; 
  } 
} 

?>

the error message now reads

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /disk1/home3/mindseye/public_html/notts_quality/info_resource/findinforesultsv1.php on line 121

please can anyone help?

[MitchEvans]

For sql1 you have two "ANDS" in your WHERE clause together. Also make sure that you are putting all the relevant details in the WHERE clause. All of the id's that are the same in the other sepearte tables need to be put in the where clause.

Example: table1.ID=table2.ID AND table1.ID=table3.ID

[Tezread]

MitchEvans wrote:

For sql1 you have two "ANDS" in your WHERE clause together. Also make sure that you are putting all the relevant details in the WHERE clause. All of the id's that are the same in the other sepearte tables need to be put in the where clause.

Example: table1.ID=table2.ID AND table1.ID=table3.ID

hi mitch. thanks for the input. ive built this query based on research from another thread:

http://phpbuilder.com/board/showthread.php?t=10235128&page=1&pp=15&highlight=joining+tables+mysql_fetch_array

I must have two AND clauses otherwise it won't work. Kudos on the comment that all ids need to be the same in seperate tables though

[Tezread]

actually i take it back- i think you have a point about the two AND's.

[Tezread]

ok- now im lost. ive changed my code to remove the two AND's and made sure any references to id's appear then same in child tables: im still getting

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /disk1/home3/mindseye/public_html/notts_quality/info_resource/findinforesultsv1.php on line 120

$activitysearch=$HTTP_POST_VARS['activitysearch']; 

if (!$activitysearch) { 

echo 'You have not selected anything. Please go back and try again.'; 
exit; 
} 

$activitysearch = addslashes($activitysearch); 

include ("connect.php");

/* The user has picked a activity_id */ 

/* Grab all the qualityadmin's with that activity */ 
$sql1 = 
  "SELECT * FROM qualityadmin
  WHERE qualityadmin.qualityadmin_id = activityrelations.qualityadmin_id"; 
$set1 = mysql_query($sql1); 

/* Process each qualityadmin */ 
while($row1 = mysql_fetch_assoc($set1)) 
{ 
  echo $row1['quality_name']; 

  /* Now get all the activities for a given qualityadmin */ 
  $relatedqualityadminid = $row1['qualityadmin_id']; 
  $sql2 = 
    "SELECT * FROM activity, activityrelations
    WHERE activity.activity_id = activityrelations.activity_id 
    AND activityrelations.qualityadmin_id = $relatedqualityadminid";
  $set2 = mysql_query(sql2); 

  /* Process each activity */ 
  while($row2 = mysql_fetch_assoc($set2)) 
  { 
    echo $row2['activity']; 
  } 
} 

[masonbarge]

The first thing you need to do is go to your mysql interface and work on the query there, instead of in your php page. If you don't have a good graphic interface, there are several free ones that are a big help to us noobs. I use phpMyAdmin.

[Tezread]

I do have phpmyadmin installed though I havent used it for testing queries. My problem is how to build the query given my database structure

[masonbarge]

Tez, listen to me. Build and test your query using phpmyadmin first. Then put the query into php. Your problem here is the query and you will get a lot more helpful error information, as well as narrowing down the information.

I don't understand how phpmyadmin would be less appropriate "given your database structure", the comment makes no sense to me.

Once it works as intended in phpmyadmin, you can do the php knowing that the query itself is valid and correct.

[Tezread]

what I meant was that im used to testing queries outside of phpmyadmin. In terms of my database structure i was concernedto it was incorrect. I have looked at other threads and established now by db structure is fine- just my query & query results need work. I have been testing the query in phpmyadmin today since you said. It is a good idea as you can get instant feedback in a tabular view (if it works!)

i'll keep you posted

[Tezread]

Some success using phpmyadmin to get the query working- questions- how can I echo the results of this query:

SELECT activity.activity_id AS activity_id, activity.activity AS activity,
qualityadmin.qualityadmin_id AS qualityadmin_id, activityuser.username AS username
FROM activity
LEFT JOIN activityrelations ON activity.activity_id = activityrelations.activity_id
LEFT JOIN qualityadmin ON activityrelations.qualityadmin_id = qualityadmin.qualityadmin_id
LEFT JOIN activityuser ON activity.activity_id = activityuser.activity_id
WHERE activityuser.username = 'administrator';

[masonbarge]

See my second post for basic php/mysql code.

[MitchEvans]

$sql = "SELECT activity.activity_id AS activity_id, activity.activity AS activity,
qualityadmin.qualityadmin_id AS qualityadmin_id, activityuser.username AS username
FROM activity
LEFT JOIN activityrelations ON activity.activity_id = activityrelations.activity_id
LEFT JOIN qualityadmin ON activityrelations.qualityadmin_id = qualityadmin.qualityadmin_id
LEFT JOIN activityuser ON activity.activity_id = activityuser.activity_id
WHERE activityuser.username = 'administrator'";

$Results = mysql_query($sql);
while ($Row = mysql_fetch_array ($Results)){
$Name = $Row["requirement_txt"];}
echo $Name;

Try this, the part in red is the name of the field that you want to show from your table, if you want to show more than one field just copy and paste the line with the red text and put it directly under the last one making sure the } is at the end.

[Tezread]

MitchEvans wrote:

Try this, the part in red is the name of the field that you want to show from your table, if you want to show more than one field just copy and paste the line with the red text and put it directly under the last one making sure the } is at the end.

do you mean like this

$result = mysql_query($sql);
while ($row = mysql_fetch_array ($result)){
$name = $row["activity"];
		$row["activity_id"];
		$row["username"];}
echo $name;

this only outputs the activity

[MitchEvans]

So it is utputting which is good but you need to put this in aswell

$result = mysql_query($sql);
while ($row = mysql_fetch_array ($result)){
$name = $row["activity"];
$name1 = $row["activity_id"];
$name2 = $row["username"];}
echo "$name<br>$name1<br>$name2";

[Tezread]

I was wondering as well- I have in my test query as a result where a qualityadmin is repeated (which is correct as qualityadmins have many activities) How do I limit the query so the qualityadmin name appears once for readability sake.

[Tezread]

MitchEvans wrote:

So it is utputting which is good but you need to put this in aswell

$result = mysql_query($sql);
while ($row = mysql_fetch_array ($result)){
$name = $row["activity"];
$name1 = $row["activity_id"];
$name2 = $row["username"];}
echo "$name<br>$name1<br>$name2";

hmmmm - strange, getting an undefined variable error message for my $name, $name1 $name2 vars