• 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

    • Also good for Debugging or troubleshooting your game.. i dont get it why nvidia app or amd does not show this data.. if steam can do it..
    • Microsoft reportedly planning to lay off thousands of employees, mostly in sales by Usama Jawad Back in May 2025, Microsoft decided to lay off 3% of its workforce, which amounted to roughly 6,000 employees. It claimed that this decision allowed it to implement better organizational changes in a "dynamic marketplace". Now, a new report claims that the Redmond tech firm is planning to lay off thousands more next month. Citing unnamed sources, Bloomberg reports that as the company continues investing heavily in its AI ventures, it is about to announce layoffs of thousands of workers as early as next month. This reduction in workforce will primarily affect sales teams, but they won't be the only ones affected. That said, the sources did mention that the timing for this announcement may change. This move, if true, won't be entirely surprising. In April 2025, Microsoft announced that it will be relying more on third-party firms to sell its software to small- and medium-sized customers. It's currently unclear how many employees will be impacted by this change, but even if the layoff percentage is in the single digits, it would still be significant as it would be impacting the professional careers of thousands. The May 2025 layoffs primarily impacted engineering and product teams. The other major round of layoffs prior to this was the decision to eliminate 10,000 jobs back in January 2023. Those represented 5% of the total workforce at that time, with numerous teams, including the one leading Mixed Reality (MR) efforts, being heavily impacted. It is interesting to note that if the timing of the announcement for layoffs is accurate, it would be soon after Microsoft closes its fiscal year at the end of June 2025. Although we'll get financial reports for the latest quarter soon after too, one has to wonder what the human cost of profit is, as Microsoft continues to report billions of dollars in revenue every quarter. Source: Bloomberg (paywall)
    • Ah .. lockout for suspicious activity. I bet they uploaded the SanDisk utility detected as malware
    • Microsoft 365 will soon disable outdated authentication protocols for file access by Usama Jawad On a fairly regular basis, Microsoft disables outdated protocols that are used to access its services. In the past few years, the company has deprecated Basic Auth in Exchange Online and cut access to Outlook for third-party apps relying on this protocol. Now, it has decided to get rid of old authentication protocols for file access across Microsoft 365 services. As reported by Bleeping Computer, Microsoft has posted a message on its Microsoft 365 Admin Center. Starting from mid-July 2025, the company will begin disabling legacy authentication protocols used to access files across Microsoft 365 and Office apps, SharePoint, and OneDrive. Essentially, applications or services which use the Relying Party Suite (RPS) or FrontPage Remote Procedure Call (FPRPC) will to perform browser-based authentication to perform open operations on Office files will no longer be able to do so. As expected, this is primarily being done to improve the cybersecurity posture of various services. Microsoft states that RPS can be brute-forced and phished with relative ease as it is fairly outdated. Similarly, FPRPC is typically used for remote web page authoring and it is susceptible to exploitation through various vulnerabilities too. As such, both of these protocols will be disabled by default starting from mid-July 2025, with the rollout of this change targeting completion by August 2025. The Redmond tech giant will update the protocol baseline by default without mandating any licensing changes for customers. In addition, once these modifications are rolled out, Microsoft 365 will require admin consent to get third-party access to files and sites. IT admins can view the guidance available here to configure admin consent workflows. Microsoft says that these changes align with the principles of its Secure Future Initiative (SFI). Earlier today, it announced the rollout of improved security defaults for Windows 365 citing the same reasons too.
  • Recent Achievements

    • First Post
      Fuzz_c earned a badge
      First Post
    • First Post
      TIGOSS earned a badge
      First Post
    • Week One Done
      slackerzz earned a badge
      Week One Done
    • Week One Done
      vivetool earned a badge
      Week One Done
    • Reacting Well
      pnajbar earned a badge
      Reacting Well
  • Popular Contributors

    1. 1
      +primortal
      704
    2. 2
      ATLien_0
      283
    3. 3
      Michael Scrip
      216
    4. 4
      +FloatingFatMan
      195
    5. 5
      Steven P.
      131
  • Tell a friend

    Love Neowin? Tell a friend!