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 »

Kekekela: Oct 28, 2004

The pHo posted:

I see on edit you've put square brackets around some of the variables... what is that for? (Everything is working as I hoped so I presume it's just a 'good measures' thing?)

I think I'm now the proud owner of the webs sloppiest RSS automator. At least it validates!

The backticks confused me since I haven't used MySQL in a while so I switched to brackets to make it more clear the difference between the column identifier and the single quote. I would've just removed the identifiers entirely but it looked like some were needed since you had some column names that are also keywords.

Kekekela fucked around with this message at 23:02 on Sep 7, 2010

# ? Sep 7, 2010 21:53

Adbot: ADBOT LOVES YOU

# ? May 28, 2024 14:21

MoNsTeR: Jun 29, 2002

ShoulderDaemon posted:

I see what you're getting at, but the point is, you'd never actually see that. You'd instead see something like something.id = y.something_id so you still get the repetition of something.

And, of course, if your queries are actually confusing enough to be burdensome to developers, they should drat well be commented better. It's not like you get points for making the densest and most difficult to read queries you can get away with.

Going back to my earlier statement, that violates the "never mix aliases and full table names" rule which in my mind is even more important than how you name your columns. At any rate...

Comments are basically a red herring here. A query that joins 8 tables is inherently challenging to understand (different from "confusing") whether it's commented or not. If the joins are straightforward and your table names, aliases, and column names have been chosen well, comments have little to add. The point is certainly not to create "dense and...difficult to read queries", quite the opposite in fact, the goal is to make queries easy to read by giving them a common, rigid structure, above and beyond what is demanded by syntax. In service of this goal, consistency is ultimately more important than perfection.

# ? Sep 7, 2010 22:50

EL BROMANCE: Jun 10, 2006; COWABUNGA DUDES!
🥷🐢😬

McGlockenshire posted:

MySQL's default setting is to use the backtick to escape identifiers.

MSSQL's default setting is to use square brackets.

Kekekela posted:

The backticks confused me since I haven't used MySQL in a while so I switched to brackets to make it more clear the difference between the column identifier and the single quote. I would've just removed the identifiers entirely but it looked like some were needed since you had some column names that are also keywords.

Gotcha, I've never used MSSQL so didn't realise it differentiated keywords differently (and uses different keywords it seems).

I should use less generic names for my fields, I know

# ? Sep 7, 2010 23:44

Vanadium Dame: May 29, 2002; HELLO I WOULD LIKE TO TALK TO YOU ABOUT MY VERY STRONG OPINIONS

MoNsTeR posted:

Going back to my earlier statement, that violates the "never mix aliases and full table names" rule which in my mind is even more important than how you name your columns. At any rate...

Comments are basically a red herring here. A query that joins 8 tables is inherently challenging to understand (different from "confusing") whether it's commented or not. If the joins are straightforward and your table names, aliases, and column names have been chosen well, comments have little to add. The point is certainly not to create "dense and...difficult to read queries", quite the opposite in fact, the goal is to make queries easy to read by giving them a common, rigid structure, above and beyond what is demanded by syntax. In service of this goal, consistency is ultimately more important than perfection.

I'm going to back monster up here. The two of us interact with 2 groups of SQL users in our daily lives - fellow Oracle developers, and Analysts. Among our developer group we all use the same rules for aliasing and column/table naming, which makes it poo poo easy for any of us to read someone else's code at a glance. Seriously, it's hard to tell who wrote what sometimes because we adhere to a really rigid set of standards. Our analysts are not technologists, and often send us SQL with a horrific mishmash of aliases and arbitrary column names that don't conform to our stardards, which makes it painful to help them out with their code, even if their syntax is perfectly valid. So pretty much the more consistent your query dialect is, the better. You can mentally parse statements more easily and diagnose problems faster.

# ? Sep 9, 2010 01:55

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

They don't let me use nulls in our database because of the lead dev's OCD. Now, I can understand OCD, I hate wasting DB rows for example, but is there any *real* reason to have every column be created NOT NULL?

