Tricky Select

[yandoo]

Hi there,

 

I was hoping for a little help please.

 

I have 3 tables in my database:

 

Animal (stores info on animals)

AnimalID

Name

Age

 

Experience(stores info experiences, i.e. house trained, on lead, off lead, cars )

ExpID

Experience name

 

AnimalExperience(combines primary keys Animal & Experience)

AnimalExpID

AnimalID*

ExipID"

 

NOTE: (* = foregin key, underline = primary key)

 

The bit i am stuck on is regarding adding AnimalExperiences. I have a page (hyperlink from pervious page makes sure that AnimalID number is appropriate) that a user inserts AnimalExperiences for a specifc animal. After the user inserts the record the a query searchs AnimalExperiences table for all ExpID's and dislpays them.

 

This works fine..... but..It displays the ExpID and i need it to display Experience Name

 

So im thinking that will involve the ExpID's that relate to an AnimalID are then queried against the Experience table to find the Experience Names.

 

I have tried to do this so far:

# $editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
   $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}
  
if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
   $insertSQL = sprintf("INSERT INTO animalexperience (AnimalExpID, AnimalID, ExpID) VALUES (%s, %s, %s)",
                        GetSQLValueString($_POST['animalexperienceid'], "int"),
                        GetSQLValueString($_POST['animalid'], "int"),
                        GetSQLValueString($_POST['select'], "text"));  
  
   mysql_select_db($database_woodside, $woodside);
   $Result1 = mysql_query($insertSQL, $woodside) or die(mysql_error());
  
   $insertGoTo = "add_animal_exp.php";
   if (isset($_SERVER['QUERY_STRING'])) {
     $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
     $insertGoTo .= $_SERVER['QUERY_STRING'];
   }
   header(sprintf("Location: %s", $insertGoTo));
}
  
$colname_animal = "-1";
if (isset($_GET['recordID'])) {
   $colname_animal = (get_magic_quotes_gpc()) ? $_GET['recordID'] : addslashes($_GET['recordID']);
}
mysql_select_db($database_woodside, $woodside);
$query_animal = sprintf("SELECT * FROM animal WHERE AnimalID = %s", $colname_animal);
$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_experience = "SELECT * FROM experience ORDER BY ExpID ASC";
$experience = mysql_query($query_experience, $woodside) or die(mysql_error());
$row_experience = mysql_fetch_assoc($experience);
$totalRows_experience = mysql_num_rows($experience);
  
$maxRows_animalexperience = 10;
$pageNum_animalexperience = 0;
if (isset($_GET['pageNum_animalexperience'])) {
   $pageNum_animalexperience = $_GET['pageNum_animalexperience'];
}
$startRow_animalexperience = $pageNum_animalexperience * $maxRows_animalexperience;
  
mysql_select_db($database_woodside, $woodside);
$query_animalexperience = "SELECT * FROM animalexperience";
$query_limit_animalexperience = sprintf("%s LIMIT %d, %d", $query_animalexperience, $startRow_animalexperience, $maxRows_animalexperience);
$animalexperience = mysql_query($query_limit_animalexperience, $woodside) or die(mysql_error());
$row_animalexperience = mysql_fetch_assoc($animalexperience);
  
if (isset($_GET['totalRows_animalexperience'])) {
   $totalRows_animalexperience = $_GET['totalRows_animalexperience'];
} else {
   $all_animalexperience = mysql_query($query_animalexperience);
   $totalRows_animalexperience = mysql_num_rows($all_animalexperience);
}
$totalPages_animalexperience = ceil($totalRows_animalexperience/$maxRows_animalexperience)-1;
  
//BELOW IS QUERY THAT SEARCHES DB FOR AnimalExperiences WITH AnimalID & DISPLAYS ExpID
  
mysql_select_db($database_woodside, $woodside);
$query_test = "SELECT * FROM animalexperience WHERE AnimalID='" . $row_animal['AnimalID'] . "'";
$test = mysql_query($query_test, $woodside) or die(mysql_error());
$row_test = mysql_fetch_assoc($test);
$totalRows_test = mysql_num_rows($test);
  
// I NEED FOR IT TO DISPLAY THE Experience (which is the name of the exeperience)
  
