RE: [PHP-DB] Dates and Count
Hi, $query=select * from clients where date between $date1 AND $date2; zareef ahmed --- Shannon Doyle [EMAIL PROTECTED] wrote: Hi Pablo, Its exactly what I wanted. Thanks. I now have an additional question. I need to show results that appear between two dates as specified by the site visitor. At the moment I am using the following SQL: WHERE NextContact = '$today' NextContact = '$week'; Second I need the same scenario, however I want to search from a variety of different fields, show those that are between the dates and are equal to one other criteria. Any help on these two? Cheers, Shannon -Original Message- From: Pablo M. Rivas [mailto:[EMAIL PROTECTED] Sent: Monday, 28 June 2004 9:21 AM To: [EMAIL PROTECTED] Subject: Re: [PHP-DB] Dates and Count Hello Shannon, SD First, I have two dates stored in a MySQL DB that I want to do a compare on SD and then only display via a web page those records that have a 5 or greater SD day difference in them. What would be the best way to achieve this. Depends on your mysql version... select * from mytable where date1 + INTERVAL 5 DAY=date2 or date2 + INTERVAL 5 DAY =date1; select * from mytable where to_days(date1)-todays(date2)5 or to_days(date2)-todays(date1)5 from the mysql manual: For other dates before 1582, results from this function are undefined if your mysql 4.1.1: select * from mytable where DATEDIFF(date1,date2)5 or DATEDIFF(date1,date2)-5 but take a look to optimization.. the first script took 0.0234 secs to return 256 row of 3096 rows the second took 0.0413 secs to return the same couldn't test the third (I have a mysql 4.0.18-standard-log) SD Second, I want to be able to return a list of 'clients' from the MySQL DB SD that have a specific number of a particular type of entry associated with SD them. I am assuming that the 'count' would be used, but how would I first SD display this count, and second only return those that have the number of SD entries that I am looking for. Select count(operation_id) as howmany, client_id, client_name from clients left join operations on operations.client_id = clients.client_id group by clients.client_id having howmany=5 Then... mysql_num_rows will give you how many clients have 5 operations, and each row will tell you: howmany (always = 5), id of the client, and name of the client. ¿is this what you where looking for?... -- Best regards, Pablo -- 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 = Zareef Ahmed :: A PHP Developer in Delhi(India). Homepage :: http://www.zasaifi.com __ Do you Yahoo!? Yahoo! Mail is new and improved - Check it out! http://promotions.yahoo.com/new_mail -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Random select -- Where am I going wrong?
See Below On Sun, 2004-07-04 at 05:54, Ronald The Newbie Allen wrote: Here is my code I keep on getting a Resource id #2 ..whatever that meansany help would be appreciated! html head titleTop Frame/title /head body bgcolor=#ff text=#00 link=#ff vlink=#800080 alink=#ff EMBED SRC= autostart=true hidden=true loop=true volume=100% ? $db_host = '##; $db_user = '##'; $db_pass = '##'; $db_name = '##'; $conn = mysql_connect($db_host,$db_user,$db_pass); if ($conn == true) { // Select the database. mysql_select_db($db_name,$conn); $db_table = 'music'; $music = mysql_query (Select * from $db_table ORDER BY RAND() Limit 1); if(!$music) die(Query Failed.); while($row = mysql_fetch_row($music)) print $music; you need something like print $row however, that still will not work. print_r( $row ) will give you all the values. print( $row-the_table_name would yield each value. NOTE: the syntax above is for a call to $row = mysql_fetch_object( $music ) lookup the correct syntax for mysql_fetch_row( $table ). You might want to check mysql_fetch_assoc and mysql_fetch_array to have even more fun! If you really wish to know more check out mysql_result( ) quite useful when you only seek a couple values from the resultset. hope that helps Alvaro Zuniga (337) 654 6515 www.zunitek.com Zunitek Solutions } ? /body /html -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re[2]: [PHP-DB] Dates and Count
Hello Shannon, mmhh.. i think this is simple as: Select * from mytable where nextcontact='$today' and nextcontact='$week' and (custid=3 or custid=5) and (languaje='de' or languaje='en') I'm not sure if it helps, but you can ask again or provide an example... ;) SD Second I need the same scenario, however I want to search from a variety of SD different fields, show those that are between the dates and are equal to one SD other criteria. SD Any help on these two? SD Cheers, SD Shannon -- Best regards, Pablo -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] DB connections ?
Hello, I am designing a web that will require access to a specific database. I am also using sessions and am storing the session data in the database. So on every page that loads there will be a connection to the mysql server (local) to access the session db and tables and the web app database and tables. My question is this would make two db connections per page right ? one after another ? -- which would not be optimized ?? Would it be better to but the session tables in with the web app tables under a common database ?? At first I thought that it would be more secure if the two where separate. Any comments ... Michael. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] DB connections ?
Michael Gale wrote: I am designing a web that will require access to a specific database. I am also using sessions and am storing the session data in the database. So on every page that loads there will be a connection to the mysql server (local) to access the session db and tables and the web app database and tables. My question is this would make two db connections per page right ? one after another ? -- which would not be optimized Would it be better to but the session tables in with the web app tables under a common database ?? At first I thought that it would be more secure if the two where separate. So long as you don't close the connection after you get your session data, the next call to mysql_connect() with the same parameters will realize there's already a connection open and use that. Why not have a single include file that connects to your database, though? Then include your custom session hanlder which selects its database and retrieves the data. Then your main script selects it's database and does what it needs to do. Still one connection with less confusion. -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ php|architect: The Magazine for PHP Professionals www.phparch.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re[2]: [PHP-DB] MySQL: Random select with specific count of a column
Hello Torsten, TR thanks for your help. Unfortunately it's always returning the same row for TR each category (maybe because of the group by) and only one row for each TR category. I need to select 2 random rows for each category. Any more ideas? I'ts returning 2 rows each RECORD, but the fields have the same name, so if you do a mysql_fetch_array, you'll find only one... Change your sql to: select a.category as categorya, a.name as namea, b.name as nameb from tablename as a left join tablename as b on a.category = b.category and a.nameb.name and a.language=b.language where a.language='de' and b.name is not null group by a.category and you'll do: echo trthlanguajetdcategorytdname\n; while ($data=mysql_fetch_array($result)) { echo trtddetd . $data[categorya] . td . $data[namea]; echo trtddetd . $data[categorya] . td . $data[nameb]; } ok?... EACH ROW CONTAINS 2 RECORDS...but.. we need something more, 'cause you're needing RANDOM rows... If you need RANDOM, i think mysql will not help you, and you must change your query to: select a.category as categorya, a.name as namea, b.name as nameb from tablename as a left join tablename as b on a.category = b.category and a.nameb.name and a.language=b.language where a.language='de' and b.name is not null order by a.category and then do something in php like store everything in an array, and take some randomly... srand(time()); $lastcat=0; echo trthlanguajetdcategorytdname\n; while ($data=mysql_fetch_array($result)) { if (($lastcat) and ($lastcat$data[categorya])) { echo trtddetd . $lastcat . td . $myarray[$lastcat][rand()%count($myarray[$lastcat])]; echo trtddetd . $lastcat . td . $myarray[$lastcat][rand()%count($myarray[$lastcat])]; } $myarray[$data[categorya]][]=$data[namea]; $myarray[$data[categorya]][]=$data[nameb]; $lastcat=$data[categorya]; } echo trtddetd . $lastcat . td . $myarray[$lastcat][rand()%count($myarray[$lastcat])]; echo trtddetd . $lastcat . td . $myarray[$lastcat][rand()%count($myarray[$lastcat])]; mhhh... i don't like... but i think this will work and you can make it better!! -- Best regards, Pablo -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php