SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

I have a change I'm planning to make to my database design but I'm not certain whether it meets any standard of normalisation or other general design principles.

Basically I have a table Game which contains the games my users are playing on my site. A game can be in one of three states: Recruiting, In Progress, or Finished. If it's recruiting then there are various options that can be set: what is the minimum number of players, what is the maximum number of players, whether the game is private and what its password is, etc. These details, obviously, are stored as columns in the database. But those columns are useless once the game has started. It is typical for there to be anything from 0 to 10 games recruiting at a time; and at the moment there are something like 3000 games overall in the other two states.

So I plan on adding a new table which describes settings for a game in the "recruiting" stage. Something like

code:

CREATE TABLE LobbySettings (
    GameID BIGINT(20) UNSIGNED NOT NULL,
    MinimumPlayers TINYINT(1) UNSIGNED NOT NULL,
    MaximumPlayers TINYINT(1) UNSIGNED NOT NULL,
    AnyPlayerStarts TINYINT(1) UNSIGNED NOT NULL,
    MinimumRating SMALLINT(4) NOT NULL,
    GPrivate TINYINT(1) UNSIGNED NOT NULL,
    Password VARCHAR(20),
    PRIMARY KEY(Game)
) CHARACTER SET utf8 COLLATE utf8_general_ci

At the moment all of my tables are MyISAM (no support for foreign keys). At some point I plan to change my tables to InnoDB at which point I'll add to this FOREIGN KEY(Game).

When a game starts, obviously, the corresponding row in LobbySettings should be deleted - manually for now, while I've no foreign keys.

Is this normal practice? Is there any reason not to do this?

# ? Jul 18, 2009 18:35

Adbot: ADBOT LOVES YOU

# ? May 28, 2024 04:07

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

If Game is your only noun, sounds pretty okay. Do you not have a bit or boolean data type? Also, might want to salt your password.

Triple Tech fucked around with this message at 21:01 on Jul 18, 2009

# ? Jul 18, 2009 19:45

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

Triple Tech posted:

If Game is your only noun, sounds pretty okay. Do you not have a bit[fixed] or [fixed]boolean data type? Also, might want to salt your password.

There is no Boolean data type in MySQL. You can specify BOOLEAN as the data type for a column, but it is just an alias for TINYINT. (one-byte integer)

The "password" is just a password someone can set in order to restrict access to a given game he has started. He sets a password and then gives it to everyone he wants to be able to join his game. I couldn't be bothered to add password encryption to this feature! Users are notified on the "create new game" screen that if they choose to give their game a password it won't be encrypted. Users have a password to log into their account, which of course IS encrypted and all that jazz.

# ? Jul 18, 2009 20:50

Acer Pilot: Feb 17, 2007; put the 'the' in therapist

Zoracle Zed posted:

Using the schema I suggested earlier, you could do:
code:
SELECT Integer 
FROM Integers AS i INNER JOIN Users_Integers  AS ui ON i.Integer = ui.Integer
WHERE i.Integer = ? AND i.Category = ? AND ui.UserHasSeenInteger = TRUE
It would save needless duplication of integer categories and would also avoid moving numbers back and forth between a seen and unseen table.

Sigh. That's clever and should save me some CPU cycles. Wish I thought of that.

Thanks.

# ? Jul 19, 2009 04:12

Falcon2001: Oct 10, 2004; Eat your hamburgers, Apollo.; Pillbug

So, I'm working on a fairly ridiculous side project which would be an item database for monster hunter similar to how wowhead is for wow, except much smaller in scale, and I'm not aiming for any sort of commercial success, just a proof of concept.

Why am I doing this? Because I have almost no database experience and would like to get some in a side project. It's how I learned html/css and a lot of other technology work, so I'm okay with it being a long irritating road full of new learning.

I'm currently using MySQL 5.1.x and Apache/PHP, since I've used apache before and after I get the database setup I'd like to muck around with php as the language.

