Tricky SELECT

drax007

Hello again,

I have 3 tables:
Animal
AnimalID
Name
Age

Experience
ExpID
Experience

AnimalExperience
AnimalExpID
AnimalID*
ExipID"

With these 3 tables i need to create a SELECT query that will do the following:

..Search the AnimalExperience table for all records = AnimalID

..Then....Search the Experience table with the previously found records matching the AnimalExperience.ExpID with the Experience.ExpID and print the Name of the experience on the screen....

So far i have tried with this SELECT:

<?php
$query_new="SELECT 'ExpID', 'ExpID' * FROM experience, animalexperience WHERE experience.Experience = '$user_criteria' AND experience.ExpID = animalexperience.ExpID";
$new = mysql_query($query_new, $woodside) or die(mysql_error());
$row_new = mysql_fetch_assoc($new);
$totalRows_new = mysql_num_rows($new);

echo $new;
echo $user_criteria
?>

Based on something like:

SELECT A.*, B.* FROM some_table A, other_table B WHERE A.user = '$user_criteria' AND A.id = B.id

I cant get it working, can you help me please???

Thank You

NogDog

I think you're looking for something like:

$query_new = "
   SELECT a.Name, a.Age, e.Experience
   FROM
      Animal AS a
      INNER JOIN AnimalExperience AS ae ON a.AnimalID = ae.AnimalID
      INNER JOIN Experience AS e ON e.ExperienceID = ae.ExperienceID
   WHERE
      e.Experience = '$user_criteria'
";

drax007

Hi,

Thanks for the reply its appreciated as been stuck on this for a while.

In the Animal table all that is required is the AnimalID (which is used in the AnimalExperience table).

Refering to the line:

SELECT a.Name, a.Age, e.Experience

Would that part be needed (certainly the Name and Age)???

Thank You 🙂

NogDog

I was just assuming that in the resulting output you would want to display the animal's name and age. But you can change the select column list to whatever it is you actually need to output.

drax007

Hi,

Thanks thats brilliant, ill try it out now 🙂.

All i need outputed is the Experience (name of experience) So the user would be able to see what they have just inserted into the database(e.g. the ExpID).

Thanks for your help its appreciated 🙂

drax007

Hello again,

OK heres how it looks nows:

<?php require_once('Connections/woodside.php'); ?>
<?php error_reporting(E_ALL);
mysql_select_db($database_woodside, $woodside); 

 $query_new = "SELECT experience.Experience

   FROM
      Animal AS a
      INNER JOIN animalexperience AS animalexperience ON animal.AnimalID = animalexperience.AnimalID
      INNER JOIN Experience AS experience ON experience.ExpID = animalexperience.ExpID
   WHERE
      experience.Experience = '$user_criteria'
"; 
$new = mysql_query($query_new, $woodside) or die(mysql_error());
$row_new = mysql_fetch_assoc($new);
$totalRows_new = mysql_num_rows($new);

echo $users_criteria;
echo $new;
?>

I assumed the a's and ae's were abreviations for the names of my tables(hope that was right?).

I find i get the error: Unknown column 'animal.AnimalID' in 'on clause...

Ive checked and checked all the table names are correct. Also because i echo $user_criteria i get error: Notice: Undefined variable: user_criteria.

What do i do??
Dont give up on me 🙂

Thanks

NogDog

Yes, I was using the AS's to create table name aliases that would require less typing in the rest of the query. If you prefer to type out the full table name for each column prefix, then just get rid of the "AS a" expressions. If you do use them, then use the alias name for the prefixes as in my prior example.

In either case, make sure the table and column names are exactly correct, including capitalization.

drax007

Ok cool,

i decided to go the long way around and type the full names (table names are lower case)

<?php require_once('Connections/woodside.php'); ?>
<?php error_reporting(E_ALL);
mysql_select_db($database_woodside, $woodside); 

 $query_new = "SELECT Experience

   FROM
      experience
      INNER JOIN animalexperience ON animal.AnimalID = animalexperience.AnimalID
      INNER JOIN experience ON experience.ExpID = animalexperience.ExpID
   WHERE
      experience.Experience = '$user_criteria'
