Date: 07/21/05     Keywords: no keywords

how do you guys go about storing things (for instance as 'items' in 'categories') with subcategories... in the past, i've had 2 tables, 'categories' and 'subCategories' with 'subCategories.categoryID') and in my 'items' table used 'items.categoryID' and 'items.subCategoryID' - another way I've done it is had a field in categories called 'categories.subCategoryOf' - this field would hold the 'categories.categoryID' value of another category if you were dealing with a sub-category, otherwise it would default to 0

any other methods?

Source: http://www.livejournal.com/community/mysql/62820.html

Date: 07/21/05     Keywords: no keywords

Query I have now that's that has bad syntax
$Query = "SELECT sum((end_time - start_time) * rate) AS subtotalA, invoice_number GROUP BY invoice_number FROM invoice_items HAVING office_id='$office_id' AND service_type!='225' ORDER BY invoice_number"

The data may have several rows with the same invoice number. I want to pull up a subtotal per invoice number as a result array. However, that subtotal is the result of 3 columns: (end_time - start_time) * rate

I am looking around the manual right now.


Thanks for any insight. I am inexperienced with these complex queries.

Source: http://www.livejournal.com/community/mysql/62701.html

Date: 07/11/05     Keywords: no keywords

I need to search a table for the an item that matches the client_id and return the date of the most recent record (column is invoice_date and the format is yyyy-mm-dd).

Is there a statement like MAX that I can use?

Thanks, I am currently searching the manual.

Source: http://www.livejournal.com/community/mysql/61823.html

Date: 07/08/05     Keywords: php

hey guys, this is my first post in here - i'm also a member of php and flashdev

this seems simple, but i hope i don't bungle it up - i don't have a concrete example for this, just something i've been thinking about

let's say i have 2 tables, categories and items and there exists items.categoryID and category.ID;

is there an easy way to select only the categories that have associated items?


for instance:

if i have:
categories
----------
1. Category A
2. Category B
3. Category C
4. Category D
(1, 2, 3 and 4 being categories.id)

and

items
-----
1. Item A (items.categoryID = 1)
2. Item B (items.categoryID = 4)


can I, with just 1 select query, pull ONLY Category A (id = 1) and Category D (id = 4) to say, build a menu of categories, selecting only those categories that are "populated"?

in the past, how i've done this has been select ALL categories, then one at a time loop through the categories and do a SELECT statement for each ID and check for at least 1 result; then flag each category that has associated "stuff"; this is allright if the page you're building has to list all the products AND categories, but let's say I just want to build a menu of only available categories and not display any items (or not ALL the items) on a given page

any help is appreciated; let me know if that makes any sense...

Source: http://www.livejournal.com/community/mysql/61688.html

Date: 07/06/05     Keywords: php

I'm trying to figure out if this is possible. I have two tables with a one-to-many relationship. Table one is Projects. Table two is Investigators. There may be more than one investigator on a project. I'm looking for a way to perform a query (probably using a join) so that, instead of having:

Project 1 | Investigator 1
Project 1 | Investigator 2

I could get something like:

Project 1 | Investigator 1 | Investigator 2

I'm using PHP to run the query.

I've fiddled around with basic join syntax, but I can't get beyond the first setup. Is it possible, or just a pipedream?

Source: http://www.livejournal.com/community/mysql/61230.html

Date: 07/05/05     Keywords: mysql, sql, google

I find these files named:

[servername]-bin.001, .002, .003, etc in my mysql folder. They look like logs, is that what they are? How can I make mysql _not_ produce them? Any assistance or pointing in the right direction would be greatly appreciated. I did try to google it, but got nothing.

Source: http://www.livejournal.com/community/mysql/61051.html

Date: 06/29/05     Keywords: database, sql

Hi all.

I'm trying to move some tables from an old 2.5.3 database to my new 2.6.2 one and I'm experiencing some difficulties. I'm doing my usual method of exporting the old files to a text SQL file and then uploading the file to the new database, which has always worked for me in other situations, however, in this situation whenever I upload the file to the v2.6.2 database, it gives me an error message of "No SQL query".

Edit: never mind, I figured out what I had been doing wrong. I had been forgetting to check "Extended inserts" when exporting the database files. Problem fixed. :)

Source: http://www.livejournal.com/community/mysql/60761.html

Date: 06/28/05     Keywords: mysql, sql

I have 6 tables, all with a column called "projid" in them. I need to create a delete statement that will delete all rows in all columns where the projid is a specified number. I've read the MYSQL docs on this topic, but I'm still mightily confused. What I think I need is something like this:

DELETE FROM projecttable, collabtable, miletable, stafftable, benetable, invtable
WHERE projid=32

Source: http://www.livejournal.com/community/mysql/60657.html

Date: 06/20/05     Keywords: mysql, database, sql, microsoft

