SQL inside SQL

[prokhaled]

In this code :

$Sql = "select * from Subject where UserID>=1";
$Result = mysql_query($Sql);
while ($Row = mysql_fetch_array($Result))
{
$UserName="$Row[UserName]";

$Sql = "select * from Members where UserName=$UserName";
$Result = mysql_query($Sql);
while ($Row = mysql_fetch_array($Result))
{
echo "$Row[HomePage]";
}

}


I try to run SQL inside SQL But it give me an error !!!

the problem that I have 2 table (Subject and Members) and I want to get UserName(s) from
Subject table and after that get the homepage of those username(s) from Members Table and echo them.

How can I do that ???

Thanks

[cpradio]

$Sql = "select * from Subject where UserID>=1"; 
$Result = mysql_query($Sql); 
while ($Row = mysql_fetch_array($Result)) 
{ 
  $UserName="$Row[UserName]"; 

  $Sql2 = "select * from Members where UserName=$UserName"; 
  $Result2 = mysql_query($Sql2); 
  while ($Row2 = mysql_fetch_array($Result2)) 
  { 
    echo "$Row2[HomePage]"; 
  } 

} 

This should not create an error

[scoutt]

$Sql = "select * from Subject, Members where Subject.UserID>=1 and Member.UserName = $UserName"; 
$Result = mysql_query($Sql); 
while ($Row = mysql_fetch_array($Result)) 
{
echo $Row["HomePage"];  
echo $Row["UserName"]; 

} 

I believe that should work.

[cpradio]

show off

[scoutt]

thanks

[thexchord]

I really like the way u guys take time out to help others


and btw, If I remember correctly, MySQL doesn't support nested queries. but pls check on the latest.

[thexchord]

I really like the way u guys take time out to help others


and btw, If I remember correctly, MySQL doesn't support nested queries. but pls check on the latest.

[thexchord]

sorry abt the multiple posts..oops and this one too.

[cpradio]

SQL will allow nested queries as long as you give it different variable names to work with. Although you cannot use the same variables over and over again in a nested query.

-Matt

[cpradio]

Originally posted by thexchord
sorry abt the multiple posts..oops and this one too.

You can delete your own posts, but pressing the edit button and clicking the Delete Checkbox and then the Delete Button

[thexchord]

cpradio - I was talking specifically abt MySQL - I don't think u can do nested SELECTs yet in it.

[scoutt]

I have version 3.0 and you can do them.

[cpradio]

MySQL and SQL both allow nested queries. I have done them in both.

(sorry for just stating SQL previously I meant both)

[The Hobo]

I thought nesting wasn't implimented yet?

SELECT * FROM (SELECT * FROM users WHERE city='Wyoming') WHERE areacode='48444'

Okay, that's a bad example, because you could use AND. But I thought you couldn't nest like that yet?

[scoutt]

that is actually looks bad. I never seen it like that. would that even work?? I am going to say not....

[The Hobo]

Originally posted by scoutt
that is actually looks bad. I never seen it like that. would that even work?? I am going to say not....

That's why I said I don't think it's implimented yet, but some website was talking about how it is going to be soon or something. I thought that's what you guys were talking about.

[scoutt]

here you go, that code is actually valid, scary isn't

http://www.mysql.com/doc/A/N/ANSI_diff_Sub-selects.html

[The Hobo]

So I'm not crazy, eh?

[cpradio]

Originally posted by The Hobo
I thought nesting wasn't implimented yet?

SELECT * FROM (SELECT * FROM users WHERE city='Wyoming') WHERE areacode='48444'

Okay, that's a bad example, because you could use AND. But I thought you couldn't nest like that yet?

If that is what thexchord and you, Hobo meant by nesting then I apologize. I thought you were refering to the code I used.

In C, C++, and many other languages what I did would be considered "Nesting", so I guess I never realized that MySQL had such a weird definition for it.

-Matt

[The Hobo]

Actually, according to what Scoutt found, it's known as Sub-selects.

[scoutt]

could be hobo, you never know.

also the code that I did is called nesting. so they is many forms I guess

[The Hobo]

forms? What?

[cpradio]

What scoutt is trying to say is the definition of "Nesting" in today's terminology is too broad, so many forms of it exist and everything that was posted in this thread has been considered some type of "Nesting" situation.

-Matt

[The Hobo]

Life is crazy, yo!

[scoutt]

yes that is right, many forms of nesting. crazy

[prokhaled]

Code :

$Sql = "select * from Subject, Members where Subject.UserID=100 and Member.UserName = $UserName";
$Result = mysql_query($Sql);
while ($Row = mysql_fetch_array($Result))
{
echo $Row["HomePage"];
echo $Row["UserName"];
}

it's very nice code .. but there is a problem here (and Member.UserName = $UserName) the problem that I do not have $UserName because I will get it form (Subject Table) at frist
so I neet code like that.

$Sql = "select * from Subject, Members where Subject.UserID=100 and Member.UserName = (UserName from Subject Table that it's UserID =100 )";
$Result = mysql_query($Sql);
while ($Row = mysql_fetch_array($Result))
{
echo $Row["HomePage"];
echo $Row["UserName"];

}

_______
There is something like that

Thanks

[scoutt]

ok I see what you want. is there a category in Member that links it to the Subject table? because you could do something like

$Sql = "select * from Subject, Members where Subject.UserID=100 and Member.Username = Subject.Username"; 
$Result = mysql_query($Sql); 
while ($Row = mysql_fetch_array($Result)) 
{ 
echo $Row["HomePage"]; 
echo $Row["UserName"]; 
} 

[The Hobo]

[prokhaled]

Code:

$Sql = "select * from Subject, Members where Subject.UserID=100 and Member.Username = Subject.Username";
$Result = mysql_query($Sql);
while ($Row = mysql_fetch_array($Result))
{
echo $Row["HomePage"];
}

if I have 2 column in the same name in Subject table and Members Table .. How can I echo HomePage from Subject table and how can I echo it from Members table

[scoutt]

what difference would it make if they are both the same? it is the same member name in both isn't it. doing the way I have it you can echo anything from either table.

besides that is what you have in the very first post. 2 tables with the same name of category.

[The Hobo]

Why is this thread still going?





I'm loosing my hair!!

[scoutt]

beats me, some poeple are hard to please

[ricmitch_uk]

Just one question.

Is there an SQL way to return the highest value a column?

[scoutt]

not that I'm aware of. you would have to get the distinct ones from the column and then put them in an array, then sort that array so you can echo it out like so echo $str[0];

actually you might wanna try

select max(columnname) from table

[The Hobo]

Originally posted by ricmitch_uk
Just one question.

Is there an SQL way to return the highest value a column?

I'd do:

$sql = "SELECT cn FROM tn ORDER by cn DESC LIMIT 1";

$hv = mysql_fetch_array(mysql_query($q)) or die(mysql_error()); 

$hv should contain the highest value in that column (cn) of the table (tn)

[ricmitch_uk]

Thanks I should have firgured that out myself. I guess I'm just getting dumb, having not done any SQL for a while. I just thought there might be an easy way I suppose.