Re: SELECT N records from each category
This one time, at band camp, "Perrin Harkins" <[EMAIL PROTECTED]> wrote: > In your case, you might be able to just cheat it with some > MySQL-specific LIMIT stuff: Thanks very much Perrin, this is most useful and I can take it from here. Kind regards Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT N records from each category
This one time, at band camp, Peter Brawley <[EMAIL PROTECTED]> wrote: > Vague? Not in the slightest. General? Indeed, by design. I have read this before.. It uses a single table, I am using multiple tables. I am not selecting the TOP 10 or whatever. This example has no relationships where the schema I presented does. If you dont know the answer, just leave it out, but thanks for your efforts. If anybody can steer me in the right direction here, that would be great. Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT N records from each category
This one time, at band camp, Peter Brawley <[EMAIL PROTECTED]> wrote: > See "Within-group quotas (Top N per group)" at > http://www.artfulsoftware.com/infotree/queries.php. Yes, I have seen that, very clever. How does it relate to my situation? Simply point to vague references is not helpful. Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT N records from each category
I have 3 tables (schema below) with categories, questions and answers. Each category can of course have many questions in it. Also, the answers are multiple choice, so each question can have several related answers. I am trying to achieve 2 goals. 1) SELECT N questions and the related answers from each category. The result needs to have say, 5 questions from each category. Of course, the answers for each of the questions needs to be there also. 2) SELECT N questions and related answers from specified categories. This time, a simple WHERE test_category_id IN(1,2,3) will do I think. I can select ALL the questions and related answers, but this is as far as I get. I think I need some group-wise additions.. or something SELECT test_question_id, test_question_text, test_answer_id, test_answer_text, test_answer_correct FROM test_questions q1 LEFT JOIN (SELECT test_answer_id, test_answer_text, test_question_id, test_answer_correct FROM test_answers) AS q2 USING(test_question_id); The schema looks like this.. CREATE TABLE test_categories ( test_category_id int(11) NOT NULL auto_increment, test_category_name varchar(450) NOT NULL default '', PRIMARY KEY (test_category_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE test_questions ( test_question_id int(11) NOT NULL auto_increment, test_category_id int(11) default NULL, test_question_text varchar(254) NOT NULL, test_question_code varchar(1024) NOT NULL, PRIMARY KEY (test_question_id), FOREIGN KEY (test_category_id) REFERENCES test_categories (test_category_id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE test_answers ( test_answer_id int(11) NOT NULL auto_increment, test_question_id int(11) NOT NULL, test_answer_correnct tinyint(4) NOT NULL, PRIMARY KEY (test_answer_id), FOREIGN KEY (test_question_id) REFERENCES test_questions (test_question_id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Any help in this matter hugely appreciated, Kind regards Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Virtual Hosting Privileges
This one time, at band camp, "Grant Peel" <[EMAIL PROTECTED]> wrote: > What priveleges should I grant users at setup time? Perhaps look at the GRANT option GRANT SELECT, UPDATE, DELETE TO [EMAIL PROTECTED] IDENTIFIELD BY 'password'; you can use GRANT * or any combination of privileges you like. Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: append on insert
This one time, at band camp, Baron Schwartz <[EMAIL PROTECTED]> wrote:
> INSERT INTO item (`key`)
> SELECT CONCAT('my-item',
> (SELECT IF(COUNT(*) = 0, '', CONCAT('-', COUNT(*)))
>FROM item WHERE `key` LIKE 'my-item%'));
> Kevin, this design is not first normal form and will cause you trouble.
> Consider what will happen if you insert my-item, my-item, and then my-ite.
I am aware of the break in 1NF but need use the item_name as a key. I was hoping
MySQL had some sort of sequence available to auto append to similar keys.
I could of course check if the key exists, and then change the value in the
application layer to my-item-2, but was hoping to do it in a single query.
Kind regards
kevin
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: append on insert
This one time, at band camp, "Martin Gainty" <[EMAIL PROTECTED]> wrote: > Kevin- > > Declare the column AUTO_INCREMENT > http://lists.mysql.com/mysql/161930 You can auto increment a non INT field? Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
append on insert
I have a table of item I wish to have the value of the item incremented if there is a duplicate. I looked at ON DUPLICATE KEY UPDATE but this modifies the exsisting value. If the item is my-item and this already exists, I need to make it my-item-2 or even my-item-123 where 123 is the key. Kind regards Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bookmarks and keywords
This one time, at band camp, Baron Schwartz <[EMAIL PROTECTED]> wrote: > > I wish to select all bookmark_title and bookmark_url that have the same > > keywords as > > the bookmark_url 'http://www.redhat.com. I do this.. > > mysql> SELECT child.bookmark_id, child.bookmark_title, child.bookmark_url > > FROM bookmarks AS child join bookmarks AS parent ON parent.bookmark_keyword > > = child.bookmark_keyword WHERE child.bookmark_url='http://www.redhat.com' > > GROUP BY child.bookmark_keyword; > > > > That query is right. > > But this returns.. > > +-++---+ > > | bookmark_id | bookmark_title | bookmark_url | > > +-++---+ > > | 4 | and four | http://www.redhat.com | > > | 8 | finally| http://www.redhat.com | > > +-++---+ > > But, the JOIN table from which those columns come actually has the > columns you want from the parent table as well. It's just that you're > selecting the child columns. Im not sure I follow, the results should be those with id's of 1, 3, 4, 5, 6, 7, 8 and If I chose http://www.php.net that has only the keyword of "php" then the results would be 1,3,5, and 8 Kind regards Kevin -- "Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote." -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
bookmarks and keywords
I have a table of bookmarks that have keyword searching. Fields +-+--+--+--+ | bookmark_id | bookmark_title | bookmark_url | bookmark_keyword | +-+--+--+--+ | 1 | number one | http://www.phpro.org | php | | 2 | number 2 | http://www.freshmeat.net | software | | 3 | three here | http://www.php.net | php | | 4 | and four | http://www.redhat.com| linux | | 5 | the fifth| http://www.ez.no | php | | 6 | this will do | http://www.google.com| search | | 7 | something about fish | http://www.youtube.com | linux | | 8 | finally | http://www.redhat.com| php | +-+--+--+--+ I wish to select all bookmark_title and bookmark_url that have the same keywords as the bookmark_url 'http://www.redhat.com. I do this.. mysql> SELECT child.bookmark_id, child.bookmark_title, child.bookmark_url FROM bookmarks AS child join bookmarks AS parent ON parent.bookmark_keyword = child.bookmark_keyword WHERE child.bookmark_url='http://www.redhat.com' GROUP BY child.bookmark_keyword; But this returns.. +-++---+ | bookmark_id | bookmark_title | bookmark_url | +-++---+ | 4 | and four | http://www.redhat.com | | 8 | finally| http://www.redhat.com | +-++---+ Where it should return also the results with the bookmark_id of 1 as the bookmark_url http://www.redhat.com has two keywords, "php" and "linux" so this should match both. What should I be looking at here? Kind regards kevin -- "Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote." -- "Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote." -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't fetch result twice in a PHP script
This one time, at band camp, "Mahmoud Badreddine" <[EMAIL PROTECTED]> wrote:
> I have something like
> while($someArr=mysql_fetch_row($result))
> {
> ...some code.
> }
>
> The first time I call mysql_fetch_row , the code inside the while loop gets
> executed, but not the second time. What is the reason behind that.
The array pointer is now at the end of the result set.
Kevin
--
"Democracy is two wolves and a lamb voting on what to have for lunch.
Liberty is a well-armed lamb contesting the vote."
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Linking tables
Hi all, having a spot of bother with a 'keywords' table. I have a table of articles with an article_id and a table of categories with a category_id, all is well. These are linked via a article_link table so that an article can be a member of multiple categories. I then have a table of 'keywords' that will relate to each article. I wish to be able to SELECT all articles associated with the keywords. No issues so far. Next, I wish to be able to dynamically add a table to the database, eg: a 'cats' table. If a cat has a keyword of 'tiddles' associated with it. I would then like to be able to SELECT all related articles, that is, all records in the articles table, with the keyword 'tiddles'. MySQL 5 and the db layout looks like this so far. thanks for looking Kevin CREATE table articles ( article_id int(9) NOT NULL auto_increment, user_id int(9) default NULL, article_category_id int(11) NOT NULL, article_title varchar(50) NOT NULL, article_subject varchar(50) NOT NULL, article_blurb varchar(250) default NULL, article_body text, article_update timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, article_publish_date timestamp NOT NULL default '-00-00 00:00:00', article_orig_author varchar(50) NOT NULL, article_image longblob, PRIMARY KEY (article_id) ); CREATE table article_categories ( article_category_id int(11) NOT NULL auto_increment, article_category_name varchar(20) NOT NULL, article_category_left_node int(11) NOT NULL, arcitle_category_right_node int(11) NOT NULL, PRIMARY KEY (article_category_id) ); CREATE table article_links( article_link_id int(11) NOT NULL auto_increment, article_id int(11) NOT NULL, article_category int(11) NOT NULL, PRIMARY KEY (article_link_id) ); CREATE table keywords( keyword_id int(11) NOT NULL auto_increment, keyword_name char(20) NOT NULL, PRIMARY KEY (keyword_id) ); -- "Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote." -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Blob data
This one time, at band camp, Warren Young <[EMAIL PROTECTED]> wrote: Storing arbitrary data in a BLOB column is > tantamount to trying to turn the database into a file system. If you > want a file system, use a file system. What is a file system, if not a database? Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL e-zine
This one time, at band camp, "Peter M. Groen" <[EMAIL PROTECTED]> wrote: > > Any thoughts? Any desires to contribute? > > Hi Keith, > > Great Idea! > I volunteer for the developer side (Mysql and C++) I have written for php|mag previously, I'll put my hand up for PHP/MySQL Kevin -- "Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote." -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: advice for blob tables?
This one time, at band camp, [EMAIL PROTECTED] wrote: > > I don't feel the implementation direction this article takes is good. It > uses single row binary storage, which anyone who has had to deal with > large files knows is a definate issue. According to your method of storing binary data in BLOB fields, rather than LONGBLOBs you would have over 11,000 rows for a single 700MB iso. I am not sure how MySQL handles blobs internally but I would assume it is like most other databases and adds some sort of delimiter to the data and has to seek to it to distinguish columns. This means a 700Mb file stored in 64k chunks would need over 11,000 file seeks per row read. LONGBLOBs are the way forward here I feel. A single table containing the relevant metadata along with the binary data. There is no need for any normalization as this is a one-to-one relationship. having over 11,000 rows for a single file I dunno, I would like to see some benchmarks on this. Kevin -- "Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote." -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: advice for blob tables?
This one time, at band camp, "Michael Higgins" <[EMAIL PROTECTED]> wrote: > So before I just jump in and go like I usually do, does anyone have any > quick advice, things to consider, links, must-RTFMs or the like to help a > newbie out? This tutorial deals with images and BLOBs. It should get you on the right path. http://www.phpro.org/tutorials/Storing-Images-in-MySQL-with-PHP.html Kevin -- "Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote." -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
referal system
Hi gang, sorta newish to MySQL so please bear with me... I am putting together a system of referals for my photography company. The referal system works, like many others. A business refers customers to me, for which they recieve a referal fee when the customer pays a deposit. I can create a the customers, referals, referers, referer_categories, jobs, tables no fuss. How the system will (hopefully) work is a customer calls to book a job, and will have a referal number. This number will be a referer_id in the db so I know who has refered them. easy so far. So I enter the customer details in the customers table, which includes the referal_id. Should I then have a trigger to INSERT the referer_id into the referals table. The referals table contains referal_id INT referer_id INT referal_date I then have a table of jobs for when they book the job. venue, date etc. The payments table also will need to have some sort of mechanism so when a deposit is taken from the customer, the referer is paid thier commission. Am I going in the right direction here? Kind regards Kevin -- "Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote." -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query date ranges - step 2
This one time, at band camp, Kevin Waterson <[EMAIL PROTECTED]> wrote: > seasonDateFrom seasonDateTo seasonRateWeekly > 2004-06-02 00:00:002004-10-31 00:00:00 42.86 > 2004-01-01 00:00:002004-06-01 00:00:00 34.29 > > When I take a booking I have yet another range > $bookingDateFrom and $BookingDateTo > > I need to get the SUM(seasonRateWeekly) for each day in the booking range. Thanks for the help guys, here is the solution I came up with so far... I think I need to break down process into 1) get the seasons 2) get the number of days used in each season 3) multiply the season rate my the number days used in each season Sounds alot easier when put that way... so firstly I n step 1 I can do with a query like SELECT * FROM seasons WHERE (booking_begin_date BETWEEN seasonDateFrom AND seasonDateTo) OR (booking_end_date BETWEEN seasonDateFrom AND seasonDateTo) OR (booking_begin_date <= seasonDateFrom AND booking_end_date >= seasonDateTo) This works well. Now, step 2... How can I get the number of days used in each season? Kind regards Kevin -- __ (_ \ _) ) | / / _ ) / _ | / ___) / _ ) | | ( (/ / ( ( | |( (___ ( (/ / |_| \) \_||_| \) \) Kevin Waterson Port Macquarie, Australia -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query date ranges
I have been trying this is several ways, currently I have a mess
MySQL 4.1.1
PHP as the interface
I have a table of with a date range called seasons.
in it I have two date ranges and an amount to be charged
for each day in the range
2004-01-01 00:00:00 2004-06-01 00:00:0044
2004-06-02 00:00:00 2004-10-31 00:00:00110
seasonDateFrom seasonDateTo seasonRateWeekly
2004-06-02 00:00:002004-10-31 00:00:00 42.86
2004-01-01 00:00:002004-06-01 00:00:00 34.29
When I take a booking I have yet another range
$bookingDateFrom and $BookingDateTo
I need to get the SUM(seasonRateWeekly) for each day in the booking range.
Currently , and here is the bad bit, I can get it to work if I calculate
the number of days in the booking range, then loop through them in php
with foreach and increment a counter
SELECT seasonRateWeekly FROM seasons WHERE DATE_ADD('{$newbookingDateFrom}', INTERVAL
$i DAY)
BETWEEN seasonDateFrom AND seasonDateTo
This of course is almost useless as it takes 40 queries for 40 days. Not efficient at
all.
But I need the individual values, I think, to be able to query across season ranges
should
a booking range span two, or more, seasons.
As always, any help greatfully recieved
Kind regards
Kevin
--
__
(_ \
_) )
| / / _ ) / _ | / ___) / _ )
| | ( (/ / ( ( | |( (___ ( (/ /
|_| \) \_||_| \____) \____)
Kevin Waterson
Port Macquarie, Australia
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bookings
This one time, at band camp, Sasha Pachev <[EMAIL PROTECTED]> wrote: > * if your application is not going to be deployed for a year or so or if you > have enough confidence in MySQL alpha, use stored procedures in the bleeding > edge MySQL 5.0 How would I benifit from a stored procedure? Kind regards Kevin -- __ (_ \ _) ) | / / _ ) / _ | / ___) / _ ) | | ( (/ / ( ( | |( (___ ( (/ / |_| \) \_||_| \____) \) Kevin Waterson Port Macquarie, Australia -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bookings
This one time, at band camp, Mark Maggelet <[EMAIL PROTECTED]> wrote: > I don't know how you're charging weekly rates, but you could just > check if daysbooked>=7 and knock a percentage off total. I was thinking of having each day of a period charged as the weekly period rate divided by 7. But this seems a bit cumbersome. I guess I would need some sort of grouping for the rooms, such that rooms could be grouped together by price and number of bedrooms/beds and rooms could be assigned to groups. Kind regards Kevin -- __ (_ \ _) ) | / / _ ) / _ | / ___) / _ ) | | ( (/ / ( ( | |( (___ ( (/ / |_| \____) \_||_| \____) \) Kevin Waterson Port Macquarie, Australia -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
bookings
I am (trying) to make a booking systems. Currently I have a table with 3 timestamps that record bookingDate, bookingFromDate and bookingToDate I have another table that records 'seasons'. This table contains two timestamps that record seasonStartDate and seasonEndDate also I have the rates as seasonRateWeekly and seasonRateNightly What I need to do, is INSERT INTO bookings the bookingFromDate and bookingToDate, no problem there, but I need to know what rate to charge them. and if the booking dates overlap seasons, the appropriate rate needs to be applied. All replies greatfully recieved, Kevin -- __ (_ \ _) ) | / / _ ) / _ | / ___) / _ ) | | ( (/ / ( ( | |( (___ ( (/ / |_| \) \_||_| \____) \____) Kevin Waterson Port Macquarie, Australia -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
