...

View Full Version : problem with join



durangod
08-15-2011, 11:36 PM
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()";

durangod
08-15-2011, 11:45 PM
ok i think i got it, you have to spell it out

(adverts_dropdown.adv_userid = adverts.adv_userid)

durangod
08-16-2011, 12:01 AM
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 ...





$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()";

Old Pedant
08-16-2011, 12:36 AM
You have to also qualify the name in your SELECT list.



SELECT *, SOMETABLENAME.adv_seeking FROM ...

*BUT*

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

durangod
08-16-2011, 12:53 AM
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.



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



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

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

Old Pedant
08-16-2011, 01:25 AM
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?)

Exactly the opposite is 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.


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.

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.

[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:
http://www.codingforums.com/showthread.php?p=818192#post818192

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.

durangod
08-16-2011, 01:32 AM
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 ...

Old Pedant
08-16-2011, 01:48 AM
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.

durangod
08-16-2011, 04:18 AM
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.




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

Old Pedant
08-16-2011, 09:44 PM
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.

guelphdad
08-16-2011, 10:20 PM
Don't worry, be happy!

Old Pedant
08-16-2011, 11:31 PM
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.

durangod
08-17-2011, 12:38 AM
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

Old Pedant
08-17-2011, 01:22 AM
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.

durangod
08-17-2011, 05:38 PM
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.

guelphdad
08-17-2011, 08:17 PM
there would be no difference to php4 or 5.
instead of having us guess at the error message help us out by showing what it is.

and are you using mysql_error to throw correct error messages or are you merely using comments (which many people do) such as "cannot connect to database".

Old Pedant
08-18-2011, 01:33 AM
And in any case, PHP will not and can not parse your SQL code. It simply passes along *exactly* what you put in your SQL string to whatever database you are connected to. If there *were* a difference it would be in the database engine. That is, in versions of MySQL and/or in differences between MySQL or some other database engine. Don't confuse PHP code with SQL code. PHP is only used to construct a *string* that you then send to the DB engine. Period.

durangod
08-18-2011, 03:07 AM
there would be no difference to php4 or 5.
instead of having us guess at the error message help us out by showing what it is.

and are you using mysql_error to throw correct error messages or are you merely using comments (which many people do) such as "cannot connect to database".

no thats the problem there is no error, i also tried error_reporting(E-ALL) and nothing came up, it just gives me a white page on the same software, same page, all i did was remove that section of code and put it back. Thats why i thought it had to be a version deal because the file should run, it runs on php5 just perfect, then i have the same software on php4 without that code it runs fine, with it, its white page. that is what is so wired.

I know not many people should still be using php4 its a bad idea but i cant get this customer to change so... I will keep searching, as long as i know that that code i shared is cool 4 or 5 i will keep looking,

thanks all...

guelphdad
08-18-2011, 03:32 AM
you'll keep searching for a long while at this rate.

E-ALL reports PHP errors, it won't report SQL errors. I asked a direct question and didn't get an answer. Are you or are you not using MySQL_ERROR() to trap your mysql error messages? if so it will give you an actual error message. If you aren't add it and post the error message. If you are and there is no error message, run your query inside of phpmyadmin and see if it runs correctly. If it does then any errors you've introduced at that point are as a result of the PHP code.

As an example you are telling us your car won't run. We're asking you to boost the hood and let us look at the engine. You're asking us if the car will run better if you paint it red instead of green.

durangod
08-18-2011, 03:35 AM
ok yes i use mysql error, and no error. Thats a really great idea i never thought of running that inside of phpmyadmin really cool. thats awesome thanks.. Thats why you get the big bucks. awesome... at least i can eliminate one of the equasions and go from there.

guelphdad
08-18-2011, 05:25 AM
Yup and if you are willing to double the big bucks I get I'll still have next to nothing. :)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum