[RESOLVED] SELECT and Display array

roscor

Any help would be appreciated,

I have an array which contains towns. I the display the towns in a table 4x columns with as many rows as is needed using columns++. I need to SELECT from my database to show how many records are held in each town, here is my code,

$towns ="Abingdon,Adderbury,Ambrosden,Ardington,Arncott,Asthall,Aston Rowant,Aynho,Bampton,Banbury,Barford St Michael,Barnard Gate,Beckley,Benson,Bicester,Black Bourton,Bledington,Bletchingdon,Blewbury,Bloxham,Brightwell Baldwin,Brize Norton,Broadwell,Buckland,Buckland Marsh,Burcot,Burford,Carterton,Chadlington,Chalgrove,Charlbury,Charney Bassett,Checkendon,Chesterton,Chinnor,Chipping Norton,Chipping Warden,Chislehampton,Christmas Common,Church Enstone,Church Hanborough,Clanfield,Clifton,Clifton Hampden,Cotswolds,Cotswolds and Central England,Countywide,Cropredy,Cuddesdon,Culham,Cumnor,Deddington,Denchworth,Didcot,Dorchester-on-Thames,Drayton St Leonard,Ducklington,Duns Tew,East Hanney,East Hendred,Edgehill,Enstone,Faringdon,Fifield,Filkins,Finmere,Finstock,Frilford,Frilford Heath,Fringford,Fritwell,Fyfield,Garsington,Goring-on-Thames,Great Bourton,Great Tew,Greatworth,Harwell,Headington,Henley,Henley on Thames,Henley-on-Thames,Hook Norton,Horton cum Studley,Horton-Cum-Studley,Kelmscot,Kidlington,Kingham,Kings Sutton,Kingston Blount,Kingston Lisle,Kirtlington,Launton,Lewknor,Little Coxwell,Littlemore,Longcot,Longworth,Lower Heyford,Lower Wolvercote,Mapledurham,Marcham,Marsh Gibbon,Marston,Middle Barton,Middleton Cheney,Milton Common,Minster Lovell,Murcott,Nettlebed,North Moreton,Nuffield,Oxford,Oxford Street,Pishill,Radley,Ramsden,Roke,Rotherfield Peppard,Shenington,Shilton,Shipton-under-Wychwood,Shrivenham,Shutford,Souldern,South Moreton,South Stoke,Southmore,Sparsholt,Stadhampton,Standlake,Stanford in the Vale,Stanton Harcourt,Stanton St John,Steeple Aston,Steventon,Stoke Row,Stow-on-the-Wold,Stratton Audley,Sutton Courtenay,Swalcliffe,Swerford,Swinbrook,Tadmarton,Tetsworth,Thame,The Barringtons,Thrupp,Tiddington,Uffington,Wallingford,Walton Manor,Wantage,Waterperry,Waterstock,Watlington,West Hanney,West Hendred,Westcott Barton,Weston on the Green,Weston-on-the-Green,Wheatley,Witney,Woodstock,Woolstone,Wootten,Wootton,Wytham";

$townsArray= explode(",", $towns);

//check array contents
//print_r($townsArray);

//connect to database
include("dbinfo.inc.php");
mysql_connect($host,$username,$password);
mysql_select_db($database) or die(mysql_error());


$query = "SELECT COUNT(*) AS `num`, `town` FROM `tenant_data` WHERE `town` IN ('" . implode("', '", $townsArray) . "')";
$exec = mysql_query($query);

$towns = array();
while($result = mysql_fetch_array($exec)) {
    $towns[] = $result;
    } 


echo "<table style='border-style:solid; border-width:1px; border-color:#000000;' width='800' bgcolor='#FFFFFF' cellpadding='0' cellspacing='0' align='center'>";
$counter = 0;
foreach($townsArray as $list)

{
  if($counter != 0 and $counter % 4 == 0)
  {
    echo "</tr>\n<tr>";
  }
  $counter++;
  echo "<td><img src='images/freelisting_arrow2.gif'>&nbsp;&nbsp;&nbsp;<a href='resulttown.php?town=$list'><font size='1' face='Verdana'>$list &nbsp;(&nbsp;$towns[num]&nbsp;)</a></font></td>";


}
echo "</tr>\n</table>\n"; 
?>