Ok see if you can wrap your head around this.. I've been hired as a contractor by this company that wants to A: Network all their computers, and B: Have all their customer data/information available to all employees on a database that can be easily read and updated.

I figured I had 2 options: Microsoft Access and MySQL. With Access, its a relatively easy interface to use and their database needs aren't very great, but I run into problems with sharing the database and its integrity (I've read that sharing an Access DB on a network is a big no-no) and redundant data. I've read about SPLITTING the database, but I don't know if that allows for easy updating of the database.

MySQL appears to be a powerful option that will allow for numerous concurrent users and seems to allow for easy data entry/updating without jeopardizing the integrity of the DB. On teh downside though, I know VERY little about how MySQL works, not to mention that I can't seem to find a decent user-frontend available to download and install on all the client machines so they can update/query the db themselves. I'm a decent programmer, but I don't know if I want to tell these guys that I'd have to WRITE my own frontend for them to use, especially since I don't think I'd be able to provide proper support for them..

So given all of this, what would u all suggest I do? I know you guys are probably more partial towards mySQL, but I don't want to put this company at risk just so I can do a test run of mySQL and how it works. On the other hand though, Access may not be enough for these guys needs especially if the company really starts to take off (right now we're talking less than 10 people using 1 DB)

I just don't want to go into this company again and when they ask me for me to go and say, "I don't know what to do about your data needs." I'd at least like to have a realistic view of our options.

Source: http://www.livejournal.com/community/mysql/60190.html

Date: 06/14/05     Keywords: php, mysql, sql

I've been wrestling with this query all afternoon. I've read the MySQL docs and got some help from the PHP LJ group, but now I'm totally stuck.

I have this query:

SELECT nuke_project_milestones.start_date, 
nuke_project_milestones.length_num, 
nuke_project_milestones.length_type, 
date_add(nuke_project_milestones.start_date, INTERVAL nuke_project_milestones.length_num nuke_project_milestones.length_type) 
AS estfinish FROM nuke_project_milestones 

Source: http://www.livejournal.com/community/mysql/59772.html

Date: 06/09/05     Keywords: mysql, sql, web

Hello all, I'm new to the list, and I want to jump right in and ask a stupid question: is there any way for me to set things up so I see user passwords unencrypted when I log in as root? I looked around on the mysql website but couldn't find any information. Any ideas?

Source: http://www.livejournal.com/community/mysql/59319.html

Date: 06/07/05     Keywords: mysql, sql, google

I have two tables. They both use the same user ids. I need to compare the tables and get all the rows in table A that aren't in table B. I suspect I need a JOIN. I have read the MYSQL.com stuff on joining, and I've tried to find other tutorials on this, but I'm not having much luck finding something that addresses the issue. If you have advice, links or at least good Google search terms, I'd be most appreciative.

Source: http://www.livejournal.com/community/mysql/58909.html

Date: 06/06/05     Keywords: mysql, sql

mysql> ALTER TABLE a CHANGE id id1 int(11);
ERROR 1171: All parts of a PRIMARY KEY must be NOT NULL; If you need NULL in a key, use UNIQUE instead
Column "id" is PRIMARY KEY, AUTOINCREMENT. How can I rename it?

Source: http://www.livejournal.com/community/mysql/58653.html

Date: 06/05/05     Keywords: php, mysql, sql

I'm hoping someone here can help me out, as I have a problem that is driving me crazy. I'm trying to get mysql to store and retrieve various bits of text in various languages, however, it's not entirely co-operating. As an example, I've stored part of the Russian alphabet (А Б В Г Д Е Ж З И Й К Л М Н О П) and I'm trying to retrieve it via a perl CGI script. I'm using PHPMyAdmin to store the strings, and it seems to think everything is fine - if I do a select on that record, it displays it properly, however if I try and display it using Perl, I get "? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?". I thought initially that it was a perl encoding thing - I've made sure that the content-type is set to utf-8 in both the HTTP header and the meta tag, but it seems to make no difference. I've also tried running it through the terminal as well as using a utf8-decode function, but I get the same result regardless. I'm beginning to wonder if it is mysql behaving oddly, though, as if I log into mysql through the terminal and select this record, I get "? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?" there as well - though for all I know, this could just mean that my terminal can't display Russian / UTF-8 characters! Does anyone have any ideas about how I can fix this or even if it's likely to be MySQL that's causing these issues or Perl?

Source: http://www.livejournal.com/community/mysql/58443.html

Date: 05/23/05     Keywords: php, mysql, database, sql

Hello! This is probably a painfully stupid question, but I'm asking anyway. I'm trying to select all records in the database where the start date (start, which is of type date, obviously) is between the user-supplied dates.

