Date: 12/28/05     Keywords: no keywords

I want to merge these three queries into one query (and then add more) but I cant seem to wrap my head arround combining the first two.

ultimately I want to combine the 3 subtotals into one value.

Query1
SELECT invoice_number, sum(((TIME_TO_SEC(end_time) - TIME_TO_SEC(start_time))/3600) * rate) AS subtotal
FROM invoice_items
WHERE office_id='$office_id' AND service_type!='225'
GROUP BY invoice_number
ORDER BY invoice_number

Query2
SELECT invoice_number, sum((1) * rate) AS subtotal
FROM invoice_items
WHERE office_id='$office_id' AND service_type='225'
GROUP BY invoice_number
ORDER BY invoice_number

Query3
SELECT invoice_number, sum((mileage*mileage_rate)+groceries_value-discount_value) AS subtotal
FROM invoice_main
WHERE office_id='$office_id'
GROUP BY invoice_number
ORDER BY invoice_number

Source: http://community.livejournal.com/mysql/79721.html

Date: 12/27/05     Keywords: database

Hi everyone,
I am writing a program that uses zip code location information. I am having a really hard time finding a database for this information in a format that I need. Almost all of the databases I have found have the records like this:

Sample Records
"Key West","FL","33040","305","12087","Monroe","P",
"Stock Island","FL","33040","305","12087","Monroe","A",
"Raccoon Key","FL","33040","305","12087","Monroe","N",
"Agoura","CA","91376","818","06037","Los Angeles","A","P"

I think what I may be looking for is a list of states with unique IDs. A list of counties tied to those state IDs. A list of zip codes tied to those county IDs.

Am I wrong? The sample records dont seem to follow the rules for database normalization.

Any insite would be much appreciated.

cross posted.

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

Date: 12/27/05     Keywords: database

Hi everyone,
I am writing a program that uses zip code location information. I am having a really hard time finding a database for this information in a format that I need. Almost all of the databases I have found have the records like this:

Sample Records
"Key West","FL","33040","305","12087","Monroe","P",
"Stock Island","FL","33040","305","12087","Monroe","A",
"Raccoon Key","FL","33040","305","12087","Monroe","N",
"Agoura","CA","91376","818","06037","Los Angeles","A","P"

I think what I may be looking for is a list of states with unique IDs. A list of counties tied to those state IDs. A list of zip codes tied to those county IDs.

Am I wrong? The sample records dont seem to follow the rules for database normalization.

Any insite would be much appreciated.

cross posted.

Source: http://community.livejournal.com/mysql/79530.html

Date: 12/21/05     Keywords: no keywords

I have a col, cats, that contains data like "#Art, Music, & Books#Consumer Electronics#Cruises"

When I am selecting rows, I want to see if any elements in an array match a sub string in cats.

Sample array to match: UserCats = array('Cruises', 'Baby');

I guess what I want to do is convert cats to an array and then find out if it intersects with UserCats.

THX

Here is my current working query


SELECT
ad.userID,
ad.adID,
ad.adTitle,
ad.adBlurb,
ad.endDate,
ad.postingDate,
ad.paymentStatus,
loc.userAdID,
loc.streetAddress,
loc.city,
loc.zip,
loc.state,
u.vendorCompanyName,
u.phone

FROM fb_user AS u
INNER JOIN vendorAd AS ad ON u.userID = ad.userID
LEFT JOIN adLocation AS loc ON ad.userID = loc.userID AND ad.userAdID = loc.userAdID

WHERE ad.status = \"active\"
AND ad.paymentStatus =\"mickeymouse\"
AND ad.hideRecord =\"n\"
AND ad.endDate >= \"".$formattedToday."\"
AND (loc.zip IN($zips) OR loc.city IN($cities))

ORDER BY ad.endDate


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

Date: 12/21/05     Keywords: no keywords

I have a col, cats, that contains data like "#Art, Music, & Books#Consumer Electronics#Cruises"

When I am selecting rows, I want to see if any elements in an array match a sub string in cats.

Sample array to match: UserCats = array('Cruises', 'Baby');

I guess what I want to do is convert cats to an array and then find out if it intersects with UserCats.

THX

Here is my current working query


SELECT
ad.userID,
ad.adID,
ad.adTitle,
ad.adBlurb,
ad.endDate,
ad.postingDate,
ad.paymentStatus,
loc.userAdID,
loc.streetAddress,
loc.city,
loc.zip,
loc.state,
u.vendorCompanyName,
u.phone