I display the table great, but I am not getting any result back form the database, neither (0) or (19) but ( ).

any suggestions

roscor

Roger_Ramjet

read up about GROUP BY and AGGREGATE FUNCTIONS

Count(*) will not do most of the things you think it will do and should not be used except in those limited circumstances mentioned in the manual.

roscor

thanks for the reply roger, cannot get my head around it,

i am using

$query = "SELECT town, COUNT(DISTINCT town) AS num, town FROM tenant_data WHERE town IN ('" . implode("', '", $townsArray) . "') group by town";$exec = mysql_query($query) or die(mysql_error().$query);
// now retrieve the data:
$towns = array();
while($row = mysql_fetch_assoc($exec))
  {
  $towns[$row['town']] = $row['num'];
  }

which returns, using print_r($towns);

Array ( [Abingdon] => 1 [Accrington] => 1 )

because at the moment there are only one record in each of those towns and none in the others. but I want to return

Array ( [Abingdon] => 1 [Accrington] => 1 [Adderbury] => 0 [Ambrosden] => 0)

and so, negative results but my Query is only bringing items with value. I have tried changing the query varying round COUNT(*) COUNT(DISTINCT town) with no joy.

Am I going about it all wrong.Any further advice would be much appreciated

Roger_Ramjet

COUNT(town) is all you need - GROUP BY takes care of distinct values so you do not need DISTINCT.

You cannot count things that are not there.

If you want zeros for towns not in the database then just add them in php.

roscor

Thanks Roger for the advice, I have now got it working with this

$towns ="Abingdon,Accrington,Adderbury,Ambrosden,Ardington,Arncott,Asthall,Aston Rowant,Aynho,Bampton,Banbury,Barford St Michael,Barnard Gate,Beckley,Benson,Bicester,Black Bourton,Bledington,Bletchingdon,Blewbury,Bloxham,Brightwell Baldwin,Brize Norton,Broadwell,Buckland,Buckland Marsh,Burcot,Burford,Carterton,Chadlington,Chalgrove,Charlbury,Charney Bassett,Checkendon,Chesterton,Chinnor,Chipping Norton,Chipping Warden,Chislehampton,Christmas Common,Church Enstone,Church Hanborough,Clanfield,Clifton,Clifton Hampden,Cotswolds,Cotswolds and Central England,Countywide,Cropredy,Cuddesdon,Culham,Cumnor,Deddington,Denchworth,Didcot,Dorchester-on-Thames,Drayton St Leonard,Ducklington,Duns Tew,East Hanney,East Hendred,Edgehill,Enstone,Faringdon,Fifield,Filkins,Finmere,Finstock,Frilford,Frilford Heath,Fringford,Fritwell,Fyfield,Garsington,Goring-on-Thames,Great Bourton,Great Tew,Greatworth,Harwell,Headington,Henley,Henley on Thames,Henley-on-Thames,Hook Norton,Horton cum Studley,Horton-Cum-Studley,Kelmscot,Kidlington,Kingham,Kings Sutton,Kingston Blount,Kingston Lisle,Kirtlington,Launton,Lewknor,Little Coxwell,Littlemore,Longcot,Longworth,Lower Heyford,Lower Wolvercote,Mapledurham,Marcham,Marsh Gibbon,Marston,Middle Barton,Middleton Cheney,Milton Common,Minster Lovell,Murcott,Nettlebed,North Moreton,Nuffield,Oxford,Oxford Street,Pishill,Radley,Ramsden,Roke,Rotherfield Peppard,Shenington,Shilton,Shipton-under-Wychwood,Shrivenham,Shutford,Souldern,South Moreton,South Stoke,Southmore,Sparsholt,Stadhampton,Standlake,Stanford in the Vale,Stanton Harcourt,Stanton St John,Steeple Aston,Steventon,Stoke Row,Stow-on-the-Wold,Stratton Audley,Sutton Courtenay,Swalcliffe,Swerford,Swinbrook,Tadmarton,Tetsworth,Thame,The Barringtons,Thrupp,Tiddington,Uffington,Wallingford,Walton Manor,Wantage,Waterperry,Waterstock,Watlington,West Hanney,West Hendred,Westcott Barton,Weston on the Green,Weston-on-the-Green,Wheatley,Witney,Woodstock,Woolstone,Wootten,Wootton,Wytham";