Are there any good websites with a series of tutorials or reference guides that come recommended by folks? I realize I've grabbed a pretty huge project with a lot to bite off here.

Edit: starting this project out by setting up the database correctly so I'm just focusing on SQL right now.

Falcon2001 fucked around with this message at 10:07 on Jul 19, 2009

# ? Jul 19, 2009 09:40

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

Hammerite posted:

The reason I originally set some of my longer string type columns to TEXT was because I had the impression (I do not know where from) that the maximum size you could give to a VARCHAR column was 255, as in MessageText VARCHAR(255). Reading http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html I see that this impression was erroneous. Do you suggest (say) MessageText VARCHAR(10000) or similar, with the limit being some large number I don't want messages to exceed?

I know this is from a few weeks ago, but I just tried

SELECT MessageText FROM Message PROCEDURE ANALYZE()

and the "Optimal_fieldtype" was given as TEXT NOT NULL. So MySQL seems to reckon it would be best to keep the column as TEXT. Any good reason to disregard its advice? (I have set a limit of 50,000 characters for the column at the application level.)

# ? Jul 22, 2009 22:18

Acer Pilot: Feb 17, 2007; put the 'the' in therapist

How can I do a LEFT JOIN with a where on the right and still include every value of the left table?

SELECT i.`integer`, ui.`seen` FROM `integers` AS i LEFT JOIN `user_integers` AS ui ON (i.`integer` = ui.`integer`) WHERE i.`category` = 1 AND ui.`user_id` = 1

this results in something like this:

code:

integer seen
1       1
2       1
3       1

but I want it to display everything regardless of the seen.

code:

integer seen
1       1
2       1
3       1
4       null
5       null
6       null

The tables are built like this:

code:

integers;
integer category
1       1
2       1
3       1
4       1
5       1
6       1
7       2
8       2

user_integers;
id user_id integer seen
1  1       1       1
1  1       2       1
1  1       3       1
1  2       2       1
1  2       1       1

Acer Pilot fucked around with this message at 21:49 on Jul 25, 2009

# ? Jul 25, 2009 21:46

Sneaking Mission: Nov 11, 2008

You can put that condition in the join clause

code:

select i.integer, ui.seen
from integers as i
left join user_integers as ui
  on i.integer = ui.integer
  and ui.user_id = 1
where i.category = 1

# ? Jul 25, 2009 21:49

Acer Pilot: Feb 17, 2007; put the 'the' in therapist

Thank you so much. I love you man, and that's the kind of love that is forbidden.

# ? Jul 25, 2009 21:53

Carthag Tuek: Oct 15, 2005; Tider skal komme,
tider skal henrulle,
sl�gt skal f�lge sl�gters gang

Is there an easy way to apply a bit mask in Postgres?

Say I have a column called type which contains an integer and I want my output to show the different bits in their own columns:

code:

SELECT
	id,
	mask(type, 1) AS first,
	mask(type, 2) AS second,
	mask(type, 4) AS third,
	mask(type, 8) AS fourth
	--etc
FROM
...

Or do I have to make one myself?

Edit: Nevermind, I can do this: type::bit(10) & 1::bit(10) AS first etc

Carthag Tuek fucked around with this message at 12:01 on Jul 28, 2009

# ? Jul 28, 2009 11:45

ether: May 20, 2001

Mercator posted:

I have a row in my MySQL table that I want to duplicate into the same table. I could do this with INSERT INTO :table SELECT * FROM :table WHERE id=:id, but since one on the columns is an auto-increment mysql balks at me for trying to have 2 rows with the same id.

Is there a real easy way to exclude/ignore the auto-increment column, or do I have to read in all column names and filter out the auto-incrementing ones?

From the top of my we usually approached it like this (assuming id is the only auto-inc column)

code:

CREATE TEMP TABLE tmpResult 
  SELECT * FROM :table WHERE id=:id;