"; 
$new = mysql_query($query_new, $woodside) or die(mysql_error());
$row_new = mysql_fetch_assoc($new);
$totalRows_new = mysql_num_rows($new);

echo $user_criteria;
echo $row_new['experience'];
?>

I keep getting error: Not unique table/alias: 'experience', which is strange as im sure all the table names and attributes are correct!? I have also tested it with the aliases left in and get the same error but 'e' not 'experience'

Also what does the $_user_criteria do?????

Thanks 🙂

NogDog

I think you want the first table named after "FROM" to be "animal", not "experience".

The $user_criteria was taken from your original post. Presumably it represents a user input that you want to use to limit what is selected by the query.

drax007

Thanks for relpy again,

Change to experience and dont get the error any more. Thanks for your help with it!

Im a little confused about what SELECT to use the $user_criteria, i tired adding the following:

mysql_select_db($database_woodside, $woodside);
$query_user_criteria = sprintf("SELECT * FROM animalexperience ORDER BY AnimalExpID ASC");
$user_criteria = mysql_query($query_user_criteria, $woodside) or die(mysql_error());
$row_user_criteria = mysql_fetch_assoc($user_criteria);
$totalRows_user_criteria = mysql_num_rows($user_criteria);

When i run page i get: Resource id #4

What should i do??

Thanks 🙂

saidbakr

Hi,

As a general hint:

In such sitiuations you should ask yourself a clear and simple question to make life easier. The question is What are fields I should to select from the tables that I have, regarding less anything else because this will allow you analyze the big problem into some more little problems and for the SQL it will allow you to set the first poartion of the query string well. The slected fields before the from are those that gives you the data in the query result i.e.

SELECT tbl1.f1,tbl1,f2, tbl3.f1,tbl2.f3 FROM .... and there you may choose if you do inner join or left join or any thing else

drax007

Hi there,

im not sure i uderstand what you mean? Are you refering to the $user_criteria bit or the $new_query ??

The SELECT for $user_criteria is selecting all animalexperience (to keep it very simple)...

Then that result is supposed to be JOINED in the $new_query so all the ExpIDs that have been inserted (animalexperience table) by the user for a particular AnimalID record, will be matched against all the ExpID's in the experience table and then the Experience (name of experience from the experience table) will be outputed.

So the user would see the NAME of the ExpID for that AnimalID record they just inserted.

i think its close to working but im obviously missing something?? ?

Thanks🙂

saidbakr

What I mean that you should determine the fields that you need its records to be available in the result. Your case may be like a public library which there are Books table, Members and Borrowing table and you want to know something about books that have not been borrowed by a specific member. Is this right?

drax007

Hi,

Yes thats right BUT becuase the experience table only has ExpID & Name , I need to match all the records from that table to the animalexperience table(which has AnimalExpID, ExpID & AnimalID ) for ExpID to the records so i can find out what the experience names are(tht is then outputed on scvreen to how the user what NAME they have just iserted the ExpID for).

Previous to that the user will be inserting experiences that relate to the appropriate AnimalID.

For the purposes of keeping it simple i built the $user_criteria to SELECT all records in from animalexperience where the AnimalID is same as another query. This query just SELECT the animal table WHERE AnimalID = url parameter .

<?php require_once('Connections/woodside.php'); ?>
<?php
$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_user_criteria = sprintf("SELECT * FROM animalexperience WHERE AnimalID ='" . $row_animal['AnimalID'] . "'");
$user_criteria = mysql_query($query_user_criteria, $woodside) or die(mysql_error());
$row_user_criteria = mysql_fetch_assoc($user_criteria);
$totalRows_user_criteria = mysql_num_rows($user_criteria);
?>
<?php error_reporting(E_ALL);
mysql_select_db($database_woodside, $woodside); 

 $query_new = "SELECT Experience

   FROM
      animal
      INNER JOIN animalexperience ON animal.AnimalID = animalexperience.AnimalID
      INNER JOIN experience ON experience.ExpID = animalexperience.ExpID
   WHERE
      experience.Experience = '$user_criteria'
