Jump to content
simona6

How do I query a DB Table's field, selected by variable as column name?

Recommended Posts

We are building this tool where admins can group the range of followers by what has been added in a 'creation' screen.

So they might put Facebook: followers 5000-10000.  Twitter: followers 2500-5000.

The users enter their followers into a field, and their social media platform.

So I need the database to query both the platform name and their followers, dynamically.

SELECT u.id, u.selfdescription,  u.usertype AS usertype, 
u.username AS username, u.firstname AS firstname, 
u.surname AS surname, u.instagram AS instagram, 
u.facebook AS facebook, u.twitter AS twitter, 
u.pinterest AS pinterest, u.youtube AS youtube, 
u.instagramurl AS instagramurl, u.facebookurl AS facebookurl, 
u.twitterurl AS twitterurl, u.pinteresturl AS pinteresturl, 
u.youtubeurl AS youtubeurl, u.profilephoto AS profilephoto, 
g.userid AS userid, g.gigid AS gigid, u.status, u.datecreated AS datecreated, 
u.town AS town, g.answer FROM gigsassigned AS g INNER JOIN users AS u ON u.id = g.userid 
WHERE u.usertype = 'influencer' AND status IS NULL AND gigid =:gid AND 
dateadded IS NOT NULL AND $rowrange->platform >=:rangelow AND $rowrange->platform <=:rangehigh

So it has to query the "platform" field in 'users' and check if that field is within the 'range'.

How do I put a variable in there, like you can see here toward the end of the script?

Share this post


Link to post
Share on other sites

Moved to MySQL.

You address this problem by refactoring your application so that the problem doesn't exist in the first place.

Storing the platforms as individual columns is not a good idea. For one, it puts you in the situation you're in now. For two, it makes it harder to support whatever platform will be popular next year.

You can generalize each platform as having a URL to the user's page. Probably don't need much more than that. Create a table that lists all the platforms you support, with an ID (like every table should have) and a nice name. Want to support a new platform? Add a row. Then create a new table that contains a row for each combination of user and platform URL; if the user has a Facebook and a YouTube page then there will be two rows in this table.

Add to that this concept of a number of followers. That's two new columns in the second table: one for the minimum and one for the maximum numbers. Or whatever.

Then querying is easy.

Share this post


Link to post
Share on other sites

Normalize your data (as @requinix has just said while I was still typing)

+------------------+                 +------------------+                  +------------------+             +------------------+    
| gigassigned      |                 |    user          |                  |  user_platform   |             | platform         | 
+------------------+                 +------------------+                  +------------------+             +------------------+ 
| userid           |>----------------| id               |-----------------<|  userid          |      +------| platformid       | 
| gigid            |                 | selfdescription  |                  |  platformid      |>-----+      | name             |
| answer           |                 | usertype         |                  |  url             |             +------------------+
| dateadded        |                 | username         |                  |  rangelow        |
+------------------+                 | firstname        |                  |  rangehigh       |
                                     | surname          |                  +------------------+ 
                                     | status           |
                                     | datecreated      |
                                     | town             |
                                     +------------------+
                    

 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.