Wrox Programmer Forums
|
Beginning PHP Beginning-level PHP discussions. More advanced coders should post to the Pro PHP forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Beginning PHP section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 23rd, 2004, 11:54 AM
Registered User
 
Join Date: Nov 2004
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Linking Tables

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?



 
Old November 23rd, 2004, 05:21 PM
Friend of Wrox
 
Join Date: Jan 2004
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

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
 
Old November 23rd, 2004, 06:05 PM
Registered User
 
Join Date: Nov 2004
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


 
Old November 23rd, 2004, 07:58 PM
Friend of Wrox
 
Join Date: Jan 2004
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

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
 
Old November 23rd, 2004, 08:46 PM
Registered User
 
Join Date: Nov 2004
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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






Similar Threads
Thread Thread Starter Forum Replies Last Post
Linking pivot tables iacon Excel VBA 3 July 24th, 2006 01:25 AM
Access2000 Linking Tables Rchanga Access 1 December 21st, 2004 05:49 PM
Prevent linking to tables MG76 Access 2 March 12th, 2004 02:29 PM
Linking Tables Con Access VBA 2 September 24th, 2003 04:32 PM
Linking tables with multiple databases jlnash Access 1 August 14th, 2003 07:22 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.