Entering select email from nexus where start between '2000-01-01' and '2005-05-01'; from the mysql prompt returns the correct information, but when I put it in a php script, it returns nothing. I've got other queries in this same script ("select email from nexus where ".$field." like '%".$term."%'") which are working as expected, so I'm pretty sure it's just that particular query.

It's probably something really stupid that I just don't see because I'm looking too hard, but I'm all out of ideas. If any of you kind folks have any insight, I'd really appreciate it. Thanks!

Source: http://www.livejournal.com/community/mysql/57350.html

Date: 05/19/05     Keywords: php, mysql, sql

I have a PHP script that was failing until I fixed the permissions. It was falling down on DELETE. The script's account in the mysql.user table had Delete_priv set to N. When it was changed to Y, the script ran.

However, Select_priv, Insert_priv, and Update_priv are also set to N, and the script has not had any problems running SELECT, INSERT and UPDATE queries. Why would this be happening?

Source: http://www.livejournal.com/community/mysql/57244.html

Date: 05/11/05     Keywords: mysql, sql

Hi everyone,

Am using MySQL 3.23.58... we have seen that it's possible to search any touple using the regular expression either using REGEXP or LIKE.

But i want to do something like searching by phonetics... like Spellchecking and closest word. Is there any method so that it can be done through SQL query only?

For Example:
Suppose if in a table 'items' the entry in 'item_name' field is 'COMPUTER' and if the searchable text is 'KOMPUTAR' then the syntax should find the table for the entry which pronounces like 'KOMPUTAR' and it should find 'COMPUTER'...

If there's not any SQL syntax then can it be done through REGEXP? if it's so then someone please give the REGEXP for this


Thank you.

Source: http://www.livejournal.com/community/mysql/57023.html

Date: 05/11/05     Keywords: php, mysql, sql, web

I've got a typical weblog system setup, which allows users to comment upon posts.

The comments are located in their own table which has a structure similar to this (actually it stores thinks like IP address, etc too):

CREATE TABLE comments (
  id int(11) NOT NULL default '0',
  article int(11) NOT NULL default '0',
  parent int(11) NOT NULL default '0',
  title varchar(65) default NULL,
  author varchar(25) NOT NULL default '',
  body text,
  PRIMARY KEY  (id,article)
);

This is pretty standard stuff - the comments are associated with a particler "article", and they may also be replies to other comments - in which case the "parent" will point to the parent comment id.

This structure allows there to be a nice nested tree of comments, in which some are "top level" because they have no parent set, and others are nested.

One problem I'm having is that with this structure is that the naive implementation for showing the comments is using recursion - and this is slowing down significantly as the size of the comments grows.

The pseudocode looks something like this:

#  $article is the article these comments are associated with
#  $parent is the parent of the children we want to see
#  $level  is increased when we go deeper into the tree,
#          used to display a nice indented tree
sub display_children($article, $parent, $level) 
{
   # retrieve all children of $parent
   $result = mysql_query('SELECT * FROM comments WHERE article=$article AND parent= $parent') ;

   # display each child
   while ($row = mysql_fetch_array($result)) 
   {
       # indent and display the title of this child
       echo str_repeat('  ',$level).$row['title']."\n";

       # call this function again to display this
       # child's children
       display_children($article,$row['id'], $level+1);
   }
}

# Display comments for article 40:
display_children( 40, 0, 0 );

(This is a pretty faithful representation in PHP, but the actual code is Perl, using DBI).

So now onto the question:

How can I speed this up, and avoid the recursion?

I've looked at CPAN for DBI::Tree, and other obvious candidates but I see nothing suitable.

I think I probably need to change the structure into something more efficient to display, but I admit my SQL-fu is weak.

Any suggestions appreciated.

Source: http://www.livejournal.com/community/mysql/56692.html

Date: 05/10/05     Keywords: mysql, sql

Does anyone have a mysql reporting tool that they like? A client of mine asked me about it and I’ve always used a homegrown one. We may continue to use my homegrown “reportmaker” but I thought it would be worth it to ask.

Source: http://www.livejournal.com/community/mysql/56347.html

Date: 05/10/05     Keywords: mysql, database, sql, web

I inherited a site with a mysql back-end. It was rather painful to get a understanding of what the heck the previous person was doing (it was not pretty). So adding to the db was ok, and making a slight modification here and there was ok. Now the pain came. The client wants to be able to search the database via part numbers (and then return the page it is on). I think to my self, hey no problem right?
bzzt wrong.
I have PartNumber, partnumber, quarter_partnumber and half_partnumber; also each product category is its own table in the database (for a total of ~45 tables). I have done some reading up on this and I believe a join could help.
now, should I bother with a huge join, try to search each table by its self (time wise very costly), or ?
let alone once I find the correct partnumber I have to then find a way to attach the webpages (a second db or a new field in each record).

my brain hurts with the stupid

Source: http://www.livejournal.com/community/mysql/56150.html