mysql_select_db($database_woodside, $woodside);
$query_experiencelist = "SELECT * FROM experience WHERE ExpID NOT IN (SELECT ExpID FROM animalexperience WHERE AnimalID='" . $row_test['AnimalID'] . "')";
$experiencelist = mysql_query($query_experiencelist, $woodside) or die(mysql_error());
$row_experiencelist = mysql_fetch_assoc($experiencelist);
$totalRows_experiencelist = mysql_num_rows($experiencelist);
?>
  
<form id="form1" name="form1" method="POST" action="<?php echo $editFormAction; ?>">
   <label>
  
  
    <table width="362" border="0">
  <tr>
    <td width="109"><span class="style11">Add Experience: </span></td>
    <td width="108" align="center"><select name="select">
     <?php
do {  
?>
     <option value="<?php echo $row_experiencelist['ExpID']?>"><?php echo $row_experiencelist['Experience']?></option>
     <?php
} while ($row_experiencelist = mysql_fetch_assoc($experiencelist));
   $rows = mysql_num_rows($experiencelist);
   if($rows > 0) {
       mysql_data_seek($experiencelist, 0);
       $row_experiencelist = mysql_fetch_assoc($experiencelist);
   }
?>
   </select>
   </label></td>
    <td width="131" align="center"><input type="submit" name="Submit" value="Submit" /> </td>
  </tr><tr><td> </td>
    <td align="center"> </td>
    <td align="left"> </td>
  </tr><tr><td> </td>
    <td align="center"><table width ="108" class="newbord">
   <tr><td width="108" align="center"><span class="style11 style15">This Animal's Experience:</span></td>
</tr>
   <?php do { ?>
    <tr><td align="center"><span class="style12"><?php echo $row_test['ExpID']?></span></td>
      </tr>
          
     <?php } while ($row_test = mysql_fetch_assoc($test)); ?>
     </table></td>
    <td align="left"> </td>
  </tr></table>
  
<table width="362">
   <tr>
     <td></td>
     <td align="center"> </td>
     <td></td>
   </tr>
   <tr><td width="109">
    </td><td align="center"><a href="add_animal2.php?recordID=<?php echo $row_animal['AnimalID']; ?>" class="style7 tyle16">CONTINUE</a></td>
    <td width="131"></td> </tr></table>
    
    <input name="animalexperienceid" type="hidden" id="animalexperienceid" value="<?php echo $totalRows_animalexperience +1 ?>" />
     <input name="animalid" type="hidden" id="animalid" value="<?php echo $row_animal['AnimalID']; ?>" />
    
       <input type="hidden" name="MM_insert" value="form1">
  
</form>
<?php
mysql_free_result($animal);
mysql_free_result($experience);
mysql_free_result($animalexperience);
?>

 

 

The Query in Questiion:

# mysql_select_db($database_woodside, $woodside);
$query_test = "SELECT * FROM animalexperience WHERE AnimalID='" . $row_animal['AnimalID'] . "'";
$test = mysql_query($query_test, $woodside) or die(mysql_error());
$row_test = mysql_fetch_assoc($test);
$totalRows_test = mysql_num_rows($test);

 

So the results fo this query would need to be further queried against the Experience table to find out the experience name!!!???

 

Im not sure where to go from here,  could you help me please??

 

Thank You 

[Barand]

<?php

$query_test = "SELECT e.`Experience name` 
              FROM animalexperience ae
                 INNER JOIN experience e ON ae.animalexpid = e.expid
              WHERE AnimalID='" . $row_animal['AnimalID'] . "'";

[yandoo]

Hi there,

 

Thanks for the quick reply thats ACE!!

 

Just tried it out and cant get it working quite right ok so i changed the $test query to your suggestion and echoed out the query itself and tried to ru the page...Found i got A RESOURCE # 9?????

 

Echoing out the query woprked though: SELECT e.`Experience` FROM animalexperience ae INNER JOIN experience e ON ae.animalexpid = e.expid WHERE AnimalID='52'

 

What am i doing worng??

 

Thanks

[cooldude832]

this is what u did
[code]
<?php
$q = "Select this from `that`";
$r= mysql_Query($q);
echo $r;
?>

this is what u want I think

<?php

$q = "Select this from `that`

$r = mysql_query($q);

echo $q;

?>

[/code]

