/    Sign up×
Community /Pin to ProfileBookmark

Getting exploded array from mysql

Copy linkTweet thisAlerts:
Nov 20.2007

Hi guys!

I’ve got a question for you all. I have a MySQL database that stores Jobtitle names for users (both in Icelandic and English). The information is stored with an ID and then the text in Icelandic and text in English. The ID is unique.

Then I have a “Users” table with all the information for each user. There I have a field / column called jobtitles and it stores the unique ID of all the jobtitles the user wants to have. They are stored with comma separator (15,20,65 etc).

Now, I need to build a search engine that can find every instance of JobtitleID = “9” in the jobtitles field in the “Users” table. I can’t just call: “select * from users where jobtitles LIKE ‘%9%'” because then I would also get every jobtitle with the numbers 19, 29, 39, 49, 91, 92 etc.!

So my question is … how can I accomplish this in a neat and effortless way?

With hope of a few responses,
Magnus

to post a comment
PHP

6 Comments(s) ↴

Copy linkTweet thisAlerts:
@MrCoderNov 20.2007 — Use a link table instead of using a comma separator field.

Otherwise..

[code=php]
$job_ids = "21,22,24,26,556,34,46,31";

$job_ids = explode(",", $job_ids);

echo "<pre>".var_export($job_ids, true)."</pre>";
[/code]
Copy linkTweet thisAlerts:
@trymbillauthorNov 20.2007 — Hmm, what is a link table? : - /

I'm going to Google it, but, if you can share, please do ?
Copy linkTweet thisAlerts:
@MrCoderNov 20.2007 — I going to make some assumptions here..

I assume you have a "User_ID" of some kind..

This is what you currently have stored in a table called "Users" with the following fields..

User_ID = 1

Job_IDs = "12,13,15"


This is what I would do, make another table called "Users_Jobs" with the following fields..

User_ID

Job_ID


Then your SQL would look like this..

SELECT * FROM Users, Users_Jobs WHERE Users.Users_ID = Users_Jobs.Users_ID

This will allow you to create 1 row in the "Users_Jobs" table for each job that you would of had to split with comma's in your old format.
Copy linkTweet thisAlerts:
@trymbillauthorNov 20.2007 — Ah, nice one! I get it, good idea! ?

Thanks a lot!
Copy linkTweet thisAlerts:
@trymbillauthorNov 20.2007 — One more thing ... is there an easy way of getting the unique ID or the primary key of a row in MySQL that has just been created?
Copy linkTweet thisAlerts:
@MrCoderNov 20.2007 — $id = mysql_insert_id();
×

Success!

Help @trymbill spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 5.28,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @AriseFacilitySolutions09,
tipped: article
amount: 1000 SATS,

tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,
)...