Register a SA Forums Account here!
JOINING THE SA FORUMS WILL REMOVE THIS BIG AD, THE ANNOYING UNDERLINED ADS, AND STUPID INTERSTITIAL ADS!!!

You can: log in, read the tech support FAQ, or request your lost password. This dumb message (and those ads) will appear on every screen until you register! Get rid of this crap by registering your own SA Forums Account and joining roughly 150,000 Goons, for the one-time price of $9.95! We charge money because it costs us money per month for bills, and since we don't believe in showing ads to our users, we try to make the money back through forum registrations.
 
  • Post
  • Reply
Victor
Jun 18, 2004

pig-fukr posted:

They have. In 2008 you get table-valued parameters that can be passed around, provided you're willing to strongly type them and read-only access will do what you need:

http://blogs.techrepublic.com.com/datacenter/?p=168
Sick! Is there some place on the net where all the features of 2008 (new over 2005/2000) are described, and described well? I've seen several overviews of what is coming in 2008 (like intellisense, yay!), but I haven't yet seen the above. I just glanced at 2008 Books Online and it does not seem conducive to learning about the new features.

Adbot
ADBOT LOVES YOU

pig-fukr
Nov 3, 2002

Victor posted:

Sick! Is there some place on the net where all the features of 2008 (new over 2005/2000) are described, and described well? I've seen several overviews of what is coming in 2008 (like intellisense, yay!), but I haven't yet seen the above. I just glanced at 2008 Books Online and it does not seem conducive to learning about the new features.


Most of the stuff I've found that was actually useful for learning how to REALLY use features have been by googling the specific feature you want to work with. There just doesn't seem to be a good central repository at the moment.

This isn't a bad starting point to get a little overview. If you click on the main category icons it will launch into presentations about what's new in each deal. You can also click on each individual item to get more information. This is what's available for table-valued parameters.

The main CTP page can be found Here if you don't already frequent it.

I think table-valued parameters and the addition of a spatial engine are going to be the biggest draws for my purposes. The other stuff is nice, but those are items I could use a daily basis.

pig-fukr fucked around with this message at 22:38 on Nov 7, 2007

Xae
Jan 19, 2005

chocojosh posted:

