Page 1 of 1

Help with PHP/SQL query.

Posted: Mon Jan 11, 2010 8:50 am
by sphinx5s5
I am trying to get a count of the number of times the same road appears for one steward so if there are 4 of the same road for "steward A" it will show "the Road" and "4".

I thought the code below was correct but not getting any count back the the road?

Please please help.

PS. 'DB' instead of 'MYSQL' is valid as using a CMS "Drupal"

Code: Select all

<?PHP
$result = DB_query("SELECT field_steward_value,field_road_name_value,COUNT(field_road_name_value) AS Volume FROM content_type_resident GROUP BY field_steward_value,field_road_name_value");
 
echo "<table border='1'>
<tr>
<th>Steward Name</th>
<th>Roads Looked After</th>
<th>count</th>
</tr>";
 
 
 
while($row = DB_fetch_array($result))
 
  {
 
  echo "<tr>";
 
  echo "<td>" . $row['field_steward_value'] . "</td>";
 
  echo "<td>" . $row['field_road_name_value'] . "</td>";
 
  echo "<td>" . $row[$result->Volume] . "</td>";
 
  echo "</tr>";
 
  }
 
echo "</table>";
 
 
?>

Re: Help with PHP/SQL query.

Posted: Mon Jan 11, 2010 10:30 am
by social_experiment
Have you tried using something like mysql_num_rows() ?

Im not sure how the code will look within your CMS, but this is how i would use it

Code: Select all

 
$query = mysql_query("SELECT * FROM table WHERE steward = 'value' AND road = 'roadValue' ");
$rowsReturned = mysql_num_rows($query);
 
echo $rowsReturned;
 
 

Re: Help with PHP/SQL query.

Posted: Mon Jan 11, 2010 11:35 am
by sphinx5s5
Thank you for the very swift reply but i have an issue with outputting it to a HTML table to diplay the results.

I guess that the syntax will look a little wierd but should be valid, but could someone eval the code and tell me if there is something obviously wrong as I get the table output but no result?

once again THANK YOU!!! :o :o :o

Code: Select all

<?php
$query = db_result(db_query("SELECT field_steward_value,field_road_name_value FROM content_type_resident AS num_rows WHERE field_steward_value = 'Steward Name 4' AND field_road_name_value = 'VALUE' "));
 
echo "<table border='1'>
<tr>
<th>Steward Name</th>
</tr>";
 
 
 
while($row = DB_result($query))
{
echo "<tr>";
echo "<td>" . $row[$query] . "</td>";
echo "</tr>";
}
echo "</table>";
?>

Re: Help with PHP/SQL query.

Posted: Mon Jan 11, 2010 4:05 pm
by social_experiment
Yes, the code looks different from what i would normally use, here is what i think might be the problem :

Code: Select all

 
<?php
 $query = db_result(db_query("SELECT field_steward_value,field_road_name_value FROM content_type_resident AS num_rows WHERE field_steward_value = 'Steward Name 4' AND field_road_name_value = 'VALUE' "));
?>
 
In that code, you are selecting two fields ( field_steward_value, field_road_name_value ), but when you display the table, you are telling the php to display the $query, instead of the field(s) you retrieved.

Code: Select all

 
<?php
  while($row = DB_result($query))
 {
 echo "<tr>";
 echo "<td>" . $row[$query] . "</td>";
 echo "</tr>";
 }
?>
 
should be something like

Code: Select all

 
 while($row = DB_result($query))
 {
  echo "<tr>";
  echo "<td>" . $row[fieldName] . "</td>";
  echo "</tr>";
 }