Is this a Case For JOIN?

Codewalkers Forums Sponsor:

August 11th, 2009, 08:54 PM

LLX

Contributing User

Join Date: Apr 2007

Location: Glendale, CA, USA

Posts: 1,270

Time spent in forums: 2 Days 10 h 6 m 51 sec
Reputation Power: 4

MySQL 5 - Is this a Case For JOIN?

just trying to improve my code some more and looking it over i got to think JOIN might work but im not sure. Here's a sample

PHP Code:


		
			
 $querystring2='SELECT * FROM Content';

    $ContentCheck=mysql_query($querystring2);

    $ContentArray = mysql_fetch_array($ContentCheck, MYSQL_ASSOC);

    $querystring3='SELECT * FROM Bylines WHERE BylineID="'.$ContentArray['BylineID'].'"';

    $BylinesCheck=mysql_query($querystring3);

    $BylinesArray = mysql_fetch_array($BylinesCheck, MYSQL_ASSOC);

echo $BylinesArray['Name'].'<BR>';

and so on?

So would a JOIN be more useful. I'm using shared ID in table 1 to locate info in table 2 to and then post that info. But I am also posting info from table 1 in the same record as the table 2 id

__________________
29 years of creative writing
13 years of HTML
10 years of Photoshop
6 years of PHP/MySQL
And I never knew Photoshop could do HTML until 2004!
You learn something new every day.

August 11th, 2009, 10:59 PM

IAmALlama

Join Date: Apr 2007

Location: Seattle, WA

Posts: 1,937

Time spent in forums: 1 Week 5 Days 1 h 54 m 18 sec
Reputation Power: 4

a join could help:

Code:

SELECT *
FROM Content
    JOIN Bylines
        ON Content.ByLineID=ByLines.BylineID

August 12th, 2009, 12:04 PM

LLX

Contributing User

Join Date: Apr 2007

Location: Glendale, CA, USA

Posts: 1,270

Time spent in forums: 2 Days 10 h 6 m 51 sec
Reputation Power: 4

Quote:

Originally Posted by IAmALlama

a join could help:

Code:

SELECT *
FROM Content
    JOIN Bylines
        ON Content.ByLineID=ByLines.BylineID

but would i still be able to pull info from the byline table as well?

what if i have multiple IDS, one of my tables shares ids with 2 other tables?

August 12th, 2009, 12:29 PM

IAmALlama

Join Date: Apr 2007

Location: Seattle, WA

Posts: 1,937

Time spent in forums: 1 Week 5 Days 1 h 54 m 18 sec
Reputation Power: 4

that query joins both tables together. I suggest just running it in a phpmyadmin query window to check out the results. you can also join in as many tables as you want by just copying the JOIN tableName ON tableName.commonColumn=table1.commonColumn

August 12th, 2009, 03:24 PM

LLX

Contributing User

Join Date: Apr 2007

Location: Glendale, CA, USA

Posts: 1,270

Time spent in forums: 2 Days 10 h 6 m 51 sec
Reputation Power: 4

Quote:

Originally Posted by IAmALlama

so say something like this

PHP Code:


		
			
 $querystring2='SELECT * FROM Content';

    $ContentCheck=mysql_query($querystring2);

    $ContentArray = mysql_fetch_array($ContentCheck, MYSQL_ASSOC);

    $querystring3='SELECT * FROM Bylines WHERE BylineID="'.$ContentArray['BylineID'].'"';

    $querystring4='SELECT * FROM Issues WHERE IssueID="'.$ContentArray['IssueID'].'"';

    $querystring5='SELECT * FROM Category WHERE CatagoryID="'.$ContentArray['CatagoryID'].'"';

    $querystring6='SELECT * FROM Menu WHERE MenuID="'.$CategoryArray['MenuID'].'"';

could be simplified to

PHP Code:


		
			
 $querystring2='SELECT * FROM Content JOIN (Content, Issue, Category, Menu) ON (Content.BylineID=Byline.BylineID AND Content.IssueID=Issue.IssueID AND Content.CatagoryID=Category.CatagoryID AND Category.MenuID=Menu.MenuID)';

    $ContentCheck=mysql_query($querystring2);

    $ContentArray = mysql_fetch_array($ContentCheck, MYSQL_ASSOC);

which wound create one array (instead of 6) with the combined data of all six tables? What if the multiple tables share field names, will their be a conflict when i try and reference $ContentArray['Name'] if say Menu, content, and issue all have a Name Field?

August 13th, 2009, 04:16 AM

IAmALlama

Join Date: Apr 2007

Location: Seattle, WA

Posts: 1,937

