Help with PHP/SQL query.

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
sphinx5s5
Forum Newbie
Posts: 2
Joined: Mon Jan 11, 2010 8:44 am

Help with PHP/SQL query.

Post 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>";
 
 
?>
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Help with PHP/SQL query.

Post 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;
 
 
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
sphinx5s5
Forum Newbie
Posts: 2
Joined: Mon Jan 11, 2010 8:44 am

Re: Help with PHP/SQL query.

Post 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>";
?>
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Help with PHP/SQL query.

Post 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>";
 }
 
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
Post Reply