RE: [PHP-DB] Multiple SQL queries...? - SOLVED
Finally got it! Cheers for all your help... it was the group by Emial that was the clincher... I didn't know that it acted like distinct() Live and learn...! Cheers everyone! "Gavin Amm" <[EMAIL PROTECTED]> 04/06/2004 00:16 To <[EMAIL PROTECTED]> cc Subject RE: [PHP-DB] Multiple SQL queries...? Have you tried - GROUP BY email - ? > Nope... > HHmmm, this is really getting to me... > I can do distinct, I can count, but I can't combine the two? > Can't be that hard ;-) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Multiple SQL queries...?
Have you tried - GROUP BY email - ? > Nope... > HHmmm, this is really getting to me... > I can do distinct, I can count, but I can't combine the two? > Can't be that hard ;-) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Multiple SQL queries...?
Not tested... how about something like this? SELECT t1.email, t2.count(*) FROM $table t1 LEFT JOIN $table t2 ON (t1.email = t2.email and t2.viewed = '1') GROUP BY EMAIL > Nope... > HHmmm, this is really getting to me... > I can do distinct, I can count, but I can't combine the two? > Can't be that hard ;-) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Multiple SQL queries...?
Nope... HHmmm, this is really getting to me... I can do distinct, I can count, but I can't combine the two? Can't be that hard ;-) "Daniel Clark" <[EMAIL PROTECTED]> 03/06/2004 16:49 Please respond to [EMAIL PROTECTED] To [EMAIL PROTECTED] cc "Daniel Clark" <[EMAIL PROTECTED]>, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> Subject Re: [PHP-DB] Multiple SQL queries...? OK, how about this? $sql = "SELECT DISTINCT(email) FROM $table"; $result = mysql_query($sql,$connection) or die("Couldn't execute query 0."); while ($row = mysql_fetch_array($result)) { echo $row[email] ; $sql1 = "SELECT * FROM $table WHERE viewed = '1' AND email = '$row[email]'"; $result1 = mysql_query($sql1,$connection) or die("Couldn't execute query 1."); echo ' ' . mysql_num_rows($result1) ; } echo $row1 ; > Kinda, but I just wanna count how many views in total... > Hence my $row1++; bit... > I'll have a play with your code though...! > Cheers, > Tris... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Multiple SQL queries...?
OK, how about this? $sql = "SELECT DISTINCT(email) FROM $table"; $result = mysql_query($sql,$connection) or die("Couldn't execute query 0."); while ($row = mysql_fetch_array($result)) { echo $row[email] ; $sql1 = "SELECT * FROM $table WHERE viewed = '1' AND email = '$row[email]'"; $result1 = mysql_query($sql1,$connection) or die("Couldn't execute query 1."); echo ' ' . mysql_num_rows($result1) ; } echo $row1 ; > Kinda, but I just wanna count how many views in total... > Hence my $row1++; bit... > I'll have a play with your code though...! > Cheers, > Tris... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Multiple SQL queries...?
$sql1 = "SELECT COUNT(*) as views FROM $table WHERE viewed = '1' AND email = '$row[email]'"; $views = mysql_resutl(mysql_query($sql1,$connection),"views"); maybe? .bobo www.elstudion.com/bitbob/ <- not finished though... - Original Message - From: <[EMAIL PROTECTED]> To: "Daniel Clark" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Thursday, June 03, 2004 4:43 PM Subject: Re: [PHP-DB] Multiple SQL queries...? > Kinda, but I just wanna count how many views in total... > Hence my $row1++; bit... > I'll have a play with your code though...! > Cheers, > Tris... > > > > > "Daniel Clark" <[EMAIL PROTECTED]> > 03/06/2004 14:28 > Please respond to > "Daniel Clark" <[EMAIL PROTECTED]> > > > To > "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>, > "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> > cc > > Subject > Re: [PHP-DB] Multiple SQL queries...? > > > > > > > Is this what you want ?? > > $sql = "SELECT DISTINCT(email) FROM $table"; > $result = mysql_query($sql,$connection) or die("Couldn't execute query > 0."); > > $row1 = 0; > > > while ($row = mysql_fetch_array($result)) { > > echo $row[email] ; > > $sql1 = "SELECT * FROM $table WHERE viewed = '1' AND > email = '$row[email]'"; > $result1 = mysql_query($sql1,$connection) or > die("Couldn't execute query 1."); > > if (mysql_num_rows($result1) >= 1) { > echo 'Viewed' ; > } else { > echo '' ; > } > } > > echo $row1 ; > > >>Right, todays fun dilema... ;-) > >>I've a user capture system set up for downloads on our site. > >>Each time a user downloads a file, their info is captured (so we'll have > > >>multiple entries for each email address). > >>Also in the table, is a field to state if the result has been viewed by > my > >>boss. (Just a 1/0 value) > >> > >>So, what my boss wants me to do now, is to show her, the total No of > >>people captured, and how many she's viewed. > >>Using the 'email' field, from the table, I've done a distinct() sort in > >>MySQL to ensure that I have a list of emails(users) and no duplicates. > >>My prob is this: > >>I want to take each email, and see if at any point, there's a '1' in the > > >>viewed field. > >>if there is, I want to add one to the total of $row1 > >>So I can output the num_rows from the distinct request, and then show > the > >>total of $row1... > >>So I'll have total No of users, and total No of viewed... as I've > >>mentioned above. > >>What am I doing wrong? > >> > >> >>$sql = "SELECT DISTINCT(email) FROM $table"; > >>$result = mysql_query($sql,$connection) > >>or die("Couldn't execute query 0."); > >> > >>$row1 = 0; > >> > >>while ($row = mysql_fetch_array($result)) { > >> > >>$sql1 = "SELECT * FROM $table WHERE viewed = '1' AND email = > >>'$row[email]'"; > >>$result1 = mysql_query($sql1,$connection) > >>or die("Couldn't execute query 1."); > >>if (mysql_num_rows($result1) >= 1) { > >>$row1++; > >>} > >>} > >>?> > >> > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Multiple SQL queries...?
Kinda, but I just wanna count how many views in total... Hence my $row1++; bit... I'll have a play with your code though...! Cheers, Tris... "Daniel Clark" <[EMAIL PROTECTED]> 03/06/2004 14:28 Please respond to "Daniel Clark" <[EMAIL PROTECTED]> To "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> cc Subject Re: [PHP-DB] Multiple SQL queries...? Is this what you want ?? $sql = "SELECT DISTINCT(email) FROM $table"; $result = mysql_query($sql,$connection) or die("Couldn't execute query 0."); $row1 = 0; while ($row = mysql_fetch_array($result)) { echo $row[email] ; $sql1 = "SELECT * FROM $table WHERE viewed = '1' AND email = '$row[email]'"; $result1 = mysql_query($sql1,$connection) or die("Couldn't execute query 1."); if (mysql_num_rows($result1) >= 1) { echo 'Viewed' ; } else { echo '' ; } } echo $row1 ; >>Right, todays fun dilema... ;-) >>I've a user capture system set up for downloads on our site. >>Each time a user downloads a file, their info is captured (so we'll have >>multiple entries for each email address). >>Also in the table, is a field to state if the result has been viewed by my >>boss. (Just a 1/0 value) >> >>So, what my boss wants me to do now, is to show her, the total No of >>people captured, and how many she's viewed. >>Using the 'email' field, from the table, I've done a distinct() sort in >>MySQL to ensure that I have a list of emails(users) and no duplicates. >>My prob is this: >>I want to take each email, and see if at any point, there's a '1' in the >>viewed field. >>if there is, I want to add one to the total of $row1 >>So I can output the num_rows from the distinct request, and then show the >>total of $row1... >>So I'll have total No of users, and total No of viewed... as I've >>mentioned above. >>What am I doing wrong? >> >>>$sql = "SELECT DISTINCT(email) FROM $table"; >>$result = mysql_query($sql,$connection) >>or die("Couldn't execute query 0."); >> >>$row1 = 0; >> >>while ($row = mysql_fetch_array($result)) { >> >>$sql1 = "SELECT * FROM $table WHERE viewed = '1' AND email = >>'$row[email]'"; >>$result1 = mysql_query($sql1,$connection) >>or die("Couldn't execute query 1."); >>if (mysql_num_rows($result1) >= 1) { >>$row1++; >>} >>} >>?> >> -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Multiple SQL queries...?
Is this what you want ?? $sql = "SELECT DISTINCT(email) FROM $table"; $result = mysql_query($sql,$connection) or die("Couldn't execute query 0."); $row1 = 0; while ($row = mysql_fetch_array($result)) { echo $row[email] ; $sql1 = "SELECT * FROM $table WHERE viewed = '1' AND email = '$row[email]'"; $result1 = mysql_query($sql1,$connection) or die("Couldn't execute query 1."); if (mysql_num_rows($result1) >= 1) { echo 'Viewed' ; } else { echo '' ; } } echo $row1 ; >>Right, todays fun dilema... ;-) >>I've a user capture system set up for downloads on our site. >>Each time a user downloads a file, their info is captured (so we'll have >>multiple entries for each email address). >>Also in the table, is a field to state if the result has been viewed by my >>boss. (Just a 1/0 value) >> >>So, what my boss wants me to do now, is to show her, the total No of >>people captured, and how many she's viewed. >>Using the 'email' field, from the table, I've done a distinct() sort in >>MySQL to ensure that I have a list of emails(users) and no duplicates. >>My prob is this: >>I want to take each email, and see if at any point, there's a '1' in the >>viewed field. >>if there is, I want to add one to the total of $row1 >>So I can output the num_rows from the distinct request, and then show the >>total of $row1... >>So I'll have total No of users, and total No of viewed... as I've >>mentioned above. >>What am I doing wrong? >> >>>$sql = "SELECT DISTINCT(email) FROM $table"; >>$result = mysql_query($sql,$connection) >>or die("Couldn't execute query 0."); >> >>$row1 = 0; >> >>while ($row = mysql_fetch_array($result)) { >> >>$sql1 = "SELECT * FROM $table WHERE viewed = '1' AND email = >>'$row[email]'"; >>$result1 = mysql_query($sql1,$connection) >>or die("Couldn't execute query 1."); >>if (mysql_num_rows($result1) >= 1) { >>$row1++; >>} >>} >>?> >> -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Multiple SQL queries...?
Right, todays fun dilema... ;-) I've a user capture system set up for downloads on our site. Each time a user downloads a file, their info is captured (so we'll have multiple entries for each email address). Also in the table, is a field to state if the result has been viewed by my boss. (Just a 1/0 value) So, what my boss wants me to do now, is to show her, the total No of people captured, and how many she's viewed. Using the 'email' field, from the table, I've done a distinct() sort in MySQL to ensure that I have a list of emails(users) and no duplicates. My prob is this: I want to take each email, and see if at any point, there's a '1' in the viewed field. if there is, I want to add one to the total of $row1 So I can output the num_rows from the distinct request, and then show the total of $row1... So I'll have total No of users, and total No of viewed... as I've mentioned above. What am I doing wrong? = 1) { $row1++; } } ?> -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Multiple SQL queries
I am new this. I have a page that has multiple queries on it, I want to do doing the following: Query a mysql db multiple times in the same page, but i must be missing something, I keep getting these AFTER the first queryis successful: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /path/php_file.php on line line_number Can someone point in the right direction on how to do multiple queries, some are not querying the same db btw. -Art