nested MySQL query

jasonmills58

Leatherback is gonna kill me if he sees this... :rolleyes:

So, lets my assume my sql connections work, cuz they do. I call this, and it works:

$data = mysql_query( "SELECT * FROM notes WHERE clientid='$id' " );
$a_row = mysql_fetch_array( $data );

Then I populate fields with

whatever field = $a_row['date']
whatever field 2 =$a_row['topic']
whatever field 3 =$a_row['comment']
I'm cool with this part

Then I come to a field that may have anywhere from 0 to 4 attachments listed. Now, how do I set up the while statement? I figured it would look something like this, but for some reason, PHP wants me to tell it exactly what to do, instead of using ESP to know what I want... :rolleyes:

print "<td>";
    while //not sure here - looking for "while there are notes"
        $data = mysql_query( "SELECT * FROM notefiles WHERE noteid=$a_row['noteid']" )
        while ( $b_row = mysql_fetch_array( $data ) ) {
            print "<a href=\"../uploads/".$a_row['filename']."\">".$a_row['filename']."</a><br>\n";

Does that make sense? I got this to work fine when there is only one file per record, but now i have to take it up a notch. IT"S EXTREME CODING! SUNDAY SUNDAY SUNDAY! YOU PAY FOR THE WHOLE SEAT, BUT YOU'LL ONLY NEED THE EDGE!

Oof, or its a little late, and the coffee is making me kinda punchy...

leatherback

OK.. Somebody here got a knife.. Want to kill someone ;-)

$data = mysql_query( "SELECT * FROM notes WHERE clientid='$id' " ); 

while($a_row = mysql_fetch_array( $data ));
{
print "<td>"; 
            $data = mysql_query( "SELECT * FROM notefiles WHERE noteid=$a_row['noteid']" ) 
        while ( $b_row = mysql_fetch_array( $data ) ) { 
            print "<a href=\"../uploads/".$a_row['filename']."\">".$a_row['filename']."</a><br>\n";
}}

Was this what you were looking for?

jasonmills58

Hey, at least its not 2 am for you... 😃

I think thats it. Are you sure we have that second SELECT outside the while statement?

Or would it be:

 
$data = mysql_query( "SELECT * FROM notes WHERE clientid='$id' " ); 

while($a_row = mysql_fetch_array( $data )); 
{ 
print "<td>"; 
while ( $data = mysql_query( "SELECT * FROM notefiles WHERE noteid='.$a_row['noteid'].'" ) ) {
    $b_row = mysql_fetch_array( $data ) ;
    print "<a href=\"../uploads/".$b_row['filename']."\">".$b_row['filename']."</a><br>\n"; 
}}

leatherback

Hey Jason,

Hm.. Try to remember what time it was when you asked me the last questions I usually answer.. THere it is 2 am.. Here it is almost 12..

As for the query:
The second query is in the first "while" loop. It GENERATES the results for tehsecond while loop.

Probably things can be queried more effectively by combining the two. But I am not clear on your tables. You could fiddle a littel if you think it could work..

Something like:
' select * from notes, notefiles where clientid='$id' and notes.noteid = notefiles.noteid'

and have just one loop.

jasonmills58

thats a great idea, i will fiddle around with that.

thanks so much for ALL your help. I really do appreciate it.

You're in the Netherlands, right? I thought it would be around 12 or 12:30pm there... anyway, thanks again!

leatherback

Here it is 11.45 AM (So I am working hard at my official job... -> Writers block on a number of articles.. Trying to get inspiration..)

jasonmills58

I think I discovered a flaw in our plan for world domination through fewer SQL queries...

"select * from notes, notefiles where clientid='$id' and notes.noteid = notefiles.noteid"

This was a great idea, but it doesn't allow for multiple notefiles with each note. The "notefiles" is a database holding the info and labels for the various uploaded files associated with each note in the "notes" database.

If you're curious, "notes" looks like:
noteid (auto-inc) | clientid | topic | date | comment

and noteid looks like:
notefileid (auto-inc) | noteid | clientid | topic | filename

Anyway, I'm going to try out that nested SQL statement. It seemed to work, and first blush.

I hope your writer's block clears up. I write fiction myself, and that can be a real pain. Sorry to bug you at work. Thanks again for all your help. 🙂