Counting rows in two files and posting warnings on PHP webpg

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
User avatar
PHP_mySQL__Newbie
Forum Commoner
Posts: 29
Joined: Fri Aug 12, 2011 5:40 pm

Counting rows in two files and posting warnings on PHP webpg

Post by PHP_mySQL__Newbie »

I receive two Excel files every week that I upload in my database.

File1: (110 columns, 1500 rows).
A B C D
st1 F 20 30
st2 M 21 31
st3 F 22 32

File 2: (115 columns, 1600 rows)
A E F B C D
st1 100 200 F 20 30
st2 101 201 M 21 31
st4 104 204 M 24 34

1) I am writing simple queries like counting rows in column B. Do I have to combine both tables to find the count? If so, how would I be able to to that?

sql1 = "SELECT File1.B FROM File, GROUP BY File1.B";

sql2 = "SELECT File2.B FROM File, GROUP BY File2.B";

2) How can I capture mySQL warnings and post on my PHP webpage?

My desired output is:
col B, count
F, 2
M, 2
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Counting rows in two files and posting warnings on PHP w

Post by social_experiment »

PHP_mySQL__Newbie wrote:2) How can I capture mySQL warnings and post on my PHP webpage?
mysql_error() contains the error from the last operation (if any) and you could use mysql_errno() which returns an error number related to the error.
“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
Dorin85
Forum Newbie
Posts: 20
Joined: Tue Aug 16, 2011 3:16 pm

Re: Counting rows in two files and posting warnings on PHP w

Post by Dorin85 »

PHP_mySQL__Newbie wrote: My desired output is:
col B, count
F, 2
M, 2
You don't have to combine both tables to get the count. You could just parse the returned count from each query with explode(", ", $result) and roll up the values with a foreach loop.
User avatar
PHP_mySQL__Newbie
Forum Commoner
Posts: 29
Joined: Fri Aug 12, 2011 5:40 pm

Re: Counting rows in two files and posting warnings on PHP w

Post by PHP_mySQL__Newbie »

Ok I am half way there. Now I am getting the count from each table. If I add both counts, it will give a count by counting duplicates in both tables (st1 and st2 are being counted in both tables). How do I avoid counting duplicates?

I haven't tried this code yet.

Code: Select all

      $sql_1 = "
            SELECT file1.colB, count(*) 
            FROM file1
            GROUP BY file1.colB  ";
        $result_1 = mysql_query($sql_1, $conn) or die(mysql_error());

      $sql_2 = "
            SELECT file2.colB, count(*) 
            FROM file2
            GROUP BY file2.colB  ";
        $result_2 = mysql_query($sql_2, $conn) or die(mysql_error());

        while($row = mysql_fetch_array( $result_1 )) 
                    echo $row['colB']." ".$row['count(*)']; 
        echo "\n";

        while($row = mysql_fetch_array( $result_2 ))
                    echo $row['colB']." ".$row['count(*)']; 
Dorin85
Forum Newbie
Posts: 20
Joined: Tue Aug 16, 2011 3:16 pm

Re: Counting rows in two files and posting warnings on PHP w

Post by Dorin85 »

Print out $result_1 and $result_2 for me and I'll help you out.
User avatar
PHP_mySQL__Newbie
Forum Commoner
Posts: 29
Joined: Fri Aug 12, 2011 5:40 pm

Re: Counting rows in two files and posting warnings on PHP w

Post by PHP_mySQL__Newbie »

Thank you for replying to my post. I was using Excel and Access but then recently started using PHP and mySQL. It's been a rocky road so far.
I am going to post my tables to give you an idea what I am working on. Then later in my next post, I will post the prints that you asked for.

Table1
Id, LN, FN, sex, town, t1, t2, t3
1, x1, y1, F, A, 50, 55, 60
2, x2, y2, M, B, NULL, 70, 75
3, x3, y3, F, A, 80, NULL, 78

Table2
Id, LN, FN, sex, town, q1, q2, t1, t2, t3
10, x5, y5, M, C, 4, 6, 71, 72, 73
11, x6, y6, F, A, NULL, 10, 90, 91, 92
1, x1, y1, F, A, 8, 9, 50, 55, 60


Table3
Id, test, score
1, test1, 80
1, test2, 58
1, test3, 67
10, test1, 80
10, test2, 85
2, test3, 88
2, test4, 89

Now this is what I am looking for:

Desired Output#1 posted on a website and outfile
Classification, count, percent
F, 3, 60%
M, 2, 40%
A, 3, 60%
B, 1, 20%
C, 1, 20%

Desired Output#2 posted on a website and outfile
Id, sex, test1, test2, test3, test4, t3
1, F, 80, 58, 67, 60
2, M, NULL, NULL, 88, 89, 75
10, M, 80, 85, NULL, NULL, 73

Code that I wrote that didn't work.

Code: Select all

$result_1 = mysql_query("                    
                     (SELECT sex, count(*) FROM table1 GROUP BY sex)
                      UNION
                     (SELECT sex, count(*) FROM table2 GROUP BY sex)
	");

)
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Counting rows in two files and posting warnings on PHP w

Post by social_experiment »

Wouldn't a group by only return 2 values? One M and one F because GROUP BY groups unique fields (or returns unique fields) which means you will have 1 unique m and 1 unique f value.
“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
User avatar
PHP_mySQL__Newbie
Forum Commoner
Posts: 29
Joined: Fri Aug 12, 2011 5:40 pm

Re: Counting rows in two files and posting warnings on PHP w

Post by PHP_mySQL__Newbie »

Yes. Agreed. For Desired output#1, F and M must appear only once but when I use Union, F and M appear once for each table.

Code: Select all

Desired Output#1 posted on a website and outfile
Classification, count, percent
F, 3, 60%
M, 2, 40% 
A, 3, 60%
B, 1, 20%
C, 1, 20%

User avatar
PHP_mySQL__Newbie
Forum Commoner
Posts: 29
Joined: Fri Aug 12, 2011 5:40 pm

Re: Counting rows in two files and posting warnings on PHP w

Post by PHP_mySQL__Newbie »

I am going to post my question on a separate thread. I realized that I have different tables posted here and I am not making any sense.
poster123
Forum Newbie
Posts: 3
Joined: Fri Aug 26, 2011 3:02 am

Re: Counting rows in two files and posting warnings on PHP w

Post by poster123 »

Wouldn't a group by only return 2 values? One M and one F because GROUP BY groups unique fields (or returns unique fields) which means you will have 1 unique m and 1 unique f value.
User avatar
PHP_mySQL__Newbie
Forum Commoner
Posts: 29
Joined: Fri Aug 12, 2011 5:40 pm

Re: Counting rows in two files and posting warnings on PHP w

Post by PHP_mySQL__Newbie »

Yes there are unique F and M values in the desired output. The problem is that I don't know how to count A, B and C and add them as rows in the same table.

Code: Select all

Desired Output#1 posted on a website and outfile
 Classification, count, percent
 F, 3, 60%
 M, 2, 40% 
A, 3, 60%
 B, 1, 20%
 C, 1, 20%
 
Post Reply