FROM fb_user AS u
INNER JOIN vendorAd AS ad ON u.userID = ad.userID
LEFT JOIN adLocation AS loc ON ad.userID = loc.userID AND ad.userAdID = loc.userAdID

WHERE ad.status = \"active\"
AND ad.paymentStatus =\"mickeymouse\"
AND ad.hideRecord =\"n\"
AND ad.endDate >= \"".$formattedToday."\"
AND (loc.zip IN($zips) OR loc.city IN($cities))

ORDER BY ad.endDate


Source: http://community.livejournal.com/mysql/79325.html

Date: 12/19/05     Keywords: php

I need to write a query that looks for records that contain matching cities and zip codes to list.

Is there a way to search a string or array for a value like we can with PHP?


***
I dont want to match city and zip, I want to match city or zip.

So I would x items to look through the records.

Some kind of WHERE syntax that looks something like this: ...zip1 OR city1 OR zip1 OR city2...


Thanks!

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

Date: 12/19/05     Keywords: php

I need to write a query that looks for records that contain matching cities and zip codes to list.

Is there a way to search a string or array for a value like we can with PHP?


***
I dont want to match city and zip, I want to match city or zip.

So I would x items to look through the records.

Some kind of WHERE syntax that looks something like this: ...zip1 OR city1 OR zip1 OR city2...


Thanks!

Source: http://community.livejournal.com/mysql/78395.html

Date: 12/18/05     Keywords: database

I have a database table with 4 keyword fields. I guess they like to store up to 4 keywords against each entry. Unfortunately this makes processing quite difficult, as I'm constantly having to search each field (key1, key2, key3, key4).

Is there an easy way to merge these into a big conglomerate sort of list that would be easier to manipulate?

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

Date: 12/18/05     Keywords: database

I have a database table with 4 keyword fields. I guess they like to store up to 4 keywords against each entry. Unfortunately this makes processing quite difficult, as I'm constantly having to search each field (key1, key2, key3, key4).

Is there an easy way to merge these into a big conglomerate sort of list that would be easier to manipulate?

Source: http://community.livejournal.com/mysql/78275.html

Date: 12/18/05     Keywords: cms, php, sql

How do you make your site searches? I am constructing (yet another) CMS of late and am once again at the issue of searching. I am trying to build an SQL statement that will do a full text search of looking in several fields and then ranks each return so they can then be ordered by that before looping them out in php.

How do other people do this? have you found a particular way of doing this that you find works well. My current solution isn’t the most efficient method out there.

Thanks

X-posted to php

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

Date: 12/18/05     Keywords: cms, php, sql

How do you make your site searches? I am constructing (yet another) CMS of late and am once again at the issue of searching. I am trying to build an SQL statement that will do a full text search of looking in several fields and then ranks each return so they can then be ordered by that before looping them out in php.

How do other people do this? have you found a particular way of doing this that you find works well. My current solution isn’t the most efficient method out there.

Thanks

X-posted to php

Source: http://community.livejournal.com/mysql/78074.html

Date: 12/17/05     Keywords: no keywords

Help! I'm a bit confused over how to set a value to NULL.

If I have a string ('NULL') that I want to convert to NULL, is this correct?
UPDATE table SET column = NULL WHERE column = 'NULL'

What about if it's an empty string?
UPDATE table SET column = NULL WHERE column = ''

Sorry if this is terribly silly. TIA. :)

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

Date: 12/17/05     Keywords: no keywords

Help! I'm a bit confused over how to set a value to NULL.

If I have a string ('NULL') that I want to convert to NULL, is this correct?
UPDATE table SET column = NULL WHERE column = 'NULL'

What about if it's an empty string?
UPDATE table SET column = NULL WHERE column = ''

Sorry if this is terribly silly. TIA. :)

Source: http://community.livejournal.com/mysql/77690.html

Date: 12/17/05     Keywords: no keywords

how can i return all rows that have duplicate data in a particular field? in other words, if i have a table


id | f_name | l_name
1  | bob    | smith
2  | bob    | johnson
3  | tim    | richards
4  | jen    | richards
5  | ben    | orchard
6  | jen    | popple


and i'm interested in duplicate first names, i'd want my query to return rows 1,2,4, and 6. any suggestions on how to do that?

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

Date: 12/17/05     Keywords: no keywords

how can i return all rows that have duplicate data in a particular field? in other words, if i have a table


id | f_name | l_name
1  | bob    | smith
2  | bob    | johnson
3  | tim    | richards
4  | jen    | richards
5  | ben    | orchard
6  | jen    | popple


