Wrox Programmer Forums
Go Back   Wrox Programmer Forums > PHP/MySQL > PHP Databases
| Search | Today's Posts | Mark Forums Read
PHP Databases Using PHP in conjunction with databases. PHP questions not specific to databases should be directed to one of the other PHP forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the PHP Databases 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 May 23rd, 2004, 03:30 PM
Authorized User
 
Join Date: May 2004
Location: Blackpool, Lancashire, United Kingdom.
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to sencee Send a message via AIM to sencee Send a message via MSN to sencee Send a message via Yahoo to sencee
Default 2 Tables - 1 Statement - 1 Output - 1 Criteria

Hey,

Im stumped, i have tried everything and the nearest i can get is a ambiguity error message.

Basically, without pasting loads of code.

Page 1 get a ID to search for. This is sent to the next page in a function, lets call this function F1.

I want to display the contents of field ID, User of table Fault and field Status of table FaultAction, where ID = F1.

ID is the Pkey of both tables.
 
Old May 24th, 2004, 03:57 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 256
Thanks: 0
Thanked 0 Times in 0 Posts
Default

SELECT f.id, f.user, fa.`field status` FROM Fault AS f, FaultAction AS fa WHERE f.ID='$F1';

Or something like that? I suspect the ambiguity was over "ID", of which there were two: one in each table.
 
Old May 24th, 2004, 11:41 AM
Authorized User
 
Join Date: May 2004
Location: Blackpool, Lancashire, United Kingdom.
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to sencee Send a message via AIM to sencee Send a message via MSN to sencee Send a message via Yahoo to sencee
Default

This is what i can deduct as the real code from what you said.... the problem i get is that if it doesn't actualyl do the filter search, it just displays all the records that are in the faultaction table, and changes (in the oputput) all the faultIDs to the searched criteria

So if i search for fault ID 12,
it will display all the records in the table (regardless of matching the criteria) and display the ID value as whatever number you searched for (stress stress pull hair out)

[the code]

("SELECT f.FaultID, f.ReporterUsername, f.FaultType, fa.Status FROM fault AS f, faultaction AS fa WHERE f.FaultID='".$frm_search."'") or die(mysql_error());


but at least it is looking in the second table
 
Old May 24th, 2004, 11:43 AM
Friend of Wrox
 
Join Date: Nov 2003
Location: , , .
Posts: 1,285
Thanks: 0
Thanked 2 Times in 2 Posts
Default

Do you have a table called 'fa'?
 
Old May 24th, 2004, 12:14 PM
Authorized User
 
Join Date: May 2004
Location: Blackpool, Lancashire, United Kingdom.
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to sencee Send a message via AIM to sencee Send a message via MSN to sencee Send a message via Yahoo to sencee
Default

no, i have a table called faultaction where i was assigning the faultaction table the prefix fa.

anyway.. i have sorted that problem, but now i have another.

It combines the tables, but now it doesn't actually filter the search.

When u put say 31 in the search. It outputs all the records in the talbe (regardless whether they match the criteria or not) and also displays in the FaultID columm, 31 for every record.

 
Old May 25th, 2004, 04:43 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 256
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by Snib
 Do you have a table called 'fa'?
He doesn't need to. I was suggesting aliasing each using AS.
 
Old May 25th, 2004, 04:50 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 256
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Snipped:
Quote:
quote:Originally posted by sencee
 This is what i can deduct as the real code from what you said.... the problem i get is that it doesn't actualyl do the filter search, it just displays all the records that are in the faultaction table, and changes (in the oputput) all the faultIDs to the searched criteria

("SELECT f.FaultID, f.ReporterUsername, f.FaultType, fa.Status FROM fault AS f, faultaction AS fa WHERE f.FaultID='".$frm_search."'") or die(mysql_error());

but at least it is looking in the second table
Well, should it be "WHERE fa.FaultID='".$frm_search."'", then? Which table are you trying to filter against? You see, my suggestion was to alias fault AS f and faultaction AS fa, so fa.FaultID is the FaultID field from "faultaction ", while f.FaultID is the FaultID from "fault".

Is that any clearer?
Dan
 
Old May 25th, 2004, 04:53 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 256
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Snipped:
Quote:
quote:Originally posted by sencee
 When u put say 31 in the search. It outputs all the records in the talbe (regardless whether they match the criteria or not) and also displays in the FaultID columm, 31 for every record.
Ah, right. I'm with you. Just select fa.FaultID instead. f.FaultID is going to be 31 in every case, since you actually stipulated that in your WHERE clause :).

Frustrating when it does exactly what you tell it to, instead of exactly what you mean, isn't it?




Similar Threads
Thread Thread Starter Forum Replies Last Post
Query criteria from two tables in main connection MikeC MikeC Infopath 0 November 21st, 2007 03:16 AM
AND/OR criteria in a JOIN fizzerchris Access 5 July 18th, 2007 02:37 PM
Creating an INSERT INTO SQL statement for 2 tables sburgess Access 1 April 18th, 2007 08:52 AM
list all tables using SELECT Statement rylemer Access 5 March 7th, 2005 08:58 PM
Efficient SQL Statement for Large Tables ritag SQL Server 2000 3 April 19th, 2004 11:03 AM





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