replace(varchar_variable, '''', '''''')


Is that the *ONLY* thing I need to do to avoid SQL injection? Can you provide me with a link (not that I don't trust you, but I'd rather not leave security holes in my software due to my sloppyness)? If so, I may just use one long exec string instead of the table variable.

You would need to check for ';' as well, since they might try to submit a compound statement. ie "Select * from sys;Drop X;"

Victor
Jun 18, 2004

chocojosh posted:

replace(varchar_variable, '''', '''''')


Is that the *ONLY* thing I need to do to avoid SQL injection? Can you provide me with a link (not that I don't trust you, but I'd rather not leave security holes in my software due to my sloppyness)? If so, I may just use one long exec string instead of the table variable.
If you do that, there is no way I know of that one could get a single quote inside a string. As far as I know, that is the only way one can terminate a string. I don't have any papers or whatnot to back this up, but I haven't searched for them either. The only SQL injection exploits I've heard about [0] are from terminating quoted values, either by putting a single quote in there or by screwing with unicode characters in such a way as to fool the character conversion routines [1] into converting a non-single-quote-character into a single-quote-character after the replace operation above. As far as I know, this cannot happen inside sprocs (any possibly-insecure character conversions have already been done by the time the replace operation is called). It isn't a problem in .NET code AFAIK, because System.String is always represented in UTF-8.

[0] assuming that anything that doesn't go through the replace function above is strongly typed into something other than a string, e.g. int, float, date, etc.
[1] that process the input from initial text input -> when the database query optimizer gets its hands on the query

Xae posted:

You would need to check for ';' as well, since they might try to submit a compound statement. ie "Select * from sys;Drop X;"
Wrong. If you cannot escape the single quotes, your text will only ever be interpreted as a literal.

jwnin
Aug 3, 2003
This is a bit of a different SQL question, but I thought I'd ask it here.

Our database stores information about our customers. In general, there are very few times where we need to do queries across the entire customer base - what happens at one customer has absolutely no relation to the other ones, other than our people who are assigned to that customer.

As a result, our devevelopment team thinks that the best thing to do is to split each customer out into its own database. The catch is that there are around 20,000 customers per year, and each customer database would be about 100 MB in size.

In general I do not feel that this is a good idea, if only because at some point you start to reach SQL's native limitations- number of databases, SP reuse in caching, and even just raw performance for backup/restore/indexing operations. Beyond that, when you do eventually have some level of cross-customer querying/reporting to do, it will not be very performant to query this, even if you go with a data waarehouse solution.

Can anyone provide me with some more ammo as to why we would not want to go down this path?

wwb
Aug 17, 2004

From a SQL injection standpoint, I would just use an enum to enumerate the table options and pass that parameter to the method you are calling this from. You can't inject squat in SomeEnum.ToString().

Ardhanari
Mar 23, 2006

jwnin posted:

Can anyone provide me with some more ammo as to why we would not want to go down this path?

Did the devteam also volunteer to manage, troubleshoot, and update those tens of thousands of databases?

It's a horrible idea and whoever suggested it should be torn into little bitsy pieces and buried alive.

Boogeyman
Sep 29, 2004

Boo, motherfucker.
Here's an interesting question. I have a database that houses customers and users, and a RADIUS server uses the database for authentication. We also keep logs of data usage for accounting purposes, and I have an ASP.NET application that generates reports that the customers can view. The customer selects which users will be included in the report with two listboxes (selected/unselected).

Some customers have thousands of users. This means that the SQL generated for the reports becomes pretty hairy if they select lots of units. Something along the lines of this...

code:
SELECT
  A.*
FROM
  AccountingTable A
WHERE
  A.UserId IN
    (
      1001,
      1002,
      1004,
      ...
      1974,
      1977
    ) AND
  <more criteria here>
The IN clause becomes ridiculously huge and while it's not a problem yet, I can see it becoming a problem in the future as we add customers (or when customers add another crapton of users). The only other way I can think of would involve creating a temporary table to hold the user IDs, then inserting the selected IDs into the table, then joining the temporary table to the accounting table to bring back the results. Would that be a better way of doing it, or is there something easier that I'm missing?

Victor
Jun 18, 2004
Hairy == slow? Ugly?

Boogeyman
Sep 29, 2004

Boo, motherfucker.

Victor posted:

Hairy == slow? Ugly?

Slow, ugly, and I think I'll be hitting a limit soon as far as characters allowed in a SQL statement. I believe it tops out at 8000 characters, correct?

Victor
Jun 18, 2004
I don't think there is some solid limit -- the 8000 number just comes from the maximum storage length of char, varchar, binary, varbinary, in SQL 2000 and earlier (2005 has [n]varchar(max), and varbinary(max)). Selecting that many IDs from a GUI is a bit questionable, so if it is possible, I would just put a limit on that (say, to a 100 entries), and also have a "select all" option, in which case you wouldn't use in. However, people can be retarded and require that users be allowed to select hundreds of entries, so this might not be an option. If so, I guess a temporary table would be the best approach, albeit ugly.

Ardhanari
Mar 23, 2006

You're pretty much in the same situation as Dakha was on page 1. If you're going to need to scale it up, a temp table is the way to go.

I'm pretty sure 8000 characters isn't the limit for a query. That's the max size of the usual text variable types.

Boogeyman
Sep 29, 2004

Boo, motherfucker.

Victor posted:

Selecting that many IDs from a GUI is a bit questionable, so if it is possible, I would just put a limit on that (say, to a 100 entries), and also have a "select all" option, in which case you wouldn't use in. However, people can be retarded and require that users be allowed to select hundreds of entries, so this might not be an option.

This is the main problem. I've told them repeatedly that common sense dictates that no one in their right mind would select 6879 individual entries out of 12543 to generate a report, and that it should be "either select up to XXX users, or all users". I can't win with these clowns though.

I'll go back and look over Dakha's post and the resulting solution to see what I can come up with. I'll also try to push the "X or all" argument on them again to see if I can get them to agree with me (not likely).

m5
Oct 1, 2001

Victor posted:

I don't think there is some solid limit -- the 8000 number just comes from the maximum storage length of char, varchar, binary, varbinary, in SQL 2000 and earlier (2005 has [n]varchar(max), and varbinary(max)). Selecting that many IDs from a GUI is a bit questionable, so if it is possible, I would just put a limit on that (say, to a 100 entries), and also have a "select all" option, in which case you wouldn't use in. However, people can be retarded and require that users be allowed to select hundreds of entries, so this might not be an option. If so, I guess a temporary table would be the best approach, albeit ugly.

SQL Server seems to have issues with the size of individual "IN" clauses, but in our experience it works OK if the lists are split up into separate IN clauses that are connected with OR.

In our application, it isn't a case of idiot customers, and selecting thousands of things to operate on makes perfect sense.

m5
Oct 1, 2001

jwnin posted:

As a result, our devevelopment team thinks that the best thing to do is to split each customer out into its own database. The catch is that there are around 20,000 customers per year, and each customer database would be about 100 MB in size.

If what you're saying is that for any given customers there's a foozle table and a thingy table, and each customer may have 50 foozles and 1000 thingies, then one possibility is to look into clustering (if your DBMS supports it; MS SQL Server does). If you keep your customer ID on each table, and cluster on that (it does not have to be the primary key), then as long as you make sure to include customer ID when you do queries (incl your joins) then it's almost like having separate tables for each customer. It'll slow your inserts down a little, but not much, and your searches will be much faster.

Victor
Jun 18, 2004

m5 posted:

In our application, it isn't a case of idiot customers, and selecting thousands of things to operate on makes perfect sense.
I'm assuming they aren't clicking thousands of items? My "idiot" comment was predicated upon that happening.

m5
Oct 1, 2001

Victor posted:

I'm assuming they aren't clicking thousands of items? My "idiot" comment was predicated upon that happening.

Well it wouldn't surprise me, but they certainly don't have to do that.

Actually we're kind-of dumb about how we do this. We provide "queue" views with filters and stuff, and things can be multi-selected or drag-selected or whatever. The easiest thing to do is to use the filters to get what you want and then click "select all". If we were smart, we'd then couple the filtering onto the running of the operation direction.

(Actually most operations, for various reasons, have to go one-at-a-time anyway.)

minato
Jun 7, 2004

cutty cain't hang, say 7-up.
Taco Defender

jwnin posted:

As a result, our devevelopment team thinks that the best thing to do is to split each customer out into its own database. The catch is that there are around 20,000 customers per year, and each customer database would be about 100 MB in size.

Can anyone provide me with some more ammo as to why we would not want to go down this path?
This is a horrible idea. If the performance is slow, then you need to first look at ensuring your indexes and filesystems are set up correctly. Once that's out of the equation, you might simply need bigger iron. Only as a last resort would I consider physically splitting up the databases, and even then I wouldn't assume 1 customer per database, I'd put as many as possible into a single database.

The more disparate databases you have, the more work you'll have to do when it comes to setup, backup and restore, and ensuring schema changes are propagated to each database. And it's utter hell to do any sort of report that requires data from multiple databases. Depending on the complexity, a cross-database report would have to extract the necessary info from each database, insert it into a temporary database , and then run the report. That's very slow.

I speak from experience: our company had a database built around our product, and then management surprised us by announcing a new product. We didn't have time to rejig the database to support multiple products, so we simply duplicated the database schema and kept them separate, hoping that some day we'd be able to merge the two. It's caused us a load of grief and time and I wish we'd kept it all in one.

fletcher
Jun 27, 2003

ken park is my favorite movie

Cybernetic Crumb
Is there any reason to use PDO for my abstraction layer if I'm using mysql and will always use mysql for this app?

Xae
Jan 19, 2005

jwnin posted:

This is a bit of a different SQL question, but I thought I'd ask it here.

Our database stores information about our customers. In general, there are very few times where we need to do queries across the entire customer base - what happens at one customer has absolutely no relation to the other ones, other than our people who are assigned to that customer.

As a result, our devevelopment team thinks that the best thing to do is to split each customer out into its own database. The catch is that there are around 20,000 customers per year, and each customer database would be about 100 MB in size.

In general I do not feel that this is a good idea, if only because at some point you start to reach SQL's native limitations- number of databases, SP reuse in caching, and even just raw performance for backup/restore/indexing operations. Beyond that, when you do eventually have some level of cross-customer querying/reporting to do, it will not be very performant to query this, even if you go with a data waarehouse solution.

Can anyone provide me with some more ammo as to why we would not want to go down this path?
The key is to find the things all the customers have in common. You may need to make some tables more abstract. There are some hideously large databases that use SQL, I doubt you are anywhere near the limit.

Just because the data does not all need to be accessed at the same time doesn't mean that they should be in separate databases. They are logically the same entity type so they should logically be grouped together.

From personal experience I have worked on systems where 15 years before someone said "Just clone the DB and put it in a different schema, we will work something out later", and 15 years later the solution was to pay through the rear end for an outsider to come in and unfuck their mess.

Xae fucked around with this message at 06:24 on Nov 9, 2007

Argue
Sep 29, 2005

I represent the Philippines
I'm using Oracle. What's the best way to do something similar to WHERE name IN (a set of values), except instead of checking for equality, we use the LIKE keyword? It would end up being functionally the same as WHERE name LIKE val1 OR name LIKE val2 OR name LIKE val3, except the number of right hand values can change. Currently, what I'm doing is pre-preparing the SQL by iterating over the list of possible names (which are not retrieved from the DB) and appending "OR name LIKE ?" each time.

minato
Jun 7, 2004

cutty cain't hang, say 7-up.
Taco Defender

Argue posted:

I'm using Oracle. What's the best way to do something similar to WHERE name IN (a set of values), except instead of checking for equality, we use the LIKE keyword? It would end up being functionally the same as WHERE name LIKE val1 OR name LIKE val2 OR name LIKE val3, except the number of right hand values can change. Currently, what I'm doing is pre-preparing the SQL by iterating over the list of possible names (which are not retrieved from the DB) and appending "OR name LIKE ?" each time.
Perhaps you can build a more general Perl-compatible regex, and that'll only use 1 statement? E.g.:
code:
SELECT * FROM foo WHERE bar *= '#(foo|bar|baz)#';
Since Perl-compatible regexes can be compiled, that might be faster. Plus, it's more flexible than LIKE.

Argue
Sep 29, 2005

I represent the Philippines

minato posted:

Perhaps you can build a more general Perl-compatible regex, and that'll only use 1 statement? E.g.:

Ah, great. That sounds cleaner than mine; the sets I'm expecting aren't all that large so it shouldn't waste too much time. Thanks.

Alex007
Jul 8, 2004

jwnin posted:

(...)

Can anyone provide me with some more ammo as to why we would not want to go down this path?

Are the database running on your server for your clients (IE they are using your server all the time) or is it just a backup you want to have access to ?

Well, at first, updating thoses databases is gonna be a BITCH. "Hey joe, can you fix that stored procedure in the clients databases ? Sure boss, let me run that query in 1097 database, I'll be right back !"

The risk of mistales is big too, because one or a few clients DB could end up different from the rest because of all that updating. comparing all theses database is also gonna be a bith, even with great tools.

Also, you absolutely WON'T be able to query all clients at once, even if your boss thinks he's never gonna need it, he WILL. "Hey let's find out if somebody had that bug where the transactions were written all reversed..."

Caching is gonna be ugly, if all clients use stored procedure X, they all gonna end up with a cached copy of the plan for that SP, even if it's exactly the same SP. That will mean fewer cached plans since you'll have so much identical plans cached (1000 copies of storedproc X's plan will be flushed when they try to run storedproc Y 1000 times too.

nbv4
Aug 21, 2002

by Duchess Gummybuns
OK, so you have databases which exist within a mysql installation. Within each database, you have tables. In each table you have fields. Is all that correct?

For a website project, should I have ALL of it in one database, each user in their own table, and then user details as a field in the tables, or do I have all this messed up?

I'm having trouble wrapping my brain around how data is represented in mysql. Most beginner tutorials focus on specific commands (which I can just learn just fine by reading code examples) and have nothing on the kind of stuff I'm having trouble with.

Basically I'm wanting to make a site that has a ton of users, and each user has a ton of data that is stored in the database (with no data shared between any other users). I'm got phpmyadmin installed, and when I create a new table, it wants me to give a name for the table, and I don't know if I should put "bob", "user_name", "users", or what-the-gently caress.

nbv4 fucked around with this message at 10:07 on Nov 12, 2007

minato
Jun 7, 2004

cutty cain't hang, say 7-up.
Taco Defender
Imagine that you want to enter the user information into an Excel spreadsheet, like so:
code:
user_id    login_name   real_name    date_of_birth
100        bob          Bob Smith    1990-11-12
101        john         John Jones   1989-07-03
102        jane         Jane Grant   1994-08-19
A single spreadsheet would be the same as a table (lets call this one "users"). The columns of the spreadsheet are the fields, and each row is a database row.

So if you want to look up the information for user "bob", then you might use the SQL:
code:
SELECT user_id, login_name, real_name, date_of_birth
FROM users
WHERE login_name="bob"
Edit: To elaborate, a table consists of a list of 0-many rows. Each row represents all the information about some "thing". A row consists of various fields that describe that single thing. So examples of tables would be "users", "projects", "products", etc. Each row in those tables describes a single user, project or product. The fields depend on the thing you're trying to describe. A user might have an ID, name, DOB, etc. A product might have an ID, name, quantity available, etc.

minato fucked around with this message at 10:17 on Nov 12, 2007

nbv4
Aug 21, 2002

by Duchess Gummybuns

minato posted:

Edit: To elaborate, a table consists of a list of 0-many rows. Each row represents all the information about some "thing". A row consists of various fields that describe that single thing. So examples of tables would be "users", "projects", "products", etc. Each row in those tables describes a single user, project or product. The fields depend on the thing you're trying to describe. A user might have an ID, name, DOB, etc. A product might have an ID, name, quantity available, etc.

OK I think I'm starting to get it now.

One more question. Is there a guide somewhere that helps you determine which data type is the best for certain kinds of data? For instance, whats the best type to use to store a users email address? What if you have a value that only has a small number of values? Such as, for example, car manufacturer. Lets say there are only 5 possible manufacturers. Would it be best to store that data as either "1,2,3,4,5", then have each number correlate to a manufacturer, or would it be best to just store it as a string? Or what about setting up 5 different boolean rows for each manufacturer? Would it matter which way you did it?

minato
Jun 7, 2004

cutty cain't hang, say 7-up.
Taco Defender

nbv4 posted:

One more question. Is there a guide somewhere that helps you determine which data type is the best for certain kinds of data? For instance, whats the best type to use to store a users email address? What if you have a value that only has a small number of values? Such as, for example, car manufacturer. Lets say there are only 5 possible manufacturers. Would it be best to store that data as either "1,2,3,4,5", then have each number correlate to a manufacturer, or would it be best to just store it as a string? Or what about setting up 5 different boolean rows for each manufacturer? Would it matter which way you did it?
In general, the field type you use should be similar to the type you use in your application. So for an email address, you'd use a string.

To store a car manufacturer, there are lots of ways you could do it, and there are pros and cons to each way. If you chose to use a string and store it directly (e.g. "Chrysler"), then it's easy to just browse the car rows and see which car corresponds with which manufacturer. However most people will use integers because it's more space efficient, faster for the database to process, is less prone to spelling/case errors, and is less effort to update if the name ever changes (say, due to a typo).

Most people would set it up like this:

code:
table : "manufacturers"
manufacturer_id  name
1                Ferrari
2                Lamborghini
3                Bugati

table: "cars"
car_id manufacturer_id name
1      3               Veyron
2      2               Contach
3      3               Type 57FC
Where there was a "referential integrity" check between cars.manufacturer_id and manufacturers.manufacturer_id. That means that you can't insert a row into the cars table unless the manufacturer_id exists somewhere within the manufacturers table. This helps prevent data integrity errors.

chocojosh
Jun 9, 2007

D00D.
Bringing the discussion back to SQL Server 2005 for a quick moment.

There is one add-on that would help me save HOURS of work. I'd love it if I have some text highlighted, that one of my right click options would be to "go to definition" for a stored proc, user defined function (and even table, although I'd prefer just a select *)

For example, right now I want to see a stored procedure called up_extractfirstvalue because I am not sure how it is being used in my stored procedure. If I could simply highlight up_extractfirstvalue, right click, and select "Go To definition" instead of finding it in our list of thousands of stored procs, it would be so helpful!

Alex007
Jul 8, 2004

chocojosh posted:

Bringing the discussion back to SQL Server 2005 for a quick moment.

There is one add-on that would help me save HOURS of work. I'd love it if I have some text highlighted, that one of my right click options would be to "go to definition" for a stored proc, user defined function (and even table, although I'd prefer just a select *)

For example, right now I want to see a stored procedure called up_extractfirstvalue because I am not sure how it is being used in my stored procedure. If I could simply highlight up_extractfirstvalue, right click, and select "Go To definition" instead of finding it in our list of thousands of stored procs, it would be so helpful!

In SQL Server management studio, just map CTRL-F1 to "sp_helptext".

Go to Tools > Options > Environment > Keyboard.

Then, all you have to do is higlight a storedproc / table / function, and press CTRL-F1.

Edit:

chocojosh
Jun 9, 2007

D00D.

Alex007 posted:

In SQL Server management studio, just map CTRL-F1 to "sp_helptext".

Go to Tools > Options > Environment > Keyboard.

Then, all you have to do is higlight a storedproc / table / function, and press CTRL-F1.

Edit:



Great tip!

Would be better if it opened a new window and allow me to edit the query also (although I didn't ask for that initially, I know). :)

Alex007
Jul 8, 2004

chocojosh posted:

Great tip!

Would be better if it opened a new window and allow me to edit the query also (although I didn't ask for that initially, I know). :)

I agree, that would be even cooler.

I just end up copy-pasting what I need into a new window and modify from there.

On thing that [b]SUCKS[b] with that, when doing that for a stored proc, each line is padded with some spaces (usually 3). It doesn't do anything wrong, but it's frustrating to press END and end up 3 spaces after the last character of that line. And if you do alter the stored proc from that script, the spaces are saved with the script and stay forever (until osmeone removes them).

nbv4
Aug 21, 2002

by Duchess Gummybuns
code:
<?
// connection information
$hostName = "localhost";
$userName = "root";
$password = "sex";
$dbName = "logbook";

// make connection to database
mysql_connect($hostName, $userName, $password) or die("Unable to connect to host $hostName");

mysql_select_db($dbName) or die( "Unable to select database $dbName");
?>

<form action="register-script.php" method="post">

username: <input type="text" name="username"><p>
email: <input type="text" name="email"><p>
password: <input type="text" name="password"><p>

<input type="submit" name="submit" value="submit">


<?
// Close the database connection
mysql_close();
?>
register-script.php:
code:
<?
// make connection
$hostName = "localhost";
$userName = "root";
$password = "sex";
$dbName = "logbook";

mysql_connect($hostName, $userName, $password) or die("Unable to connect to host $hostName");

mysql_select_db($dbName) or die( "Unable to select database $dbName");
//////////


//$username = $_GET['username'];
//$password = $_POST['password'];

//$query = "INSERT INTO pilots (username) VALUES('$username')";
//$result = mysql_query($query);

print 'hi<br>';
print $username;
print $query;

// Close the database connection
mysql_close();
?>
what am i missing? even if i uncomment out the lines i have commented out, it still doesn't work. I even change the _POST's to _GET's and still no go

minato
Jun 7, 2004

cutty cain't hang, say 7-up.
Taco Defender
Not closing the </form>?

nbv4
Aug 21, 2002

by Duchess Gummybuns

minato posted:

Not closing the </form>?

that was what it was, I feel stupid.

Now I've got another problem that is stumping me. Here is my query:

$result = mysql_query("SELECT * FROM pilots WHERE email=$email and password=" . MD5($password);

then below that, I have:

$row = mysql_fetch_row($result);

print $row[0];

which should print out something, but it's giving me this error:

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /var/www/logbook/login-script.php on line 20

makes me believe the problem is with my query string. I copied the string exactly as how it comes from php:

SELECT * FROM users WHERE email=nbvfour@gmail.com and password=a4f9767401ed74edc2321b7b1bc7790e

into phpmyadmin, and it says it's an invalid query string. I'm thinking I'm supoosed to escape the '@' but I can't seem to find anything on google.

minato
Jun 7, 2004

cutty cain't hang, say 7-up.
Taco Defender
You need to put the parameters into quotes. Try something like this:
php:
<?
$sql = sprintf(
       "SELECT *
        FROM pilots
        WHERE email='%s'
          AND password='%s'",
        mysql_real_escape_string($email),
        mysql_real_escape_string(md5($password))
        );
$result = mysql_query($sql);
?>

fletcher
Jun 27, 2003

ken park is my favorite movie

Cybernetic Crumb
Let's say I've got a simple users table like
code:
id   name
----------------------------
1    bob
2    joe
3    frank
and a messages table like
code:
id   recipient    author    message
---------------------------------------
1    1            2         hello bob its joe
2    3            1         hey frank its bob
And I want a query that will output
code:
recipient    author    message
----------------------------
bob          joe       hello bob its joe
frank        bob       hey frank its bob
I can get one of the user id columns to return the name using this query, but how do I get both of the user id columns to be returned as the users name? Am I doing this completely the wrong way? Is this query the same as doing a JOIN?

SELECT users.name, messages.to, messages.message FROM messages, users WHERE messages.author = users.id

I'm running MYSQL5.

hey mom its 420
May 12, 2007

My SQL is a little rusty but here's a shot at it
code:
SELECT recipient.name, author.name, messages.message
FROM users AS recipient
INNER JOIN messages ON messages.recipient = recipient.id
INNER JOIN users AS author ON author.id = messages.author
WHERE recipient.id = [the id of the guy who is looking at his messages]

fletcher
Jun 27, 2003

ken park is my favorite movie

Cybernetic Crumb

Bonus posted:

My SQL is a little rusty but here's a shot at it
code:
SELECT recipient.name, author.name, messages.message
FROM users AS recipient
INNER JOIN messages ON messages.recipient = recipient.id
INNER JOIN users AS author ON author.id = messages.author
WHERE recipient.id = [the id of the guy who is looking at his messages]

What does the INNER do? What happens if you take it out?

Adbot
ADBOT LOVES YOU

Alex007
Jul 8, 2004

fletcher posted:

What does the INNER do? What happens if you take it out?

It JOINs tables for a SELECT, you don'T want to take it out, that'S your solution.

It's like your "WHERE a.id = b.id" clause, that's a JOIN too, but that's the old way to join tables.

  • 1
  • 2
  • 3
  • 4
  • 5
  • Post
  • Reply