Please help me! Query results form another query

yandoo · January 26, 2008

Hi there,

I neeed a little help please. I have 2 queries here. The 1st selects records from a database and the 2nd query is suppose to query the results of the 1st query.

Basically the First Query displays a most recent entered record into the database:

SELECT * FROM animal ORDER BY AnimalID DESC - First Query (This brings up most recently inserted record)

SELECT breed.BreedID FROM breed, animal WHERE breed.AnimalTypeID = animal.AnimalTypeID ORDER BY animal.AnimalID - Second Query

The Second Query is suppose to SELECT the Breeds of an animal that is of the same AnimalType of the First Query.

For example:

if i added details on of an animal that was a DOG....I need the DOG attribute to be compared against the Breed table so ALL types of DOG BREED are displayed (like Alsation, Terrier etc.. at mo only brings up alsation). And if i added a CAT animal....The BREED should select all records of BREED that are of the type of a CAT.

The result i get is that ALL of the BREEDS of ALL the AnimalTypes are listed NOT just the Breed that is related to one type!??

So i need to somehow use the record results (AnimalType) of the First Query to then be queried by the Second Query which selects all the breeds of that specific AnimalType.

Please Help, how do i do this????

This is an illustration of what the form looks like to give u an idea of what im talking about this is what the first form looks like:

AnimalID

AnimalType

AnimalName

Age

Sex

Second form

AnimalID

AnimalType

BreedID - NOTICE BREEDID IS ADDED AT THIS STAGE

AnimalName

Age

Sex

So the 1st query pulls the relevant record (user just inserted in form 1) PLEASE NOTE that here WE KNOW what AnimalType of that animal is!

And finally the 2nd query needs to select the appropriate BREED depending on the AnimalType it is!!

Currently the 2nd query will search ALL records in Animal Table and Breed Table are and SELECT all recrods that hav the same AnimalType!! This obivously wont wortk becuase i DONT want it to search ALL the recordsd just the ONE record of the Animal that is being displayed in form 2.

I know its long winded, i find it difficult to try and explain....Hope it was well enough!!

Please please please help me!

Thank You

Tom

revraz · January 26, 2008

It doesn't work like that. You need to do it in one Query. You can do a SELECT query inside a SELECT query if you want, or use a JOIN to get the results you require.

yandoo · January 26, 2008

Hi there,

thanks for reply...

Any idea how to do that??? beraring in mind i would need to JOIN only a SINGLE record in the animal table (most recently inserted record) that displays the animal type in it.

The second query i have would work perfectly IF it only selected the Animal SINGLE RECORD! It doesnt at the moment it selects all of the animal records....

Thanks

revraz · January 26, 2008

Then use a LIMIT 1

paul2463 · January 26, 2008

i am confused at this stage.......have i got it clear..

1. you add a new animal to the table ( AnimalID(automatic lets say 15), animalType = dog, animalName = rusty, Age = 3, sex = M)

2. you query the database to get the animalType ( "select animalType from animal order by animalID DESC limit 1")

3. you then need all the breed of that animal type from your breeds table ("SELECT breed from breeds where animalType = answer to last query"

<?php
$query = "SELECT breed FROM breed WHERE animalType = (SELECT animalType FROM animal ORDER BY animalID DESC LIMIT 1)";
?>

yandoo · January 26, 2008

Hi there & thanks for reply,

If i use LIMIT 1 which i have tried all that happens is that ONLY 1 TYPE of BREED is listed....I need All the BREEDs of that particular aninmal type.

any more ideas please???

Thank you

paul2463 · January 26, 2008

the limit 1 in the nested query means that you only get the animaltype of the last inserted record, it is not limiting the main query

laffin · January 26, 2008

SELECT * FROM animal LEFT JOIN breed on breed.animalTypeID=animalTypeID order by DESC AnimalID LIMIT 1

yandoo · January 26, 2008

Hi Theres,

1) add new animal record to database (which includes to important detail - AnimalType)

2)I then go to another page (form part 2) wherea query is made to bring the most recent record that the user has just inserted on previous page)

3) At this page an additional detail is added (that the user can insert) BREED

In order to selct ONLY the appropriate BREEDs that the user can enter (because we know what animalType the animal is now because of previous page insert) I need to Match the BREED with AnimalType.....But also NEED to make sure that i am matching The record of an animal that matches that specific AnimalType NOT all the anmialtypes there are....

