[RESOLVED] Printing Number of rows that adhere to a rule

[RussKern]

I'm new to this forum, and while I normally dig at things until I figure them out, I'm working on something that has me stumped.

I have a a group of records in a MySQL database, one of the elements of the records is a phone number.

Getting the data out is easy enough, but now I want to takke a portion of that data and narrow it down.

all phone #s are recorded as 000-000-0000 consistently.

I want to run a MySql query for all the phone numbers, explode the Phone number so I can isolate the area code

Compare the area code of each line to an array of area codes availiable in a given State (Say Ohio for example)

and then print the number of rows to the screen of users in the state of Ohio and so forth.

My basic logic I worked out is:
SELECT phone FROM users
while userrow has a result
explode phone # at "-"
Compare the Area Code portion to an array of Ohio Area codes
and print the number of rows with a match.

What I want to be able to Print to the screen is
"entries from Ohio: 22" (or whatever number it is)

If necessary, I can post the code I've worked out so far.

Hopefully I explained it well enough.
Anyone have any ideas?

Russ

[trandom]

I get the feeling that you are just having a problem exploding the area code from the rest of the number...

Here's a bit of code that might help out... :

list($section1, $section2, $section3) = split('[-]', $full_phone_number);

I'm from the UK and don't know much about US phone numbers but I imagine it's the first 3 numbers that are the area code.

So now that we have it split we can compare $section1 against our list of area's and print each one that matches.

If you need help with the array bit just post back. Would also be handy to see some of your code 🙂

[RussKern]

Exploding the ac out isn't my problem I don't think, although I was using an explode() function as:

$hphone_array=explode("-", $result);
$hphone_ac=$hphone[0]; 

And you are correct, Area Code are the first 3

Below is my code for the whole bit. - including your explosion of the AC:

