Help with sql_query -> where statement.

[jitu]

I have a table with say 2000 members name.

 

Now when the viewers search for a name by first name and doesn't find that he got some suggestion for similar user name/ nick name.

 

Problem is when I have say 50 people with same nick name what should be the query statement. I worte something as below:

 

$result = mysql_query("SELECT * FROM theTable WHERE nickname='{$_GET['who']}' ORDER BY id ASC")

 

It shows me something like this:

Nick            ID
xyz        123456789
xyz        123456789
xyz        123456789
xyz        123456789

 

But I nedd something like

Nick            ID
xyz        123456789
xyz        987456321
xyz        521478935
xyz        357412599

   

 

What should be the statement ? Please help me.

[Barand]

try

 

$result = mysql_query("SELECT DISTINCT * FROM theTable WHERE nickname='{$_GET['who']}' ORDER BY id ASC")

[jitu]

Didn't work ??? :-[

[Illusion]

 

It shows me something like this:

Nick            ID
xyz        123456789
xyz        123456789
xyz        123456789
xyz        123456789

 

are these resutls are seen from CUI or PHP. If PHP, post the code.

[jitu]

The code om simplest form:

<?php
//call datatbase
$result = mysql_query("SELECT * FROM theTableName WHERE nickname='{$_GET['who']}' ORDER BY id ASC")
or die(mysql_error());

$row = mysql_fetch_array($result);

//start table
echo("<table boder='0' cellpadding='0' cellspacing='3' width='500'><tr>");

//asign variable
$num = mysql_num_rows($result);
$i=0;

//loop start
while ($i < $num)
{
echo("<td width='50%' align='center'>".$row['nick']."</td><td width='50%' align='center'>".$row['id']."</td>");
$i++;
}
//end table
echo("</tr></table>");
?>

[Barand]

fetch_array statement needs to be inside the loop, not before it, otherwise you just read the first record and repeat its contents.

 

<?php
while ($i < $num)
{
$row = mysql_fetch_array($result);

echo("<td width='50%' align='center'>".$row['nick']."</td><td width='50%' align='center'>".$row['id']."</td>");
$i++;
}

[jitu]

Sorry for my fault, I mistype while re-writing, In my original script it is inside the loop, 

 

corrected code

 

<?php
//call datatbase
$result = mysql_query("SELECT * FROM theTableName WHERE nickname='{$_GET['who']}' ORDER BY id ASC")
or die(mysql_error());


//start table
echo("<table boder='0' cellpadding='0' cellspacing='3' width='500'><tr>");

//asign variable
$num = mysql_num_rows($result);
$i=0;

//loop start
while ($i < $num)
{

$row = mysql_fetch_array($result);

echo("<td width='50%' align='center'>".$row['nick']."</td><td width='50%' align='center'>".$row['id']."</td>");
$i++;
}
//end table
echo("</tr></table>");
?>

 

But still it's not working!!

[fenway]

Why wouldn't you just use a while ( $row = mysql_fetch_assoc( $result ) )?