Page 1 of 1

Unwanted exponential display

Posted: Sat Jan 22, 2005 2:24 am
by Serengeti
I realize what the error is doing here. I'm just not understanding how to fix it. It is supposed to display each photo related to the MID once in a 3 column table. What it actually does is display each photo as many times as there are photos in the database. For instance, if there were 3 total photos in the database it would display 1, 2, 3, 1, 2, 3, 1, 2, 3. If there were 2 photos it would display 1, 2, 1, 2. An exponential display. Any clues? I'm thinking my SELECT statement isn't isolating the MID but I'm not sure how to do something like this in MySQL.

Code: Select all

function getMembersPhoto(){
    Global $connection;
        $today = date("Ymd");
        $member = $_GETї'MID'];
	
        $numcols = 3; // how many columns to display
        $numcolsprinted = 0; // no of columns so far
	
    hookUpDb();
        $query = "SELECT t1.*, t3.MemberNum
                   FROM photos t1, xrefphotomember t2, memberinfo t3
                   WHERE t3.MID = $member";
        $result = mysql_query($query,$connection);

    openPhotoTable();
    
    while ($row = mysql_fetch_row($result)){
        $PID           = $rowї0];
        $PhotoNum      = unFormatData($rowї1]);
        $PhotoDescr    = unFormatData($rowї2]);
        $MemberNum     = unFormatData($rowї4]);
		   
        $PhotoURL      = "images/cars/". $MemberNum. "-". $PhotoNum. ".jpg";
        $Thumb		   = "images/cars/". $MemberNum. "-". $PhotoNum.  "-thumb.jpg";
		  $OnClick		 = "onClick="JustSoPicWindow('". $PhotoURL. "','600','451','* * Click screen to close * *','#000000','hug image','0');return document.MM_returnValue"><img src="$Thumb" alt="" width="169" height="127" border="0"></a><br />$PhotoDescr</td>";
        $Output         = "<td class="memberdata"><a href="javascript:;" ". $OnClick;

		   if ($numcolsprinted == $numcols) &#123;
		   print "</tr>\n<tr>\n";
		   $numcolsprinted = 0;
		   &#125;

			print("$Output");

	 	  $numcolsprinted++; 
		   
    &#125;//end while
	 
    $colstobalance = $numcols - $numcolsprinted;
    for ($i=1; $i<=$colstobalance; $i++) &#123;
    print "<TD class="memberdata"></TD>\n";
    &#125;

    closePhotoTable();

    closeMemberTable();
    mysql_free_result($result);
    mysql_close($connection);
&#125;

Posted: Sat Jan 22, 2005 2:33 am
by feyd
your query is at fault.

multi table inner joins need to attach together, or you'll get ~duplicated results.. like this example:

Code: Select all

SELECT * FROM table1 a, table2 b, table3 c WHERE a.id = b.a_id AND b.somethingelse = c.other AND c.id = '6'

Posted: Sat Jan 22, 2005 2:41 am
by Serengeti
sweet! I didn't really expect an answer tonight, especially within minutes!

I fixed the query per your example and it works perfectly.

THANKS!


btw, i'm new to PHP/MySQL but I've been working about 2 years in CF/MSSQL. Its been a while though so I'm a little rusty....and its late...and I'm hungry...and well, I suck, lol.

thanks again

jason



for those wanting the working query:

Code: Select all

$query = "SELECT t1.*, t3.MemberNum
                   FROM photos t1, xrefphotomember t2, memberinfo t3
                   WHERE t3.MID = $member 
				   AND t2.PID = t1.PID
				   AND t3.MID = t2.MID";

Posted: Sat Jan 22, 2005 2:44 am
by feyd
goodie, we can use some more people experienced in MSSQL around here.. :)

Posted: Sat Jan 22, 2005 2:49 am
by Serengeti
check out my CF/MSSQL site...
http://www.brazosportinfo.com

its still under dev, i need to input more records, add review functionality, and get some history of the area for static content

Posted: Sat Jan 22, 2005 2:50 am
by Serengeti
go to Industrial > Transportation > Port Freeport to see the full business listing ;)

Posted: Sat Jan 22, 2005 2:55 am
by feyd
psssst.. you forgot to set the page background colors 8O

Posted: Sat Jan 22, 2005 2:58 am
by Serengeti
its supposed to be white...what browser are you using? i've only tested it on IE 5.5 & 6 and FF

Posted: Sat Jan 22, 2005 2:59 am
by feyd
Firefox.. but my default background color is grey, to specifically tell me when someone forgot to set the background color.. ;)

You wouldn't believe how many "major" sites forget to set the background color on a LOT of pages...

*cough* http://msdn.microsoft.com/library/ :oops: 8O excuse me!

Posted: Sat Jan 22, 2005 3:02 am
by Serengeti
lol, that's cool......i set it ;)


seems as if this site didn't set it either 8O

edit: or maybe i set the wrong thing in ff....now everything has a grey background, lol