and i'm interested in duplicate first names, i'd want my query to return rows 1,2,4, and 6. any suggestions on how to do that?

Source: http://community.livejournal.com/mysql/77313.html

Date: 12/16/05     Keywords: mysql, sql

This is bound to be something simple I've overlooked (and besides, I'm new at this)...

First, the table. Created from a pre-existing spreadsheet for a community library so please forgive the non-optimum design...


mysql> create table books (
-> book_id mediumint unsigned not null auto_increment,
-> author1 varchar(255) not null,
-> author2 varchar(255) not null,
-> title varchar(255) not null,
-> subtitle varchar(255) not null,
-> subject1 varchar(255) not null,
-> subject2 varchar(255) not null,
-> subject3 varchar(255) not null,
-> subject4 varchar(255) not null,
-> publisher varchar(255) not null,
-> year smallint unsigned not null,
-> loc varchar(255) not null,
-> edition varchar(255) not null,
-> isbn varchar(255) not null,
-> location varchar(255) not null,
-> notes varchar(255) not null,
-> PRIMARY KEY (book_id)
-> );
Query OK, 0 rows affected (0.01 sec)


Then a data import (load data local infile "/home/lev/Desktop/test.csv" into table books;) - no problem.

Then adding a fairly large fulltext index i.e.,

mysql> alter table books add fulltext index (author1, author2, title, subtitle, subject1, subject2, subject3, subject4, publisher, location);

But a search, any search e.g.,

mysql> select * from books where match (author1,author2) against ('ali');
ERROR 1191 (HY000): Can't find FULLTEXT index matching the column list

help?

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

Date: 12/16/05     Keywords: mysql, sql

This is bound to be something simple I've overlooked (and besides, I'm new at this)...

First, the table. Created from a pre-existing spreadsheet for a community library so please forgive the non-optimum design...


mysql> create table books (
-> book_id mediumint unsigned not null auto_increment,
-> author1 varchar(255) not null,
-> author2 varchar(255) not null,
-> title varchar(255) not null,
-> subtitle varchar(255) not null,
-> subject1 varchar(255) not null,
-> subject2 varchar(255) not null,
-> subject3 varchar(255) not null,
-> subject4 varchar(255) not null,
-> publisher varchar(255) not null,
-> year smallint unsigned not null,
-> loc varchar(255) not null,
-> edition varchar(255) not null,
-> isbn varchar(255) not null,
-> location varchar(255) not null,
-> notes varchar(255) not null,
-> PRIMARY KEY (book_id)
-> );
Query OK, 0 rows affected (0.01 sec)


Then a data import (load data local infile "/home/lev/Desktop/test.csv" into table books;) - no problem.

Then adding a fairly large fulltext index i.e.,

mysql> alter table books add fulltext index (author1, author2, title, subtitle, subject1, subject2, subject3, subject4, publisher, location);

But a search, any search e.g.,

mysql> select * from books where match (author1,author2) against ('ali');
ERROR 1191 (HY000): Can't find FULLTEXT index matching the column list

help?

Source: http://community.livejournal.com/mysql/77082.html

Date: 12/15/05     Keywords: no keywords

When importing large chunks of data, what characters need escaped out?

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

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

SQL Query:
----------
SELECT grps.groupid, grps.title, grps.description, grps.approved, grps.create_date, grps.leaderid, user.username AS leadername, grps_categories.catid, grps_categories.title AS catname, grps_setting.image_name, grps_setting.private_posts, grps_setting.moderate_members, grps_setting.hidden_group, grps_setting.edit_details
FROM grps_setting
LEFT JOIN user ON (user.userid = grps.leaderid)
LEFT JOIN grps ON (grps.groupid = grps_setting.groupid)
LEFT JOIN grps_categories ON (grps_categories.catid = grps.catid)
WHERE grps.groupid = 1;


Error Message:
--------------
MySQL Error : Cross dependency found in OUTER JOIN. Examine your ON conditions
Error Number : 1120


My Question:
------------
where's the cross dependency and why does this cause the query to malfunction?

x-posted to php_mysql

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

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

I am trying to globally replace a character in mysql. Specifically, I would like to replace the string '#&39symbol#&39;' with the string '\'symbol\''. Al already know how to do the update/replace operation, but in this case that's not sufficient. The string could potentially appear in any field of any table, so it needs to be global. Does anyone know a way to do a global find and replace, or have a script already written to do this? I am willing to do it on my own if necessary, but I figure there's no point in reinventing the wheel.

Thanks,
Patrick

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