# ? Sep 9, 2010 15:39

butt dickus: Jul 7, 2007; top ten juiced up coaches
and the top ten juiced up players

Because NULL is different than '', and if you have both, you have to do things like

code:

WHERE column IS NOT NULL AND column <> ''

when you want an empty column. Rarely would you want both of these. Also if you make it not null, it will use the default value when not specified.

edit: AND not OR

butt dickus fucked around with this message at 15:55 on Sep 9, 2010

# ? Sep 9, 2010 15:52

Vanadium Dame: May 29, 2002; HELLO I WOULD LIKE TO TALK TO YOU ABOUT MY VERY STRONG OPINIONS

I'm not sure what rdbms you're using, but NOT NULL changes the way the Oracle optimizer works. http://jonathanlewis.wordpress.com/2010/09/05/not-null/

# ? Sep 9, 2010 15:58

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

Golbez posted:

They don't let me use nulls in our database because of the lead dev's OCD. Now, I can understand OCD, I hate wasting DB rows for example, but is there any *real* reason to have every column be created NOT NULL?

Semantically, NULL values mean "the value is not known". If you have columns where a value might genuinely be unknown, it makes sense to make them nullable.

Sometimes, it might be convenient to have NULL values correspond to some special meaning, like "this attribute doesn't apply to this row", or if a table has a foreign key and there need not be a corresponding value in a parent table.

So, NULL has valid uses. Most columns in a database should probably be NOT NULL, though.

# ? Sep 9, 2010 16:00

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

So, let's say an "Address2" field, the second line of an address (as much as I dislike such fields, an address belongs on one line dang it, unless you're separating out number and street into their own fields too in a postal DB) would be blank or null in the case it doesn't exist? Its value is known - their address only has one line, so the second line does not exist. In the past I would have checked this for null, but now you've got me realizing I was probably doing it wrong.

On the other hand, in my chat app where it uses an "operator_id" field to see which operator said a line, and is blank if the user said it (since there can be multiple operators but only one user), that would be a valid usage of null.

# ? Sep 9, 2010 17:19

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

McGlockenshire posted:

Really though, you don't need to escape most identifiers, just ones that could be mistaken for SQL keywords, like DESC and DATE, though most SQL parsers are smart enough to not screw it up.

A table I'm hitting every day is called [transaction]. I'd like to meet the guy who made that one... though of course the PK is called IDTrans but of course any table that stores it as a secondary calls it TransID... ugh

# ? Sep 9, 2010 18:45

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

Golbez posted:

So, let's say an "Address2" field, the second line of an address (as much as I dislike such fields, an address belongs on one line dang it, unless you're separating out number and street into their own fields too in a postal DB) would be blank or null in the case it doesn't exist? Its value is known - their address only has one line, so the second line does not exist. In the past I would have checked this for null, but now you've got me realizing I was probably doing it wrong.

Or alternatively you could take the view that you need 2 extra tables.

Let's say your Address and Address2 fields are in a table called Customer.

A Customer could have multiple addresses, so you make an extra table called Address, with a foreign key back to Customer. Now you put fields Address1, Address2, Address3 in table Address, right? You could do, or you could go the whole hog and make another table called AddressLine, with a foreign key back to Address.

Edit: An address probably should be at least one line long, so maybe you store the first line of the address in the Address table, and extra lines can go in the AddressLine table.

Hammerite fucked around with this message at 19:01 on Sep 9, 2010

# ? Sep 9, 2010 18:58

Fiend: Dec 2, 2001

Is it possible to pass a varchar value to a sproc in MSSQL2008 and have the sproc parse the values into a comma-separated list of int values?

I want to be able to pass a string that can be parsed to comma-separated ints as part of a WHERE-IN expression.

code:

CREATE PROCEDURE [dbo].[Ponyta_ble] @csvIDs VARCHAR(MAX)
AS
  SELECT pkid,
         col1,
         col1,
         col3
  FROM   table
  WHERE  pkid IN ( @csvIDs )

I've gone over the idea here, but the architecture I'm working with I can only specify SqlParameters and stored procedure names.

