Programming Help

Web Development



What's Here?

  • Members: 132,605
  • Replies: 471,695
  • Topics: 73,132
  • Snippets: 2,541
  • Tutorials: 666
  • Total Online: 921
  • Members: 59
  • Guests: 862

Who's Online?

Welcome to Dream.In.Code
Getting PHP Help is Easy!

Join 132,605 PHP Programmers for FREE! Get instant access to thousands of PHP experts, tutorials, code snippets, and more! There are 921 people online right now. Registration is fast and FREE... Join Now!




Too Many Results

2 Pages V  1 2 >  
 Reply to this topicStart new topic

Too Many Results
klewlis
post 24 Oct, 2002 - 11:51 AM
Post #1


cur tu me vexas?

*****
Joined: 9 Nov, 2001
Posts: 1,723
I have the following snippet:
CODE

$strSQL = mysql_query("SELECT * FROM projects, clients WHERE projects.client_id = clients.client_id ORDER BY enddate DESC");
echo "<table width=\"500\" cellpadding=\"0\" cellspacing=\"0\" border=\"0\">\n";
while ($row = mysql_fetch_array($strSQL)) {
    $strSQL = mysql_query("SELECT filename FROM photos WHERE project_id = '$row[project_id]'");
    $row2 = mysql_fetch_array($strSQL);
    echo "<tr><td valign=\"top\" width=\"220\"><img src=\"images/$row[project_id]/$row2[filename]\" width=\"200\" alt=\"$row2[filename]\"></td>\n";
    echo "<td valign=\"top\"><p><b>$row[project_name]</b></p>\n";
    echo "<p>Client: $row[company_name]<br>\n";
    echo "Started: $row[startdate]<br>\n";
    echo "Finished: $row[enddate]</p>\n";
    echo "</td></tr>\n";
}
echo "</table>\n";


There is only one row in the db, which is returning fine. But I'm also getting an extra row with empty fields. Any idea why? Is it because of my combined statement? Is there a better way to do that?
User is offlineProfile CardPM

Go to the top of the page

Spider
post 24 Oct, 2002 - 12:59 PM
Post #2


Arachnid

****
Joined: 10 Jul, 2002
Posts: 769


My Contributions
I believe it is because of the combined statements.

you should be able to put them all into one single statement, but I'm not sure how exactly, my php/mysql isn't exactly great.

I think another solution may be to run the second query first, it'll retain the results in $row2 and you should be able to do the rest as it is without any problems...
User is offlineProfile CardPM

Go to the top of the page

arniie
post 24 Oct, 2002 - 01:04 PM
Post #3


D.I.C Addict

****
Joined: 8 Oct, 2002
Posts: 999
Do a LEFT JOIN in the sql statement
User is offlineProfile CardPM

Go to the top of the page

klewlis
post 24 Oct, 2002 - 01:13 PM
Post #4


cur tu me vexas?

*****
Joined: 9 Nov, 2001
Posts: 1,723
spider: that won't work because the second query needs to be inside the while loop.

arniie: elaborate, please?
User is offlineProfile CardPM

Go to the top of the page

MathewS
post 24 Oct, 2002 - 01:22 PM
Post #5


D.I.C Regular

***
Joined: 14 May, 2002
Posts: 252



Thanked 1 times

Dream Kudos: 1
My Contributions
http://www.mysql.com/doc/en/LEFT_JOIN_opti...timisation.html

has information on left and right joins,

http://www.mysql.com/doc/en/JOIN.html

has information on the join syntax.
i have no idea what they are for, But i just did a search on mysql.com for it.
User is offlineProfile CardPM

Go to the top of the page

arniie
post 24 Oct, 2002 - 01:26 PM
Post #6


D.I.C Addict

****
Joined: 8 Oct, 2002
Posts: 999
use a LEFT JOIN to make two sql statements into one, this should fix the problem
User is offlineProfile CardPM

Go to the top of the page

klewlis
post 24 Oct, 2002 - 01:37 PM
Post #7


cur tu me vexas?

*****
Joined: 9 Nov, 2001
Posts: 1,723
I don't want to combine the two statements.

I want to know why I'm getting an extra row. Where is it coming from?

I've done this same procedure a thousand times with ASP, SQL Server, and MySQL. Why suddenly with PHP/MySQL am I getting an empty row?

:)
User is offlineProfile CardPM

Go to the top of the page

arniie
post 24 Oct, 2002 - 01:42 PM
Post #8


D.I.C Addict

****
Joined: 8 Oct, 2002
Posts: 999
sorry i never looked at your code - i read the post that said you wanted to combine two sql statements i apologise...erm

Not repeating code... i dunno - i remember hearing this b4 tho. I'll tell you if i remember
User is offlineProfile CardPM

Go to the top of the page

klewlis
post 24 Oct, 2002 - 02:40 PM
Post #9


cur tu me vexas?

*****
Joined: 9 Nov, 2001
Posts: 1,723
well... it seems that the problem was in reusing the $strSQL inside the while loop... I changed the inside one to $strSQL2 and now it works... *sigh*
User is offlineProfile CardPM

Go to the top of the page

fyrestorm
post 24 Oct, 2002 - 03:30 PM
Post #10


D.I.C Lover

Group Icon
Joined: 4 Apr, 2002
Posts: 3,103



Thanked 2 times

Dream Kudos: 228
My Contributions
gotta watch those variable names....whenever doing a query i'd use different names unless you cleared the variable of all data once you were done using it...
User is offlineProfile CardPM

Go to the top of the page

klewlis
post 24 Oct, 2002 - 04:31 PM
Post #11


cur tu me vexas?

*****
Joined: 9 Nov, 2001
Posts: 1,723
QUOTE(fyrestorm @ Oct 24 2002, 06:30 PM)
gotta watch those variable names....whenever doing a query i'd use different names unless you cleared the variable of all data once you were done using it...

normally it doesn't matter because of the scope... but apparently php is more picky than other languages... :P
User is offlineProfile CardPM

Go to the top of the page

fyrestorm
post 24 Oct, 2002 - 04:36 PM
Post #12


D.I.C Lover

Group Icon
Joined: 4 Apr, 2002
Posts: 3,103



Thanked 2 times

Dream Kudos: 228
My Contributions
even with scope, this would matter because you're while loop is dependant upon the first definition of your variable and you overwrote your variable when you redefined it within the while loop...
User is offlineProfile CardPM

Go to the top of the page

2 Pages V  1 2 >
Fast ReplyReply to this topicStart new topic
Time is now: 11/23/08 02:22AM
Invision Power Board v2.1.7 © 2008  IPS, Inc.
Licensed to: MediaGroup1

Live PHP Help!

PHP Tutorials

Reference Sheets

PHP Snippets

Bye Bye Ads

Free DIC T-Shirt

T-Shirt Example

Related Sites

Monthly Drawing

Thumb Drive

Partners

Top Contributors

Top 10 Kudos This Month