 |
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
|
|
|

November 23rd, 2004, 11:54 AM
|
Registered User
|
|
Join Date: Nov 2004
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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?
|

November 23rd, 2004, 05:21 PM
|
Friend of Wrox
|
|
Join Date: Jan 2004
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
|
|
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
|

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

November 23rd, 2004, 07:58 PM
|
Friend of Wrox
|
|
Join Date: Jan 2004
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
|
|
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
|

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