UPDATE tmpResult SET id = NULL;

INSERT INTO :table SELECT * FROM tmpResult;

Convoluted as well, no longer a single statement but easier on the eyes compared to concatting all the column-names in there. Worked on virtue of id not being an autoinc field in the tmpResult table.

# ? Jul 28, 2009 14:37

Metonymy: Aug 31, 2005

I have a quick question about general database design and aggregate statistics. Sort of a OLAP/OLTP best practices issue.

Lets say I'm a service like YouTube and I've got videos that fall into three categories: Sports, News, and Entertainment. Users can rate the videos on a scale from 1 to 5.

If I want to present summary statistics (total number of views, avg rating, etc.) for both individual videos and the categories, does it make sense to add columns for those summary statistics (or a normalized summary table) rather than making constant calls to aggregate functions in SQL?

If so, would you maintain those summary columns with a scheduled job (cron or in the sql server), or simply with each transaction (as users watch and rate videos, update the column accordingly)?

Bonus follow-up: lets say you then want to display summary statistics for a category or a user, but limit the summary stats to a dynamic sub-group of users. That seems like a good time to use aggregate functions, rather than a column... no?

# ? Jul 29, 2009 18:31

Begby: Apr 7, 2005; Light saber? Check. Black boots? Check. Codpiece? Check. He's more machine than kid now.

Metonymy posted:

I have a quick question about general database design and aggregate statistics. Sort of a OLAP/OLTP best practices issue.

Lets say I'm a service like YouTube and I've got videos that fall into three categories: Sports, News, and Entertainment. Users can rate the videos on a scale from 1 to 5.

If I want to present summary statistics (total number of views, avg rating, etc.) for both individual videos and the categories, does it make sense to add columns for those summary statistics (or a normalized summary table) rather than making constant calls to aggregate functions in SQL?

If so, would you maintain those summary columns with a scheduled job (cron or in the sql server), or simply with each transaction (as users watch and rate videos, update the column accordingly)?

Bonus follow-up: lets say you then want to display summary statistics for a category or a user, but limit the summary stats to a dynamic sub-group of users. That seems like a good time to use aggregate functions, rather than a column... no?

The basic rule of thumb is that if it can be calculated from the data in the tables, then there is no reason to store it in there. For instance, if you have someones birthdate stored in there, you don't want or need to store the age. I think it actual breaks 3rd normal form since some of the fields are no longer dependent upon the entity key, but rather other pieces of data.

Also, the solutions you are mentioning to aggregate the data, make it so the database no longer stands on its own, its not complete without these outside events happening which adds unnecessary complexity.

The only scenario where I thinks this makes sense is if your database is getting hammered and you're queries are slowing down. In that case it still would make more sense to do some sort of result caching instead.

# ? Jul 29, 2009 18:44

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

I think if you're performance needs are getting out of hand, that's where OLAP steps in. Doing aggregates on the fly provides the most update-to-date form of the data at the cost of speed. I guess if you don't mind getting slightly stale statistics ("good enough"), you can get them at a blazing speed via an OLAP setup.

I'm not totally sure, this is all new to me and I'm actually starving for OLAP literature now. I think it describes exactly what my company does but clearly we have missed the train.

# ? Jul 29, 2009 20:09

Metonymy: Aug 31, 2005

Triple Tech posted:

I think if you're performance needs are getting out of hand, that's where OLAP steps in. Doing aggregates on the fly provides the most update-to-date form of the data at the cost of speed. I guess if you don't mind getting slightly stale statistics ("good enough"), you can get them at a blazing speed via an OLAP setup.

I'm not totally sure, this is all new to me and I'm actually starving for OLAP literature now. I think it describes exactly what my company does but clearly we have missed the train.

Yeah, this is where I was heading with it. I mean, this is the whole reason there's a distinction between OLAP and OLTP - even if the data is present in the transactional model and beautifully normalized, the latency involved in aggregating it on the fly could get out of hand quickly.

