*SOLVED* Distinct only show one row

[slack]

Hi all,

I have the following which only shows one row. I want it to show all rows. -:

$query_rsAction = "SELECT distinct Call_ID FROM `action`";
$rsAction = mysql_query($query_rsAction, $connClient) or die(mysql_error());

while($row_rsAction = mysql_fetch_array($rsAction))
{
echo $row_rsAction['Call_ID'];
echo '<br>';
}


The above should display:

14
16

But this does not display. Only shows 16. Does anyone know why and how I can get it to display all results?

Thank you

[Desdinova]

It seems to only find one row.
Could you pass the table data from the database?

so like copy the 2 rows it's supposed to find.

[slack]

[!--quoteo(post=360918:date=Apr 2 2006, 12:25 PM:name=Desdinova)--][div class=\'quotetop\']QUOTE(Desdinova @ Apr 2 2006, 12:25 PM) [snapback]360918[/snapback][/div][div class=\'quotemain\'][!--quotec--]
It seems to only find one row.
Could you pass the table data from the database?

so like copy the 2 rows it's supposed to find.
[/quote]


Sorry don't know what you mean. I am new to this. How do I do this?

[toplay]

Is that the exact code that you're using?

If you have accidentally issued a mysql_fetch_array() before going into the while loop, then it would be a reason why you're only seeing one.

Run the same query in phpmyadmin and see what you get.

Get rid of distinct and see what you get without it.

[Desdinova]

try this:

$query_rsAction = "SELECT * FROM 'action'";
$rsAction = mysql_query($query_rsAction, $connClient) or die(mysql_error());

while($row_rsAction = mysql_fetch_array($rsAction))
{
echo $row_rsAction['Call_ID'];
echo '<br>';
}




if it doesn't work, try this:

$query_rsAction = "SELECT * FROM 'action'";
$rsAction = mysql_query($query_rsAction, $connClient) or die(mysql_error());

while($row_rsAction = mysql_fetch_object($rsAction))
{
echo $row_rsAction->Call_ID;
echo '<br>';
}

[toplay]

[!--quoteo(post=360936:date=Apr 2 2006, 10:22 AM:name=Desdinova)--][div class=\'quotetop\']QUOTE(Desdinova @ Apr 2 2006, 10:22 AM) [snapback]360936[/snapback][/div][div class=\'quotemain\'][!--quotec--]
try this:

$query_rsAction = "SELECT * FROM 'action'";
$rsAction = mysql_query($query_rsAction, $connClient) or die(mysql_error());

while($row_rsAction = mysql_fetch_array($rsAction))
{
echo $row_rsAction['Call_ID'];
echo '<br>';
}
if it doesn't work, try this:

$query_rsAction = "SELECT * FROM 'action'";
$rsAction = mysql_query($query_rsAction, $connClient) or die(mysql_error());

while($row_rsAction = mysql_fetch_object($rsAction))
{
echo $row_rsAction->Call_ID;
echo '<br>';
}
[/quote]
You got single quotes around action which won't work. I'm pretty sure you meant to use backtick marks (i.e. `action`).

The use of fetching by object should have no bearing on the outcome.

Slack, it's best to use mysql_fetch_assoc() instead of mysql_fetch_array() when you're going to use $row_rsAction['Call_ID'] syntax. The mysql_fetch_array() will work fine though because it creates numeric as well as associative column name indexes.

[Desdinova]

[!--quoteo(post=360940:date=Apr 2 2006, 08:31 PM:name=toplay)--][div class=\'quotetop\']QUOTE(toplay @ Apr 2 2006, 08:31 PM) [snapback]360940[/snapback][/div][div class=\'quotemain\'][!--quotec--]
You got single quotes around action which won't work. I'm pretty sure you meant to use backtick marks (i.e. `action`).

The use of fetching by object should have no bearing on the outcome.

Slack, it's best to use mysql_fetch_assoc() instead of mysql_fetch_array() when you're going to use $row_rsAction['Call_ID'] syntax. The mysql_fetch_array() will work fine though because it creates numeric as well as associative column name indexes.
[/quote]

even better yet, there aren't supposed to be any quotes around the table.

so "SELECT * FROM table"; should work fine.

[slack]

Ok.. Thanks for everyones replies. I made a stupid mistake in the code.

I called the $row_rsAction = mysql_fetch_array($rsAction) twice.

Sorry for the mistake.

[toplay]

[!--quoteo(post=360945:date=Apr 2 2006, 10:57 AM:name=Desdinova)--][div class=\'quotetop\']QUOTE(Desdinova @ Apr 2 2006, 10:57 AM) [snapback]360945[/snapback][/div][div class=\'quotemain\'][!--quotec--]
even better yet, there aren't supposed to be any quotes around the table.

so "SELECT * FROM table"; should work fine.
[/quote]
It's good to use backtick marks especially if a column or table is named the same as a MySQL reserved word (i.e. date, time). The word "action" has been used as a reserved word. Depending on the MySQL version, you may get an SQL syntax error unless you enclose them in backtick marks.

See:

[a href=\"http://dev.mysql.com/doc/refman/4.1/en/legal-names.html#id2863816\" target=\"_blank\"]http://dev.mysql.com/doc/refman/4.1/en/leg....html#id2863816[/a]

[a href=\"http://dev.mysql.com/doc/refman/4.1/en/reserved-words.html\" target=\"_blank\"]http://dev.mysql.com/doc/refman/4.1/en/reserved-words.html[/a]

[!--quoteo(post=361024:date=Apr 2 2006, 03:07 PM:name=Slack)--][div class=\'quotetop\']QUOTE(Slack @ Apr 2 2006, 03:07 PM) [snapback]361024[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Ok.. Thanks for everyones replies. I made a stupid mistake in the code.

I called the $row_rsAction = mysql_fetch_array($rsAction) twice.

Sorry for the mistake.
[/quote]
I'm glad you figured it out. That's why I mentioned it in my first post here. It's a common mistake. I recommend that you always post exactly the code you have, that way members can find the problem right away.

I've marked this topic as solved.

:)