http://stackoverflow.com/questions/182060/sql-where-in-array-of-ids

Am I stuck using this?

code:

CREATE PROCEDURE [dbo].[Ponyta_ble] @csvIDs VARCHAR(MAX)
AS
  DECLARE @sqlStatement VARCHAR(MAX)

  SELECT @sqlStatement =
         'SELECT pkid, col1, col1, col3 FROM table WHERE pkid in (' +
                @csvIDs + ')'

  EXEC @sqlStatement

# ? Sep 9, 2010 20:38

Fiend: Dec 2, 2001

function to transform a varchar into a table of ints

code:

CREATE FUNCTION [dbo].[ParseIntCSV] (@IDs VARCHAR(MAX))
RETURNS @tempList TABLE (id INT)
AS
  BEGIN
      SET @IDs = @IDs + ','

      DECLARE @pos1 INT
      DECLARE @pos2 INT
      SET @pos1 = 1
      SET @pos2 = 1
      WHILE @pos1 < Len(@IDs)
        BEGIN
            SET @pos1 = Charindex(',', @IDs, @pos1)
            INSERT @tempList
            SELECT CAST(Substring(@IDs, @pos2, @pos1 - @pos2) AS INT)
            SET @pos2 = @pos1 + 1
            SET @pos1 = @pos1 + 1
        END
      RETURN
  END

a sample to implement:

code:

CREATE PROCEDURE [dbo].[Ponyta_ble] @csvIDs VARCHAR(MAX)
AS
  SELECT pkid,
         col1,
         col1,
         col3
  FROM   table
  WHERE  pkid IN (SELECT id
                  FROM   dbo.ParseIntCSV(@csvIDs))

a banana:

code:

 _                                          