Time spent in forums: 1 Week 5 Days 1 h 54 m 18 sec
Reputation Power: 4

more like:

Code:

SELECT *
FROM Content
    JOIN Bylines
        ON Content.ByLineID=ByLines.BylineID
    JOIN Issues
        ON Content.IssueID=Issues.IssueID
    JOIN Category
        ON Content.CategoryID=Category.CategoryID
    JOIN Menu
        ON Content.MenuID=Menu.MenuID
WHERE 1=1

Just know that if there are any tables that have more than one row, you will get seemingly duplicate data. you can do a join on more than one column if you have more to join the tables on. like I said before, run it in phpmyadmin to check the results and see if you get what you want back. It has to do with how the tables are connected, it kinda gets confusing, but there are some good tutorials around. w3schools has one (http://www.w3schools.com/Sql/sql_join.asp) and although I haven't looked at this specific tutorial, I usually find their tutorials of high quality.

Also so you know, most people who write sql for mysql use a non-standard join. the above join (at least the one I most commonly write) is considered an ANSI join. they are considered to be better practice, but since most people who learn sql for mysql learn the non-standard way, you will most commonly see the non-standard (so if it looks different than most queries you see, thats why).

August 13th, 2009, 12:11 PM

LLX

Contributing User

Join Date: Apr 2007

Location: Glendale, CA, USA

Posts: 1,270

Time spent in forums: 2 Days 10 h 6 m 51 sec
Reputation Power: 4

yeah their will be more then one row so maybe join isnt the best way? for example in my content example you will have several bylines in the byline row but your really only going to have 1 byline per content (blog/article/what have you)

But content could be in multiple menus or categories...

August 13th, 2009, 12:58 PM

IAmALlama

Join Date: Apr 2007

Location: Seattle, WA

Posts: 1,937

Time spent in forums: 1 Week 5 Days 1 h 54 m 18 sec
Reputation Power: 4

a join might be the right way to go, you would just have to work with the results. like I said above, make the join query and run it in phpmyadmin to see what the results are like (if you gave all the right queries in your attempts, then the one I wrote should work). also check out that w3schools link in the previous post for a better idea of how joins work.

August 13th, 2009, 03:00 PM

LLX

Contributing User

Join Date: Apr 2007

Location: Glendale, CA, USA

Posts: 1,270

Time spent in forums: 2 Days 10 h 6 m 51 sec
Reputation Power: 4

thanks I'll do that once i have some data right now I'm still building the infrastructure so my database is empty.

#10

August 13th, 2009, 05:26 PM

IAmALlama

Join Date: Apr 2007

Location: Seattle, WA

Posts: 1,937

Time spent in forums: 1 Week 5 Days 1 h 54 m 18 sec
Reputation Power: 4

if you want/need any help come time to setup some of those queries, feel free to PM me (and I can get you my email if you want) and I can help. at that time you could either get me access to the database (phpmyadmin/other admin tool/custom write a query, show a table tool) or you can send me a copy of the database layout and some random data (http://generatedata.com) to test.

#11

August 13th, 2009, 05:45 PM

LLX

Contributing User

Join Date: Apr 2007

Location: Glendale, CA, USA

Posts: 1,270

Time spent in forums: 2 Days 10 h 6 m 51 sec
Reputation Power: 4

i'll keep that in mind. Thanks!

Viewing: Codewalkers Forums > Other Technologies > Database Help > MySQL 5 - Is this a Case For JOIN?

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump

Free IT White Papers!

How to Present Effectively Online
This white paper offers practical and actionable advice on the key steps that any presenter should consider as they plan and execute a Webinar or online meeting.
Request Your Free Technology Downloads!

Open Source Security Myths
Open Source Software (OSS) is computer software whose source code is available to the general public with relaxed or non-existent intellectual property restrictions (or arrangement such as the public domain), and is usually developed with the input of many contributors.
Request Your Free Technology Downloads!

Power and Cooling Capacity Management for Data Centers
This paper describes the principles for achieving power and cooling capacity management.
Request Your Free Technology Downloads!

Scalable, Fault-Tolerant NAS for Oracle - The Next Generation
For several years NAS has been evolving as a storage alternative for Oracle databases, and for good reason: NAS is quite often the simplest, most cost-effective storage approach for Oracle. Learn about the benefits that HP's approach to scalable NAS brings to Oracle environments in this comprehensive white paper.
Request Your Free Technology Downloads!

Understanding Web Application Security Challenges
This white paper discusses many common threats and preventive measures for Web application security, and explains what you can do to help protect your organization.
Request Your Free Technology Downloads!

More Free IT White Papers!