Problem wirh mysql_num_rows, maybe?

[jmr3460]

I am trying to pull out a count of rows that have a specific string within a string from a table. The string for example might be something like: Discussion, Book Study, ... etc. This string is actually created from the array $formats. I want to know how many rows have the phrase "Discussion" in it, and how many rows have the phrase "Book Study" in it. I have tried to do this by using "LIKE" in my query, but what I am getting is a set of numbers that are not right. For example I know there are at least 2 "Men" meetings, but the number for it is "0". Can someone tell me why or what I am doing wrong with my code? Or, is there a better way besides using LIKE in my query to do this?

Here is my code:

<?php
ini_set ("display_errors", "1");
error_reporting(E_ALL);
session_start();
$db_name = "arscnaor_meetings";
//connect to server and select database
$connection = mysql_connect("localhost", "username", "password") or die(mysql_error());
$db = mysql_select_db($db_name, $connection) or die(mysql_error());

//formats from add_meetings
$formats = array("Discussion", "Literature Study", "Book Study", "Basic Text", "It Works", "Step Study Guides", "IP Study", "Step Study",
							"Traditions Study", "Concepts Study", "Just For Today", "New Comer", "Participation", "Topic", "Candlelight", "Speaker",
							"Birthday", "Serenity", "Men", "Women", "Varies", "Other");

foreach($formats as $format){

$format_sql = "SELECT format FROM meeting WHERE format LIKE '$format'";
$format_query = mysql_query($format_sql) or trigger_error(mysql_error());
$format_num = mysql_num_rows($format_query);
echo $format . ": " . $format_num . "<br />";
}
?>

Thanks for any help

[ngreenwood6]

you should probably be using the % in your like statement. try this:

 

<?php
ini_set ("display_errors", "1");
error_reporting(E_ALL);
session_start();

$db_name = "arscnaor_meetings";
//connect to server and select database
$connection = mysql_connect("localhost", "username", "password") or die(mysql_error());
$db = mysql_select_db($db_name, $connection) or die(mysql_error());

//formats from add_meetings
$formats = array("Discussion", "Literature Study", "Book Study", "Basic Text", "It Works", "Step Study Guides", "IP Study", "Step Study", "Traditions Study", "Concepts Study", "Just For Today", "New Comer", "Participation", "Topic", "Candlelight", "Speaker", "Birthday", "Serenity", "Men", "Women", "Varies", "Other");

foreach($formats as $format){

$format_sql = "SELECT format FROM meeting WHERE format LIKE '%$format%'";
$format_query = mysql_query($format_sql) or trigger_error(mysql_error());
$format_num = mysql_num_rows($format_query);
echo $format . ": " . $format_num . "<br />";
}
?>

 

However, that is alot of queries you are performing there. I would do something like this:

 

<?php
ini_set ("display_errors", "1");
error_reporting(E_ALL);
session_start();

$db_name = "arscnaor_meetings";
//connect to server and select database
$connection = mysql_connect("localhost", "username", "password") or die(mysql_error());
$db = mysql_select_db($db_name, $connection) or die(mysql_error());

//formats from add_meetings
$formats = array("Discussion", "Literature Study", "Book Study", "Basic Text", "It Works", "Step Study Guides", "IP Study", "Step Study", "Traditions Study", "Concepts Study", "Just For Today", "New Comer", "Participation", "Topic", "Candlelight", "Speaker", "Birthday", "Serenity", "Men", "Women", "Varies", "Other");

//make only one query
$format_sql = "SELECT format FROM meeting";
$format_query = mysql_query($format_sql) or trigger_error(mysql_error());

//initialize an array for formats
$returned_formats = array();

while($format_row = mysql_fetch_array($format_query)){
$returned_formats[$format_row['format']] = ($returned_formats[$format_row['format']]) ? $returned_formats[$format_row['format']] + 1 : $returned_format[$format_row['format']] = 0;
}

echo $returned_formats['Discussion'];
?>

 

This just performs one query and then assigns a count to the format based on the name of the format that you cna use through its own array. Please let me know if you have any questions.

[jmr3460]

Thank you very much. I was just reading more on the LIKE clause and just about to go their. You were a great help. That fixed it.

[ngreenwood6]

which one did you use, just out of curiousity?