I would like to link 2 tables within a database, each table has a common denominator of a unique number - how do I do this. I cuurently use both tables within the same query but have come up against various problems, my hope is that linking the tables by the relevant field will be the answer. Can anyone advise?

Using foreign keys, primary keys and relationships a 'basic' description:
1..primary keys are a field in a table that is unique (usually an auto incrementing integer)
2..Forign keys are a field in a table that represent a primary key field in another table

An example of linking tables, we have two tables USERS and COUNTRIES:
USERS TBLE
id = int (unique auto incrementing primary key)
CountryId = int (foriegn key to country table)
firstName = varChar
lastName = varChar
active = bit

COUNTRY TBLE
id = int (unique auto incrementing primary key)
description = varchar

NOTE 1:If you are thinking why have a seperate table for the countries, you need to research normalizing tables (good data base design)

NOTE 2: The CountryId field in the USERS tble MUST represent a number in the id field of the country table for a true relationship. Once enforced, you cant even enter a number directly into the CountryId field if it isn’t present in the id field of the country table.

So to join them:

SELECT users.id,users.firstname,users.lastName,country.de scription FROM users LEFT OUTER JOIN country ON users.countryID = country.ID WHERE users.active = " & FALSE & ";"

;;;each table has a common denominator of a unique number
Each table must also have a foreign key

;;;how do I do this
see above

;;;I currently use both tables within the same query but have come up against various problems
What problems exactly?


Wind is your friend
Matt

Hi Matt

My problem lies with the following code:
$Query = "SELECT * from $TableName,$TablePhoto WHERE $TableName.RegNo = $TablePhoto.title LIMIT $start, $end";

(line 52) while ($Row = mysql_fetch_array ($Result))

this produces an error message of:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /content/StartupHostPlus/u/s/www.usedcars.gb.com/web/Display.php on line 52

I thought by creating a link between the 2 tables I could avoid the error message. Or do you have an alternative solution?
My goal is to enable the results to be split over several pages.

Regards

Bruce

Without seeing your table structure and query printed at run time it’s hard to point out where the error is. Do all the variables in your query have values? Are these values what they should be (EG are $start and $end integers)?

Generally your error means your query didn't execute. I recommend adding an or die() line to : $Row = mysql_fetch_array ($Result)
EG:
$Result = mysql_fetch_array($Query) or die (mysql_error()."<br>Couldn't execute query: $Query");

To troubleshoot this I would:
1..Add the or die syntax to your query
2..Copy the query from within the browser
3..Paste and run it directly in mySql, do you get a result set?

;;;My goal is to enable the results to be split over several pages
I am not sure what you are getting at here. If a page needs a result set do a query, if you want to use the same result set on another page without doing a query, carry then from page to page in hidden form variables.


Wind is your friend
Matt

Hi Matt
Thanks for your help, I have successfully joined my tables! The error came from putting the LIMIT in single quotes rather than Double.

One further question if you dont mind:

I have used the following code

start = $_GET['start'];
$end = 4 + $start;
$Query = "SELECT * FROM VehicleStock LEFT JOIN cpg11d_pictures ON VehicleStock.RegNo = cpg11d_pictures.title LIMIT $start,$end";

to LIMIT the number of pictures per page, starting the page as Display.php?start=0

The problem I have is that the 1st page is fine with 4 pictures, however page 2 now has 7 pictures and not 4, with page 3 having 3 pictures. This gives a total of 14 pictures when I only started with 11 !!!! How can I restrict each page to i.e. 4 pictures?

I really am appreciating your assitance

Regards

Bruce