Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > PHP/MySQL > Beginning PHP
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old November 23rd, 2004, 11:54 AM
Registered User
 
Join Date: Nov 2004
Location: , , United Kingdom.
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?



Reply With Quote
  #2 (permalink)  
Old November 23rd, 2004, 05:21 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
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
Reply With Quote
  #3 (permalink)  
Old November 23rd, 2004, 06:05 PM
Registered User
 
Join Date: Nov 2004
Location: , , United Kingdom.
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


Reply With Quote
  #4 (permalink)  
Old November 23rd, 2004, 07:58 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
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
Reply With Quote
  #5 (permalink)  
Old November 23rd, 2004, 08:46 PM
Registered User
 
Join Date: Nov 2004
Location: , , United Kingdom.
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

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 01:32 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.