Query Help MSQL & PHP

[wabash]

I have two queries

 

$queryColor and $queryMaintenance....  I'm able to successfully echo the following queries individually:

 

$queryColor = "SELECT actualplanttable.id,actualplanttable.common, plantcolorlinktable.plant_id, plantcolorlinktable.color_id, plantcolortable.color, plantcolortable.id
        FROM plantcolorlinktable 
    	INNER JOIN actualplanttable ON plantcolorlinktable.plant_id = actualplanttable.id
INNER JOIN plantcolortable ON plantcolorlinktable.color_id = plantcolortable.id
AND plantcolortable.color = '$color'"; 

$resultColor = mysql_query($queryColor) or die(mysql_error());

$queryMaintenance = "SELECT actualplanttable.id, actualplanttable.common, plantmaintenancelinktable.plant_id, plantmaintenancelinktable.maintenance_id, plantmaintenancetable.id, plantmaintenancetable.maintenance 
	FROM plantmaintenancelinktable
	INNER JOIN actualplanttable ON plantmaintenancelinktable.plant_id = actualplanttable.id
	INNER JOIN plantmaintenancetable ON plantmaintenancelinktable.maintenance_id = plantmaintenancetable.id
	AND plantmaintenancetable.maintenance = '$maintenance'";

$resultMaintenance = mysql_query($queryMaintenance) or die(mysql_error());

 

Now, I'm looking to only echo the plants that are both = to $resultColor and $resultMaintenance

 

I.E.  I now get results for several plants that are "yellow" and several plants that are "low maintenance" from these queries.  I'm looking for help to only echo results of plants that are both "yellow" and "low maintenance only".

 

Any help would be greatly appreciated!

 

Thanks 

 

Bill... 

 

 

[requinix]

Have you tried merging the two queries? That's pretty much all there is to do: merge the selected fields, merge the INNER JOINs, and merge the conditions.

[mikosiko]

any reason why you need 2 queries?

 

test this: (no tested on mi side but should work)

$justOneQuery = "SELECT actualplanttable.id, 
                        actualplanttable.common,
                        plantcolortable.color, 
                        plantmaintenancetable.maintenance 
                  FROM actualplanttable
                         JOIN plantcolorlinktable ON plantcolorlinktable.plant_id = actualplanttable.id
                         JOIN plantcolortable ON plantcolorlinktable.color_id = plantcolortable.id
                                                   AND plantcolortable.color = '$color'
                         JOIN plantmaintenancelinktable ON plantmaintenancelinktable.plant_id = actualplanttable.id
                         JOIN plantmaintenancetable ON plantmaintenancelinktable.maintenance_id = plantmaintenancetable.id
                                                  AND plantmaintenancetable.maintenance = '$maintenance'";

[wabash]

Ah thanks guys.

 

I was trying to do this earlier but couldn't find any examples of this scope and didn't know it was possible.

 

Mikosiko your code worked perfectly thanks so much for writing that up for me!

 

Thanks again!