Wordpress MU
I am currently evaluating the multi-user version of the popular blogging software, Wordpress. One of the things I am not so sure about is its schema. To support multiple users, Wordpress creates about ten tables per user. I have, at worst case, 4000 users who may be using this software, and I assume roughly 100 posts per user. My question is whether or not it is good design to have each user have his or her own set of tables. Would it not make sense to have all user posts in a single table, referenced by a BlogID? This table could easily grow to be a few million rows, and I know MySQL won't choke on that, as long as the indexes are maintained. Aside from ease of backing up user data in the individual table scenario, is there any other benefits to doing it this way? I am considering modifying the product to support a single table, but want some insight before doing so. -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: important! help plsss
Umit tas wrote:
hello i'm umit Tas;
i have a problem.
i'm writing programme in visual basic 6.0 but i must use MySQL server.
i'm installed MySQL server and executing my program (no problem) but my
problem is :
MySQL must be installed "A" computer and my programme must be installed
"B" computer
in LOCAL area network :( pls help
Private Function dbConnect()
'{
'If we hit an error, go on to the next command
On Error Resume Next
'Create a new instance of the ADODB connection onject
Set conn = New ADODB.Connection
'Control the cursors on the client side
conn.CursorLocation = adUseClient
'Create the DSN string using properties from the preference file
conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
& "SERVER=" & strDBHost & ";" _
& "DATABASE=" & strDBName & ";" _
& "UID=" & strDBUser & ";" _
& "PWD=" & strDBPass & ";" _
& "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 163841 'SET ALL PARAMETERS
'Open the MySQL database connection
conn.Open
'Sanity check - verify we have a connection to the database
If conn.State <> 1 Then
'{
MsgBox "Error connecting to the database")
'}
Else
'{
MsgBox("Connected to the database.")
'}
End If
'Return a reference to the database connection object
Set dbConnect = conn
'}
End Function
--
Erich Beyrent
--
http://www.beyrent.net
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Runing MySQL on boot
Andrew Kuebler wrote: I'm running FreeBSD and MySQL version 4.1. How do I get FreeBSD to load MySQL on boot? I don't see a script file that came with the installation. Thank you. Andrew Place a script called mysql-server.sh in /usr/local/etc/rc.d #!/bin/sh case "$1" in start) /usr/local/server/mysql/bin/mysqld_safe --old-passwords & ;; stop) /usr/bin/killall -TERM mysqld /usr/bin/killall -TERM mysqld_safe ;; *) echo "Usage: `basename $0` (start|stop)">&2 ;; esac exit 0 And make a corresponding entry in /etc/rc.conf: mysql_enable="YES" -- Erich Beyrent -- http://www.beyrent.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP/MySQL vs ASP/MSSQL?
Jigal van Hemert wrote: Hi Joerg (and other list readers), Joerg Bruehe wrote: Jigal van Hemert wrote: Win/IIS/ASP/MSSQL might need a bit more hardware to run on compared to Linux/Apache/MySQL/PHP (aka LAMP). You could always run MySQL on Windows Server along with your IIS and ASP. You don't need to throw the baby out with the bathwater. If you decide to keep your ASP code, then I would keep your IIS setup, as opposed to running something like Chilisoft and Apache. -- Erich Beyrent -- http://www.beyrent.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Reporting question
This is probably a silly question, but here goes... I have a table that logs access to web services, and I am looking to create a report to track the number of times each service was accessed in a given month. I know I can loop through the days in the month in PHP and do a seperate query for each day, but is there a way to do this in MySQL and populate dates not accessed with zeros? In other words, if I have hits for the 23rd and 25th, but not the 24th, can I get something like +++ | Day| Hits | +++ | 23 | 46 | | 24 | 0 | | 25 | 156| +++ Thanks in advance! -- Erich Beyrent -- http://www.beyrent.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Setup for MythTV
[EMAIL PROTECTED] wrote: Erich Beyrent <[EMAIL PROTECTED]> wrote on 10/25/2005 12:06:28 PM: [EMAIL PROTECTED] wrote: "Jim C." <[EMAIL PROTECTED]> wrote on 10/25/2005 11:10:50 AM: Where can I get tips on how to manually set up MySQL for MythTV? MythTV's site was not very helpful in this regards. Jim C. Jim, Did you see this: http://www.mythtv.org/docs/mythtv-HOWTO-6.html And also: http://wilsonet.com/mythtv/tips.php (see the section on Front Ends) And lastly: http://dipper.info/project/ivtv/ -- Erich Beyrent -- http://www.beyrent.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Setup for MythTV
[EMAIL PROTECTED] wrote: "Jim C." <[EMAIL PROTECTED]> wrote on 10/25/2005 11:10:50 AM: Where can I get tips on how to manually set up MySQL for MythTV? MythTV's site was not very helpful in this regards. Jim C. [attachment "signature.asc" deleted by Shawn Green/Unimin] I think they (MythTV) probably know more about MySQL than we (the list members) do about them. What is MythTV? Is is a RTOS, a regular OS, some kind of application platform, a game system, ...? The more you can tell us about the platform you are trying to use, the more help you can get as I am sure there are others out there that can help but don't have the slightest clue what you are talking about. Sorry! Shawn Green Database Administrator Unimin Corporation - Spruce Pine MythTV is a kick-ass PVR application that runs under Linux. Like an open-source TiVO. -- Erich Beyrent -- http://www.beyrent.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL dump (OT?)
Hi all, This is perhaps off-topic, but I need to dump my MySQL database into a format that FileMaker Pro will understand. Does anyone have any tips for doing this? Thanks in advance, Erich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Pulling a database schema out of a unknown MySQL database?
> I have a system with set of web pages that use PHP and a MySQL database. > > Apparently the old webmaster has disappeared, and a new webmaster has been hired. She needs to know the schema of the database. > > So my question is: Is there a way of querying MySQL not for values of fields, but rather for the schema of the database? > > Thanks! If you use DBDesigner by fabForce (open source), it will connect to your MySQL database and reverse engineer it for you, building both the sql and a graphical representation of the database. Pretty cool stuff... And it's free! -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Export database to XML/UML/Webpage?
[[snip]] > Have you looked into using the MySQL Administrator yet? It's a GUI program > that should do most of what you want (I know it doesn't diagram but it's > somewhere to start). You will have to search around to find some other > ER-based GUI administrator tools because I can't think of any off the top > of my head (sorry! no coffee yet!). Maybe others on the list will > recommend some? > > Shawn Green Fabforce's DBDesigner 4 is fantastic... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Limit on text field select
Is there a way I can select from a text field and limit the amount of text I get back in my query? For example, if I have an articleText field of type TEXT, and the article contains 4000 words, is there a way to select that text with a limit of 200 words, or should this kind of logic go in my php code? Best regards, Erich Beyrent Information Technology Services Plymouth State University -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MyODBC 3.51.10
> You need to set the OLD_PASSWORDS variable in the [MYSQLD] section of the my.ini file. > > Set-variable = old_passwords=1 > > It's not the ODBC, but your version of MySQL. 4.0.x uses 1 variant of password hashing, 4.1.x uses a more > > secure one that will be covered in MyODBC 3.53 (coming soon...). > > J.R. I did read that in the manual, but I was confused as to why I get the error on one pc and not the other. I am also somewhat confused as to why I have 3.51.10 on one pc, but cannot find it anymore for my other. Did it get pulled from the website? -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MyODBC 3.51.10
I somehow managed to download and install MyODBC 3.51.10 on my XP box, and it connects with no problems to my database server running version 4.1.7. However, it seems that MySQL is only allowing downloads for 3.51.9, and when I installed it on my Server 2003 and attempted to connect, I get the following error: Client does not support authentication protocol requested by server; consider upgrading MySQL client I can only assume it's because of the older driver? Best regards, Erich Beyrent Systems Administrator Information Technology Services Plymouth State University (603) 535-2948 [EMAIL PROTECTED] "Si vis pacem para bellum" -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
User variables
I am having a problem with a query: SET @SoftwareID = 7; SELECT s.softwareID, s.softwareName, s.softwareVersion, s.softwareCreated, s.softwareChanged, b.buildName, s.supportFlag, s.softwareDesc, s.softwareLicense, s.softwareLocations, s.softwareProductKey, p.platformName, v.vendorName, v.vendorURL FROM software s INNER JOIN platform_groups pg on s.softwareID = pg.softwareID INNER JOIN platforms p on pg.platformID = p.platformID INNER JOIN vendors v on s.vendorID = v.vendorID INNER JOIN builds b on s.buildTypeID = b.buildTypeID WHERE s.softwareID = @SoftwareID This does not work. It appears that the user variable is not picked up in the WHERE clause - the query works fine if I have: WHERE s.softwareID = 7 Is what I am trying to do not supported? I am using version 4.1.7 on FreeBSD 5.3. Best regards, Erich Beyrent -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Backing up directly to tape.
I have seen people dump the database with mysqldump and then use a cpio command to write the files to the tape device... -Erich- -Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED] Sent: Monday, September 06, 2004 4:35 AM To: [EMAIL PROTECTED] Subject: Re: Backing up directly to tape. "Tucker, Gabriel" <[EMAIL PROTECTED]> wrote: > I have been searching the archives and was unable to find an answer. > > I need the ability to backup MySQL instances directly to a tape device. > > Currently, I run a mysqldump to disk and have legato pick up the file. = > As I get to some larger databases, hundred's of gigs, and higher = > transaction rates, I will need an online solution that goes directly to = > a tape device. Currently we are using MySQL table types, though this = > may change. tar? mysqlhotcopy? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
User Permission System
Hi all, I have a need to use a kind of user permission system in the database, but because the database is a hosted solution, I do not have access to the real user tables and permissions and such. SO... What we're talking about is a document management system: 1. Some users can read, write, and add documents 2. Some users can only read documents 3. Some users cannot do any of the above I need to implement some kind of table that allows these things. CREATE TABLE MemberPermissions ( MemberID bigint NOT NULL auto_increment, canRead tinyint NOT NULL default 0, canWrite tinyint NOT NULL default 0, canInsert tinyint NOT NULL default 0, PRIMARY KEY (MemberID) ) TYPE=MyISAM; where the canRead, canWrite, and canInsert are Boolean flags of 0 or 1. Clearly, I will need to implement a login system. With this design, it appears that there will either need to be two queries - one to check the permissions, and the second to perform the action requested. I suppose the other thing I could do is query the permissions on login and cache that info in session data while the user is logged in. I had another thought to assign permissions to groups, and then assign a group to a user. Would this be easier and more manageable? I am looking for feedback as to whether this is an appropriate design, or perhaps some suggestions for a better design, pitfalls to watch out for, etc. Best regards, -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Design Question
I think I understand. So instead of my queries being centered around the listings table, they will be centered around this new table? Currently, I pull the records for each category like so: $query = "select l.CatalogNumber, l.PDFLink, l.PDFName, l.MP3Name, l.Title, p.PublisherName, c.ComposerLname, a.ArrangerLname, l.Price, l.Description, l.DiscountID, l.DiscountType, l.DiscountAmount, o.Alias, l.Description from listings l, publishers p, composers c, arrangers a, categories o where l.CategoryID=o.CategoryID and o.Name='".$Category."' and l.PublisherID=p.PublisherID and l.ComposerID=c.ComposerID and l.ArrangerID=a.ArrangerID order by ".$OrderBy; To follow your example, I would add these other fields to the listings_projects table you defined below, and restructure the query around that? Thanks for your insight! -Erich- -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 04, 2004 11:51 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Design Question As posted, your data structure supports two one-to-many relationships, not the one-to-one relationships as you described. You can have multiple Listings per Category and multiple Categories per Group. What it sounds like you have been asked to do is to support a many-to-many relationship. You need to support both multiple Listings per Category and multiple Categories per Listing. As you have it now: Groups (1..*) Categories (1..*) Listings As you need it to be: Groups (1..*) Categories (*..*) Listings To create a (*..*) relationship between two tables, you need a third table. Each entry in this table represents one Listing-Category association (relationship). CREATE TABLE listings_projects ( ListingID bigint not null , CategoryID int , ... any additional fields as needed ... , PRIMARY KEY (ListingID, CategoryID) ) The primary key ensures that at each Listing/Category combination appears only once (no duplicate assignments). I showed you where additional fields can fit into the relation table because sometimes there are facts about relationships that do not fit into either of the tables they relate A recent example in this list was a relation table between chemical compounds and the various plants in which those compounds could be found. A fact that belongs to the *relationship* could be the concentration of that chemical in that plant. That concentration value would not belong to the plants table nor would it belong to the compounds table but does belong to the relationship of plant to compound. Make sense? I have personally used additional fields like those to indicate "deletion". That way "old" values are no longer available for new relationships (in my applications) and my queries won't break as I didn't actually get rid of any information. My "historical" reports still function as the "old" names are still in the system, even if you can't use the the old names for any current purposes. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Erich Beyrent" <[EMAIL PROTECTED]> wrote on 08/04/2004 10:35:33 AM: > Hi all, > > I need some advice on a project I have. Basically, I have some tables: > > CREATE TABLE listings ( > ListingID bigint(20) unsigned NOT NULL auto_increment, > CatalogNumber varchar(12) NOT NULL default '', > PDFLink varchar(100) default NULL, > PDFName varchar(80) default NULL, > Title varchar(100) NOT NULL default '', > ComposerID int(11) default NULL, > ArrangerID int(11) default NULL, > PublisherID int(11) default NULL, > Price double(16,2) NOT NULL default '0.00', > DiscountID int(11) default NULL, > Description text, > NewTitles tinyint(1) default NULL, > CategoryID int(11) NOT NULL default '0', > PRIMARY KEY (ListingID) > ) TYPE=MyISAM; > > CREATE TABLE categories ( > CategoryID int(11) NOT NULL auto_increment, > Name varchar(50) NOT NULL default '', > Alias varchar(60) default NULL, > DiscountID int(11) default NULL, > Description text, > GroupID int(11) NOT NULL default '0', > PRIMARY KEY (CategoryID) > ) TYPE=MyISAM; > > CREATE TABLE groups ( > GroupID int(11) NOT NULL auto_increment, > Name varchar(50) default NULL, > DiscountID int(11) default NULL, > PRIMARY KEY (GroupID) > ) TYPE=
RE: Design Question
EB> My thought was to add a new field to the listings table that would EB> contain a comma-separated list of CategoryIDs, but something doesn't EB> feel right about this solution. > This would break the first normalization form and is extremely bad Okay - I thought something was off... > First of all ask your customer - what is the relation between listings > and categories - is it one-to-many or many-to-one or many-to-many relation The current relationship is one to one - each listing can only have one category. The customer is requesting a change to this, so that each listing can have many categories. > if it is one-to-many (many-to-one) then you should add a field to > details table that constitutes a primary key in the main table and > define a foreign key. That means having either CategoryID in listings > table or ListingID in categories table. My current table definition for the listings already has the foreign key of CategoryID. What you are saying is that the categories table should have a field for ListingID? Thanks! -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Design Question
Hi all, I need some advice on a project I have. Basically, I have some tables: CREATE TABLE listings ( ListingID bigint(20) unsigned NOT NULL auto_increment, CatalogNumber varchar(12) NOT NULL default '', PDFLink varchar(100) default NULL, PDFName varchar(80) default NULL, Title varchar(100) NOT NULL default '', ComposerID int(11) default NULL, ArrangerID int(11) default NULL, PublisherID int(11) default NULL, Price double(16,2) NOT NULL default '0.00', DiscountID int(11) default NULL, Description text, NewTitles tinyint(1) default NULL, CategoryID int(11) NOT NULL default '0', PRIMARY KEY (ListingID) ) TYPE=MyISAM; CREATE TABLE categories ( CategoryID int(11) NOT NULL auto_increment, Name varchar(50) NOT NULL default '', Alias varchar(60) default NULL, DiscountID int(11) default NULL, Description text, GroupID int(11) NOT NULL default '0', PRIMARY KEY (CategoryID) ) TYPE=MyISAM; CREATE TABLE groups ( GroupID int(11) NOT NULL auto_increment, Name varchar(50) default NULL, DiscountID int(11) default NULL, PRIMARY KEY (GroupID) ) TYPE=MyISAM; Currently, there is a one-to-one relationship between listings and categories, and listings and groups. Now, the customer is requesting that a listing be included in several categories. I am not quite sure how to do this. My thought was to add a new field to the listings table that would contain a comma-separated list of CategoryIDs, but something doesn't feel right about this solution. What would be a good approach to this problem? -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: BLOB's - General Guidance
> Luis - you're quite polite so I'll assume that you are ignorant rather th > an prejudiced. > > You have a university email so I'll also assume you're a student who wants > to learn. > > So here's your lesson for today: Don't judge people by their cover, or by > their color, or by their name, or by their service provider. Don't assume > all AOL users are technical lightweights, just like you don't want people > to assume that all people Named Rodriguez are illegal aliens. > > AOL may be a favorite with internet newbies but in many parts of the > > country it also offers the best service. Never make assumptions about > > people based on what ISP they use. No kidding. Why the hell would you ask for help on an online group, only to insult and trash the person who is helping you? I certainly would not be quick to help him in the future... I, for one, would like to thank each and every person on this list who has helped me in the past - you are all extremely knowledgeable, and it's a real honor to learn from you all. -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Sorting Varchar
> OK, I'm sorry. The solution I gave doesn't work.
>
> You need to have some sort of conditional in the sort expression so
that
> numbers are sorted numerically and other things are sorted >
> alphanumerically.
> I'm not aware of a test for numeric vaues in MySql, so you need to use
>
> some
> trick to differentiate between the two.
>
> This seems to work, provided there are no negative numbers and the
text
> things start with letters
>
> ORDER BY IF (cost < ':', LPAD(cost,10,'0'), CONCAT('1',cost))
>
> In this,
> cost < ':' tests if the string starts with a digit (':' is the
character
> after '9')
> LPAD(cost,10,'0') pads the integer on the left with zeros -- replace
the
> 10
> with a number at least one more than the maximum number of digits
> CONCAT('1',cost) causes the text items to sort to the end (the
numbers
> now start with '0')
>
> Pasha's solution is probably cleaner, but you have to change the
> table definition. For Pasha's solution to work, you would need to
> have the text_val column be null (or '') when the value is numeric.
You could try something like this alphanumeric sort:
order by
case
when substring(cost,1,1) between '0' and '9'
then ''
when substring(cost,2,1) between '0' and '9'
then left(cost,1)
when substring(cost,3,1) between '0' and '9'
then left(cost,2)
...
else 'Z'
end
, cast(
case
when substring(cost,1,1) between '0' and '9'
then substring(cost,1)
when substring(cost,2,1) between '0' and '9'
then concat('0',substring(cost,2))
when substring(cost,3,1) between '0' and '9'
then concat('00',substring(cost,3))
...
else 9
end
as integer)
-Erich-
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Last inserted ID
> Well, you don't need the distinct. > > Are you inserting with your PHP script? LAST_INSERT_ID(), as per the > > manual, > only returns the id from the last insert on that connect. You cannot get >the > LAST_INSERT_ID() for another connection. j- k- I used distinct because otherwise, I was getting 3575 results from the command line - I only wanted one. My PHP script uses an insert method in a class, and then tries to get the last insert id. After reading the manual, I was under the impression that the class would use the same connection, but that doesn't seem to be the case. I suppose my insert could return the last id... -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Last inserted ID
Hi there, I seem to be having a problem retrieving the last inserted ID for a table. The query I am using is as follows: mysql> select distinct LAST_INSERT_ID() as LastID from listings; ++ | LastID | ++ | 3575 | ++ 1 row in set (0.00 sec) However, when I run this from my PHP script, I get a value of 0. Any clues as to how to resolve this? Thanks! -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Group By Order By problem
Hi all, I am trying to get a bunch of results, group them by category, and then order each group of categories. My query is thus: SELECT l.CatalogNumber, l.MP3Name, l.PDFLink, l.PDFName, l.Title, p.PublisherName, c.ComposerLname, a.ArrangerLname, l.Price, l.Description, o.Alias FROM listings l, publishers p, composers c, arrangers a, categories o WHERE (a.ArrangerLname like '%$Criteria%' or p.PublisherName like '%$Criteria%' or c.ComposerLname like '%$Criteria%' or l.Title like '%$Criteria%' or l.CatalogNumber like '%$Criteria%' or l.Price like '%$Criteria%' or l.Description like '%$Criteria%') AND l.PublisherID=p.PublisherID and l.ComposerID=c.ComposerID and l.ArrangerID=a.ArrangerID and l.CategoryID=o.CategoryID GROUP BY o.Alias ASC ORDER BY o.Alias, c.ComposerLname ASC; This only displays 1 row in each category, so clearly I have an error in my Group By and/or Order By clause(s). I am sure my error is fairly basic, but I don't have enough experience with MySQL to figure it out. Does anyone have any insight into the problem? -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
question about searches
Hi all, This is probably a dumb question... I am in the planning stages for a database project, where I will need to store a bunch of documents in both PDF and MS Word format. Assuming that the documents are stored as binary in the database, suppose I want to be able to search the database for text that is included in these documents. Is there any way to do text searches on the contents of binary data in the database, or do I have to implement something like a Keywords field and a Description field containing specific words or phrases that are in the stored document, and do a full text search against that? This is probably the most obvious answer, but I wasn't sure if there was a mechanism for searching directly in the files. Thanks! -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Dumping sql commands
Hi there, I am running a website on a host who has blocked access to mysqldump. Is there any way I can show all the sql commands for creating and populating a table from within mysql? Again, I don't have access to mysqldump. Thanks! -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Sorting with an alpha numeric field.
> I have something like A001C, B689, B1001 etc...
I had to do something like this, and someone suggested the following:
order by
case
when substring(YourField,1,1) between '0' and '9'
then ''
when substring(YourField,2,1) between '0' and '9'
then left(YourField,1)
when substring(YourField,3,1) between '0' and '9'
then left(YourField,2)
...
else 'Z'
end
, cast(
case
when substring(YourField,1,1) between '0' and '9'
then substring(YourField,1)
when substring(YourField,2,1) between '0' and '9'
then concat('0',substring(YourField,2))
when substring(YourField,3,1) between '0' and '9'
then concat('00',substring(YourField,3))
...
else 9
end
as integer)
I never was able to get it to work the way I wanted though... Perhaps
it will trigger some ideas from the talented people on this list.
HTH
-Erich-
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: AddressBook CMS
> Hi Everyone, > > Fisrtoff, I know nothing about databases and even less about web design. > What I do know is that I want to move my Outlook contacts (~10,000) to a > real database :) > I've looked at FileMaker Pro and MyGroupWare and the likes but I just want > something simple where I can export my contacts and sort them into > templates. And those have either to much cost attached to them or simply > try > to offer to many features for what I want. > > Can someone point me in the right direction? I know I need to learn MySQL > and that I will (I'm getting more and more comfortable with PHPmyAdmin). > My > main concern is how to access and manage the content in a user-friend way. > > Cheers, > Phil Hi Phil, Under the File Menu, choose Import Export... Export your Contacts to a CSV file, and then import that file into MySQL. I believe you can also export your contacts directly to Access and FileMaker Pro. Of course, you mentioned that you want to move your contacts to a real database, so MySQL is the way to go! Look up in the MySQL documentation how to import CSV files. HTH, -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Saving file into database
>Use the BLOB, Luke! > >See your local MySQL manual for details. > >We're using BLOBs to store PDF in our database, and through the use of HTTP >headers, we're able to let user download the PDFs without having to store a >local copy on disk, directly from the database (content-disposition >header). Hi Kurt, I have been using MySQL to store links to PDFs which live in other directories. Is there an advantage to storing the PDFs directly into the database? -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Optimizing Queries
> Chris, > Is it faster if you remove the 'IS NOT NULL'? I know that's not the > > results > you want, but we have found that is NOT NULL will do a full scan. But we > normally use it with a join. Since you are using one table, I'm not sure > how it would affect it. > > Donny This is an interesting point. This may be off topic, but I work with a guy who has an allergic reaction to NULLs in database fields. It is his opinion that a "proper" database design would set default values for every field. If MySQL truly does a full scan for NOT NULL, it would seem that my co-worker is correct. Being new to MySQL (and databases in general), I was wondering what the rest of you thought about this topic. -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Duplicates returns in query?
You have no join criteria between your tables. Of course. I am so stupid... select e.EventID, date_format(e.EventDate, '%c/%d/%y') as EventDate, e.EventTime, e.EventDetails, e.VenueID, v.VenueName, v.VenueID, v.VenueURL, v.Directions from events e, venues v where e.VenueID = v.VenueID; Works beautifully! Thanks all! -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Duplicates returns in query?
Hi all, I have a problem with a select returning duplicates, even though there aren't any duplicates in the database. select e.EventID, date_format(e.EventDate, '%c/%d/%y') as EventDate, e.EventTime, v.VenueName, v.VenueID from events e, venues v; +-+---+---+-+-+ | EventID | EventDate | EventTime | VenueName | VenueID | +-+---+---+-+-+ | 2 | 3/06/04 | 09:00:00 | The Bombshelter | 1 | | 3 | 3/12/04 | 09:00:00 | The Bombshelter | 1 | | 2 | 3/06/04 | 09:00:00 | Goodtimes | 2 | | 3 | 3/12/04 | 09:00:00 | Goodtimes | 2 | +-+---+---+-+-+ I tried adding a "distinct" to the above select, which had no effect. I also ran an "explain" on the query: +---+--+---+--+-+--+--+---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+--+--+---+ | e | ALL | NULL | NULL |NULL | NULL |2 | | | v | ALL | NULL | NULL |NULL | NULL |2 | | +---+--+---+--+-+--+--+---+ Which tells me nothing. What have I done wrong here? -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Logging in Users
Hi all,
I need some advice as to how to manage a user login system using PHP and
MySQL. Currently, I have the following table:
+-+--+--+-+-++
| Field | Type | Null | Key | Default | Extra |
+-+--+--+-+-++
| MemberID| bigint(20) | | PRI | NULL| auto_increment |
| MemberFname | varchar(30) | | | ||
| MemberLname | varchar(40) | | | ||
| Login | varchar(8) | | | ||
| Password| varchar(32) | | | ||
| Bio | text | YES | | NULL||
| Address | varchar(127) | YES | | NULL||
| City| varchar(40) | YES | | NULL||
| State | char(2) | YES | | NULL||
| Zip | int(5) | YES | | NULL||
| Phone | varchar(20) | YES | | NULL||
| Cell| varchar(20) | YES | | NULL||
| Email | varchar(40) | YES | | NULL||
+-+--+--+-+-++
And my PHP function looks like this:
function login($user, $pass)
{
// Validate the fields passed in
if(($user == "") || ($pass == ""))
{
$status = array('code' => -1,
'msg' => '[ERROR] Invalid form'
);
return $status;
}
// Build the query
$query = "select
MemberID,
MemberFname,
MemberLname,
Login,
Password
from
band
where
Login = '$login' and
Password = MD5('$password'))";
$this->query($query) or die"[ERROR] Could not login: ".mysql_error());
// Loop through all of the records, and push into an assoc array
while($this->nextRecord())
{
$User[] = $this->Record;
}
// Return the results
return $User;
}
My question is, is this the best way to be doing this? I would love
some feedback and suggestions as to perhaps better methods to do this.
The database is on a hosted account, so I don't have access or privs to
change configurations or add real MySQL users.
Thanks!
-Erich-
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help with query
OH MY GOD IT WORKS!!! I got 32 rows in set (0.08 sec). That is fantastic! Thank you so much Now, when I do an explain on this query, I get the following: +---++---+-+ | table | type | possible_keys | key | +---++---+-+ | l | ALL| NULL | NULL| | p | eq_ref | PRIMARY | PRIMARY | | c | eq_ref | PRIMARY | PRIMARY | | a | eq_ref | PRIMARY | PRIMARY | | o | eq_ref | PRIMARY | PRIMARY | +---++---+-+ +-+---+--+-+ | key_len | ref | rows | Extra | +-+---+--+-+ |NULL | NULL | 2647 | Using temporary; Using filesort | | 8 | l.PublisherID |1 | Using where | | 8 | l.ComposerID |1 | Using where | | 8 | l.ArrangerID |1 | Using where | | 4 | l.CategoryID |1 | | +-+---+--+-+ This seems really efficient, since the only large number of rows to search against is the main listings table, if I read this right. Is there any further optimization that I can do, or this as good as it gets? Believe me, I am NOT complaining!!! Thanks again! -Erich- > If every record in the listing table will have a corresponding record in > > the category table you may just include the category clause in with the > rest. > > WHERE > (a.ArrangerLname like '%$Criteria%' or > p.PublisherName like '%$Criteria%' or > c.ComposerLname like '%$Criteria%' or > l.Title like '%$Criteria%' or > l.CatalogNumber like '%$Criteria%') > AND > l.PublisherID=p.PublisherID and > l.ComposerID=c.ComposerID and > l.ArrangerID=a.ArrangerID and > l.CategoryID=o.CategoryID > > > > > Hi Evelyn, > > How would I do that - would something like this be what you had in > > mind? > > left join categories o on o.CategoryID = l.CategoryID > > > This goes in the WHERE clause, right? > > Thanks! > > -Erich- > > > -Original Message- > From: Schwartz, Evelyn [mailto:[EMAIL PROTECTED] > Sent: Friday, February 06, 2004 8:53 AM > To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: RE: Help with query > > You will need parentheses around the 'or' clauses of your where > > clause. > > You also don't seem to join the categories table with any other > > tables. If > you don't join tables you will create what is called a 'cross product' > query. If table A has 10 rows and table B has 20 rows then querying A > and B > will return 200 rows (every row of A will be joined with every row of > B!). -Original Message- From: Erich Beyrent [mailto:[EMAIL PROTECTED] Sent: Fri 2/6/2004 8:46 AM To: [EMAIL PROTECTED] Cc: Subject: Help with query Hi everyone, I am having a rough time with a query, which seems to be taking so long it hangs the systems. SELECT l.CatalogNumber, l.PDFLink, l.PDFName, l.Title, p.PublisherName, c.ComposerLname, a.ArrangerLname, l.Price, l.Description, o.Alias FROM listings l, publishers p, composers c, arrangers a, categories o WHERE a.ArrangerLname like '%$Criteria%' or p.PublisherName like '%$Criteria%' or c.ComposerLname like '%$Criteria%' or l.Title like '%$Criteria%' or l.CatalogNumber like '%$Criteria%' AND l.PublisherID=p.PublisherID and l.ComposerID=c.ComposerID and l.ArrangerID=a.ArrangerID ORDER BY o.Alias"; How can I rewrite this query to be efficient (and functioning!) - I am fairly new to MySQL and could use lots of advice! Thanks! -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help with query
Hi Evelyn, How would I do that - would something like this be what you had in mind? left join categories o on o.CategoryID = l.CategoryID This goes in the WHERE clause, right? Thanks! -Erich- -Original Message- From: Schwartz, Evelyn [mailto:[EMAIL PROTECTED] Sent: Friday, February 06, 2004 8:53 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Help with query You will need parentheses around the 'or' clauses of your where clause. You also don't seem to join the categories table with any other tables. If you don't join tables you will create what is called a 'cross product' query. If table A has 10 rows and table B has 20 rows then querying A and B will return 200 rows (every row of A will be joined with every row of B!). -Original Message- From: Erich Beyrent [mailto:[EMAIL PROTECTED] Sent: Fri 2/6/2004 8:46 AM To: [EMAIL PROTECTED] Cc: Subject: Help with query Hi everyone, I am having a rough time with a query, which seems to be taking so long it hangs the systems. SELECT l.CatalogNumber, l.PDFLink, l.PDFName, l.Title, p.PublisherName, c.ComposerLname, a.ArrangerLname, l.Price, l.Description, o.Alias FROM listings l, publishers p, composers c, arrangers a, categories o WHERE a.ArrangerLname like '%$Criteria%' or p.PublisherName like '%$Criteria%' or c.ComposerLname like '%$Criteria%' or l.Title like '%$Criteria%' or l.CatalogNumber like '%$Criteria%' AND l.PublisherID=p.PublisherID and l.ComposerID=c.ComposerID and l.ArrangerID=a.ArrangerID ORDER BY o.Alias"; How can I rewrite this query to be efficient (and functioning!) - I am fairly new to MySQL and could use lots of advice! Thanks! -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with query
Hi everyone, I am having a rough time with a query, which seems to be taking so long it hangs the systems. SELECT l.CatalogNumber, l.PDFLink, l.PDFName, l.Title, p.PublisherName, c.ComposerLname, a.ArrangerLname, l.Price, l.Description, o.Alias FROM listings l, publishers p, composers c, arrangers a, categories o WHERE a.ArrangerLname like '%$Criteria%' or p.PublisherName like '%$Criteria%' or c.ComposerLname like '%$Criteria%' or l.Title like '%$Criteria%' or l.CatalogNumber like '%$Criteria%' AND l.PublisherID=p.PublisherID and l.ComposerID=c.ComposerID and l.ArrangerID=a.ArrangerID ORDER BY o.Alias"; How can I rewrite this query to be efficient (and functioning!) - I am fairly new to MySQL and could use lots of advice! Thanks! -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Search multiple fields across multiple tables
Hi everyone, Sorry to ask this question, because this is going to get complicated... Okay - what I need to do is be able to search for data across multiple fields which are organized in multiple tables. Here is a basic description of my database schema (not quite all of the fields...): Table listings - ListingID - CatalogNumber* - Title* - ComposerID - ArrangerID - PublisherID - Price - CategoryID Table arrangers - ArrangerID - ArrangerLname* Table publishers - PublisherID - PublisherName* Table composers - ComposerID - ComposerLname* Table categories - CategoryID - Alias* (* = field to search against) My queries have been based on categories, so for a given category, I can easily pull out all of the appropriate records: But what I need to be able to do is search the CatalogNumber, Title, Arranger, Composer, Publisher, and Description for a given search string. I tried to put that into one big query and hung the database pretty badly. So I resorted to doing five separate queries, and then merging the result arrays into one array. This however, does not quite do what I need it to do, because now I have to group all of the search results according to their categories (all listings in category x displayed together, all listings in category y displayed together, etc). My problem is that I don't even know where to start. Can it be done with MySQL, or does it need to be done on the PHP side? -Erich- PS: If you want to see all of this in action, you can go to www.bvdpress.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Search multiple fields across multiple tables
Hi everyone, Sorry to ask this question, because this is going to get complicated... Okay - what I need to do is be able to search for data across multiple fields which are organized in multiple tables. Here is a basic description of my database schema (not quite all of the fields...): Table listings - ListingID - CatalogNumber* - Title* - ComposerID - ArrangerID - PublisherID - Price - CategoryID Table arrangers - ArrangerID - ArrangerLname* Table publishers - PublisherID - PublisherName* Table composers - ComposerID - ComposerLname* Table categories - CategoryID - Alias* (* = field to search against) My queries have been based on categories, so for a given category, I can easily pull out all of the appropriate records: But what I need to be able to do is search the CatalogNumber, Title, Arranger, Composer, Publisher, and Description for a given search string. I tried to put that into one big query and hung the database pretty badly. So I resorted to doing five separate queries, and then merging the result arrays into one array. This however, does not quite do what I need it to do, because now I have to group all of the search results according to their categories (all listings in category x displayed together, all listings in category y displayed together, etc). My problem is that I don't even know where to start. Can it be done with MySQL, or does it need to be done on the PHP side? -Erich- PS: If you want to see all of this in action, you can go to www.bvdpress.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: After successful INSERT, no record found
> Yes, the transaction was committed. I was using MyCC at the same time > the anomaly occurred, also. Could this have had anything to do with > it? I am new to MySQL - do you *have* to commit the transaction after the insert? What is the benefit of the commit - does it do anything? -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
