Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 15 of 21
Thread: problem with join
08-15-2011, 11:36 PM #1
problem with join
I have tried several things but i just cant figure out how to join two tables with the same id.
adverts and adverts_dropdown both have adv_user, the members table uses mem_userid.
im trying an alias but i dont know if can compare the alias or what. I have read the mysql page and it just confuses me more and more.
obviously this does not work.
//find all others with same value in all tables table and exclude the session user self $queryfind="SELECT *, adv_userid as id, LEFT JOIN adverts ON (id=adv_userid) LEFT JOIN members ON (adv_userid=mem_userid) FROM adverts_dropdown WHERE adv_seeking = '$myvalue' AND mem_suspend='N' AND adv_paused='N' AND adv_approved='1' AND adv_userid != '$Sess_UserId' ORDER BY RAND()";
Last edited by durangod; 08-15-2011 at 11:39 PM.
08-15-2011, 11:45 PM #2
ok i think i got it, you have to spell it out
(adverts_dropdown.adv_userid = adverts.adv_userid)
08-16-2011, 12:01 AM #3
nope now its telling me
Column 'adv_seeking' in field list is ambiguous which is exactly what was fighting with before so im back to square one lmao omg ...
Code:$queryfind="SELECT *,adv_seeking from adverts_dropdown LEFT JOIN adverts ON (adverts.adv_userid=adverts_dropdown.adv_userid) LEFT JOIN members ON (adv_userid=mem_userid) WHERE adverts_dropdown.adv_seeking = '$myvalue' AND mem_suspend='N' AND adv_paused='N' AND adv_approved='1' AND adv_userid != '$Sess_UserId' ORDER BY RAND()";
08-16-2011, 12:36 AM #4
You have to also qualify the name in your SELECT list.
*BUT*Code:SELECT *, SOMETABLENAME.adv_seeking FROM ...
But because you are doing SELECT * you are *already* getting that field. * means "all fields in all tables".
Now, if a field name appears in more than one table (as MUST be the case here, or MySQL wouldn't be telling you that the name is ambiguous), then you have problems:
How, in your PHP code, will you be able to figure out *WHICH* adv_seeking you are getting from the record??? Even if they both have the same value, PHP may give you an error trying to figure out which one you mean. [It may not; ASP would, but maybe PHP will just choose the first one it sees...dunno.]
You should NEVER NEVER NEVER use SELECT * when you join tables, in my opinion. You really shouldn't even do so when using only one table, but especially with multiple tables it is guaranteed that you are getting at least one or two fields that you don't need (that is, the fields you do the JOIN ON must match, if nothing else, so why get both?).
Also, if a name *is* unique among multiple tables, it is still much much clearer if you qualify it by table name in your query.
For example, in that query where you have
AND adv_approved='1', how is anybody reading that supposed to know which table adv_approved is from? I would *always* prefix the field name with the table name, thus:
adverts_dropdown.adv_approved=1(I'm almost sure you don't need/want apostrophes around the 1 there).
FInally, if you had done that, I could tell if the query is likely to have other problems. (HINT: if any of those fields in your WHERE clause are *NOT* from the table adverts_dropdown then the query is wrong.)
08-16-2011, 12:53 AM #5
thanks, one of the reasons i use select * in a normal query even if i only need a few fields (i may be wrong here) is that i read a while back in the docs that it is better to do all the fields than to pick and choose, because the querys are faster and at least you have everything there for the taking. (is that not true?)
now the reason i use '1' and not 1 is because i want to make sure that 1 is an integer an i dont want it seen as a string so thought that put '1' would tell mysql that it has to be an integer value.
adv_paused is in adverts
adv_approved is only in the adverts table
mem_suspend is only in the members table
adv_seeking is in both adverts and adverts dropdown
the adv_seeking in adverts is not written to so i want the one in adverts_dropdown.
heres what confuses me bud.
i found this elsewhere in the software by poking around and it seems to work so i used it as a model.
so all i did was copy it and take out the joins id did not need and adapt the WHERE and it does not work and that blows my mind bud lol..Code:$resquery="SELECT *, (YEAR(CURDATE())-YEAR(adv_dob)) - (RIGHT(CURDATE(),5) < RIGHT(adv_dob,5)) AS age, timestamp AS session_active, mem_timeout FROM adverts LEFT JOIN members ON (adverts.adv_userid=mem_userid) LEFT JOIN adverts_dropdown ON (adverts_dropdown.adv_userid = adverts.adv_userid) LEFT JOIN geo_country ON (adv_countryid = gcn_countryid) LEFT JOIN geo_state ON (adv_stateid = gst_stateid) LEFT JOIN geo_city ON (adv_cityid = gct_cityid) $gallery_from $qryphotos WHERE adv_paused='N' $qrygender $qrygeo $qrywhere $qrykey $qryAge $qryOnline $qryheight AND adv_approved=1 $qryorder"; $_SESSION['s_querystring'] = $resquery;
i know this could all be avoided in the beginning if developers would name their table field values different lol
what i am doing is i am preloading a query to use in my pagination image display, which requires all fields from all those tables for those selected people, so instead of doing one query on adverts_dropdown and then doing a while loop on the other two tables i decided to try to do the join, but im a so burned out right now, i been at that since 4am and my brain has had it. I dont understand why mysql requires me to to list all 50 field names as table.name for every table i do a join on. OMG that query would be a mile long and my fingers would fall off LMAO
Last edited by durangod; 08-16-2011 at 01:13 AM.
08-16-2011, 01:25 AM #6
Exactly the opposite is true.one of the reasons i use select * in a normal query even if i only need a few fields (i may be wrong here) is that i read a while back in the docs that it is better to do all the fields than to pick and choose, because the querys are faster and at least you have everything there for the taking. (is that not true?)
Look, MySQL is a *process* that is separate from PHP. That means that in order for MySQL to send data to PHP, it has to bundle it up and then ship it across a process boundary. Depending on the machine you are on, it might do that via shared memory or a named pipe or or or... Doesn't matter which, really. The more data you ship across, the longer it takes.
If it so happens that your MySQL DB is on a different machine than your web server (often true if you are using a shared host or if you have a really big site), then the data even has to be shoved across a "wire" from one machine to the other. So ditto: the more data, the worse it is.
Using SELECT to limit yourself to exactly and only the fields that your PHP code needs is by far the best strategy.
Again, exactly backwards. If you put apostrophes around *ANY* value, then it no longer must be a number at all. It is *ONLY* numbers that can be coded without apostrophes. So the very best way to ensure that MySQL (or any SQL DB) sees something as a number is to omit the apostrophes.now the reason i use '1' and not 1 is because i want to make sure that 1 is an integer an i dont want it seen as a string so thought that put '1' would tell mysql that it has to be an integer value.
[Okay, technically, there are a few CONSTANTS, such as the keywords NULL and TRUE and FALSE that don't use apostrophes. So it's not just numbers. But numbers and keywords.]
I don't see how the query you show in that last post has anything at all to do with the one in your third post. Just for starters, the primary table isn't even the same: advert_dropdowns in #3, adverts in #5.
And my point about using fields from *OTHER* than advert_dropdowns in your WHERE clause is explained here:
When you use a field from anything except the primary table (the first table in a LEFT JOIN, last one in a RIGHT JOIN), you *destroy* the LEFT part of the JOIN and effectively convert the entire thing to an INNER JOIN.
Now, it is *possible* that you don't need/want a LEFT JOIN here. Maybe you really only need an INNER JOIN. But if that's the case, then you need to analyze the query and, if it's true, stop trying to use LEFT when INNER is called for.
08-16-2011, 01:32 AM #7
ok bud thanks for that, fair enough, excellent explanation and i really appreciate that so very much. the reason i even posted the other join in there was to show you what i was trying to use as a model then switch it around for my needs. but obviously that idea blew up in my face didnt it man. im gonna cry lmao, not really ...
08-16-2011, 01:48 AM #8
Describe what you are trying to get out of your JOIN.
I kind of suspect you don't need any LEFT JOIN. Or, at least, you don't need all of them.
That's because if you really want to limit your selections to (for example) only advertisements from members who are not suspended, then clearly LEFT JOIN is a mistake.
LEFT JOIN means "get all the records from the first (left) table NO MATTER if there is a match in the second (right) table or not".
So start with figuring out which fields you *really* need from this query. Is this supposed to be getting a list of <option> values for a <select>??? The "dropdown" would suggest that. If so, you would only need a max of two fields in the sql SELECT: the value and the text of each <option>. But whatever. Figure out what fields you need, then figure out how you want to filter for those fields.
08-16-2011, 04:18 AM #9
this is what i got to work and this is what its doing. it is part of a mymatch deal, meaning that the member clicks my matches and everyone in the adverts dropdown table that shares what they like , love, romance whathave you.
so first it grabs to find out what they seek.
then i looks in the members and adverts tables to make sure the member is approved and also has not paused their account and also that they have not been suspended and it excludes the session member themselfs from the list.
Then it takes those final results and loads that array query with all their member infomation into the pagination function that uses that information to pull their photo and all that stuff.
then it display those results on the page.
so here is what i got to work, it works fine but i do not know if it is correctly coded as far as using INNER JOIN instead of LEFT JOIN.
All i know is that the output is what i want, but if i can do a better job i will do so.
anyway here is the final result.
Code://get value of what user is seeking $resultm=mysql_query("SELECT * FROM adverts_dropdown WHERE adv_userid='$Sess_UserId'") or die(mysql_error()); $sql_mymatch=mysql_fetch_object($resultm); $myvalue=$sql_mymatch->adv_seeking; //now find everyone else with similar values and approved and exclude self $queryfind="SELECT *, adverts.adv_approved, adverts.adv_paused FROM adverts INNER JOIN members ON (adverts.adv_userid=mem_userid) INNER JOIN adverts_dropdown ON (adverts.adv_userid=adverts_dropdown.adv_userid) WHERE adverts_dropdown.adv_seeking = '$myvalue' AND members.mem_suspend='N' AND adverts.adv_paused='N' AND adverts.adv_approved=1 AND adverts.adv_userid != '$Sess_UserId' ORDER BY RAND()"; $resultfind=mysql_query($queryfind,$link) or die(mysql_error()); $finallist = mysql_fetch_object($resultfind); $totmem_rows = mysql_num_rows($resultfind); $limit = $pager->GetLimit($finallist); $pager = new PS_Pagination($link,$queryfind,$record_per_page,5); $rs = $pager->paginate();
what is really strange is that this code works fine on php5 but not on php4, both files are the same, the existing file is running on a php4 environment, i took it from there and just replace this section, which should be fine but it wont run, it just give a white page, but i put the same file no changes on php5 env and it works fine. There is nothing in this section that i can see that is php5 specific so that one totally makes me scratch my head.
Last edited by durangod; 08-17-2011 at 05:41 PM. Reason: UPDATE changes LEFT to INNER
08-16-2011, 09:44 PM #10
Well, part of me says "if it ain't broke, don't fix it."
No, you neither want nor need LEFT JOINs in any of that. If your really *were* using LEFT JOINs, you would NOT get the results you are after.
Fortunately for you, because you violated the rule about not using fields from dependent tables in the WHERE clause, you have effectively turned all those joins into INNER JOINs.
I'm betting that if you changed each LEFT to INNER you would see no change at all in the records returned.
And I still say you should stop doing SELECT * but I don't suppose you care to try to figure out what you really need there, so...
If you are happy, I am happy.
Users who have thanked Old Pedant for this post:
08-16-2011, 10:20 PM #11
- Join Date
- Mar 2006
- St. Catharines, Ontario Canada
- Thanked 148 Times in 139 Posts
Don't worry, be happy!
08-16-2011, 11:31 PM #12
My 15-month old granddaughter is just starting to talk, and one of her favorite words is "happy".
So I'm hearing plenty of "happy, happy!" Makes me happy.
Share the happy.
08-17-2011, 12:38 AM #13
Old Pendant i never said i was against removing the select *, what i said was that i know i need improvment and am willing to do so, but i also wanted to share with you what i got to work, prob out of luck i now see.
So yes your good advice is well taken to heart ok bud. And i thank you for your kind and informative replies.
UPDATE: and of course you are quite right about the INNER, i just tested it... thanks
Last edited by durangod; 08-17-2011 at 01:18 AM.
08-17-2011, 01:22 AM #14
Let's be truthful here: If this web page isn't hit 1000 times or more an hour and/or if you aren't getting back more than 100 records from the SELECT, you probably won't be able to measure the difference between using SELECT * and not doing so. My name is well chosen: Pedant. I need to sometimes back off and realize that for many web pages, good enough is good enough.
08-17-2011, 05:38 PM #15
just curious is there anything in that code i posted that would run on php5 but not on php4, there is nothing i can see and thats the only difference in the file is that section of code. I thought at first that maybe php4 is more sensitive to the LEFT vrs INNER and the forcing that i did, but even after the change to INNER it that code will not run on PHP4, and i dont see anything that would cause that. I just get a white page that tells me there is a syntax error, but wouldnt there also be a syntax error in php5 as well. Nothing in that section is wrong, it looks high and tight as far as syntax goes.