[El Forum]

[eluser]Wonder Woman[/eluser]
Hello,

I want to concat two fields together but I am having trouble with the query containing joins...

Code:

$this->db->select('*, news.id as news_id, news.created as created_on, CONCAT(users.firstname, users.surname) as article_author, CONCAT(modified_user.firstname, modified_user.surname) as modified_author');
$this->db->join('users', 'users.id = news.author', 'left');
$this->db->join('users modified_user', 'modified_user.id = news.modified_by', 'left');

Any idea how I can fix this? Thanks

[El Forum]

[eluser]jmadsen[/eluser]
the users tables id is a full name? and you want to join it with a LIKE?

sorry, but you need to spend a little time working out your sql. It will really payoff in the long run if you put CodeIgniter aside for 24 hours and read up on some basic sql tutorials to understand joins, etc. or you are just going to get stuck every 10 minutes and become frustrated

[El Forum]

[eluser]Wonder Woman[/eluser]
Sorry I posted wrong code, ignore the likes - I've put it in there now...basically it works if I don't concat but I want to concat my fields...and I have to say your comment sounds rude.

[El Forum]

[eluser]jmadsen[/eluser]
well, gee - sorry to sound rude.

the code you put up before was really horrendous - I was telling you as nicely as I could.

Now that we have the correct code, can we see what error message you are getting?

[El Forum]

[eluser]Wonder Woman[/eluser]
I know it was I didn't realise what I posted, that's what you get for clutching straws with someone elses suggestion!

Basically, the concat to get the modified_author returns blank, the first concat article_author works fine, but the second one doesn't

Code:

CONCAT(modified_user.firstname, modified_user.surname) as modified_author

[El Forum]

[eluser]danmontgomery[/eluser]
Nothing jumps out as obviously wrong... Need you to do a little more investigating. Are you getting back any rows from modified_user? Include firstname and surname in your select to see if you're getting back any rows at all. Enable the profiler with:

Code:

$this->output->enable_profiler();

To see the query being run, and paste it in it's entirety here, if you would.


Also, just as a note, if any of the values you're trying to concatenate are null, the entire concatenated string will be null.

[El Forum]

[eluser]jmadsen[/eluser]
if you

echo $this->db->last_query();

I think you'll see that your concats are producing:

Code:

CONCAT(users.firstname, `users`.`surname)` as article_author

you see the problem with the ` ? This is what I get when I try to test - first check that it is your same issue.

If you add FALSE like below, it should not protect your query and get rid of the problem

Code:

$this->db->select('*, news.id as news_id, news.created as created_on, CONCAT(users.firstname, users.surname) as article_author, CONCAT(modified_user.firstname, modified_user.surname) as modified_author', FALSE);

of course, if you weren't using Active Record you never would have had a problem with "hidden" characters ;-)

[El Forum]

[eluser]osci[/eluser]
or have logging and profiler enabled at least in your development machine

[El Forum]

[eluser]Wonder Woman[/eluser]
jmadsen that is what the problem is, thanks, its all fixed now - whoot!!

[El Forum]

[eluser]jmadsen[/eluser]
[quote author="osci" date="1308673942"]or have logging and profiler enabled at least in your development machine [/quote]

yes, i was going to add something like that - Wonder Woman, do you have

error_reporting(E_ALL);

in your index.php, and profiling turned on? You should be getting error messages back for these things that will help you alot