[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.nameb.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: Date help needed
Hope this helps:
?php
$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 form action=\test.php\ method =\post\
select name=\days\;
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 option name=\day\ . date(D M j Y, mktime(0, 0, 0, $month,
$day, $year)) . /option;
if (++$day 31 || !checkdate($month,$day,$year) ) {
$day = 1;
if (++$month 12 ) {
$month = 1;
++$year;
}
}
}
echo /selectinput type=\submit\ value=\go\/form;
?
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(pYour entry has been added. br
$movie_name/p);
}
else
{
echo(pError adding entry: .
mysql_error() . /p);
}
?
And the addmovie.htm page (atleast the form action is this)
body bgcolor=#FF
form method=post action=addmovie.php name=addmovies
table width=300 border=0 cellspacing=2 cellpadding=2
bordercolordark=#FF0033 bordercolorlight=#66
tr
td width=41% bgcolor=#99Movie Name /td
td width=59% bgcolor=#99FFCC
input type=text name=movie_name
/td
/tr
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: 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: mysql results, arrays, and for loops
hope this code will help you
TABLE WIDTH=100% HEIGHT=100%
?php
$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 TRTD$row[0]/TD/TR;
}
}else echo no records found!;
?
/TABLE
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: 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
1delete from online where timestamp . (time() - 1800); // Delete
inactive users / uniqe ghosts or whatever
2Update 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( pconnection to database server failed!/p);
exit();
}
if (! @mysql_select_db(news) )
{
echo( pImage Database Not Available!/p );
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
