Noob Warning - Total Number of Comma Sep'd Values from All Entries of a Field

[Blake_Niteo]

Hello Everyone,

I'm a complete PHP noob so please bear with me.

 

I have a problem with counting values of rows that are comma separated. The problem is, I have no idea what to do.

 

I am updating the interface design of a LAMP site and a quick addition that was requested was to show the total number of words and forms of each word as a number (it's a vocabulary site) in the sidebar.

 

To explain how the DB is set up, the table "target_word" contains several fields, one being "wordid" which is the id of the word, the next field is "word" which is the actual word, and "forms" which lists the alternate forms of the words comma separated. 

 

INSERT INTO `target_word` VALUES (1 (wordid), 'Act' (word), 'acted, acting, acts, action, actions, actress, actor' (forms));

 

The previous developer was able to get the total number of words by using ...

 

<? $sql_words = "SELECT count(wordid) as wrd_cnt from target_word";

$rs_words = mysql_query($sql_words,$db);

$row_words = mysql_fetch_object($rs_words); ?>

 

<?=$row_words->word_cnt?>

 

This, however, will not work to get the number of items contained in the forms field entries as they're comma separated values and appear as one entry.

 

QUESTION: How do I count each "forms" field entries' comma separated values and then add all of those together to get a total number of comma seperated values contained in the "forms" field.

 

Any help is greatly appreciated! Thanks!

 

[joel24]

pull the field from the DB and run this function on it

//get words from database
$words = $row['forms'];

//count occurences of comma, then add one since the first word doesn't have a corresponding comma.
$count = substr_count($words, ',') + 1;

[Blake_Niteo]

Joel, thanks for such a quick response!

 

You're solution is brilliant! Only, I can't seem to get the "pull the field from the DB" to work as I am probably using the entirely wrong code... Here is the code I have:

 

<?
          $sql_forms = "SELECT forms FROM target_word";
  $rs_forms = mysql_query($sql_forms,$db);
  $row = mysql_fetch_object($rs_forms);
  $forms = $row['forms'];
  $count = substr_count($forms, ',') + 1;
?>            
Total Word Forms = <span class="digit"><?=$count?></span>

 

It is only displaying "1" from the +1.  Thanks again for your help!

[joel24]

mysql_fetch_object creates the mysql results as an object, where as mysql_fetch_array puts the results into an array...

 

$row = mysql_fetch_object($rs_forms);

$row->forms; will retrieve forms

 

or, a much more practical solution in this case

$row = mysql_fetch_array($rs_forms);

$row['forms'];

 

... in the code

<?
          $sql_forms = "SELECT forms FROM target_word";
  $rs_forms = mysql_query($sql_forms,$db);
  $row = mysql_fetch_array($rs_forms);

  $forms = $row['forms'];
  $count = substr_count($forms, ',') + 1;
?>            
Total Word Forms = <span class="digit"><?=$count?></span>

[Blake_Niteo]

I'm still having issues getting it to add up.

 

I think the problem is that when it calls the forms field, it only uses the first entry and because there are no commas in it (the entry is just "none") it only displays a 1 as it should. i double checked this by changing <?=$count?> to <?=$forms?> and it displayed the "none" from the first field. For some reason the whole array isn't being added in the substr_count() call.

 

<?
  $sql_forms = "SELECT forms FROM target_word";
$rs_forms = mysql_query($sql_forms, $db);
$row = mysql_fetch_array($rs_forms);
$forms = $row['forms'];
$count = substr_count($forms, ',') + 1;  
?>            
Total Word Forms = <span class="digit"><?=$count?></span><br />

 

I tried adding a while() loopity thingie around the last call, but it seemed to stall out the whole thing out as it did again when i tried to loop the 2nd to last call. Only when i while'd the middle call did it return something different, a 5, which I don't entirely understand.

 

Anyways, that's the latest. Any ideas?

 

 

 

[Pikachu2000]

Since you're new, one pointer I'd offer is stop using the <? short open tags and <?=$variable?> quick echo syntax right now. It WILL come back to haunt you eventually, and you'll pull your hair out trying to figure out why your code stops working. Use the full <?php open tag, and the full <?PHP echo $var: ?> syntax instead. With that said, on to your problem . . .

 

In the OP, the INSERT query syntax should be

"INSERT INTO `target_word` (wordid, word, forms) VALUES (1, 'Act', 'acted, acting, acts, action, actions, actress, actor')";

 

Then the segment to select and display the data, assuming you want to display all the information at the same time, this uses a while() loop. I threw this into a <table>, but you can use whatever display structure you want.

<?php
$sql_forms = "SELECT forms FROM target_word";
$rs_forms = mysql_query($sql_forms, $db);
echo '<table>\n';
while( $row = mysql_fetch_assoc($rs_forms) ) { // mysql_fetch_assoc() is more efficient than mysql_fetch_array()
$forms = $row['forms'];
$count = substr_count($forms, ',') + 1;
?>
<tr>
<td>Total Word Forms = <span class="digit"><?php echo $count; ?></span><br /></td>
<td>Forms: <?php echo $row['forms']; ?></td>
</tr>
<?php
}
echo '</table>\n';
?>

[Pikachu2000]

Oops, I messed up the BBCODE tags and ran out of time to edit the last post . . . This should look better.

 

<?php
$sql_forms = "SELECT forms FROM target_word";
$rs_forms = mysql_query($sql_forms, $db);
echo '<table>\n';
while( $row = mysql_fetch_assoc($rs_forms) ) { // mysql_fetch_assoc() is more efficient than mysql_fetch_array()
   $forms = $row['forms'];
   $count = substr_count($forms, ',') + 1;
   ?>
   <tr>
   <td>Total Word Forms = <span class="digit"><?php echo $count; ?></span><br /></td>
   <td>Forms: <?php echo $row['forms']; ?></td>
   </tr>
   <?php
}
echo '</table>\n';
?>

[Blake_Niteo]

Pickachu, Thank you so much for your advice and assistance! It's finally working!

 

Is there a way to take all of the $count;'s from each line and add them all together to get a total number for the whole field? I'd like to display a total at the bottom as well if at all possible. Thanks in advance!

 

Also, that video link in your sig is hilarious!

 

 

 

[Pikachu2000]

I haven't tested this, but I think it should work. Just add the lines indicated by the comments I inserted and give it a try.

 

<?php
$sql_forms = "SELECT forms FROM target_word";
$rs_forms = mysql_query($sql_forms, $db);
$total = 0; // ADD ME
echo '<table>\n';
while( $row = mysql_fetch_assoc($rs_forms) ) { // mysql_fetch_assoc() is more efficient than mysql_fetch_array()
   $forms = $row['forms'];
   $count = substr_count($forms, ',') + 1;
   $total += $count; // ADD ME
   ?>
   <tr>
   <td>Total Word Forms = <span class="digit"><?php echo $count; ?></span><br /></td>
   <td>Forms: <?php echo $row['forms']; ?></td>
   </tr>
   <?php
}
echo '<tr><td colspan="2">Total: ' . $total . '</td></tr>'; // ADD ME
echo '</table>\n';
?>

[Blake_Niteo]

Works like a charm! Thanks so much! YOU ROCK!