Do you see what im getting at????

Thank you

laffin · January 26, 2008

Ahhh, i see where u are going now.

U cant give a list of breeds unless the animal type is known.

so on yer second form, which dusn yet update the db, u can keep them in hidden post fields.

since u can get these post fields, u make a 2nd query

SELECT * from breeds where AnimalTypeID=$_POST[AnimalType]

now u can list the breeds for that animal type on the 2nd form (dropdown box or radio buttons)

and submit to a 3rd script where u can take all the POSTs and do an db INSERT

yandoo · January 26, 2008

Hey thanks again for reply!

Though this may help so no misunderstandings....

Here arre the 3 tables that are used here:

ANIMAL TABLE

AnimalID - Prmary key

AnimalTypeID - index

BreedID - index

AnimalName

Age

Sex

ANIMALTYPE TABLE

AnimalTypeID - Primary Key

AnimalTypeName

BREED TABLE

BreedID - Primary Key

AnimalTypeID - index

BreedName

Hope this helps too...

Thank You

P.S.

I just tried sugested code above and found i had syntax error so change too:

SELECT * FROM animal LEFT JOIN breed on breed.animalTypeID=animalTypeID order by AnimalID

And get error: 1052 - Column 'animalTypeID' in on clause is ambiguous

oooh man

yandoo · January 26, 2008

Hey thats brill,

Ill give it a go now

Thank You

yandoo · January 26, 2008

Hi there,

Im must bit of newbie at this cozz still having probs....

Created a hidden form with:

Added the Breed recordset:

#mysql_select_db($database_woodside, $woodside);

$query_Recordset1 = "SELECT * FROM breed WHERE AnimalTypeID=$_POST[AnimalTypeID]";

$Recordset1 = mysql_query($query_Recordset1, $woodside) or die(mysql_error());

On Breed lit menu #

How do i put it together to make this work??

Thankyou

#

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

<?php

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

{

$theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue;

switch ($theType) {

case "text":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "long":

case "int":

$theValue = ($theValue != "") ? intval($theValue) : "NULL";

break;

case "double":

$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";

break;

case "date":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "defined":

$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

break;

}

return $theValue;

}

$editFormAction = $_SERVER['PHP_SELF'];

if (isset($_SERVER['QUERY_STRING'])) {

$editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);

}

if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) {

$updateSQL = sprintf("UPDATE animal SET AnimalTypeID=%s, BreedID=%s, Name=%s, Age=%s, Sex=%s, `Date`=%s, HouseTrained=%s, ExpCat=%s, ExpDog=%s, ExpCar=%s, ExpChild=%s, ExpAlone=%s, ExpOnlead=%s, ExpOffLead=%s, Valid=%s WHERE AnimalID=%s",

GetSQLValueString($_POST['AnimalTypeID'], "text"),

GetSQLValueString($_POST['BreedID'], "text"),

GetSQLValueString($_POST['Name'], "text"),

GetSQLValueString($_POST['Age'], "int"),

GetSQLValueString($_POST['Sex'], "text"),

GetSQLValueString($_POST['Date'], "date"),

GetSQLValueString(isset($_POST['HouseTrained']) ? "true" : "", "defined","1","0"),

GetSQLValueString(isset($_POST['ExpCat']) ? "true" : "", "defined","1","0"),

GetSQLValueString(isset($_POST['ExpDog']) ? "true" : "", "defined","1","0"),

GetSQLValueString(isset($_POST['ExpCar']) ? "true" : "", "defined","1","0"),

GetSQLValueString(isset($_POST['ExpChild']) ? "true" : "", "defined","1","0"),

GetSQLValueString(isset($_POST['ExpAlone']) ? "true" : "", "defined","1","0"),

GetSQLValueString(isset($_POST['ExpOnlead']) ? "true" : "", "defined","1","0"),

GetSQLValueString(isset($_POST['checkbox']) ? "true" : "", "defined","1","0"),

GetSQLValueString(isset($_POST['Valid']) ? "true" : "", "defined","1","0"),

GetSQLValueString($_POST['AnimalID'], "text"));

mysql_select_db($database_woodside, $woodside);

$Result1 = mysql_query($updateSQL, $woodside) or die(mysql_error());

$updateGoTo = "stage2.php";

if (isset($_SERVER['QUERY_STRING'])) {

$updateGoTo .= (strpos($updateGoTo, '?')) ? "&" : "?";

$updateGoTo .= $_SERVER['QUERY_STRING'];

}

header(sprintf("Location: %s", $updateGoTo));

}