$townsArray= explode(",", $towns);

include("dbinfo.inc.php");
mysql_connect($host,$username,$password);
mysql_select_db($database) or die(mysql_error());
//include("rows basic.php");
//print_r($num);
$result = mysql_query("SELECT town, COUNT(town) AS num FROM tenant_data WHERE town IN ('" . implode("', '", $townsArray) . "') group by town");

echo "<table style='border-style:solid; border-width:1px; border-color:#000000;' width='800' bgcolor='#FFFFFF' cellpadding='0' cellspacing='0' align='center'>";
$counter = 0;

 if($counter != 0 and $counter % 4 == 0)
  {
    echo "</tr>\n<tr>";
  }
  $counter++;

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

echo "<td><img src='images/freelisting_arrow2.gif'>&nbsp;&nbsp;&nbsp;<a href='resulttown.php?town=$row[town]'><font size='1' face='Verdana'>$row[town] &nbsp;(&nbsp;$row[num]&nbsp;)</a></font></td>";

   }
echo "</tr>\n</table>\n";

because I have selected from my database only records held are displayed there are no zero returns. Is there any way I can use the $towns array with all towns then display a (0) if no records are held. I am now well and truly stuck with this problem.

regards

roscor

Roger_Ramjet

If you have a table with all the towns in then you can do it in sql using a LEFT JOIN.

(One thing, change your coding style: create the sql query as a string var then use it mysql_query($sql), you will find it much easier to debug this way. Also get into the habit of using all capitals for the sql operators, makes it much easier to read)

"SELECT * FROM towns t LEFT JOIN (SELECT town, COUNT(town) AS num FROM tenant_data  GROUP BY town) AS c USING(town)
WHERE t.town IN ('" . implode("', '", $townsArray) . "')"

This will return NULL for those towns with no count, deal with that in php.

roscor

Sorted it,

There may be times when your database is unpopulated you just want records from an array to display the numbers of records even if the result is Zero. My database will only be populated when users submit the data so I needed the facility to draw the information from the array.

<table style="border-style:solid; border-width:1px; border-color:#000000;" width="800" bgcolor="#FFFFFF" cellpadding="0" cellspacing="0" align="center"><tr>

<?php

$countiesni="Belfast,County Antrim,County Armagh,County Down,County Fermanagh,County Londonderry,County Tyrone";

$countiesniArray= explode(",", $countiesni);
$countiesniArray = array_diff($countiesniArray, array(""));

for($item=0;$item<count($countiesniArray);$item++){
if($counter != 0 and $counter % 4 == 0)
  {
    echo "</tr>\n<tr>";
  }
  $counter++;

  include("dbinfo.inc.php");
mysql_connect($host,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM tenant_data WHERE county IN ('$countiesniArray[$item]')" ;
$result=mysql_query($query);
$num=mysql_num_rows($result);

	echo "<td><img src='images/freelisting_arrow2.gif'>&nbsp;&nbsp;&nbsp;<a href='$countiesniArray[$item].php'><font size='1' face='Verdana'>". $countiesniArray[$item] . "&nbsp;(&nbsp;$num&nbsp;)</a></font></td>";
	}

echo "</tr>\n</table>\n"; 

?>

The array in set, exploded then the loop is set to display 4 columns up to the value of $item<count($countiesniArray); in this case the array count is 7. Then A database conection is made using $countiesniArray[$item]. This the displays a Table with results per query such as

Belfast ( 1 ) County Antrim ( 0 ) County Armagh ( 0 ) County Down ( 7 )
County Fermanagh ( 0 ) County Londonderry ( 9 ) County Tyrone ( 0 )

and so on, this method enables negative results to be displayed, basically if there is no such record as Belfast in table county the this returns (0). otherwise results are shown as County Londonderry ( 9 ). And so on. There was no need to use COUNT within the mysql query. Hope this helps others.

roscor