Problems with INNER JOIN

Problems with INNER JOIN

am 29.01.2009 16:14:15 von Terion Miller

--001636498f11a1939b0461a08a63
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Hi Everyone! I am having problems getting an INNER JOIN to work and need
some tips trouble shooting where the problem may be.

What I'm trying to do is match up AdminID's from two tables and display only
that users orders, sounds simple enough right...but I can't get it to return
the AdminID...

My Query:

$sql =
"SELECT admin.AdminID , workorders.AdminID
FROM admin
INNER JOIN
workorders ON
(admin.AdminID=workorders.AdminID)
WHERE admin.UserName = '".$_SESSION['user']."' ";


$result = mysql_query ($sql);
$row = mysql_fetch_assoc ($result);
$Total = ceil(mysql_num_rows($result)/$PerPage);

Thanks for any tips on how else I can accomplish this...
Terion

--001636498f11a1939b0461a08a63--

Re: Problems with INNER JOIN

am 29.01.2009 16:24:49 von Eleonora De Marinis

--------------050000070805020208090303
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit

Try this:

$sql =
"SELECT admin.AdminID AS adminID, workorders.AdminID AS workadmin
FROM admin
INNER JOIN
workorders ON
(admin.AdminID=workorders.AdminID)
WHERE admin.UserName = '".$_SESSION['user']."' ";


maybe you have to call with different name the table fields

Terion Miller ha scritto:
> Hi Everyone! I am having problems getting an INNER JOIN to work and need
> some tips trouble shooting where the problem may be.
>
> What I'm trying to do is match up AdminID's from two tables and display only
> that users orders, sounds simple enough right...but I can't get it to return
> the AdminID...
>
> My Query:
>
> $sql =
> "SELECT admin.AdminID , workorders.AdminID
> FROM admin
> INNER JOIN
> workorders ON
> (admin.AdminID=workorders.AdminID)
> WHERE admin.UserName = '".$_SESSION['user']."' ";
>
>
> $result = mysql_query ($sql);
> $row = mysql_fetch_assoc ($result);
> $Total = ceil(mysql_num_rows($result)/$PerPage);
>
> Thanks for any tips on how else I can accomplish this...
> Terion
>
>


--------------050000070805020208090303--

Re: Problems with INNER JOIN

am 29.01.2009 16:58:32 von danaketh

--------------000205080807040508070804
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Maybe LEFT instead of INNER?

Terion Miller napsal(a):
> Hi Everyone! I am having problems getting an INNER JOIN to work and need
> some tips trouble shooting where the problem may be.
>
> What I'm trying to do is match up AdminID's from two tables and display only
> that users orders, sounds simple enough right...but I can't get it to return
> the AdminID...
>
> My Query:
>
> $sql =
> "SELECT admin.AdminID , workorders.AdminID
> FROM admin
> INNER JOIN
> workorders ON
> (admin.AdminID=workorders.AdminID)
> WHERE admin.UserName = '".$_SESSION['user']."' ";
>
>
> $result = mysql_query ($sql);
> $row = mysql_fetch_assoc ($result);
> $Total = ceil(mysql_num_rows($result)/$PerPage);
>
> Thanks for any tips on how else I can accomplish this...
> Terion
>
>

--

S pozdravem

Daniel Tlach
Freelance webdeveloper

Email: mail@danaketh.com
ICQ: 160914875
MSN: danaketh@hotmail.com
Jabber: danaketh@jabbim.cz


--------------000205080807040508070804--

Re: Problems with INNER JOIN

am 29.01.2009 18:56:05 von Martin Zvarik

This will work:

$sql = "
SELECT admin.AdminID, workorders.WHAT_YOU_WANT
FROM admin
INNER JOIN workorders ON admin.AdminID=workorders.AdminID
HAVING admin.username='".$_SESSION['user']."'
";


Terion Miller napsal(a):
> Hi Everyone! I am having problems getting an INNER JOIN to work and need
> some tips trouble shooting where the problem may be.
>
> What I'm trying to do is match up AdminID's from two tables and display only
> that users orders, sounds simple enough right...but I can't get it to return
> the AdminID...
>
> My Query:
>
> $sql =
> "SELECT admin.AdminID , workorders.AdminID
> FROM admin
> INNER JOIN
> workorders ON
> (admin.AdminID=workorders.AdminID)
> WHERE admin.UserName = '".$_SESSION['user']."' ";
>
>
> $result = mysql_query ($sql);
> $row = mysql_fetch_assoc ($result);
> $Total = ceil(mysql_num_rows($result)/$PerPage);
>
> Thanks for any tips on how else I can accomplish this...
> Terion
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Re: Problems with INNER JOIN

