split COUNT results

[RON_ron]

I'm getting the number of friends in each STATE. How do I split the results in $returnS?

 

$query = "SELECT statez, COUNT(statez)FROM abs GROUP BY statez"; 
$results = mysql_query($query);
$returnS="";
while($line = mysql_fetch_array($results))
{
$returnS.= $line['COUNT(statez)'].",,".$line['statez'].",,,";
}
echo $returnS;
mysql_close($link);
?>

[mikosiko]

why are you creating that $resultS variable? .... seems to me that what you want to do is display the records right?

it example will do it (very simplified)... notice also how I did assign an alias for your aggregate function to simplify it usage.

 

$query = "SELECT statez, COUNT(statez) AS Tstate FROM abs GROUP BY statez"; 
$results = mysql_query($query) or die( "Query Error: " . mysql_error());

while($line = mysql_fetch_array($results))
{
   // here you can display your record in any format that you want  probably in a html table
      echo $line['Tstate'] . " ----- " . $line['statez'] . "<br />";
}
mysql_close($link);
?>

[RON_ron]

IS it possible to split the results in to several echos?

 

$query = "SELECT statez, COUNT(statez) AS Tstate FROM abs GROUP BY statez"; 
$results = mysql_query($query) or die( "Query Error: " . mysql_error());

while($line = mysql_fetch_array($results))
{
   // here you can display your record in any format that you want  probably in a html table
echo $line['Tstate1'] . " ----- " . $line['statez1'].",,";
echo $line['Tstate2'] . " ----- " . $line['statez2'].",,";
echo $line['Tstate3'] . " ----- " . $line['statez3'].",,";
echo $line['Tstate4'] . " ----- " . $line['statez4'].",,";
echo $line['Tstate5'] . " ----- " . $line['statez5'].",,";
}
mysql_close($link);
?>

[mikosiko]

that defeat the purpose of your while loop.  the while loop is doing exactly that... echoing a line for each record in your recordset. 

 

maybe if you explain a little more your objective I can suggest something different

[RON_ron]

I'm using this to pull in data in to my flash website. I need to get the COUNT value of each state as separated/ as individual outputs. Because I need to load the values in separate text boxes in several locations.

 

E.g.

Assume the echo gives the following results.

STATE A - 12

STATE B - 30

STATE C - 41

STATE D - 11

 

I need php to send them individually in several echos.

echo $line["number_of_friends_in_state_1"].",,";

echo $line["number_of_friends_in_state_2"].",,";

echo $line["number_of_friends_in_state_3"].",,";

etc...

 

[mikosiko]

in that case you should store your results in an array and after that manipulate the array as you wish... per example:

$query = "SELECT statez, COUNT(statez) AS Tstate FROM abs GROUP BY statez"; 
$results = mysql_query($query) or die( "Query Error: " . mysql_error());

$values = array();  // define your array

while($line = mysql_fetch_array($results))
{
    // store each record as an element into the array
    $values[] = $line;
}

   // here you can display your record in any format that you want  
echo $values[0]['Tstate'] . " ----- " . $values[0]['statez1'].",,";
echo $values[1]['Tstate'] . " ----- " . $values[1]['statez1'].",,";
echo $values[2]['Tstate'] . " ----- " . $values[2]['statez1'].",,";
echo $values[3]['Tstate'] . " ----- " . $values[3]['statez1'].",,";
echo $values[4]['Tstate'] . " ----- " . $values[4]['statez1'].",,";
echo $values[5]['Tstate'] . " ----- " . $values[5]['statez1'].",,";
.....
echo $values[n]['Tstate'] . " ----- " . $values[n]['statez1'].",,";  
// obviously n represent the total number of records /elements in you array .... 
// no a good solution IMHO... it should be done in a loop


mysql_close($link);
?>

[RON_ron]

Thanks mikosiko. Do I have to do any adjustments to make it working? or just a cut and paste would do it? (I'm not a php person)

 

Did not work though.

[mikosiko]

you have to adjust it to the rest of your code...

 

what part did not work?.... probably because you cut/paste it without making it coherent with the rest of your code?

 

post your complete code for further help

 

[RON_ron]

Awesome!!! It works a treat! (I was so excited and I did not change the 'n' in my first attempt)

 

Thanks a million mikosiko.

[mikosiko]

good.... but I'm still don't like it as a solution....

 

what is going to happens if tomorrow you have another "statez"  ... your query is going to return an additional row and your code will need to be manually adjusted... not good....

 

you should try to review your code and make it work automatically with a loop... that is the right way IMHO.