"; 
$new = mysql_query($query_new, $woodside) or die(mysql_error());
$row_new = mysql_fetch_assoc($new);
$totalRows_new = mysql_num_rows($new);
//echo $query_new;
echo $user_criteria;
echo $row_new['experience'];
?>
<?php
mysql_free_result($user_criteria);
mysql_free_result($new);
mysql_free_result($animal);
?>

Im not sure if im fundamentally doing something wrong now, but lots of the code has been given from forum peeps, so assume it all pukka.

Hmmm I dont know what to do now help, am i doomed? 🙂

saidbakr

Could you supply details about data types of your tables fields and its keys. I thought that AnimalID is an integer but your query sprintf deals with it as a string.

Please rember that I did not talk about SQL right now.

saidbakr

SELECT item.*
FROM item LEFT JOIN item_cat ON item_cat.item_id = item.item_id
WHERE item_cat.item_id IS NULL

Please get a look at the above SQL, where I have table for items named item and a table that handles items presented under categries which named item_cat and ofcourse there is a third table ( but it is not regarded in the SQL) that handles categories and named cat.

The item_cat table just only need to have new fields and both of them may regarded as forign key from cat as cat_id and the other from item as item_id and the both fields are set to be primary key in the item_cat table to introduce waht's called composite primary key.

The above SQL selects items that have not assigned to categories yet. It is some thing like the members who has not borrowed books from the library. Observe the LEFT JOIN.

drax007

Hi there,

The AnimalID is an int, but it in the user_criteria it calls for the WHERE AnimalID = $row_animal['AnimalID']............

The query $animal is one that will SELECT AnimalID that is = url parameter (which is a number).

Heres the table strucutre with all the relevant fields, including type and keys:
PLEASE NOTE: * = foreign key & underline = primary key.

I have 3 tables:
Animal table
AnimalID (int)
Name
Age

Experience table
ExpID (int)
Experience

AnimalExperience table
AnimalExpID (int)
AnimalID
ExipID

What ya think??? 🙂

p.s. just reading latest post, ty 🙂

drax007

Hi there,

Just trying to work out your tables here...got:

item table
item_id

item_cat table
item_cat_id
item_id*

So what else is in there??? Also what about this 3rd (composite) table that isnt shown??

I think we have pretty simiar structure here..This is well tricky though..

Thanks 🙂

saidbakr

Hi,

I regarded composite primary key and it is not a third table. The composite primary key is more than one primary key in one table. This prevents repetation of the two keys values togather.
For instance item_cat table. The two fields of item_cat are primary key:
cat_id
item_id

This means that every cat will have one item and any item will be represented in many cats

cat_id item_id
1 2
1 3
2 2
1 4
3 1

The third table you asked for is cat table. which is:
cat_id(int)*
cat_title
cat_description
etc...

From the sample of tables that you supplied, I can advice you with the following:

Keep the first two tables as they are, but ofcourse be sure that fields are primary keys
Animal table
AnimalID (int)primary key
Name
Age

Experience table
ExpID (int)* primary key
Experience

The change will be carried out to the third table:

AnimalExperience table
// Remove this-> AnimalExpID (int)
AnimalID(int)
ExipID(int)

You don't need this AnimalExpID, the other two primary keys are fine, if you have any other useful data to be replaced with it it will be more logic.

In these three tables we esatblish a state know, in the database design, by Normalization.
You have many to many relationship Animals and Their experience. This mean that it is possible for any animal to have one or more experience and in the same time the it is possible for any experience to have or to include one or more animal.

This is the core and the major use of Relational Database The ability of normalization

drax007

Hi there,

Thanks for reply, I was reading this message in bed...think it gave me nightmares 🙂

I think i understand about Normalisation, at least thought i did....

I found the the animal table the exerpeicne table has a MANY to MANY relationship and thats why i created the animalexperiences table.....

Please tell me if my tables are NOT Normalised properly???

Thanks

🙂