[PHP-DB] Re: Case sensitive search
Hi, i suggest you to use the LIKE operator instead of the relational operator. Rui Cunha Rosen writes: Hi, I have a simple table: test ( id int unsigned NOT NULL auto_increment, data varchar(30) default NULL, PRIMARY KEY (id)) with two simple records: id data 1 "a" 2 "A" When I perform "select * from test where data='a' " - it return me both rows. By default in MySQL comparing of strings is case-insensitive. How can I perform a case sensitive search in text fields ? Tnanks in advance, Rosen * * Rui Pedro Cunha * * Dpto. de Ciências e Tecnologias * * Universidade Autónoma de Lisboa * * Rua de Santa Marta, 56, * * 1169-023 Lisboa * * Telefone (+351) 21 317 76 35/49 * * Fax (+351) 21 353 37 02 * * Url : http://www.ual.pt/dct/ * * E-mail: [EMAIL PROTECTED] * * -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Database design for calendar
Hi Arnout, i'd choose your first suggestion. Using the calendar table (you can use the date field as Primary Key) and the other tables (birthdays,testpapers,) where you could use the date field as Foreign Key. With simple 1-N relationships you can solve the prob. hope it helps. Rui Cunha Arnout Boks writes: Hi, I'm currently working on a php-based community website for my school class. Part of that website is a calendar function that includes the dates of all testpapers, birthdays, school parties and so on... Of course, a testpaper has got other properties than a birthday, so they should go into different tables with different fields. They have to be mixed when showing the calendar overview, but the detail pages should be different for each type of calendar entry. After some thinking, I came up with two different solutions: I:Make one table 'calendar', which contains all the common fields(like date, time etc.). Make tables 'birthdays', 'testpapers'... and link each record in them to a record in the calendar with a 'calendar_id'. II:Create only tables 'birthdays', 'testpapers' and combine them with a UNION statement. Could someone give his/her opinion about what he/she thinks to be the best solution. (I use MySQL, if you need to know.) Thanks in advance, Arnout -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: MySQL query, using DISTINCT...
i don't know if someone already gave you a suggestion such as: select distinct mail from ( select mail , filename, count(*) from table having count(*) > 1 group by mail,filename ); i presume that you're looking for disctinct mails of users that downloaded the same file more than once. hope it helps Rui Cunha [EMAIL PROTECTED] writes: I have a system that tracks downloads, capturing loadsa info, but of interest here, is email and filename. Simply put, I wanna show all results where file name AND email are unique. (so if email '[EMAIL PROTECTED]' has filename 'word.doc' 5 times in a table, I want to only see it once.) What am I doing wrong...? SELECT DISTINCT(file_name, email) FROM `completed_downloads` WHERE `bu` = 'reech' AND date BETWEEN '2004-06-01' AND '2004-06-30' Tris... * The information contained in this e-mail message is intended only for the personal and confidential use of the recipient(s) named above. If the reader of this message is not the intended recipient or an agent responsible for delivering it to the intended recipient, you are hereby notified that you have received this document in error and that any review, dissemination, distribution, or copying of this message is strictly prohibited. If you have received this communication in error, please notify us immediately by e-mail, and delete the original message. *** -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: MySQL: Random select with specific count of a column
Hi Torsten, until now,the best i could get was getting 6 random rows...still working on ensuring to retrive just 2 rows for category...meanwhile,you can solve the problem easily with a php loop trough the following query: select field_id , language , category from yourtable where language = 'de' group by category , field_id order by category , rand() limit 6; PS1: i'm considering you're using just one table and my table structure is as follows: yourtable (field_id , language, category). in case you're using more than 1 table, just add the required joins... hope this helps. Rui Cunha Torsten Roehr writes: "Pablo M. Rivas" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] Hello Torsten, This is one, but I think you can find another one: Supose your table is called tablename select * from tablename as a left join tablename as b on a.category = b.category and a.name<>b.name and a.language=b.language where a.language='de' and b.name is not null group by a.category Hi Pablo, thanks for your help. Unfortunately it's always returning the same row for each category (maybe because of the group by) and only one row for each category. I need to select 2 random rows for each category. Any more ideas? Thanks, Torsten -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: return array
the field variable has been defined outside the function as a global var? if not, don't forget to do it. Inside the function, the var behaves as local, unless you declare it as global.here's an example: $x = 2; function test_x () { echo $x; //doesn't work because x is not global; //but if you do something like: global $x; echo $x; // will print 2 } ?> Rui Cunha Sukanto Kho writes: Hi All, how to return array var from function ?? I have try that but just can't return it.. here's my code :(function to return any fields name from table) function fields($table,$db_name) { $link=$this->database(); // this open the database connection $fields = mysql_list_fields($db_name, $table, $link); $columns = mysql_num_fields($fields); for ($i = 0; $i < $columns; $i++) { $field[$i] = mysql_field_name($fields, $i);} return $field[]; } Thanx a lot Sukanto -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Date help needed
Hope this helps: $days_2_search = array ( 1 => "Fri" , 2=>"Sun" , 3=>"Tue"); $num_days = 365; $day = (int) date("d"); $month = (int) date("n"); $year = (int) date("Y"); echo " "; for ($i = 1 ; $i <= $num_days; $i++) { $loop_day = date("D", mktime(0, 0, 0, $month, $day, $year) ); if ( in_array ("$loop_day" , $days_2_search) ) echo " " . date("D M j Y", mktime(0, 0, 0, $month, $day, $year)) . " "; if (++$day > 31 || !checkdate($month,$day,$year) ) { $day = 1; if (++$month > 12 ) { $month = 1; ++$year; } } } echo ""; ?> Rui Cunha Daniel Clark writes: A drop down with 365 days !?!? Isn't that a "little" big? I have a problem, I currently have some code which populates a dropdown box - this code gives me every day for the next x amount of days (EG: a years worth of days), however what I really need to be able to do, is to find a way to display this data in the dropdown box but ONLY show 3 days a week, IE: Mondays, Fridays and Sundays, so it would show the dates for each Monday, Friday and Sunday for X amount of days (IE: 365 days in the dropdown). Does anyone have any idea how to do this? I would really appreciate any help, I'd send my sample code only I'm not at my home/work computer ATM. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Multiple Inserts
some tips when inserting data: instead of inserting into the the table, do some debugging,echo the insert string and check if: 1- all string fields are between single comma ('field_name').Numéric fields do not need single comma; 2- all not null fields are present in the field list; 3- you're respecting the fields order (insert into t (x,y,z) values (val_x,val_y,val_z) ); hope it helps. Rui Cunha Daniel Clark writes: Any errors? Is the all the other data inserting into the second table? Hello, I am using a form to Insert data into 2 tables in the same database. $TimesheetID needs to be in each table. However, it is not being inserted into the second table, "tblTimesheetDetails" . Any advise? $result_timesheet=mysql_query("INSERT INTO tblTimesheet (TimesheetID, WorkerID, ClientID, TimesheetDate, ProspectiveOrRetrospective) VALUES ('$TimeSheetID','$WorkerID','$ClientID','$TimesheetDate','$ProspectiveOrRetr ospective')")or die("Insert Error: ".mysql_error()); $result_timesheetdetails=mysql_query("INSERT INTO tblTimesheetDetails (TimesheetID, ActivityTypeID, TimeSpentHours, TimeSpentMinutes) VALUES ('$TimeSheetID','$ActivityTypeID','$TimeSpentHours','$TimeSpentMinutes')")or die("Insert Error: ".mysql_error()); Thanks, Declan. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Delete Subquery
"...I want to be able to delete a rowid from the ROLES TABLE, but only if the roleid does not exist in the USERS TABLE..." DELETE FROM roles WHERE roleid NOT IN ( SELECT DISTINCT roleid FROM users); Rui Chris Bolt writes: How about: DELETE FROM users WHERE userid IN (SELECT u.userid FROM users AS u LEFT JOIN roles AS r ON (u.roleid = r.roleid) WHERE r.roleid IS NULL LIMIT 1); On Mon, 14 Jun 2004 11:59:25 -0400, Brock Jimmy D Contr 74 MDSS/SGSI <[EMAIL PROTECTED]> wrote: I've been able to figure how to use a delete subquery to delete all rows, but how do I use a delete subquery to delete only one row? I have two tables: users and roles with the following fields: Users: userid roleid Roles: roleid I want to be able to delete a rowid from the roles table, but only if the roleid does not exist in the users table. thanks -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Mysql not receiving the data
take a look at the sql insert sintaxe... it's supposed to be: INSERT INTO table VALUES (val1,val2,...,valn); all string and date values go between ' '...numeric values don't need them... here's an example: INSERT INTO table VALUES ('abc',500,'12-12-2003'); if your table has 5 fields but you don't want to insert values for them all then you must specify the fields that you want to insert: INSERT INTO table (field1,field2) VALUES (val1,val2); hope this helps. Rui Cunha Andrew Rothwell writes: Hi Larry, Thank you very much for the very quick response, I set my php.ini file (located /etc/php.ini ) for the register_globals = On (it was off by default) Now however I get an error Error adding entry: You have an error in your SQL syntax near 's spanish driver is found shot dead, Inspector Jacques Clouseau is the first off' at line 8 My Database is a movie database of my dvd's that I own (for insurance reasons) My addmovie.php is this mysql_connect("localhost","username","password"); mysql_select_db("movies"); $add = "INSERT INTO movies SET movie_name='$movie_name', genre='$genre', director='$director', star1='$star1', star2='$star2', star3='$star3', brief_synopsis='$brief_synopsis', imdb_link='$imdb_link'"; if (@mysql_query($add)) { echo("Your entry has been added. $movie_name"); } else { echo("Error adding entry: " . mysql_error() . ""); } ?> And the addmovie.htm page (atleast the form action is this) bordercolordark="#FF0033" bordercolorlight="#66"> Movie Name Andrew -Original Message- From: Larry E. Ullman [mailto:[EMAIL PROTECTED] Sent: Sunday, June 13, 2004 11:22 AM To: Andrew Rothwell Cc: [EMAIL PROTECTED] Subject: Re: [PHP-DB] Mysql not receiving the data Online I could see everything, and the pages gave the appearance of working, however when I went into the DB using PHPMYADMIN to check the status of the new data entered, all I found was blank rows ( for the new data since the rebuild, all the old data was there) There were the correct number of new rows for the amount of records that I had entered, which tells me (unless I am nistaken) that the PHP is talking to the DB, and is atleast sending a insert command, but the rest of the data is not getting in. - Without seeing any code whatsoever and since this worked before but no longer works on a new install, I can only assume that your code was written with the assumption that register_globals was turned on and it's not on in your current configuration. If that is the case, see the PHP manual or search the Web for the solution ($_POST, $_GET, etc.). Larry -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: mysql results, arrays, and for loops
hope this code will help you $db = mysql_connect ('localhost') or die ($MYSQL_NO_CON); mysql_select_db ('database_name',$db) or die ($MYSQL_NO_BD); $query = "SELECT * FROM table" or die ($MYSQL_NO_QUERY); $result = mysql_query($query); if (mysql_num_rows($result) > 0 ) { $num_rows = mysql_num_rows($result); for ($i=0; $i <$num_rows; $i++) { $row = mysql_fetch_row($result); echo "$row[0]; } }else echo "no records found!"; ?> Rui Cunha Philip Thompson writes: Hi all! I am using a select statement to obtain all the dates whenever someone submitted a problem in a database. Well, I want to get the result (which could be multiple dates) and then print that in a table format with some other information on a webpage. So I want to use a FOR loops and go through each of the dates and dynamically create a table. My question is: how do I store the results of the select query? Would I want to store them in an array, and then just parse through each element of the array, and what is the syntax for that? Or is there a better way? Thanks, ~Philip -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: GROUP_CONCAT query
i presume you want something like: select ip,hostname,referer,count(*) from table group by ip,hostname,referer; note: all fields in the select clause that are not affected by any aggregate function must appear in the group by clause; I.A. Gray writes: Hi all, I sometimes find the MYSQL manual very difficult to understand- perhaps I am alone in this. I am wanting to do the following, and I think I need to use GROUP_CONCAT in my query. I have a table of webstats for my site with columns for IP, Hostname, referer website etc and I want to show the occurences of the the same IP, hostname and referer website and how many times. How do I do this? My normal query would look like this? $query = "SELECT `id` , `clicks` , `os` , `browser` , `ip` , `port` , `host` , `country_code` , `country_name` , `cookies` , `crawler` , `referer_website` , `qs` , `datestart` , `dateend` , `timestart` , `timeend` , `history` , `fulltimestart` , `daystart` , `fulltimeend` , `dayend` , `timelength` FROM `webstatstable`" I have tried doing SELECT referer_website, GROUP_CONCAT(referer_website) FROM 'webstatstable' but that doesn't seem to work. Any ideas? * * Rui Pedro Cunha * * Dpto. de Ciências e Tecnologias * * Universidade Autónoma de Lisboa * * Rua de Santa Marta, 56, * * 1169-023 Lisboa * * Telefone (+351) 21 317 76 35/49 * * Fax (+351) 21 353 37 02 * * Url : http://www.ual.pt/dct/ * * E-mail: [EMAIL PROTECTED] * * -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Count unique visits in PHP/MySQL
take a look at this: http://otn.oracle.com/oramag/oracle/04-mar/o24asktom.html and search for the "Analytics to the Rescue" example. Instead of 3 seconds you want 1800 and instead of sum you want count.Don't forget to group by ip,of course... And you're done. No need for an extra table. Hope it helps you out. Rui Cunha Kim Steinhaug writes: Whatabout creating a table containing online users, where you log every activity with IP, BrowserSession and Timestamp. You also create a table to track the accual unique visits. So my logic to solve it : Update the online table like this (Some rough coding below, not tested at all, read the logic). 30 minutes = 60sec*30 = 1800 1"delete from online where timestamp<" . (time() - 1800); // Delete inactive users / uniqe ghosts or whatever 2"Update online set timestamp = '" . time() . "' where ip='" . $ip . "' and browsersession = '" . $browsersession . "'"; 3If (!mysql_affected_rows()){ // Update the Unique visitor table // Insert new entry with IP, Browsersession and time() into the online database } As far as my midnight brain would see it this would work nicely. -- -- Kim Steinhaug -- There are 10 types of people when it comes to binary numbers: those who understand them, and those who don't. -- www.steinhaug.com - www.easywebshop.no - www.webkitpro.com -- <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] ast.net... I am making a PHP/MySQL traffic report page from a table that records some user activity using PHP referrer information. I have a table with three rows: IP, page_name, and timestamp. The IP row records the user's IP address, page_name records the name of the page that the user loaded, and the timestamp row records in Unix timestamp format the time of day that the user requested the page. I want to be able to count unique visits per IP according to Internet Advertising Bureau standards, which count a "Unique Visit" as a log in by the same IP once every thirty minutes. IAB verbatim definition: "Visit - One or more text and/or graphics downloads from a site qualifying as at least one page, without 30 consecutive minutes of inactivity, which can be reasonably attributed to a single browser for a single session. A browser must "pull" text or graphics content to be considered a visit." So I need to make a MySQL query that will count how many times an IP logged a timestamp within a given time period. For example, the publisher checking traffic could request a date between May 1 and May 31, and I'd like to be able to return a page that counted unique users (count distinct IP), pages viewed (list distinct pages) and how many times they visited in that period. I have the first two down, but not the unique visits. Any ideas? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: DISTINCT and multiple results...
Hi, maybe you're looking 4 something like this: select user , filename , count(filename) from table group by user,filename having count(filename) = 3 order by 1,2; Rui [EMAIL PROTECTED] writes: Hi there... I'm trying to create a MYSQL query that does the following... I've a table that lists ALL downloads from our site. so I may have the same user many times, but different files, or infact, same user, same file. What my boss wants to see is: If a user downloaded a file 3 times, she doesn't care, she just wants to see that it was downloaded. So I tried using DISTINCT(email), but then I loose the data about the other files that they've downloaded How can I create a query, where I can list only one instance of email, per multiple instatnces of a file name. Does that make sence? I'm reallyu stumped... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: strange results in mysql with readdir.php
Alessandro, i think the sql insert statement should be: insert into images(sixfourdata) values ('$encoded'); Rui Cunha Alessandro Folghera writes: Anybody has an idea about what happened to me ? The following script should just charge in a mysql db the images located in a directory. Unfortunately everytime I call "readdir.php" (never mind if there are or not new images) mysql is charging 2 or 6 copies of the same last image uploaded into the directory. Anybody may explain to me where I'm failing? Thanks for all the phpers! Alessandro $dbcnx = mysql_connect("localhost", "root", "password"); mysql_select_db("news"); if (!$dbcnx) { echo( "connection to database server failed!"); exit(); } if (! @mysql_select_db("news") ) { echo( "Image Database Not Available!" ); exit(); } $path = "./"; $dir_handle = @opendir($path) or die("Unable to open directory $path"); while ($file = readdir($dir_handle)) { $filetyp = substr($file, -3); if ($filetyp == 'gif' OR $filetyp == 'jpg') { $handle = fopen($path . "/" . $file,'r'); $file_content = fread($handle,filesize($path . "/" . $file)); fclose($handle); $encoded = chunk_split(base64_encode($file_content)); $sql = "INSERT INTO images SET sixfourdata='$encoded'"; @mysql_query($sql); } } closedir($dir_handle); echo("complete"); ?> * * Rui Pedro Cunha * * Dpto. de Ciências e Tecnologias * * Universidade Autónoma de Lisboa * * Rua de Santa Marta, 56, * * 1169-023 Lisboa * * Telefone (+351) 21 317 76 35/49 * * Fax (+351) 21 353 37 02 * * Url : http://www.ual.pt/dct/ * * E-mail: [EMAIL PROTECTED] * * -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php