hierarchical Structure in Mysql
Hi listErs! I need to design a database, under a hierarchical structure. This Mysql database aims to store a catalog in which I should be able to define: CATEGORIES - SubCategories - Sub-SubCategories - Product the only problem, is that I usually use cascaded indexes, but in this case I should use a different approach to allow the catalog for instance to hold products not only in final Sub-Subcategory, but under Subcategories or even directly under a category. What is more the same product should be appear in many different categories at the same time. I have no experience in designing such a structure, and I was wondering if someone can give me hint on how to start, basically if there is a tutorial, to be able to do it the best possible way, and If someone has an example, that would be much appreciated. well, I hope to have been clear enough thanx in advance - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Calculating rows in a result set
Hi everybody, is there a way to calculate the number of rows retrieved by a mysql query, without actually performing it? I want to make a paginated result like in most search engines, and for that I need to display the number of pages the result would have, but I do not want to actually retrieve all the rows to be able to count them, because this is a loss of performance, in particular if the result set retrieves 10 rows and 100 people make a search; this would end in 1 x 100 rows just to say how many pages the result will have, I think there must be a better way to do it, isn't it? thanks to everybody in advance W.D.F. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Calculating rows in a result set
Got it, Thanks to all of your replies! W.D.F. - Original Message - From: Cal Evans [EMAIL PROTECTED] To: Walter D. Funk [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, July 16, 2002 11:44 AM Subject: RE: Calculating rows in a result set Not without actually performing SOME kind of query. You could select count(*) from... and insert the rest of your query there. This would give you the count. Then store this in a session variable (PHP) for subsequent pages. Most of the time though, I just put a NEXT button. ADODB has some great pagination tools to make this stuff easy. =C= * * Cal Evans * The Virtual CIO * http://www.calevans.com * -Original Message- From: Walter D. Funk [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 16, 2002 9:02 AM To: [EMAIL PROTECTED] Subject: Calculating rows in a result set Hi everybody, is there a way to calculate the number of rows retrieved by a mysql query, without actually performing it? I want to make a paginated result like in most search engines, and for that I need to display the number of pages the result would have, but I do not want to actually retrieve all the rows to be able to count them, because this is a loss of performance, in particular if the result set retrieves 10 rows and 100 people make a search; this would end in 1 x 100 rows just to say how many pages the result will have, I think there must be a better way to do it, isn't it? thanks to everybody in advance W.D.F. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mysql : Problems to make a file dump /n and br
Hi, I'm making a CSV from a Mysql table, in which text is stored as is, with all br to keep format etc (I need to keep line breaks because the same text has to be printed out in a page taking care of the original fomatting); The problem comes actually when Excell tries to read the CSV (; separates columns and ;; is a new row) all br inside the text are parsed as a Carriage return causing Excel to display a new Row in the middle of a text field; I've been looking up the mysql manual but I couldn't find a workaround for this, can someone help me, please? thanks in advance to everybody - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mysql SELECT IF()
Hi everybody! I need to count the ACTIVE users of my Mysql users table; where 'active' is a flag showing this status, using the following query it returns all the user in the table, and I need only those who have tha flag active set to 'Y' I know this could be done by using a where clause, but in this case it is not possible, because this is actually only a part of the query and I do not want to limit the result set with a where clause, this is the query I am using, I tried other combinations , but it did not work :-( SELECT IF( STRCMP(active,'Y') = 0 , COUNT(userId) , 0 ) as activeUsers FROM usersTbl any help will be appreciated, thanx - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Encrypting with PASSWORD() function
Hi, I am trying Mysql Password() function, to encrypt password in a a user/password table, it works fine both ways; it is to say, when I submit a login (user/password) combination it fetches the pair ok. My question is, as I read in the documentation, that the process is irreversible, how can I deal with the fact that a user can forget his password, if I will not be able to retrieve the original string, because what i can see is the encrypted data is there a workaround to deal with this, either than storing in a separeted table the original password string (not encrypted), or shall i simply not use this function ? thanks in advance to any suggestion Walter - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How can I Sort by Relevance?
I have a query like this select * from PROFILES where UCASE(DESCRIPTION) like '%A%' the result of this will be a set in which any member contains at least an A, but I'd like to sort them so as the result set becomes like this first: A second: AAA third: any string containing an A and so on I´d like to display results this way , in this case the first data is much relevant than the third one. But in the practise i get all results mixed up i will appreciate any help thanx - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
OT: Please Somebody can explain me what this message means??? Fw: ezmlm warning
Sorry for the length of this message, but, I don´t understand what it means, and why it says my address will be removed from the list without explanation. thanx - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, February 27, 2002 12:58 AM Subject: ezmlm warning Hi! This is the ezmlm program. I'm managing the [EMAIL PROTECTED] mailing list. Messages to you from the mysql mailing list seem to have been bouncing. I've attached a copy of the first bounce message I received. If this message bounces too, I will send you a probe. If the probe bounces, I will remove your address from the mysql mailing list, without further notice. I've kept a list of which messages from the mysql mailing list have bounced from your address. Copies of these messages may be in the archive. To retrieve a set of messages 123-145 (a maximum of 100 per request), send an empty message to: [EMAIL PROTECTED] To receive a subject and author list for the last 100 or so messages, send an empty message to: [EMAIL PROTECTED] Here are the message numbers: 98921 98918 98916 98924 98919 98920 98917 98922 98949 98950 98951 98934 98943 98956 98957 98925 98958 98927 98959 98947 98923 98935 98940 98939 98944 98928 98931 98926 98960 98936 98946 98932 98942 98938 98937 98929 98930 98941 98933 98948 98945 98987 98988 98981 98982 98983 98978 98979 98976 98977 98974 98964 98963 98980 98984 98967 98968 98962 98965 98969 98952 98953 98954 98961 98955 98989 98975 98986 98985 98972 98973 98970 98992 98971 98990 98966 99006 99000 98993 98994 98991 99002 98995 98996 99003 98997 99004 98998 98999 99009 99007 99001 99005 99008 99014 99015 99013 99011 99010 99016 99012 99017 99025 99026 99031 99030 99032 99027 99018 99019 99020 99022 99021 99036 99034 99033 99028 99023 99029 99024 99042 99037 99041 99038 99045 99043 99049 99040 99047 99044 99048 99039 99046 99051 99050 99035 99052 99053 99054 99055 99056 99064 99063 99057 99058 99059 99067 99061 99068 99070 99060 99062 99071 99069 99065 99072 99066 99079 99075 99085 99076 99087 99093 99088 99078 99081 99077 99074 99082 99084 99080 99073 99090 99089 99083 99086 99101 99100 99107 99105 99106 99102 99109 99094 99108 99095 99103 99092 99091 99098 99104 99096 99097 99099 99111 99119 99120 99114 99123 99117 99118 99112 99110 99115 99132 99137 99139 99140 99113 99136 99149 99147 99116 99148 99131 99144 99145 99146 99141 99127 99126 99130 99133 99135 99125 99124 99129 99128 99160 99122 99121 99134 99163 99150 99153 99152 99151 99158 99157 99138 99155 99142 99156 99143 99154 99164 99165 99166 99162 99159 99161 --- Enclosed is a copy of the bounce message I received. Return-Path: Received: (qmail 23324 invoked for bounce); 15 Feb 2002 08:23:05 - Date: 15 Feb 2002 08:23:05 - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] MIME-Version: 1.0 Content-Type: multipart/mixed; boundary=1013152904web.mysql.com305023 Subject: failure notice Hi. This is the qmail-send program at web.mysql.com. I'm afraid I wasn't able to deliver your message to the following addresses. This is a permanent error; I've given up. Sorry it didn't work out. [EMAIL PROTECTED]: 66.109.98.8 does not like recipient. Remote host said: 550 5.7.1 [EMAIL PROTECTED]... Relaying denied. Please check your mail first. Giving up on 66.109.98.8. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
OT: Calculating resources
Hi everybody! I am developing a site under PHP and Mysql which is now in the first steps. My question is, (since I don't administer the server), how can I calculate the resources that the system will need to run as smothly as possible? I mean, for example I am planning to use temporary table to gather subsets of data from a big table but I don't know how many temporary tables can I create at the same time, and not crashing the server in the process. Which configuration should I ask the administrator to meet from the point of view of the server features? If someone can give me a hint, i´ll be absolutely happy! Thanx in advance - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
TEMPORARY TABLES
I´ve just another question, I couldn´t get the info from the manual, I suppose that´s because I don´t know other basics on Mysql .. :-( I´ve been using the temporary table, and it works well, but, I can only display the results set just once in my script, is that normal? I´ve been trying to paginate the result but when I try to show the second page it seems to be empty. thanks in advance if there are too many simultaneous connections which create several temporary tables is it posible to knock down the server? thanks in advance No, server will not be knocked down by many tables opened, provided your OS has sufficient number of file descriptors to handle. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
UNION
HI, I found a very usefull function in Mysql (to combine the result of many SELECTS in one result set ), which is UNION SELECT UNION [ALL] SELECT [UNION SELECT ...] the problem is, that it was implemented in version 4.0 and above; anyone can tell me if there is a way to re-phrase the same query but for an older engine, like 3.23.33 best regards Walter D. Funk - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Fw: locking specific rows
Hi, I need to implement an access/logging system in mysql+php, which once a user logs in, does not let another individuals log in using the same user-password. I don´t care if users share a user-password, the only thing I want to avoid is that this users sharing the same user-pw combination, don´t acces at the same time. is there a way to lock the row when the user first logs-in I know that it exist table locking, but I only need to lock a specific row when the user is logged in. sorry if I´m not very clear, hope everybody can figure out what I´m trying to do thanx - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Subtracting Dates
Why don´t you try select TO_DAYS(end_date)-TO_DAYS(start_date) as duration : this will return the difference in days if you need mor accuracy i.e. difference in hours, you should word on a TIMESTAMP 14 instead of date ; this data type holds up to seconds and is mathematically easy to operate ... i hope it helps - Original Message - From: Ken Kinder [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, November 15, 2001 8:32 PM Subject: Subtracting Dates The date addition and subtraction functions in mysql don't seem to quiet do this. I was wondering if anyone know a way I could do this in the query: Say you have two fields, start_time, and end_time, representing the start and end of a period of time. Both are datetime datatypes. What I want is the logical equivalent of: select end_time - start_time as duration from I want the difference between those two, such that it would say 0 days, 4 hours or something. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Japanese content in Mysql + PHP
HI, does anyone have a clue on how to manage japanese characters in a textfield in MySQL PHP it works on varchar type fields , defining it as binary; but no way to make it work in a text or blob field thanx - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mysql and Japanese Characters
Hi everybody, I need some help !!! does anyone now how to store japanese content into mysql ??? I found out that using a VARCHAR field set to binary does seem to work; but how can I manage larger amounts of text, since TEXT data type does not support binary encoding? lease ... or i´ll do harakiri - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
