[Resolved] duplicates/sql running wierdly

[tuf-web_co_uk]

hey

i have a script which shows links from a database, so far the code works when there is only 1 link in a given category

when 2 links are in the same category it duplicates results

heres is my code

<?       	

    	$sql = "SELECT * from links ORDER BY type asc";
    	$query = mysql_query($sql) or die ("error");

    	while ($row=mysql_fetch_array($query)) {

    		echo "<u>".$row["type"]."</u>";
    		echo "<br><br>";

    		$sql2 = "SELECT * from links where type='".$row["type"]."'";
    		$query2 = mysql_query($sql2) or die ("error");

    		while ($link=mysql_fetch_array($query2)) {

    			echo "<a href=\"".$row["url"]."\" target=\"_BLANK\">".$row["url"]."</a>";
    			echo "<br>";

    		}

    		echo "<br><br>";

    	}

    	?>

here is the output

---

Amusement

http://www.creaturesinmyhead.com
http://www.creaturesinmyhead.com

Amusement

http://weebl.jolt.co.uk
http://weebl.jolt.co.uk

Other

http://www.markbeal.co.uk

this should be the output

---

Amusement

http://www.creaturesinmyhead.com
http://weebl.jolt.co.uk

Other

http://www.markbeal.co.uk

any ideas?!

thanks Comms 😉

[tekky]

I bet if you look at your table structure + your first query + think about it really hard for 5 minutes you will figure it out.... 😉

[tekky]

ROW = SELECT * FROM table (example)
you get back
www.myfunplace.com amusement
www.barneyandfriends.com amusement
www.tellytubbies.com amusement
www.espn.com sports
www.thumbwrestling.com sports

now you output the type you come across first
echo "Amusement";

now you are calling your second loop
LINK = SELECT * FROM table WHERE type='amusement'
you get
www.myfunplace.com amusement
www.barneyandfriends.com amusement
www.tellytubbies.com amusement

however, NOW when you loop, you are calling ROW['url'] which will be the same since it hasnt iterated yet so you get
echo "www.myfunplace.com";
echo "www.myfunplace.com";
echo "www.myfunplace.com";

first loop iterates... you get
echo "Amusement";
second loop
echo "www.barneyandfriends.com";
echo "www.barneyandfriends.com";
echo "www.barneyandfriends.com";
first loop again
echo "Amusement";
second loop
echo "www.tellytubbies.com";
echo "www.tellytubbies.com";
echo "www.tellytubbies.com";
first loop again
echo "Sports";
second loop
echo "www.espn.com";
echo "www.espn.com";
echo "www.espn.com";
first loop
echo "Sports";
second loop
echo "www.mlb.com";
echo "www.mlb.com";
echo "www.mlb.com";
exit loops

make sense?

and heres's the code fixed... from what I see wrong...

<?       	

    	$sql = 'SELECT * from links ORDER BY type asc';
    	$query = mysql_query($sql) or die ("error");

    	while ($row=mysql_fetch_array($query)) {

    		echo '<u>'.$row['type'].'</u>';
    		echo '<br><br>';

    		$sql2 = "SELECT * from links where type='".$row['type']."'";
    		$query2 = mysql_query($sql2) or die ("error");

    		while ($link=mysql_fetch_array($query2)) {

    			echo "<a href=\"".$link['url']."\" target=\"_BLANK\">".$link['url'].'</a>';
    			echo '<br>';

    		}

    		echo '<br><br>';

    	}

    	?>

[tuf-web_co_uk]

isnt that the exact same code as the one i have?

[tekky]

Originally posted by tuf-web.co.uk
isnt that the exact same code as the one i have?

nope

however, NOW when you loop, you are calling ROW['url'] which will be the same since it hasnt iterated yet so you get

(in reference to your second loop) notice i changed $row to $link which is what your 2nd loop names its array from fetch_array 😉

although I think you still may get duplicates.. but ti will be

Amustment
link1
link2
Amustment
link1
link2

just set a variable to keep track of what you parsed, personally I would have a "topic" table with id/name and use that to select TYPES, then for each iteration of that loop, SELECT * FROM table where type=$curtype, that would eliminate your duplicate problem the easiest 😉

[tuf-web_co_uk]

hhmm.... very strange

i sorted out the duplicate problem easily

$sql = 'SELECT DISTINCT type from links ORDER BY type asc';
$query = mysql_query($sql) or die ("error");

worked great