//\                                         
V  \                                        
 \  \_         DON'T LOOK AT ME             
  \,'.`-.       I'M IRRELEVANT              
   |\ `. `.                                 
   ( \  `. `-.                        _,.-:\
    \ \   `.  `-._             __..--' ,-';/
     \ `.   `-.   `-..___..---'   _.--' ,'/ 
      `. `.    `-._        __..--'    ,' /  
        `. `-_     ``--..''       _.-' ,'   
          `-_ `-.___        __,--'   ,'     
             `-.__  `----"""    __.-'       
hh                `--..____..--'

# ? Sep 9, 2010 23:25

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Hammerite posted:

Or alternatively you could take the view that you need 2 extra tables.

Let's say your Address and Address2 fields are in a table called Customer.

A Customer could have multiple addresses, so you make an extra table called Address, with a foreign key back to Customer. Now you put fields Address1, Address2, Address3 in table Address, right? You could do, or you could go the whole hog and make another table called AddressLine, with a foreign key back to Address.

Edit: An address probably should be at least one line long, so maybe you store the first line of the address in the Address table, and extra lines can go in the AddressLine table.

Addresses should never have more than one line. :colbert:

# ? Sep 10, 2010 03:26

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

Golbez posted:

Addresses should never have more than one line.

My address has 10 lines and half of them are NULL and half of the rest have newline characters in the middle.

# ? Sep 10, 2010 04:15

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Hammerite posted:

My address has 10 lines and half of them are NULL and half of the rest have newline characters in the middle.

A properly formed American address needs a name, an address (everything on one line - apartment, suite, etc), a city, state, and zipcode. Building name optional.

The only time I can justify giving two lines to the user is if they are insistent on giving both a PO Box and a street address, which makes sense if they can't get US mail at their street address and UPS won't deliver to their post office. But in that case, make it clear which one you want.

The problem with giving multiple address lines is the first address line will cease to have semantic meaning - it could be an address, or it could be "Gelman Library", or it could be their backup PO Box, or it could be "Knock on door". Screw that. That's what a comment is for. Nothing should ever be mailed out to an address that hasn't been looked at by human eyes anyway, so.

Sometimes a building/firm is useful because of a specific ZIP+4 code, but that should be in a separate field, not shlomped in with 'Address'.

Same rules apply to most foreign addresses as well, though I'll allow multiple lines/textarea entry for those if a format isn't strictly known. Canadian addresses are aggressively one-liners, whereas I remember having some British addresses take up two lines, and Korean addresses even more. But on the other hand, if you're an American company, you should always spend extra time vetting foreign addresses.

Basically, my complaint isn't so much using multiple columns for an address; it's that there are always multiple "address" columns, i.e. the actual street address. There's no reason that my apartment should be expected on a second line, etc. One line for the address, one line for the building/form, one line for other instructions, etc. Keep the semantics. None of this "address1" "address2" stuff.

# ? Sep 10, 2010 14:37

MoNsTeR: Jun 29, 2002

Golbez posted:

There's no reason that my apartment should be expected on a second line, etc.

Well, yes there is, mainly that that's how 99% of people in the US write addresses, and how 99% of web forms for addresses are structured. Line 1 is street address, Line 2 is optionally for apartment/suite number etc. I'm a big advocate of obsessive hyper-normalization, but there are some places where the data structure meets reality and you just have to go with reality.

Addresses are actually a great example for another reason, namely zipcode. A zipcode exists in exactly one state, so your address data shouldn't even have a state field (and nor should your user form) since it's redundant. Zipcodes also partially determine your city, and if you stick to "preferred" records it fully determines your city, so your data and form shouldn't include city either. But how useful is that, really? Now you can't select from your address table and get useful data without joining to a zipcode lookup or invoking functions. And how big of a problem, honestly, is people entering cities and/or states that are inconsistent with their zipcode? So even though our instinct as good data architects is to Normalize Normalize Normalize!, doing so ends up producing a structure that is less usable, without compensating gains in data integrity.

# ? Sep 10, 2010 16:45

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

MoNsTeR posted:

Well, yes there is, mainly that that's how 99% of people in the US write addresses, and how 99% of web forms for addresses are structured. Line 1 is street address, Line 2 is optionally for apartment/suite number etc. I'm a big advocate of obsessive hyper-normalization, but there are some places where the data structure meets reality and you just have to go with reality.

They should learn to put apartment numbers on the same line as the address. Give them one line and they'll figure it out! Working in shipping for a few years has made me an addressing snob. :v:

quote:

Addresses are actually a great example for another reason, namely zipcode. A zipcode exists in exactly one state, so your address data shouldn't even have a state field (and nor should your user form) since it's redundant. Zipcodes also partially determine your city, and if you stick to "preferred" records it fully determines your city, so your data and form shouldn't include city either. But how useful is that, really? Now you can't select from your address table and get useful data without joining to a zipcode lookup or invoking functions. And how big of a problem, honestly, is people entering cities and/or states that are inconsistent with their zipcode? So even though our instinct as good data architects is to Normalize Normalize Normalize!, doing so ends up producing a structure that is less usable, without compensating gains in data integrity.

There's the rare situation where someone doesn't know their ZIP code, so entering it should be optional so long as someone enters both city and state. And yes, storing only the ZIP code would require some pretty complex (and outsourced) tables explaining which zip goes to which city, etc. Not to mention the fun times when there's multiple 'acceptable' city names for a ZIP code. Also, ZIP code boundaries can change, so it helps to have the other data, as a proper postal system will figure it out. And ZIP codes can be phased out; looking at usps.com, it looks like the post office finally retired 10048, which was formerly the location of 1-7 World Trade Ctr.

So it makes sense to store all three pieces of data, because the ZIP code is not immutable and is sometimes not available.

And if you were talking total normalization, you'd also store the street and address and apartment (and apartment type) separately, to more easily lookup the +4.

I still see no reason to give people two lines for an address. :colbert:

# ? Sep 10, 2010 17:06

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

An actual database question, in MySQL. I'm loading an Access table (via CSV) into PHP, then modifying some things and putting it into a MySQL table. I want to change it from this...

pre:

log_id
user_id
date
status

to simply get rid of log_id (currently the primary key) and make the other three fields the primary key, since, the way this is set up, there can never be more than one entry of a particular status change per user per day.

So, when I do change the key (through an ALTER TABLE command, rather than dealing with it in the CREATE, then INSERT), there's a duplicate record - so much for my previous statement, right? I chalk it up to Access being a dick about data, and I'm wondering, is there some easy way to fix this, or do I have to manually prune out the duplicate records?

# ? Sep 10, 2010 17:38

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

Golbez posted:

An actual database question, in MySQL. I'm loading an Access table (via CSV) into PHP, then modifying some things and putting it into a MySQL table. I want to change it from this...
pre:
log_id
user_id
date
status
to simply get rid of log_id (currently the primary key) and make the other three fields the primary key, since, the way this is set up, there can never be more than one entry of a particular status change per user per day.

So, when I do change the key (through an ALTER TABLE command, rather than dealing with it in the CREATE, then INSERT), there's a duplicate record - so much for my previous statement, right? I chalk it up to Access being a dick about data, and I'm wondering, is there some easy way to fix this, or do I have to manually prune out the duplicate records?

Do you have an idea of how arduous it would be to weed out the duplicates? Find out how many there are and if there are only like half a dozen, gently caress it, do it manually. I think this (untested) should work:

code:

SELECT
    GROUP_CONCAT(log_id) AS log_ids,
    user_id,
    date,
    status,
    COUNT(*) AS ct
FROM
    table
GROUP BY
    user_id,
    date,
    status
HAVING
    ct >= 2
LIMIT 25

# ? Sep 10, 2010 18:11

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Hammerite posted:

Do you have an idea of how arduous it would be to weed out the duplicates? Find out how many there are and if there are only like half a dozen, gently caress it, do it manually. I think this (untested) should work:
code:
SELECT
    GROUP_CONCAT(log_id) AS log_ids,
    user_id,
    date,
    status,
    COUNT(*) AS ct
FROM
    table
GROUP BY
    user_id,
    date,
    status
HAVING
    ct >= 2
LIMIT 25

I'm assuming only a half dozen, but the problem is, this is somewhat live data. I'm designing this transition based on a snapshot of the Access database from a week ago, but when it comes to the final transition, we've been trying to design it as fire-and-forget, one command one conversion, rather than have to take it down for extra time.

I'll look over that one; the idea that popped into my head was to basically wipe the table clean and replace it (via a temporary table) with the results of a SELECT DISTINCT user_id, date, status FROM whatever GROUP BY user_id, date, status.

# ? Sep 10, 2010 19:33

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

Actually, now that I think about it, couldn't you declare the three-column primary key to MySQL, leave the log_id column as indexed and auto_increment, then do the inserts using INSERT IGNORE and then drop log_id?

# ? Sep 10, 2010 19:38

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Hammerite posted:

Actually, now that I think about it, couldn't you declare the three-column primary key to MySQL, leave the log_id column as indexed and auto_increment, then do the inserts using INSERT IGNORE and then drop log_id?

Huh. That's simple enough that it might just work. I'll try it out when I touch that one next.

(Though, why even keep log_id around? Why not just never create it?)

# ? Sep 10, 2010 19:43

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

Golbez posted:

(Though, why even keep log_id around? Why not just never create it?)

Well, if you can eliminate it from the dump file too, go for it. I was just thinking that MySQL would complain about the extra column in the CSV that it doesn't know about.

# ? Sep 10, 2010 20:00

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Hammerite posted:

Well, if you can eliminate it from the dump file too, go for it. I was just thinking that MySQL would complain about the extra column in the CSV that it doesn't know about.

Oh. ... true. The things I forget when not looking smack at the code.

# ? Sep 10, 2010 20:08

butt dickus: Jul 7, 2007; top ten juiced up coaches
and the top ten juiced up players

In MySQL, is there a way to do UPDATE a row if it exists, or INSERT the row if not? I know I can do REPLACE, but that will blow away the existing row instead of inserting it. Right now I'm doing a SELECT to find out if it's there, and UPDATE if it is, INSERT if it isn't. It seems like this would be a fairly common thing to do.

# ? Sep 10, 2010 22:39

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

You could try using ON DUPLICATE KEY UPDATE.

# ? Sep 10, 2010 23:18

butt dickus: Jul 7, 2007; top ten juiced up coaches
and the top ten juiced up players

Hammerite posted:

You could try using ON DUPLICATE KEY UPDATE.

That's exactly what I needed. My boss, who has been writing queries since probably before I was born, didn't even know you could do this. Thanks!

# ? Sep 10, 2010 23:25

accipter: Sep 12, 2003

Are there any good concise references on important elements to database design?

# ? Sep 10, 2010 23:40

Aredna: Mar 17, 2007; Nap Ghost

Golbez posted:

An actual database question, in MySQL. I'm loading an Access table (via CSV) into PHP, then modifying some things and putting it into a MySQL table. I want to change it from this...
pre:
log_id
user_id
date
status
to simply get rid of log_id (currently the primary key) and make the other three fields the primary key, since, the way this is set up, there can never be more than one entry of a particular status change per user per day.

So, when I do change the key (through an ALTER TABLE command, rather than dealing with it in the CREATE, then INSERT), there's a duplicate record - so much for my previous statement, right? I chalk it up to Access being a dick about data, and I'm wondering, is there some easy way to fix this, or do I have to manually prune out the duplicate records?

If you can modify the Access database, create a new query that removes duplicates and doesn't include your log_id and export that to the CSV.

If you have to export the table as is, load it into a temp table first, then select only unique records from the temp table as part of your insert into for the final table.

# ? Sep 11, 2010 05:25

boating safety: Jan 3, 2001; Are you okay?

Wasn't sure wether to put this in the php or sql thread..

Here is what my mySQL table looks like:

name__0__1__2__3__4__5__6
user1__0__1__1__0__1__0__0
user2__1__1__0__1__0__1__0
user3__1__0__1__0__0__0__1

$var = $wpdb->get_var( "SELECT '4' FROM broken WHERE name='user1'" );

(using wordpress) returns nothing because of the 4 being a number.

Any help would be great, thanks!

# ? Sep 15, 2010 00:30

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

boating safety posted:

Wasn't sure wether to put this in the php or sql thread..

Here is what my mySQL table looks like:

name__0__1__2__3__4__5__6
user1__0__1__1__0__1__0__0
user2__1__1__0__1__0__1__0
user3__1__0__1__0__0__0__1

$var = $wpdb->get_var( "SELECT '4' FROM broken WHERE name='user1'" );

(using wordpress) returns nothing because of the 4 being a number.

Any help would be great, thanks!

When you put '4' in that means it will always return a string of '4' if your wanted your field you would want to do something like

$var = $wpdb->get_var( "SELECT broken.4 FROM broken WHERE name='user1'" );

Sprawl fucked around with this message at 00:41 on Sep 15, 2010

# ? Sep 15, 2010 00:36

boating safety: Jan 3, 2001; Are you okay?

Thanks for the response, I've been googling but have found nothing.

$var = $wpdb->get_var( "SELECT broken.4 FROM broken WHERE name='user1'" );

still returns nothing for me. If I rename 4 to 'test' in the table and then use broken.test, it works fine though.

# ? Sep 15, 2010 00:53

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

oh well i guess even then numbers are special even on tables

$var = $wpdb->get_var( "SELECT `broken`.`4` FROM broken WHERE name='user1'" );

Should work i think assuming its mysql like i think it is.

# ? Sep 15, 2010 00:56

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

Sprawl posted:

oh well i guess even then numbers are special even on tables

$var = $wpdb->get_var( "SELECT `broken`.`4` FROM broken WHERE name='user1'" );

Should work i think assuming its mysql like i think it is.

to clarify on this - this is because '4' is a sequence of characters that is presumed to have a meaning other than the name of a column, i.e. the literal integer 4. The same thing happens if you name a column after an SQL reserved word, like 'select' or 'where'. You can name columns anything you like, but if it might confuse the SQL interpreter you generally have to make it clear you're referring to a column name (or a table name, or whatever) using 'identifier quotes'. Most people will recommend using as table and column names sequences of alphanumeric characters (and underscores), starting with a letter.

Identifier quotes are backticks in MySQL by default, although in the SQL standard they are double-quotes (MySQL can be made to use double-quotes as identifier quotes as well).

# ? Sep 15, 2010 01:01

boating safety: Jan 3, 2001; Are you okay?

Hammerite posted:

Most people will recommend using as table and column names sequences of alphanumeric characters (and underscores), starting with a letter.

Didn't think about that.. that might work.. thanks!

Sprawl posted:

oh well i guess even then numbers are special even on tables

$var = $wpdb->get_var( "SELECT `broken`.`4` FROM broken WHERE name='user1'" );

Should work i think assuming its mysql like i think it is.

Yup it is mysql, although that didn't seem to work either, with a number or using 'test'.

# ? Sep 15, 2010 01:18

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

boating safety posted:

Didn't think about that.. that might work.. thanks!

Yup it is mysql, although that didn't seem to work either, with a number or using 'test'.

Then wordpress is doing something dumb i tested that on mysql 4 and 5 and it worked fine.

# ? Sep 15, 2010 01:23

mindphlux: Jan 8, 2004; by R. Guyovich

more dumb questions, this time really simple.

I have (in Access) a Store table that has a list of storeIDs and ownerIDs. storeID is a unique ID, but there are ownerIDs tied to 3, 4, or more storeIDs. I now need to track data that's tied to individual ownerIDs, and don't want to do a messy solution where I have redundant information.

So, I want to create a new Owner table that has two fields to track this information - ownerID and yes/no. the thought is that even if ownerID isn't unique in my Store table, it will be in my Owner table.

The problem is, I have an existing form to search and view all my storeIDs. I want to add a checkbox to this form so I can track the yes/no in my new table. If I bring up a store, using the ownerID field to tie the Store and Data table together, I want to be able to update/make an appropriate entry in the new (completely empty) Owners table.

So, I made the Owners table, put the checkbox on my form, and defined my relationship (many in Store to one in Owner), and was a bit surprised when the checkbox didn't work. I have a feeling I was being naieve. am I going about this all wrong? I'm going to stop typing now because I feel like I'm not making any sense. bbbbbbb

# ? Sep 15, 2010 01:52

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

mindphlux posted:

more dumb questions, this time really simple.

I have (in Access) a Store table that has a list of storeIDs and ownerIDs. storeID is a unique ID, but there are ownerIDs tied to 3, 4, or more storeIDs. I now need to track data that's tied to individual ownerIDs, and don't want to do a messy solution where I have redundant information.

So, I want to create a new Owner table that has two fields to track this information - ownerID and yes/no. the thought is that even if ownerID isn't unique in my Store table, it will be in my Owner table.

The problem is, I have an existing form to search and view all my storeIDs. I want to add a checkbox to this form so I can track the yes/no in my new table. If I bring up a store, using the ownerID field to tie the Store and Data table together, I want to be able to update/make an appropriate entry in the new (completely empty) Owners table.

So, I made the Owners table, put the checkbox on my form, and defined my relationship (many in Store to one in Owner), and was a bit surprised when the checkbox didn't work. I have a feeling I was being naieve. am I going about this all wrong? I'm going to stop typing now because I feel like I'm not making any sense. bbbbbbb

On the gui part did you rember to bind the checkbox to a datamember? Also what kind of field type did you store it as?

# ? Sep 15, 2010 02:45

Adbot: ADBOT LOVES YOU

# ? May 28, 2024 14:21

mindphlux: Jan 8, 2004; by R. Guyovich

Sprawl posted:

On the gui part did you rember to bind the checkbox to a datamember? Also what kind of field type did you store it as?

The checkbox's data source is the Y/N field in the Owner's table, so I think yes? The datatype for the Y/N is just access's dedicated Yes/No datatype. this sucks.

# ? Sep 15, 2010 18:21

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

«‹›303 »