Counting rows in two files and posting warnings on PHP webpg
Moderator: General Moderators
- 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
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
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
- 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
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.PHP_mySQL__Newbie wrote:2) How can I capture mySQL warnings and post on my PHP webpage?
“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
Re: Counting rows in two files and posting warnings on PHP w
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.PHP_mySQL__Newbie wrote: My desired output is:
col B, count
F, 2
M, 2
- 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
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.
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
Print out $result_1 and $result_2 for me and I'll help you out.
- 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
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.
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)
");
)
- 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
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
- 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
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%
- 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
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
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.
- 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
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%