Does anyone know of any good online reading on the subject?

Or on scalability in general?

# ? Jul 29, 2009 22:21

var1ety: Jul 26, 2004

Oracle has a Data Warehousing Guide that talks about concepts and ways to solve problems in very large databases. The concepts are talked about in mostly broad, database independent terms.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/toc.htm

Talking about specific problems and solutions, I had a problem recently where a certain query with some computed columns and complex joins took 8-15 seconds to run, which was unacceptable. Oracle offers "Materialized Views", which let you store precomputed results that can be kept up to date by the database either after commits or on a schedule (or manually) in a write-few read-many environment.

These results can then be transparently used by existing queries to speed them up ("select * from complex_join" will be rewritten in the background to use materialized_complex_join). Adding a materialized view in took the query down to a fraction of a second, with a large reduction in the required system resources.

# ? Jul 29, 2009 23:00

Safety Shaun: Oct 20, 2004; the INTERNET!!!1

Sorry to lower the tone but how do I make the following query

code:

SELECT link_title,link_desc,link_slug,link_submittedby,link_added,count( links_log.links_logid ) as hits
FROM links_links,links_log
WHERE links_links.link_slug = links_log.links_slug
ORDER BY `links_links`.`link_added`  DESC

return all results, even if there aren't any entries in links_log that match on links_slug?

# ? Jul 30, 2009 03:14

supster: Sep 26, 2003; I'M TOO FUCKING STUPID
TO READ A SIMPLE GRAPH

Safety Shaun posted:

Sorry to lower the tone but how do I make the following query
code:
SELECT link_title,link_desc,link_slug,link_submittedby,link_added,count( links_log.links_logid ) as hits
FROM links_links,links_log
WHERE links_links.link_slug = links_log.links_slug
ORDER BY `links_links`.`link_added`  DESC
return all results, even if there aren't any entries in links_log that match on links_slug?

I think you're looking for something like this:

code:

select
    link_title,
    link_desc,
    link_slug,
    link_submittedby,
    link_added,
    count(links_log.links_slug) as hits
from
    links_links
left join
    links_log on links_log.links_slug = links_links.link_slug
group by
    links_links.link_slug
order by
    links_links.link_added desc

ps your table and column names suck

supster fucked around with this message at 04:08 on Jul 30, 2009

# ? Jul 30, 2009 04:05

Civil: Apr 21, 2003; Do you see this? This means "Have a nice day".

I've got a fun little problem here. Using mysql.

I have 2 tables:

code:

Table:Project
Proj            Ad Date            City
------------------------------------------------
1-456        2009-10-23         LA
2-467        2009-09-10         San Jose
4-678        2009-09-15         Petaluma
6-784        2009-10-02         Rocklin

Table:Bidders
Bidder               Proj
---------------------------------------
Joe                   1-456
Joe                   6-784
Jim                   1-456
Fred                  1-456
Fred                  2-467
Dudley                6-784

I'll normally select a single project at a time, very simple.

SELECT Proj, Ad Date, City FROM Project WHERE Proj = '$proj'

And I'll get the expected output.

I can also have a logged in user via a cookie. That user will now be $user, and if they're part of the Bidders table for that project, I'd like it indicated. If they're not logged in or logged in as someone else, I'd just like a blank or NULL result.

Therefore, if Dudley is logged in, and requests project 1-456, he'll get this as the record return.

code:

Proj            Ad Date         City          Bidder
--------------------------------------------------------
1-456        2009-10-23         LA            <blank>

If Fred is logged in and asks to see project 2-467, he'll see this.

code:

Proj            Ad Date         City          Bidder
--------------------------------------------------------
2-467        2009-09-23         San Jose      Fred

I can do this easy with 2 queries, but I'm trying to squeeze it to one. I can't figure out a join that allows this (I only want 1 record returned). How would I do this?

