Page 1 of 1

Counting rows in two files and posting warnings on PHP webpg

Posted: Sun Aug 21, 2011 3:20 am
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

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

Posted: Sun Aug 21, 2011 9:18 am
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.

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

Posted: Sun Aug 21, 2011 9:32 am
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.

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

Posted: Sun Aug 21, 2011 12:25 pm
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(*)']; 

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

Posted: Mon Aug 22, 2011 10:42 am
by Dorin85
Print out $result_1 and $result_2 for me and I'll help you out.

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

Posted: Mon Aug 22, 2011 10:51 pm
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)
	");

)

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

Posted: Tue Aug 23, 2011 2:33 am
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.

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

Posted: Tue Aug 23, 2011 5:43 am
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%


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

Posted: Tue Aug 23, 2011 6:32 am
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.

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

Posted: Fri Aug 26, 2011 4:01 am
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.

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

Posted: Fri Aug 26, 2011 12:08 pm
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%