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?
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.
Thank you very much for your quick reply. So I insert a join into the query?
Again, thank you.
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?
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.
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!
To illuminate what happens with joins you might find this old post very helpful. I still have it pinned up on my wall.
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!
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.
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.
@SpacePhoenix Nice one, that is very memorable, I shall quote that in future too.
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.
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.
thank you, thank you, thank you
the exact same diagrams are also in Simply SQL on pages 38-45
no, you can... but you shouldn't
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'
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)
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.
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
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 →