# ? Jul 30, 2009 04:09

Safety Shaun: Oct 20, 2004; the INTERNET!!!1

supster posted:

I think you're looking for something like this:
<<snip>>
ps your table and column names suck

Thank you very much. I'm helping a friend and I got stuck on what I thought seemed a simple enough query.

# ? Jul 30, 2009 04:12

Zoracle Zed: Jul 10, 2001

Civil posted:

90% of the time, when you want "something if X, otherwise null", you're looking for an outer join.

In your case,

code:

SELECT Proj, Ad Date, City 
FROM Project LEFT OUTER JOIN Bidders ON Project.Proj = Bidders.Proj
WHERE Project.Proj = '$proj' AND Bidders.Bidder = '$user'

# ? Jul 30, 2009 07:54

Squashy Nipples: Aug 18, 2007

There are times when weekly and/or monthly summary tables make a lot of sense. My Datamart goes down every Monday at 8:00PM for a few hours of maintenance, and this is when the summary scripts run.

Here is a good example of when to use this: every quarter we produce executive summaries for our clients, full of data about their 401K plans. It takes about 2-3 minutes to produce a PowerPoint book with all of the data in it, and we run all of the clients in a big batch.

Most of the Plan-level and Client-level data is calculated on the fly, even though it could be in a summary table. However, all of the data that deals with individual participants and/or transactions all come from a summary table, becuase these calculations involve tables with millions of rows.

# ? Jul 30, 2009 16:30

8ender: Sep 24, 2003; clown is watching you sleep

var1ety posted:

Oracle offers "Materialized Views", which let you store precomputed results that can be kept up to date by the database either after commits or on a schedule (or manually) in a write-few read-many environment.

We use this for a few complex queries in our database and its like magic. Works really well in particular for our web applications where we might need to do a whole lot of aggregate work very quickly for something like a Google Maps mashup or an interactive pie chart.

# ? Jul 30, 2009 18:20

Civil: Apr 21, 2003; Do you see this? This means "Have a nice day".

Zoracle Zed posted:

90% of the time, when you want "something if X, otherwise null", you're looking for an outer join.

In your case,
code:
SELECT Proj, Ad Date, City 
FROM Project LEFT OUTER JOIN Bidders ON Project.Proj = Bidders.Proj
WHERE Project.Proj = '$proj' AND Bidders.Bidder = '$user'

Won't this fail completely if there is no match for $user?

# ? Jul 30, 2009 20:47

Metonymy: Aug 31, 2005

Civil posted:

Won't this fail completely if there is no match for $user?

Yes. Well, it won't fail, but it won't return any results in the 'Dudley' case. To get the null behavior duder was looking for, he's probably going to want to put the bidder matching clause on the join, rather than the where.

code:

SELECT Proj, Ad Date, City 
FROM Project LEFT OUTER JOIN Bidders ON Project.Proj = Bidders.Proj AND Bidders.Bidder = '$user'
WHERE Project.Proj = '$proj'

# ? Jul 30, 2009 20:56

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

Definitely looks like someone needs a lesson on bind parameters...

# ? Jul 30, 2009 21:40

Zoracle Zed: Jul 10, 2001

Civil posted:

Won't this fail completely if there is no match for $user?

arrgggg sorry i was up way too late last night

# ? Jul 30, 2009 22:13

brewkakke: Aug 1, 2009; by Fistgrrl

Maybe someone can help...

I'm looking for a way or a front end that I can run a fairly simple relational database that contains client information and case information (already have this taken care of) and can also say.... contain the location of their .pdf forms on my hard drive and give me a way to pull up all the info and a way to get to their forms. It seems to me that there would be a way to point to the file on my hard drive and let me open it with the required program when I pull up the info.

Does this make sense or am I forced to contain it as a blob?

I've tried using Open Office Base as a front end to mySQL and it was awful, and now I'm dicking around with a program called Alpha Five and it's turning into a nightmare as well.

