[SOLVED] PHP SQL search problem.. PLEASE HELP :-)

[steve2007]

Hello

 

Please can someone help me. I am trying to print a list from my sql database which will show counties and quantity of late deals.

 

example:-

France      (5)

Spain        (2)

USA          (5)

 

For some reason I'm only able to achieve the following result.

 

example:-

 

France    (5)

France    (2)

France    (5)

 

As you can see the below script is listing the quantities correctly however it is listing France for ever result.

 

{
$query = "SELECT 'country', COUNT(latedeals)  FROM properties WHERE latedeals NOT LIKE '' GROUP BY country ORDER BY RAND()
LIMIT 25"; 

$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
echo"	<table width=100% border=0 cellspacing=0 cellpadding=2><tr><td width=90>";
echo "$country";
echo"</td><td width=10%><div align=right> $latedeals";
echo "(". $row['COUNT(latedeals)'] .")";
echo "</div></td></tr></table>";

}
}

 

 

Any ideas why I can only list France as a country within the list???

 

Regards

 

Steve

[Yesideez]

You're echo'ing "$country" and should be using

$row['country']

 

Although I can't see where $country is being set to "France" - maybe earlier in the script somewhere?

[steve2007]

Hello

 

Thanks for your response. I have changed the string to echo $row['country']; as you have recommended.

 

Now I am getting the following results.

 

Example

 

            (5)

            (2)

            (5)

 

 

I am missing the countries... any ideas????

 

Here is the edited script:

 

############################################################

 

[steve2007]

 

Sorry...try again.... here's the updated code

 

##############################################################

 

{

$query = "SELECT 'country', COUNT(latedeals)  FROM properties WHERE latedeals NOT LIKE '' GROUP BY country ORDER BY RAND()

LIMIT 25";

 

$result = mysql_query($query) or die(mysql_error());

 

// Print out result

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

echo" <table width=100% border=0 cellspacing=0 cellpadding=2><tr><td width=90>";

echo $row['country'];

echo"</td><td width=10%><div align=right> $latedeals";

echo "(". $row['COUNT(latedeals)'] .")";

echo "</div></td></tr></table>";

 

}

}

[GingerRobot]

I think all you need to do is get rid of some single quotes around 'country' in your query:

 

<?php
$query = "SELECT country, COUNT(latedeals)  FROM properties WHERE latedeals NOT LIKE '' GROUP BY country ORDER BY RAND()
LIMIT 25"; 
?>

 

When referring to table names, columns etc, you should either place nothing around the word, or use backticks(`). You'll need to use backticks if the name is a reserved word in mysql.

 

[steve2007]

Hi

 

That worked a treat... Thank you

 

All running smoothly now, Fantastic!!!!!

 

regards

 

Steve