am 29.01.2009 19:28:50 von Terion Miller

--0016364581988221ab0461a34285
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Thanks Martin, oddly enough that still doesn't pull any results, it won't
even print the variables if I try to list them to see, I know my db
connection is good checked that, and can do really simple queries, would
something be preventing a JOIN from working?
When I Reveal my variables this query snippet:
$sql =3D "SELECT admin.AdminID, workorders.WorkOrderID
FROM admin
INNER JOIN workorders ON admin.AdminID=3Dworkorders.AdminID
HAVING admin.username=3D'".$_SESSION['user']."' ";



$result2 =3D mysql_query ($sql);
$row2 =3D mysql_fetch_assoc ($result2);
$Total =3D ceil(mysql_num_rows($result2)/$PerPage);

Returns this:
$sql"SELECT admin.AdminID, workorders.WorkOrderID FROM admin INNER JOIN
workorders ON admin.AdminID=3Dworkorders.AdminID HAVING
admin.username=3D'tmiller' "$result2""$row2""

Terion

On Thu, Jan 29, 2009 at 11:56 AM, Martin Zvar=EDk wrote=
:

> This will work:
>
> $sql =3D "
> SELECT admin.AdminID, workorders.WHAT_YOU_WANT
> FROM admin
> INNER JOIN workorders ON admin.AdminID=3Dworkorders.AdminID
> HAVING admin.username=3D'".$_SESSION['user']."'
> ";
>
>
> Terion Miller napsal(a):
>
> Hi Everyone! I am having problems getting an INNER JOIN to work and need
>> some tips trouble shooting where the problem may be.
>>
>> What I'm trying to do is match up AdminID's from two tables and display
>> only
>> that users orders, sounds simple enough right...but I can't get it to
>> return
>> the AdminID...
>>
>> My Query:
>>
>> $sql =3D
>> "SELECT admin.AdminID , workorders.AdminID
>> FROM admin
>> INNER JOIN
>> workorders ON
>> (admin.AdminID=3Dworkorders.AdminID)
>> WHERE admin.UserName =3D '".$_SESSION['user']."' ";
>>
>>
>> $result =3D mysql_query ($sql);
>> $row =3D mysql_fetch_assoc ($result);
>> $Total =3D ceil(mysql_num_rows($result)/$PerPage);
>>
>> Thanks for any tips on how else I can accomplish this...
>> Terion
>>
>>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

--0016364581988221ab0461a34285--

Re: Re: Problems with INNER JOIN

am 29.01.2009 19:55:12 von Martin Zvarik

What I wrote with HAVING doesn't do any difference - replace back to WHERE.

Your query is OK.

Try: echo mysql_error();

And try to remove the condition "WHERE admin.user..." if that outputs
anything.

Martin