# ? Aug 3, 2009 03:29

Carthag Tuek: Oct 15, 2005; Tider skal komme,
tider skal henrulle,
sl�gt skal f�lge sl�gters gang

brewkakke posted:

Maybe someone can help...

I'm looking for a way or a front end that I can run a fairly simple relational database that contains client information and case information (already have this taken care of) and can also say.... contain the location of their .pdf forms on my hard drive and give me a way to pull up all the info and a way to get to their forms. It seems to me that there would be a way to point to the file on my hard drive and let me open it with the required program when I pull up the info.

Does this make sense or am I forced to contain it as a blob?

I've tried using Open Office Base as a front end to mySQL and it was awful, and now I'm dicking around with a program called Alpha Five and it's turning into a nightmare as well.

You could save the path and have the frontend just open it with whatever file-system call works on your system. On OS X it would just be "open /path/to/file.pdf" so you'd have the path-part in the DB.

# ? Aug 3, 2009 15:46

Squashy Nipples: Aug 18, 2007

Yeah, I was thinking that the solution would have more to do with your front end then the DB... if your link or server location is just a text string, then you can store it any way you want.

I have a similar system here for the phone reps. Fund Fact Sheets for all of the 401k plans are kept in PDF files and updated quarterly, but they are always in the same place on the intranet. All the DB stores is an intranet link, the front end does the work of invoking the PDF viewer and loading the link.

# ? Aug 3, 2009 16:01

brewkakke: Aug 1, 2009; by Fistgrrl

Carthag posted:

You could save the path and have the frontend just open it with whatever file-system call works on your system. On OS X it would just be "open /path/to/file.pdf" so you'd have the path-part in the DB.

Dr.Khron posted:

Yeah, I was thinking that the solution would have more to do with your front end then the DB... if your link or server location is just a text string, then you can store it any way you want.

I have a similar system here for the phone reps. Fund Fact Sheets for all of the 401k plans are kept in PDF files and updated quarterly, but they are always in the same place on the intranet. All the DB stores is an intranet link, the front end does the work of invoking the PDF viewer and loading the link.

This is what I was thinking, just saving the path as a string. I couldn't see why a program couldn't pull that info from the DB and not have a function to call on that path.

I need a front end to open the thing though. Other than mySQL, I don't know any languages and I can't write my own GUI, but if there's something out there that is fairly simple then I'd be happy to use it. I'm not creating anything super complex here and I'm not using mySQL for web applications.

# ? Aug 3, 2009 18:02

LP0 ON FIRE: Jan 25, 2006; _{beep boop}

I'm so close to have something working, but I'm trying to search for a product name ignoring spaces for what it's using to search for. I just replaced it with the letter "a" just see if I could get it to work at all. I know there's products that start with "a" so that shouldn't be an issue.

I've tried all sorts of combinations:

php:

<?
$result = mysql_query("SELECT REPLACE(prodName, ' ','') AS temp_name * FROM products WHERE temp_name LIKE 'a%'"); 

$result = mysql_query("SELECT  * FROM products REPLACE(prodName, ' ','') AS temp_name WHERE temp_name LIKE 'a%'"); 

$result = mysql_query("SELECT  * REPLACE(prodName, ' ','') AS temp_name FROM products WHERE temp_name LIKE 'a%'"); 

?>

Eventually I want to ignore spaces, case and characters that aren't 0-9 and a-z.

# ? Aug 3, 2009 18:07

McGlockenshire: Dec 16, 2005; GOLLOCKS!

awdio posted:

Eventually I want to ignore spaces, case and characters that aren't 0-9 and a-z.

Well, this should ignore spaces:

code:

SELECT prodName FROM products WHERE REPLACE(' ', '', prodName) LIKE '%stuff%'

