gwpaul — 2011-03-23T11:59:03-04:00 — #1
I have a query that is calling data from the wrong row.
If I create a new row and do not fill information in some of the rows, it brings the information from a different row that has a different ai unique id.
This is the query.
$query_profile = "SELECT * FROM law_firm , attorney WHERE law_firm.firm_id ='$thisfirm'";
$query_limit_profile = sprintf("%s LIMIT %d, %d", $query_profile, $startRow_profile, $maxRows_profile);
$profile = mysql_query($query_limit_profile, $assess_remote) or die(mysql_error());
$row_profile = mysql_fetch_assoc($profile);
This is the session variable I am using.
<?php $thisfirm=$_SESSION['firm_id']; ?>
Someone tell me where I am going wrong?
guido2004 — 2011-03-23T12:02:11-04:00 — #2
You are joining two tables (law_firm and attorney) without specifying the join conditions. That means the result will be a cross join : all rows from law_firm will be joined with all rows from attorney.
gwpaul — 2011-03-23T12:06:39-04:00 — #3
Thank you very much for your quick reply. So I insert a join into the query?
Again, thank you.
guido2004 — 2011-03-23T12:10:09-04:00 — #4
You already have an (implicit) join in the query:
FROM law_firm , attorney
That comma means a join between the two tables, law_firm and attorney.
What is missing is the join condition. In the WHERE clause you should add a line that says what columns to use to join. What is the logical connection between the two tables? How do you know which attorney works for what firm?
gwpaul — 2011-03-23T12:21:58-04:00 — #5
I use a foreign key (innodb) to link the two tables.
I also have a 4 field form that inserts 3 fields into law_firm and 1 into attorney.
gwpaul — 2011-03-23T12:36:33-04:00 — #6
Thank you again for your help, I believe the issue is solved with your kind guidance.
This is the code.
$query_profile = "SELECT * FROM law_firm , attorney WHERE law_firm.firm_id ='$thisfirm' AND attorney.firm_id ='$thisfirm' ";
Please let me know if you feel there is a better way.
Again, thank you!
cups — 2011-03-23T12:39:00-04:00 — #7
To illuminate what happens with joins you might find this old post very helpful. I still have it pinned up on my wall.
gwpaul — 2011-03-23T12:42:50-04:00 — #8
I'm afraid my walls are not big enough to keep handy all the information I am lacking...
Thank you for reply and the link!
spacephoenix — 2011-03-23T13:50:14-04:00 — #9
Just to add to the link to an old forum post (post #7 by Cups), there is this one which visually shows using venn diagrams.
gwpaul — 2011-03-23T15:40:49-04:00 — #10
So the original page/query is working fine, however on a different page I need to expand the query.
I need to data from both law_firm and attorney, however I am still getting results from all rows. There is only one row with the status of 2, but I get all the rows.
If I remove attorney, from the query, it works fine, but does not get me the information from the attorney table.
$query_profile = "SELECT * FROM attorney, law_firm WHERE law_firm.status = '2' AND law_firm.county_0 ='$_POST[county]' or law_firm.county_1 ='$_POST[county]' or law_firm.county_2 ='$_POST[county]' or law_firm.county_3 ='$_POST[county]' or law_firm.county_4 ='$_POST[county]' AND state = '$_POST[state]' ";
Does it make more sense to create a seperate query for the attorney table? If so, how would I reference the original query?
Thank you again.
cups — 2011-03-23T15:44:27-04:00 — #11
@SpacePhoenix Nice one, that is very memorable, I shall quote that in future too.
cups — 2011-03-23T15:55:13-04:00 — #12
SELECT attorney.thing1, attorney.thing2
LEFT JOIN attorney
ON law_firm.firm_id = attorney.law_firm
law_firm.firm_id = 2
(other where clauses here)
Make the join implicit using the fuller syntax and it should work, though I am shaky when it comes to so many OR clauses.
If it does not - then consider posting some or all of your table schema* and say, 2 or 3 rows which typify your problem along with the result you want.
- submit DESCRIBE law_firm into your database as an sql query to get this.
gwpaul — 2011-03-23T16:31:15-04:00 — #13
It looks like I will be able to get it to work using your advice and example.
Does this mean you cannot SELECT * on a join?
Thank you for your help.
r937 — 2011-03-23T17:10:45-04:00 — #14
thank you, thank you, thank you
the exact same diagrams are also in Simply SQL on pages 38-45
r937 — 2011-03-23T17:12:19-04:00 — #15
no, you can... but you shouldn't
guido2004 — 2011-03-24T06:03:57-04:00 — #16
I'm sure you've already figured it out with all the help you've got, but let me say it anyway : this query works, but is not the correct way to join the two tables.
The right way with implicit join:
FROM law_firm ,
WHERE [B]<font color='"Red"'>law_firm.firm_id = attorney.firm_id </font>[/B]
AND law_firm.firm_id ='$thisfirm'
The right way with INNER JOIN (preferable):
INNER JOIN attorney
[B]<font color='"Red"'>ON law_firm.firm_id = attorney.firm_id </font>[/B]
WHERE law_firm.firm_id ='$thisfirm'
r937 — 2011-03-24T06:30:53-04:00 — #17
WHERE law_firm.firm_id = '$thisfirm' works, but only in mysql, which silently performs an implicit conversion
WHERE law_firm.firm_id = $thisfirm is much preferred
(assuming firm_id is a numeric column, e.g. integer)
gwpaul — 2011-03-24T11:44:04-04:00 — #18
Just when I think I have it, a curve comes in.
Parts of the query work, other parts do not.
I have the query to select where one of the "county columns" in law_firm contains the name of a county, that works.
However, it does not filter the state, so if there are more than one "chester counties" in the states, they all show up.
I also have a filter of status, that does not work.
It would appear that my AND statements are not working or correct.
$query_profile = "SELECT attorney.fname, attorney.initial,
attorney.lname,attorney.email, attorney.att_image, attorney.lawyer_description,
law_firm.fax, law_firm.url, law_firm.description,
law_firm.realestate, law_firm.business, law_firm.criminal,law_firm.bankruptcy, law_firm.family_law,
law_firm.labor, law_firm.estate, law_firm.pi, law_firm.general
FROM law_firm LEFT JOIN attorney ON law_firm.firm_id = attorney.firm_id
law_firm.county_0 ='$_POST[county]' or law_firm.county_1 ='$_POST[county]' or
law_firm.county_2 ='$_POST[county]' or law_firm.county_3 ='$_POST[county]' or
law_firm.county_4 ='$_POST[county]' AND law_firm.state = '$state'
AND attorney.firm_id = 'law_firm.firm_id' AND law_firm.status = '2' ";
Thanks again for all the help.
r937 — 2011-03-24T11:55:07-04:00 — #19
it's actually caused by you mixing ANDs and ORs
they work like addition and multiplication
for example, what is 1+2*3 ? is it 1+(2*3) or (1+2)*3 ?
to mix ANDs and ORs, always use parentheses to ensure you get exactly the logic you want
also, another thing that is affecting your results is this --
attorney.firm_id = 'law_firm.firm_id'
the attorney's firm_id is never going to be equal to that character string
let's revise the query to avoid the ORs problem...
ON attorney.firm_id = law_firm.firm_id
WHERE '$_POST[county]' IN ( law_firm.county_0
, law_firm.county_4 )
AND law_firm.state = '$state'
AND law_firm.status = '2'
finally, you have a repeating group in the law_firm table, those 5 county columns -- these should be normalized into a separate table because as they are now, they will slow down this query
gwpaul — 2011-03-24T12:10:49-04:00 — #20
Thank you for your help, it works perfect.
This should always match since it is the foreign key, I was concerned about redundancy. If it does not match then I suspect I have a big problem on my hands.
attorney.firm_id = 'law_firm.firm_id'
Thank you again for taking your time to help.
next page →