Terion Miller napsal(a):
> Thanks Martin, oddly enough that still doesn't pull any results, it won't
> even print the variables if I try to list them to see, I know my db
> connection is good checked that, and can do really simple queries, would
> something be preventing a JOIN from working?
> When I Reveal my variables this query snippet:
> $sql = "SELECT admin.AdminID, workorders.WorkOrderID
> FROM admin
> INNER JOIN workorders ON admin.AdminID=workorders.AdminID
> HAVING admin.username='".$_SESSION['user']."' ";
>
>
>
> $result2 = mysql_query ($sql);
> $row2 = mysql_fetch_assoc ($result2);
> $Total = ceil(mysql_num_rows($result2)/$PerPage);
>
> Returns this:
> $sql"SELECT admin.AdminID, workorders.WorkOrderID FROM admin INNER JOIN
> workorders ON admin.AdminID=workorders.AdminID HAVING
> admin.username='tmiller' "$result2""$row2""
>
> Terion
>
> On Thu, Jan 29, 2009 at 11:56 AM, Martin Zvarík wrote:
>
>> This will work:
>>
>> $sql = "
>> SELECT admin.AdminID, workorders.WHAT_YOU_WANT
>> FROM admin
>> INNER JOIN workorders ON admin.AdminID=workorders.AdminID
>> HAVING admin.username='".$_SESSION['user']."'
>> ";
>>
>>
>> Terion Miller napsal(a):
>>
>> Hi Everyone! I am having problems getting an INNER JOIN to work and need
>>> some tips trouble shooting where the problem may be.
>>>
>>> What I'm trying to do is match up AdminID's from two tables and display
>>> only
>>> that users orders, sounds simple enough right...but I can't get it to
>>> return
>>> the AdminID...
>>>
>>> My Query:
>>>
>>> $sql =
>>> "SELECT admin.AdminID , workorders.AdminID
>>> FROM admin
>>> INNER JOIN
>>> workorders ON
>>> (admin.AdminID=workorders.AdminID)
>>> WHERE admin.UserName = '".$_SESSION['user']."' ";
>>>
>>>
>>> $result = mysql_query ($sql);
>>> $row = mysql_fetch_assoc ($result);
>>> $Total = ceil(mysql_num_rows($result)/$PerPage);
>>>
>>> Thanks for any tips on how else I can accomplish this...
>>> Terion
>>>
>>>
>> --
>> PHP Database Mailing List (http://www.php.net/)
>> To unsubscribe, visit: http://www.php.net/unsub.php
>>
>>
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Re: Problems with INNER JOIN

am 29.01.2009 20:25:14 von Terion Miller

--0016e6d647da34d8ae0461a40c90
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

When I removed the WHERE admin.user... part it did return something, grante=
d
it returned one adminID and one order (which did belong together) but the
adminID was not mine so I don't know how it picked that one randomly ...is
there a way to do what I need using two queries one to find out the users I=
D
then another to pull the workorders with that id? Can't believe this is
proving so hard..
Thanks for helping!
Terion



On Thu, Jan 29, 2009 at 12:55 PM, Martin Zvar=EDk wrote=
:

> What I wrote with HAVING doesn't do any difference - replace back to WHER=
E.
>
> Your query is OK.
>
> Try: echo mysql_error();
>
> And try to remove the condition "WHERE admin.user..." if that outputs
> anything.
>
> Martin
>
>
> Terion Miller napsal(a):
>
>> Thanks Martin, oddly enough that still doesn't pull any results, it won'=
t
>>
>> even print the variables if I try to list them to see, I know my db
>> connection is good checked that, and can do really simple queries, would
>> something be preventing a JOIN from working?
>> When I Reveal my variables this query snippet:
>> $sql =3D "SELECT admin.AdminID, workorders.WorkOrderID
>> FROM admin
>> INNER JOIN workorders ON admin.AdminID=3Dworkorders.AdminID
>> HAVING admin.username=3D'".$_SESSION['user']."' ";
>>
>>
>>
>> $result2 =3D mysql_query ($sql);
>> $row2 =3D mysql_fetch_assoc ($result2);
>> $Total =3D ceil(mysql_num_rows($result2)/$PerPage);
>>
>> Returns this:
>> $sql"SELECT admin.AdminID, workorders.WorkOrderID FROM admin INNER JOIN
>> workorders ON admin.AdminID=3Dworkorders.AdminID HAVING
>> admin.username=3D'tmiller' "$result2""$row2""
>>
>> Terion
>>
>> On Thu, Jan 29, 2009 at 11:56 AM, Martin Zvar=EDk
>> wrote:
>>
>> This will work:
>>>
>>> $sql =3D "
>>> SELECT admin.AdminID, workorders.WHAT_YOU_WANT
>>> FROM admin
>>> INNER JOIN workorders ON admin.AdminID=3Dworkorders.AdminID
>>> HAVING admin.username=3D'".$_SESSION['user']."'
>>> ";
>>>
>>>
>>> Terion Miller napsal(a):
>>>
>>> Hi Everyone! I am having problems getting an INNER JOIN to work and ne=
ed
>>>
>>>> some tips trouble shooting where the problem may be.
>>>>
>>>> What I'm trying to do is match up AdminID's from two tables and displa=
y
>>>> only
>>>> that users orders, sounds simple enough right...but I can't get it to
>>>> return
>>>> the AdminID...
>>>>
>>>> My Query:
>>>>
>>>> $sql =3D
>>>> "SELECT admin.AdminID , workorders.AdminID
>>>> FROM admin
>>>> INNER JOIN
>>>> workorders ON
>>>> (admin.AdminID=3Dworkorders.AdminID)
>>>> WHERE admin.UserName =3D '".$_SESSION['user']."' ";
>>>>
>>>>
>>>> $result =3D mysql_query ($sql);
>>>> $row =3D mysql_fetch_assoc ($result);
>>>> $Total =3D ceil(mysql_num_rows($result)/$PerPage);
>>>>
>>>> Thanks for any tips on how else I can accomplish this...
>>>> Terion
>>>>
>>>>
>>>> --
>>> PHP Database Mailing List (http://www.php.net/)
>>> To unsubscribe, visit: http://www.php.net/unsub.php
>>>
>>>
>>>
>>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

--0016e6d647da34d8ae0461a40c90--

Re: Re: Problems with INNER JOIN

am 29.01.2009 20:28:20 von Martin Zvarik

--------------000501040201000104030702
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

Are you sure you have MORE than ONE [order + admin that belongs
together] in those tables?

Now, turn on your brain and don't wait for my instructions, you already
know what to do... test test test



Terion Miller napsal(a):
> When I removed the WHERE admin.user... part it did return something,
> granted it returned one adminID and one order (which did belong
> together) but the adminID was not mine so I don't know how it picked
> that one randomly ...is there a way to do what I need using two
> queries one to find out the users ID then another to pull the
> workorders with that id? Can't believe this is proving so hard..
> Thanks for helping!
> Terion
>
>
>
> On Thu, Jan 29, 2009 at 12:55 PM, Martin Zvarík > > wrote:
>
> What I wrote with HAVING doesn't do any difference - replace back
> to WHERE.
>
> Your query is OK.
>
> Try: echo mysql_error();
>
> And try to remove the condition "WHERE admin.user..." if that
> outputs anything.
>
> Martin
>
>
> Terion Miller napsal(a):
>
> Thanks Martin, oddly enough that still doesn't pull any
> results, it won't
>
> even print the variables if I try to list them to see, I know
> my db
> connection is good checked that, and can do really simple
> queries, would
> something be preventing a JOIN from working?
> When I Reveal my variables this query snippet:
> $sql = "SELECT admin.AdminID, workorders.WorkOrderID
> FROM admin
> INNER JOIN workorders ON admin.AdminID=workorders.AdminID
> HAVING admin.username='".$_SESSION['user']."' ";
>
>
>
> $result2 = mysql_query ($sql);
> $row2 = mysql_fetch_assoc ($result2);
> $Total = ceil(mysql_num_rows($result2)/$PerPage);
>
> Returns this:
> $sql"SELECT admin.AdminID, workorders.WorkOrderID FROM admin
> INNER JOIN
> workorders ON admin.AdminID=workorders.AdminID HAVING
> admin.username='tmiller' "$result2""$row2""
>
> Terion
>
> On Thu, Jan 29, 2009 at 11:56 AM, Martin Zvarík
> > wrote:
>
> This will work:
>
> $sql = "
> SELECT admin.AdminID, workorders.WHAT_YOU_WANT
> FROM admin
> INNER JOIN workorders ON admin.AdminID=workorders.AdminID
> HAVING admin.username='".$_SESSION['user']."'
> ";
>
>
> Terion Miller napsal(a):
>
> Hi Everyone! I am having problems getting an INNER JOIN
> to work and need
>
> some tips trouble shooting where the problem may be.
>
> What I'm trying to do is match up AdminID's from two
> tables and display
> only
> that users orders, sounds simple enough right...but I
> can't get it to
> return
> the AdminID...
>
> My Query:
>
> $sql =
> "SELECT admin.AdminID , workorders.AdminID
> FROM admin
> INNER JOIN
> workorders ON
> (admin.AdminID=workorders.AdminID)
> WHERE admin.UserName = '".$_SESSION['user']."' ";
>
>
> $result = mysql_query ($sql);
> $row = mysql_fetch_assoc ($result);
> $Total = ceil(mysql_num_rows($result)/$PerPage);
>
> Thanks for any tips on how else I can accomplish this...
> Terion
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

--------------000501040201000104030702--

Re: Re: Problems with INNER JOIN

am 29.01.2009 20:39:28 von danaketh

--------------090309060305070203000203
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

Try that query in console or use Navicat/phpMyAdmin to run it. Remove
the HAVING part to see if the SELECT returns what you need.

Terion Miller napsal(a):
> Thanks Martin, oddly enough that still doesn't pull any results, it won't
> even print the variables if I try to list them to see, I know my db
> connection is good checked that, and can do really simple queries, would
> something be preventing a JOIN from working?
> When I Reveal my variables this query snippet:
> $sql = "SELECT admin.AdminID, workorders.WorkOrderID
> FROM admin
> INNER JOIN workorders ON admin.AdminID=workorders.AdminID
> HAVING admin.username='".$_SESSION['user']."' ";
>
>
>
> $result2 = mysql_query ($sql);
> $row2 = mysql_fetch_assoc ($result2);
> $Total = ceil(mysql_num_rows($result2)/$PerPage);
>
> Returns this:
> $sql"SELECT admin.AdminID, workorders.WorkOrderID FROM admin INNER JOIN
> workorders ON admin.AdminID=workorders.AdminID HAVING
> admin.username='tmiller' "$result2""$row2""
>
> Terion
>
> On Thu, Jan 29, 2009 at 11:56 AM, Martin Zvarík wrote:
>
>
>> This will work:
>>
>> $sql = "
>> SELECT admin.AdminID, workorders.WHAT_YOU_WANT
>> FROM admin
>> INNER JOIN workorders ON admin.AdminID=workorders.AdminID
>> HAVING admin.username='".$_SESSION['user']."'
>> ";
>>
>>
>> Terion Miller napsal(a):
>>
>> Hi Everyone! I am having problems getting an INNER JOIN to work and need
>>
>>> some tips trouble shooting where the problem may be.
>>>
>>> What I'm trying to do is match up AdminID's from two tables and display
>>> only
>>> that users orders, sounds simple enough right...but I can't get it to
>>> return
>>> the AdminID...
>>>
>>> My Query:
>>>
>>> $sql =
>>> "SELECT admin.AdminID , workorders.AdminID
>>> FROM admin
>>> INNER JOIN
>>> workorders ON
>>> (admin.AdminID=workorders.AdminID)
>>> WHERE admin.UserName = '".$_SESSION['user']."' ";
>>>
>>>
>>> $result = mysql_query ($sql);
>>> $row = mysql_fetch_assoc ($result);
>>> $Total = ceil(mysql_num_rows($result)/$PerPage);
>>>
>>> Thanks for any tips on how else I can accomplish this...
>>> Terion
>>>
>>>
>>>
>> --
>> PHP Database Mailing List (http://www.php.net/)
>> To unsubscribe, visit: http://www.php.net/unsub.php
>>
>>
>>
>
>

--

S pozdravem

Daniel Tlach
Freelance webdeveloper

Email: mail@danaketh.com
ICQ: 160914875
MSN: danaketh@hotmail.com
Jabber: danaketh@jabbim.cz


--------------090309060305070203000203--

Re: Re: Problems with INNER JOIN

am 29.01.2009 20:40:53 von Terion Miller

--001636c5a66f3281110461a44497
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Yes I've already done that and checked the db etc and I've turned my brain
ON hours ago, I came to the list because I'm stumped and have tried all
manners of different ways to tell mySql

if admin ID =3D session user then get workorders where orderID =3D admin ID



.....

On Thu, Jan 29, 2009 at 1:28 PM, Martin Zvar=EDk wrote:

> Are you sure you have MORE than ONE [order + admin that belongs together=
]
> in those tables?
>
> Now, turn on your brain and don't wait for my instructions, you already
> know what to do... test test test
>
>
>
> Terion Miller napsal(a):
>
> When I removed the WHERE admin.user... part it did return something,
> granted it returned one adminID and one order (which did belong together)
> but the adminID was not mine so I don't know how it picked that one rando=
mly
> ...is there a way to do what I need using two queries one to find out the
> users ID then another to pull the workorders with that id? Can't believe
> this is proving so hard..
> Thanks for helping!
> Terion
>
>
>
> On Thu, Jan 29, 2009 at 12:55 PM, Martin Zvar=EDk wro=
te:
>
>> What I wrote with HAVING doesn't do any difference - replace back to
>> WHERE.
>>
>> Your query is OK.
>>
>> Try: echo mysql_error();
>>
>> And try to remove the condition "WHERE admin.user..." if that outputs
>> anything.
>>
>> Martin
>>
>>
>> Terion Miller napsal(a):
>>
>>> Thanks Martin, oddly enough that still doesn't pull any results, it won=
't
>>>
>>> even print the variables if I try to list them to see, I know my db
>>> connection is good checked that, and can do really simple queries, woul=
d
>>> something be preventing a JOIN from working?
>>> When I Reveal my variables this query snippet:
>>> $sql =3D "SELECT admin.AdminID, workorders.WorkOrderID
>>> FROM admin
>>> INNER JOIN workorders ON admin.AdminID=3Dworkorders.AdminID
>>> HAVING admin.username=3D'".$_SESSION['user']."' ";
>>>
>>>
>>>
>>> $result2 =3D mysql_query ($sql);
>>> $row2 =3D mysql_fetch_assoc ($result2);
>>> $Total =3D ceil(mysql_num_rows($result2)/$PerPage);
>>>
>>> Returns this:
>>> $sql"SELECT admin.AdminID, workorders.WorkOrderID FROM admin INNER JOIN
>>> workorders ON admin.AdminID=3Dworkorders.AdminID HAVING
>>> admin.username=3D'tmiller' "$result2""$row2""
>>>
>>> Terion
>>>
>>> On Thu, Jan 29, 2009 at 11:56 AM, Martin Zvar=EDk
>>> wrote:
>>>
>>> This will work:
>>>>
>>>> $sql =3D "
>>>> SELECT admin.AdminID, workorders.WHAT_YOU_WANT
>>>> FROM admin
>>>> INNER JOIN workorders ON admin.AdminID=3Dworkorders.AdminID
>>>> HAVING admin.username=3D'".$_SESSION['user']."'
>>>> ";
>>>>
>>>>
>>>> Terion Miller napsal(a):
>>>>
>>>> Hi Everyone! I am having problems getting an INNER JOIN to work and
>>>> need
>>>>
>>>>> some tips trouble shooting where the problem may be.
>>>>>
>>>>> What I'm trying to do is match up AdminID's from two tables and displ=
ay
>>>>> only
>>>>> that users orders, sounds simple enough right...but I can't get it to
>>>>> return
>>>>> the AdminID...
>>>>>
>>>>> My Query:
>>>>>
>>>>> $sql =3D
>>>>> "SELECT admin.AdminID , workorders.AdminID
>>>>> FROM admin
>>>>> INNER JOIN
>>>>> workorders ON
>>>>> (admin.AdminID=3Dworkorders.AdminID)
>>>>> WHERE admin.UserName =3D '".$_SESSION['user']."' ";
>>>>>
>>>>>
>>>>> $result =3D mysql_query ($sql);
>>>>> $row =3D mysql_fetch_assoc ($result);
>>>>> $Total =3D ceil(mysql_num_rows($result)/$PerPage);
>>>>>
>>>>> Thanks for any tips on how else I can accomplish this...
>>>>> Terion
>>>>>
>>>>>
>>>>> --
>>>> PHP Database Mailing List (http://www.php.net/)
>>>> To unsubscribe, visit: http://www.php.net/unsub.php
>>>>
>>>>
>>>>
>>>
>> --
>> PHP Database Mailing List (http://www.php.net/)
>> To unsubscribe, visit: http://www.php.net/unsub.php
>>
>>
>

--001636c5a66f3281110461a44497--

Re: Re: Problems with INNER JOIN

am 29.01.2009 20:53:54 von Martin Zvarik

--------------000106090900020604060005
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

SELECT adminID FROM admin WHERE username=" $username "

$adminid = mysql_fetch_row ...

SELECT * FROM workorders WHERE adminid = $adminid



Terion Miller napsal(a):
> Yes I've already done that and checked the db etc and I've turned my
> brain ON hours ago, I came to the list because I'm stumped and have
> tried all manners of different ways to tell mySql
>
> if admin ID = session user then get workorders where orderID = admin ID
>
>
>
> ....
>
> On Thu, Jan 29, 2009 at 1:28 PM, Martin Zvarík > > wrote:
>
> Are you sure you have MORE than ONE [order + admin that belongs
> together] in those tables?
>
> Now, turn on your brain and don't wait for my instructions, you
> already know what to do... test test test
>
>
>
> Terion Miller napsal(a):
>> When I removed the WHERE admin.user... part it did return
>> something, granted it returned one adminID and one order (which
>> did belong together) but the adminID was not mine so I don't know
>> how it picked that one randomly ...is there a way to do what I
>> need using two queries one to find out the users ID then another
>> to pull the workorders with that id? Can't believe this is
>> proving so hard..
>> Thanks for helping!
>> Terion
>>
>>
>>
>> On Thu, Jan 29, 2009 at 12:55 PM, Martin Zvarík
>> > wrote:
>>
>> What I wrote with HAVING doesn't do any difference - replace
>> back to WHERE.
>>
>> Your query is OK.
>>
>> Try: echo mysql_error();
>>
>> And try to remove the condition "WHERE admin.user..." if that
>> outputs anything.
>>
>> Martin
>>
>>
>> Terion Miller napsal(a):
>>
>> Thanks Martin, oddly enough that still doesn't pull any
>> results, it won't
>>
>> even print the variables if I try to list them to see, I
>> know my db
>> connection is good checked that, and can do really simple
>> queries, would
>> something be preventing a JOIN from working?
>> When I Reveal my variables this query snippet:
>> $sql = "SELECT admin.AdminID, workorders.WorkOrderID
>> FROM admin
>> INNER JOIN workorders ON admin.AdminID=workorders.AdminID
>> HAVING admin.username='".$_SESSION['user']."' ";
>>
>>
>>
>> $result2 = mysql_query ($sql);
>> $row2 = mysql_fetch_assoc ($result2);
>> $Total = ceil(mysql_num_rows($result2)/$PerPage);
>>
>> Returns this:
>> $sql"SELECT admin.AdminID, workorders.WorkOrderID FROM
>> admin INNER JOIN
>> workorders ON admin.AdminID=workorders.AdminID HAVING
>> admin.username='tmiller' "$result2""$row2""
>>
>> Terion
>>
>> On Thu, Jan 29, 2009 at 11:56 AM, Martin Zvarík
>> > wrote:
>>
>> This will work:
>>
>> $sql = "
>> SELECT admin.AdminID, workorders.WHAT_YOU_WANT
>> FROM admin
>> INNER JOIN workorders ON admin.AdminID=workorders.AdminID
>> HAVING admin.username='".$_SESSION['user']."'
>> ";
>>
>>
>> Terion Miller napsal(a):
>>
>> Hi Everyone! I am having problems getting an INNER
>> JOIN to work and need
>>
>> some tips trouble shooting where the problem may be.
>>
>> What I'm trying to do is match up AdminID's from
>> two tables and display
>> only
>> that users orders, sounds simple enough
>> right...but I can't get it to
>> return
>> the AdminID...
>>
>> My Query:
>>
>> $sql =
>> "SELECT admin.AdminID , workorders.AdminID
>> FROM admin
>> INNER JOIN
>> workorders ON
>> (admin.AdminID=workorders.AdminID)
>> WHERE admin.UserName =
>> '".$_SESSION['user']."' ";
>>
>>
>> $result = mysql_query ($sql);
>> $row = mysql_fetch_assoc ($result);
>> $Total = ceil(mysql_num_rows($result)/$PerPage);
>>
>> Thanks for any tips on how else I can accomplish
>> this...
>> Terion
>>
>>
>> --
>> PHP Database Mailing List (http://www.php.net/)
>> To unsubscribe, visit: http://www.php.net/unsub.php
>>
>>
>>
>>
>> --
>> PHP Database Mailing List (http://www.php.net/)
>> To unsubscribe, visit: http://www.php.net/unsub.php
>>
>>
>

--------------000106090900020604060005--

Re: Problems with INNER JOIN

am 29.01.2009 22:42:01 von dmagick

Terion Miller wrote:
> Hi Everyone! I am having problems getting an INNER JOIN to work and need
> some tips trouble shooting where the problem may be.
>
> What I'm trying to do is match up AdminID's from two tables and display only
> that users orders, sounds simple enough right...but I can't get it to return
> the AdminID...
>
> My Query:
>
> $sql =
> "SELECT admin.AdminID , workorders.AdminID
> FROM admin
> INNER JOIN
> workorders ON
> (admin.AdminID=workorders.AdminID)
> WHERE admin.UserName = '".$_SESSION['user']."' ";
>
>
> $result = mysql_query ($sql);
> $row = mysql_fetch_assoc ($result);
> $Total = ceil(mysql_num_rows($result)/$PerPage);
>
> Thanks for any tips on how else I can accomplish this...

I explained all of this here:
http://marc.info/?l=php-general&m=123318173022009&w=2
and here:
http://marc.info/?l=php-general&m=123318438826250&w=2

Which part didn't make sense?

--
Postgresql & php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Re: Problems with INNER JOIN

am 29.01.2009 22:44:04 von dmagick

Martin Zvarík wrote:
> This will work:
>
> $sql = "
> SELECT admin.AdminID, workorders.WHAT_YOU_WANT
> FROM admin
> INNER JOIN workorders ON admin.AdminID=workorders.AdminID
> HAVING admin.username='".$_SESSION['user']."'
> ";

Don't use having in this case - this should be a 'where' clause.

A 'having' clause is processed AFTER everything else (after where, after
joins, after aggregate's are calculated like sum/max/min), using it like
this will cause performance issues where you shouldn't have any.

--
Postgresql & php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Re: Problems with INNER JOIN

am 30.01.2009 00:04:56 von Terion Miller

--0016e6d9a15af6e0c20461a71d6e
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Actually I have tried EVERYTHING suggested and it just does not work, in my
last query here I put in a user I know has orders and still nothing :

$sql3 =3D "SELECT admin.AdminID, workorders.WorkOrderID,
workorders.DATE_FORMAT(CreatedDate,'%m/%e/%y') AS SubmitDate,
workorders.Location, workorders.AdminID, workorders.FormName,
workorders.Status FROM admin INNER JOIN workorders ON admin.AdminID =3D
workorders.AdminID WHERE admin.username =3D 'lhanford' ";

Are there mySQL settings that could prevent JOINS?


On Thu, Jan 29, 2009 at 3:44 PM, Chris wrote:

> Martin Zvar=EDk wrote:
>
>> This will work:
>>
>> $sql =3D "
>> SELECT admin.AdminID, workorders.WHAT_YOU_WANT
>> FROM admin
>> INNER JOIN workorders ON admin.AdminID=3Dworkorders.AdminID
>> HAVING admin.username=3D'".$_SESSION['user']."'
>> ";
>>
>
> Don't use having in this case - this should be a 'where' clause.
>
> A 'having' clause is processed AFTER everything else (after where, after
> joins, after aggregate's are calculated like sum/max/min), using it like
> this will cause performance issues where you shouldn't have any.
>
> --
> Postgresql & php tutorials
> http://www.designmagick.com/
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

--0016e6d9a15af6e0c20461a71d6e--

Re: Re: Problems with INNER JOIN

am 30.01.2009 00:09:17 von dmagick

Terion Miller wrote:
> Actually I have tried EVERYTHING suggested and it just does not work, in
> my last query here I put in a user I know has orders and still nothing :
>
> $sql3 = "SELECT admin.AdminID, workorders.WorkOrderID,
> workorders.DATE_FORMAT(CreatedDate,'%m/%e/%y') AS SubmitDate,
> workorders.Location, workorders.AdminID, workorders.FormName,
> workorders.Status FROM admin INNER JOIN workorders ON admin.AdminID =
> workorders.AdminID WHERE admin.username = 'lhanford' ";
>
> Are there mySQL settings that could prevent JOINS?

Definitely not.

Try it manually in two parts:

select adminID from admin WHERE username='lhanford';

remember that id, then:

select * from workorders where adminID='admin_id_from_last_result';

Any results?

--
Postgresql & php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Re: Problems with INNER JOIN

am 30.01.2009 00:21:09 von Terion Miller

--001636c5a2b1e8835b0461a757a2
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Yep they both work separately ... can't believe I've been stuck on the same
thing all day, it seems like an easy thing...

On Thu, Jan 29, 2009 at 5:09 PM, Chris wrote:

> Terion Miller wrote:
>
>> Actually I have tried EVERYTHING suggested and it just does not work, in
>> my last query here I put in a user I know has orders and still nothing :
>>
>> $sql3 = "SELECT admin.AdminID, workorders.WorkOrderID,
>> workorders.DATE_FORMAT(CreatedDate,'%m/%e/%y') AS SubmitDate,
>> workorders.Location, workorders.AdminID, workorders.FormName,
>> workorders.Status FROM admin INNER JOIN workorders ON admin.AdminID =
>> workorders.AdminID WHERE admin.username = 'lhanford' ";
>>
>> Are there mySQL settings that could prevent JOINS?
>>
>
> Definitely not.
>
> Try it manually in two parts:
>
> select adminID from admin WHERE username='lhanford';
>
> remember that id, then:
>
> select * from workorders where adminID='admin_id_from_last_result';
>
> Any results?
>
>
> --
> Postgresql & php tutorials
> http://www.designmagick.com/
>
>

--001636c5a2b1e8835b0461a757a2--

Re: Re: Problems with INNER JOIN

am 30.01.2009 00:27:24 von dmagick

Please post under the other replies, it makes it easier to follow.

Terion Miller wrote:
> Yep they both work separately ... can't believe I've been stuck on the
> same thing all day, it seems like an easy thing...

From what you've shown us it should work.

Maybe post the output of:

show create table workorders;
show create table admin;

to see if we can spot any discrepencies (maybe one has 'AdminID' and one
has 'ID' or something).

Do you get any errors from mysql when you run the full query with the join?

--
Postgresql & php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php