<?php 
$result = mysql_query("SELECT hphone FROM user");
while ($hphone = mysql_fetch_array($result)) {

//  break up hphone to isolate Area Code
list($section1, $section2, $section3) = split('[-]', $hphone); 

// set the area code array for a given State
$ohio_ac= array("216", "234", "283", "330", "419", "440", "513", "567", "614", "740", "937");

// compare Area Code to array of possible area codes
if (in_array($section1,$ohio_ac) {
$num_ohio_rows=mysql_num_rows($section1);
}
}
echo $num_ohio_rows;
?>

Thanks for he reply.

[johanafm]

You might want to split the full phone number into area_code and phone_number at insert and keep them in separate columns in the database table and also index area_code. Then loop over your area code array and for each area code of a given state:

SELECT count(area_code)
FROM users
WHERE area_code = $area_code

and just sum it up. This way you only have to parse the full phone numbers one single time instead of each time you retrieve them. The index should also ensure that the query runs fast as opposed to working with the full users table data for every single state.

[bpat1434]

Or just use an SQL query to limit what you get.....

SELECT LEFT(`hphone`, 3) AS phone
FROM user
WHERE phone IN (216,234,283,330,419,440,513,567,614,740,937)

That query should get you all phone numbers with area-codes with Ohio starts. Then just call [man]mysql_num_rows[/man] on the result resource (not the array like you have) and you'll have your answer.

Also, in this code:

<?php
$result = mysql_query("SELECT hphone FROM user");
while ($hphone = mysql_fetch_array($result)) {

//  break up hphone to isolate Area Code
list($section1, $section2, $section3) = split('[-]', $hphone);

// set the area code array for a given State
$ohio_ac= array("216", "234", "283", "330", "419", "440", "513", "567", "614", "740", "937");

// compare Area Code to array of possible area codes
if (in_array($section1,$ohio_ac) {
$num_ohio_rows=mysql_num_rows($section1);
}
}
echo $num_ohio_rows;
?> 

You should be getting an error stating that there's no result resource. This is because mysql_num_rows wants the $result variable (or the result resource of the query), not an array or string or whatever. So the line where you use it should probably be:

$num_ohio_rows = $num_ohio_rows+1;

But!!! remember to initialize $num_ohio_rows outside the loop(s).

[RussKern]

johanafm wrote:

You might want to split the full phone number into area_code and phone_number at insert and keep them in separate columns in the database table and also index area_code. Then loop over your area code array and for each area code of a given state:

SELECT count(area_code)
FROM users
WHERE area_code = $area_code

and just sum it up. This way you only have to parse the full phone numbers one single time instead of each time you retrieve them. The index should also ensure that the query runs fast as opposed to working with the full users table data for every single state.

Good suggestion... This little exercise was an afterthought. I currently have fields for all 3 parts of the Phone # to verify they have given me the whole thing, Then I just combine them and post them to the database.

Next time I'll definitely do that. Thanks.

[RussKern]

bpat1434 wrote:

Or just use an SQL query to limit what you get.....

SELECT LEFT(`hphone`, 3) AS phone
FROM user
WHERE phone IN (216,234,283,330,419,440,513,567,614,740,937)

That query should get you all phone numbers with area-codes with Ohio starts. Then just call [man]mysql_num_rows[/man] on the result resource (not the array like you have) and you'll have your answer.....

This looks lot's simpler than what I currently have... thank you... however, I put it into my code and it doesn't return anything (Page prints fine.... just no row count like I expect). And it should because I know the 330 area code is in there...

Here is my NEW code... Something doesn't look right, but I haven't put my finger on it yet...

<?php
$result = mysql_query("SELECT LEFT('hphone', 3) AS phone
FROM user
WHERE phone IN (216,234,283,330,419,440,513,567,614,740,937)");

$num_rows = mysql_num_rows($result);

echo $num_rows;
?>

Thanks for the input ... I apppreciate it.

Russ

[bpat1434]

column names in back ticks "`" not single quotes 🙂

[RussKern]

OK... fixed that... oops...

Still.. no result.

EDIT

To help figure this out, I changed the script & put some error checking in - this is the result

"Invalid query: Unknown column 'phone' in 'where clause' Whole query: SELECT LEFT(hphone, 3) AS phone FROM user WHERE phone IN (216,234,283,330,419,440,513,567,614,740,937)"

[bpat1434]

Well, for one thing "user" is a reserved word in MySQL, so that must be encased in back ticks. And there was a minor problem with my query. Here's a proper one:

SELECT id
FROM `user`
WHERE LEFT(`hphone`, 3) IN (216,234,283,330,419,440,513,567,614,740,937);

Then run that query and then do mysql_num_rows() on the result and it should work!!

$area_codes = array('216', '234', '283', '330', '419', '440', '513', '567', '614', '740', '937');

$query = "SELECT id
FROM `user`
WHERE LEFT(`hphone`, 3) IN (" . implode(',', $area_codes) . ")";
$result = mysql_query($query);
$num_of_rows = mysql_num_rows($result);

Alternatively you can just use MySQL's COUNT function in the query, and then the only result is the number of phone numbers:

SELECT COUNT(*) AS myCount
FROM `user`
WHERE LEFT(`hphone`, 3) IN (216,234,283,330,419,440,513,567,614,740,937)

Then, run the query, and either use [man]mysql_fetch_array[/man] or [man]mysql_result[/man] to get the only result, which will be the number Ohio phone numbers.

$area_codes = array('216', '234', '283', '330', '419', '440', '513', '567', '614', '740', '937');

$query = "SELECT COUNT(*) AS myCount
FROM `user`
WHERE LEFT(`hphone`, 3) IN (" . implode(',', $area_codes) . ")";
$result = mysql_query($query);
$num_users = mysql_result($result, 0, 'myCount');

Hope that helps!!

[RussKern]

I used the first one and it seems to have worked perfectly! (provided that 103 entrants out of 2862 are really from Ohio. 🙂 )
Thank you!