• 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

    • Thanks to Herr Musk being a total poison pill, they can't even give those pieces of scrap away.  They can't even ship them to the UK/EU because they're completely illegal over here.  
    • Intel vs AMD? Microsoft seemingly has a clear recommendation for Windows 11 Pro PC upgrade by Sayan Sen Microsoft and its partners are now quite actively and regularly promoting the upgrade to Windows 11. Asus, for example, recently published blog posts about the "mandatory Windows 11 upgrade" that is coming as the Windows 11 end of support date nears. Microsoft itself, from time to time, urges users to upgrade to its newest OS. Back in February 2024, Microsoft released an advert highlighting the best things about Windows 11 over Windows 10. Later, in June in the same year, the tech giant busted "myths and misconceptions" surrounding a Windows 11 upgrade. And towards the end of 2024, in December, Microsoft put up a blog post outlining the gaming features a user enjoys on 11 if they were to upgrade from Windows 10. While technically there is nothing wrong with a company promoting its own product, sometimes these campaigns make little sense and they fall flat. For example, in January earlier this year, Microsoft shared a blog post headlined "Free Upgrade to Windows 11 (For a Limited Time Only)" which did not make sense as it offered little information about it being a "free upgrade," and it was rightfully, later taken down. The company is back again with a new commercial about Windows 11. This time it is aimed mainly at IT professionals and enterprises as the advert talks about upgrading to Windows 11 Pro from Windows 10. This landed a few days after Microsoft released a new backup tool for organizations for such a purpose. What is interesting is that the company is promoting Intel's vPro processors and there is no mention of AMD's Ryzen PRO parts. The commercial is posted on the Windows official YouTube channel and has been titled "Right side of risk | Windows 11 Pro and Intel". The video description says, "Windows 10 support ends October 14. Stay on the right side of risk—upgrade now to the power of Windows 11 Pro PCs with Intel vPro®." AMD does have a support article about the subject headlined "Support Your Customers’ Move to Windows 11, With AMD Ryzen™ PRO Processors" and you can find it here. This is not the first time Microsoft has promoted Intel CPUs over AMD ones. Back in 2021, the company also put up a full page explaining how users should "look for the Intel EVO badge" on a new device before making a purchase decision because such PCs are "verified wonderful" which was a bit of an odd language. Like the limited upgrade time article, the page above was taken down after we reported on it (can be viewed via the archive) and replaced with something else. The new commercial was published about a couple of days ago, and it is possible that Microsoft may have a dedicated AMD advert too in the pipeline scheduled for a later release, and that would only be fair if both companies get a similar treatment.
    • Don’t blame web developers for the downfall of Firefox. 😂
    • Microsoft, Indian police bust AI-powered tech support scam ring targeting elderly in Japan by Paul Hill Pop-up scams pretending to be Microsoft Working with India’s Central Bureau of Investigation (CBI), Microsoft recently assisted in busting a scam network that was targeting the elderly in Japan. The CBI raided 19 locations on May 28, leading to the arrest of six key operatives and the taking down of two call centers. The scammers were impersonating Microsoft specifically and using tech support scams against Japanese seniors. The raid led to the seizure of both digital and physical infrastructure, including computers, storage devices, and phones. The scammers were targeting older adults, who are more vulnerable to fraud. To put this activity to an end, Microsoft’s Digital Crimes Unit (DCU), the Japan Cybercrime Control Center (JC3), Japan’s National Police Agency (NPA), and India’s CBI conducted significant cross-border collaboration to trace the criminals. Thanks to the internet, cross-border crimes like these have been around for a while and multinational tech firms like Microsoft are making significant efforts to help law enforcement agencies crack down on cybercrime. Artificial intelligence is also starting to be used to make more sophisticated scams. The evolving threat This case reveals an evolution in how Microsoft’s DCU addresses cybercrime involving tech support fraud. Thanks to AI, scammers have been able to scale their operations. In response, Microsoft has moved away from focusing on individual call centers to target the heads of criminal operations and disrupting their technical infrastructure. Notably, Microsoft’s collaboration with JC3 is the first time the DCU has partnered with a Japan-based organization to assist victims. Microsoft is continually getting tips from JC3 about malicious pop-ups urging recipients to call fake technical support lines that claim to be Microsoft. This data has allowed Microsoft to shut down 66,000 malicious domains and URLs globally since May 2024. Microsoft noted that artificial intelligence is now being used by criminals to scale their operations. Some ways in which these entities leverage AI are for victim identification, writing convincing scam emails and building fake web pages, as well as for convincing translations. Anyone can use AI for malicious purposes so it could increase the number of people or groups carrying out attacks. It also makes attacks much more sophisticated and harder to detect and necessitates better consumer protections and more sophisticated security tools such as passkeys to reduce hacks. Protecting vulnerable populations and what readers can do Tech support fraud attacks have been found by the FBI to disproportionately affect older people, resulting in $590 million in losses in 2023 for just older Americans alone. In this operation that targeted Japanese victims, around 90% of the 200 affected people were over 50. If you’ve ever received suspicious communications from a party claiming to be Microsoft, you should know that Microsoft never sends unsolicited emails or makes phone calls requesting personal or financial information, and it doesn’t offer unsolicited tech support. If you do get any suspicious communications, then you should report it to Microsoft so that it can take action.
  • Recent Achievements

    • Week One Done
      luxoxfurniture earned a badge
      Week One Done
    • First Post
      Uranus_enjoyer earned a badge
      First Post
    • Week One Done
      Uranus_enjoyer earned a badge
      Week One Done
    • Week One Done
      jfam earned a badge
      Week One Done
    • First Post
      survivor303 earned a badge
      First Post
  • Popular Contributors

    1. 1
      +primortal
      434
    2. 2
      +FloatingFatMan
      239
    3. 3
      snowy owl
      214
    4. 4
      ATLien_0
      211
    5. 5
      Xenon
      157
  • Tell a friend

    Love Neowin? Tell a friend!