[uniflare]

when trying to echo a mysql result resource (like from a mysql_query), you should use a result function like mysql_result or mysql_fetch_array etc.

 

try

print_r(mysql_fetch_array($result));

 

this should print a readable array of the result resource, you would assign it to a variable and call it normally.

[Daniel0]

First of all, PHP functions are case-insensitive, so whether he does mysql_Query() or mysql_query() doesn't matter. The reason why it isn't working is because that function returns a resource, so when you print it to the screen it will show the resource id. To get the actual results of a query you'll have to one of the mysql_fetch_*() functions.

[uniflare]

cooldude832 was actually pointing out how to eacho the query, he obviously thought this person was getting the resource id instead of showing the query string.

 

yandoo actually managed to eacho the query string, but in fact got the resource id instead of the data from mysql he expected.

[Daniel0]

Oh... my bad.

[cooldude832]

Oh... my bad.

 

 

yeah it was a sloppy post I walked up and got some food and didn't realize its sloppyness

[yandoo]

hi there,

 

thanks for replies

 

I see what you mean with resources error now thanks, when i echo $query_test and NOT just echo $query the resource error disappears - AcE!

 

I also tried  print_r(mysql_fetch_array($test)); but nothing is outputted???

 

There are no errors now when runing the page but the the trouble is the Experience names arent outputed at all all either???

 

Oh man why??

 

Thanks

 

[Barand]

try

<?php
$sql = "SELECT a.name, a.age, GROUP_CONCAT(e.experience SEPARATOR '<br />') as experiences
        FROM animal a
            INNER JOIN AnimalExperience ae ON a.animalID = ae.animalID
            INNER JOIN Experience e ON ae.AnimalExpID = e.ExpID
        GROUP BY a.name";
        
echo "<table border='1'>";
echo "<tr><th>Name</th><th>Age</th><th>Experiences</th></tr>";

$res = mysql_query($sql) or die (mysql_error()."<pre>$sql</pre>");
while (list($name, $age, $exp) = mysql_fetch_row($res))
{
    echo "<tr><td>$name</td><td>$age</td><td>$exp</td></tr>"; 
}
echo '</table>';
?>

[yandoo]

Hi,

 

Tried it out and theres no errors at all but again nothing is outputed??

 

Something could be conflicting with something else it perhaps???

 

I really appreciate your help on this

 

Thanks

[cooldude832]

take the output of $sql to phpmyadmin and run it and tweak stuff maybe it is truthfully returning 0 rows

[yandoo]

Hi there,

 

I justed tested it in phpmyadmin and your right it runs, but returns 0 rows...Thats a bit wierd because there are records in the the AnimalExperiences table... But only for AnimalID number 52 (as in testing stage.

 

The user gets to this page via a hypelink on prev page that parses the AnimalID parameter....So then on the form the AnimalID is automatically inserted into each AnimalExperience record.

 

Its like with this SELECT query there isnt a need to know the AnimalID or knowing where for it to be included in it? The only info that is to be outpued is just the Experience name.

 

Could this be the reaon it isnt bringing any records up??

 

I have bee stuck on this for a long time now

 

Thanks

[cooldude832]

try using LEFT JOIN instead of INNER JOIN

I believe an INNER JOIN fails when those criteria result in empty sets in those tables, however left join shouldn't

[yandoo]

Sorry what  meant is nothing is outputed, although Experience (name of experience) is all that is required to be.

 

[Barand]

For it to work you need animals matching animalExperiences on animalID AND experiences matching animalExperiences on expID

[yandoo]

Hi there,

 

Ok cool, if i use LEFT join and test in phpmyadmin it works sort of!! It brings all 29 out of 52 (There are only 2 AnimalID records that have AnimalExperiences) animal records showing:  name, age & experiences. The experience column is NULL for every record??????

 

All thats needed to be outputed is just the Experiences (names)themselves that have same AnimalID as was parse through link from previous page...

 

Were close, i can feel it in my  bones...

 

Thank You

[yandoo]

Sorry...to be clear again what i meant was....

 

All thats needed to be outputed is just the Experiences (names)themselves that have same ExpID(in Experience table) as the ExpIDs found in AimalExperiences table which have the Same AnimalID thats parsed through a link from previous page...

 

Thanks