But for the rest, you'll probably just want to create an entire new column that contains the search-safe data you're looking for. Beware that this entire thing is a Bad Idea(tm) and is very likely to produce bogus search results that will confuse users.

# ? Aug 3, 2009 18:44

LP0 ON FIRE: Jan 25, 2006; _{beep boop}

Thanks for the fast help! Are you sure you got that right? I tried both a% and %a% with no results.

edit: I tried this $result = mysql_query("SELECT prodName FROM products WHERE REPLACE(prodName, ' ', '') LIKE 'a%'"); and it works but the product names without the spaces won't bring back results because it's comparing against something that has characters that aren't only 0-9 and a-z.

LP0 ON FIRE fucked around with this message at 19:17 on Aug 3, 2009

# ? Aug 3, 2009 18:56

Carthag Tuek: Oct 15, 2005; Tider skal komme,
tider skal henrulle,
sl�gt skal f�lge sl�gters gang

LIKE is case sensitive (at least in postgres), could that be the problem? ILIKE would be the case insensitive version.

# ? Aug 3, 2009 20:28

McGlockenshire: Dec 16, 2005; GOLLOCKS!

MySQL uses collations when thinking about case sensitivity. The default collation is case insensitive.

Can we have a sample of a few real product names, and a sample of a real search string?

# ? Aug 3, 2009 20:38

LP0 ON FIRE: Jan 25, 2006; _{beep boop}

McGlockenshire posted:

MySQL uses collations when thinking about case sensitivity. The default collation is case insensitive.

Can we have a sample of a few real product names, and a sample of a real search string?

Sure. It's good to know about ILIKE, but I know the real problem is that when it's searching for the product names it has characters that would not be in the search.

Example:

Search: adamp22apair

needs the result of:

ADAM : P22A (Pair)

All it needs to do is ignore case, get rid of spaces and any characters that aren't 0-9 and a-z.

# ? Aug 3, 2009 20:43

McGlockenshire: Dec 16, 2005; GOLLOCKS!

Who or what is providing that search term?

I'm gonna fall back to my previous suggestion of just adding a column explicitly for the condensed search terms and populating it programatically. There's no way in SQL to replace ranges of characters, regex-style. Not without a REPLACE() chain a few dozen layers deep, and that's just stupid.

# ? Aug 3, 2009 21:17

LP0 ON FIRE: Jan 25, 2006; _{beep boop}

McGlockenshire posted:

Who or what is providing that search term?

I'm gonna fall back to my previous suggestion of just adding a column explicitly for the condensed search terms and populating it programatically. There's no way in SQL to replace ranges of characters, regex-style. Not without a REPLACE() chain a few dozen layers deep, and that's just stupid.

The search term needs to be added to the same table in the correct row as the product name. It's problem solving for a company that needs to have their image filenames go with the correct products. Luckily enough, the image names are very similar aside from not having spaces or special characters. It's going to be a giant query that will just add the filenames to the correct product.

# ? Aug 3, 2009 22:13

Adbot: ADBOT LOVES YOU

# ? May 28, 2024 04:07

Begby: Apr 7, 2005; Light saber? Check. Black boots? Check. Codpiece? Check. He's more machine than kid now.

awdio posted:

The search term needs to be added to the same table in the correct row as the product name. It's problem solving for a company that needs to have their image filenames go with the correct products. Luckily enough, the image names are very similar aside from not having spaces or special characters. It's going to be a giant query that will just add the filenames to the correct product.

What about writing a script that will go through each record, get rid of the special characters, then search for the filename in the file system. If it exists it will update the record, if not it will write to a log. After its all done, assuming most of your names match as you say, 99% of them will be done. Then all you have to do is go through the log and take care of the missing ones.

Looping through your records will mean only one query to grab all the records and one update for each found file (vs. doing a cumbersome sql search for every file, then an update). Then you can use regular expressions or whatever in a language instead of dicking around with MySQL replace.

# ? Aug 4, 2009 14:27

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »