• 0

SQL query - need some help


Question

Hi guys. I need some help with a damn sql query that I can't seem to make it work. It should be easy, but I just can't figure out what I'm doing wrong. 

So, I have a table with a few columns that is editable by users on the front-end. Each user sees only his entries. The first column is the user's ID (I'm on Wordpress, by the way). Next, I'm trying to do a query that will pull all the data submitted by users and output it into a "public" table. Everything is fine, except that I want the table to show the user's name instead of the ID. I've read a little and it seems I can do this with a INNER JOIN on that query, but I can't get it to work, I constantly get sql statement errors when trying to save the damn thing. Somehow I need to link the user_nicename column from wp_users to the ID column and output the name instead of the id.

 

Any help? Thank you!

 

P.S. Here's how my query looks like, and it doesn't work (the JOIN part):

 

SELECT wp_table_1.userID,
       wp_table_1.date,
       wp_table_1.name,
       wp_table_1.input1,
       wp_table_1.input2,
FROM wp_table_1
SELECT wp_users.user_nicename, wp_table_1.name
FROM wp_users
JOIN wp_table_1
ON wp_users.ID=wp_table_1.name

 

Link to comment
https://www.neowin.net/forum/topic/1317318-sql-query-need-some-help/
Share on other sites

7 answers to this question

Recommended Posts

  • 0
  On 24/12/2016 at 00:27, TDT said:

Hi guys. I need some help with a damn sql query that I can't seem to make it work. It should be easy, but I just can't figure out what I'm doing wrong. 

So, I have a table with a few columns that is editable by users on the front-end. Each user sees only his entries. The first column is the user's ID (I'm on Wordpress, by the way). Next, I'm trying to do a query that will pull all the data submitted by users and output it into a "public" table. Everything is fine, except that I want the table to show the user's name instead of the ID. I've read a little and it seems I can do this with a INNER JOIN on that query, but I can't get it to work, I constantly get sql statement errors when trying to save the damn thing. Somehow I need to link the user_nicename column from wp_users to the ID column and output the name instead of the id.

 

Any help? Thank you!

 

P.S. Here's how my query looks like, and it doesn't work (the JOIN part):

 

SELECT wp_table_1.userID,
       wp_table_1.date,
       wp_table_1.name,
       wp_table_1.input1,
       wp_table_1.input2,
FROM wp_table_1
SELECT wp_users.user_nicename, wp_table_1.name
FROM wp_users
JOIN wp_table_1
ON wp_users.ID=wp_table_1.name

 

Expand  

I can't imagine that join working unless the database is really that poorly designed. Are those two columns even the same data types?

  • 0
  On 24/12/2016 at 00:41, adrynalyne said:

I can't imagine that join working unless the database is really that poorly designed. Are those two columns even the same data types?

Expand  

What do you mean? It's a basic table, with a few columns. As for the Wordpress users table, how would I know if it's poorly designed? :) About the data type, not realy. The one in my table is automatic, it just adds the user ID (each user that submits an entry). The Wordpress one is the user's name. Should I create another column in my table and join that one with the one in wp_users?

  • 0
  On 24/12/2016 at 01:04, TDT said:

What do you mean? It's a basic table, with a few columns. As for the Wordpress users table, how would I know if it's poorly designed? :) About the data type, not realy. The one in my table is automatic, it just adds the user ID (each user that submits an entry). The Wordpress one is the user's name. Should I create another column in my table and join that one with the one in wp_users?

Expand  

You should be joining the tables on a column that they both share, preferably with one them being a primary key and the other a foreign key. They don't need to be named the same but good design suggests they should at least be apparent that they are related. 

  • 0
  On 24/12/2016 at 01:15, adrynalyne said:

You should be joining the tables on a column that they both share, preferably with one them being a primary key and the other a foreign key. They don't need to be named the same but good design suggests they should at least be apparent that they are related. 

Expand  

But there is one that both tables share, the ID table. My output shows the correct id with its own entries, but how on earth do I replace that ID with the user's name? 

  • 0

Id think it would be something more like

 

SELECT wp_table_1.userID,
       wp_table_1.date,
       wp_table_1.name,
       wp_table_1.input1,
       wp_table_1.input2,
       wp_users.user_nicename
FROM wp_table_1
JOIN wp_table_1
ON wp_users.ID=wp_table_1.userID

 

  • 0

you need to have a matching UserID (key-int) column in your users table, along with a matching column in your data table... and the join must be on that field.

 

SELECT table2.UserName,
       table1.date,
       table1.input1,
       table1.input2,
FROM table1
INNER JOIN table2
ON Table1.UserID = Table2.UserID
Where Table2.UserName like 'Value'

 

 

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Posts

    • A few things I am wondering about for S3: A.  What's Pike doo gonna look like this time around....yea I've watched the trailers & it looks poofed up, but still.... B. When will the unintelligible, physco-babbling Pelia go away (or at least learn how to speak English properly, without that crappy, slurred accent that sounds like she's on crack, Mary Jane, & LSD at the same time) ?  Hopefully Scotty's arrival will mean her departure is near.... C.  When do we get to see more of the stunningly gorgeous No. 1, preferably in regular/civilian clothes that show off her physical attributes better ? D. Is Spock EVER gonna get laid properly, human style ?  I feel certain that Christine could make any Vulcan-human horizontal bomp a thing to remember for a LONG time  E.  Can we PLEEEEEEZE get rid of the hatchet-head/buzz cut hair styles on Ortega & anyone else that has it....  But otherwise, as Pike says:  HIT IT !  
    • RIP Hotlips..... IMHO, her best scenes were the few where she dared to let her REAL feminine side show through from underneath all that crappy, worn-torn soldier facade that she had to keep up with...she instantly lit up every room or situation where she was featured !
    • Helium Converter 3.3.69.0 by Razvan Serea Helium Converter is a free Windows utility for converting audio files between formats such as MP3, FLAC, AAC, WMA, OGG, and WAV. It supports batch conversion, preserves or updates tag information, and offers features like volume normalization. With a simple interface, it's ideal for users who need to convert large music libraries quickly and efficiently while retaining metadata. Helium Converter key features: Supports file formats: MP3, MP4, FLAC, AAC, M4A, WMA, WAV, OGG, OPUS, APE.... Batch conversion for large music libraries Preserves and edits metadata (ID3, Vorbis Comments, etc.) Volume normalization to equalize loudness Album art extraction and embedding Drag-and-drop interface for quick file selection Adjustable encoding parameters (bitrate, sample rate, channels) Uses internal codecs for consistent performance Supports CUE sheets for split track conversion File renaming based on tags during export Unicode support for international file and tag names Logging of conversion processes for troubleshooting Multi-core CPU support for faster conversions Download: Helium Converter 3.3.69.0 | 25.1 MB (Freeware) Links: Helium Converter Home Page | Screenshot Get alerted to all of our Software updates on Twitter at @NeowinSoftware
    • Since it's been quite a while since the last episode aired, would it be fair (or cruel) to refer to Peggy as a MILF, or even a SMILF ?  HAHAHAHAHAHA
    • Their computers are not gonna stop working in October
  • Recent Achievements

    • One Year In
      WaynesWorld earned a badge
      One Year In
    • First Post
      chriskinney317 earned a badge
      First Post
    • Week One Done
      Nullun earned a badge
      Week One Done
    • First Post
      sultangris earned a badge
      First Post
    • Reacting Well
      sultangris earned a badge
      Reacting Well
  • Popular Contributors

    1. 1
      +primortal
      172
    2. 2
      ATLien_0
      122
    3. 3
      snowy owl
      121
    4. 4
      Xenon
      116
    5. 5
      +Edouard
      93
  • Tell a friend

    Love Neowin? Tell a friend!