mysql_select_db($database_woodside, $woodside);

$query_animal = "SELECT * FROM animal ORDER BY AnimalID DESC";

$animal = mysql_query($query_animal, $woodside) or die(mysql_error());

$row_animal = mysql_fetch_assoc($animal);

$totalRows_animal = mysql_num_rows($animal);

mysql_select_db($database_woodside, $woodside);

$query_Recordset1 = "SELECT * FROM breed WHERE AnimalTypeID=$_POST[AnimalTypeID]";

$Recordset1 = mysql_query($query_Recordset1, $woodside) or die(mysql_error());

$row_Recordset1 = mysql_fetch_assoc($Recordset1);

$totalRows_Recordset1 = mysql_num_rows($Recordset1);

//$breedtype = "SELECT BreedID, Breed.AnimalTypeID FROM Breed

//INNER JOIN AnimalType ON AnimalType.AnimalTypeID = Breed.AnimalTypeID";

$result = mysql_query($test) or die(mysql_error());

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<head>

<title>Untitled Document</title>

</head>

<body>

<td nowrap align="right">AnimalID:</td>

</tr>

<td nowrap align="right">AnimalTypeID:</td>

</tr>

<td nowrap align="right">BreedID:</td>

<?php

do {

?>

<?php

} while ($row_Recordset1 = mysql_fetch_assoc($Recordset1));

$rows = mysql_num_rows($Recordset1);

if($rows > 0) {

mysql_data_seek($Recordset1, 0);

$row_Recordset1 = mysql_fetch_assoc($Recordset1);

}

?>

</select>

</label>

</tr>

</tr>

</tr>

</tr>

</tr>

<td nowrap align="right">HouseTrained:</td>

</tr>

<td nowrap align="right">ExpCat:</td>

</tr>

<td nowrap align="right">ExpDog:</td>

</tr>

<td nowrap align="right">ExpCar:</td>

</tr>

<td nowrap align="right">ExpChild:</td>

</tr>

<td nowrap align="right">ExpAlone:</td>

</tr>

<td nowrap align="right">ExpOnlead:</td>

</tr>

<td nowrap align="right">ExpOffLead:</td>

<label>

<input <?php if (!(strcmp($row_animal['ExpOffLead'],1))) {echo "checked=\"checked\"";} ?> type="checkbox" name="checkbox" value="checkbox" />

</label></td>

</tr>

<td nowrap align="right">Valid:</td>

</tr>

</tr>

</table>

<?php echo $breedtype;?>

</form>

</body>

</html>

<?php

mysql_free_result($animal);

mysql_free_result($Recordset1);

?>

#

laffin · January 26, 2008

<?php
$step=isset($_POST['step'])?$_POST['step']:1;

if($step==1)  // Okay we start with step 1 our default
{
?>
<FORM METHOD="POST"><H2>Step 1</H2><BR>
<INPUT TYPE="HIDDEN" NAME="step" value="2">
<INPUT TYPE="TEXT" Name="username" value="John Doe">
<INPUT TYPE="SUBMIT">
</FORM>
<?
elseif($step==2)
{
$username=$_POST['username'];
?>
<FORM METHOD="POST"><H2>Step 2</H2><BR>
<INPUT TYPE="HIDDEN" NAME="step" value="3">
<INPUT TYPE="HIDDEN" NAME="username" value="<?=$username;?>">
<INPUT TYPE="TEXT" Name="email" value="John@Doe.com">
<INPUT TYPE="SUBMIT">
</FORM>
<?
}
else {
<H2>Step 3</H2><BR>
Username: <?=$_POST['username'];?> <BR>
Email: <?=$_POST['email'];?><BR>
}
?>[code]

Okay quick example of what I mean.
Step 1) Display form for Username
Step 2) Save the username in a hidden field on the form, and ask for email
Step 3) Display information from both steps above

Step 1 does no processing
Step 2 u can do processing before the form is displayed with the info from step 1
Step 3 is final processing this is where you want to update the database and do all the fun stuff

[/code]

Sign In

Please help me! Query results form another query

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information