Problem with mysql_fetch_assoc

Question

I'm having a little trouble with a mysql_fetch_assoc script i hope you can help with.

Background: I'm retrieving my data for part of my site with a typical MySQL query and echoing out the results from various fields etc etc from my main table whose structure is not important but which has a unique id which is 'job_id'

In order to have multiple catagories associated with that 'job_id' i have employed a toxi solution which associates catgories to each 'job_id'.

TABLE `tags` (
`tag_id` INT NOT NULL AUTO_INCREMENT, 
`tag_name` VARCHAR(20) NOT NULL, 
PRIMARY KEY (`tag_id`)
)

CREATE TABLE `tag_relational` (
`job_id` INT NOT NULL, 
`tag_id` INT NOT NULL
)

When i echo out the info from the main table (using 'job_id') i also want to echo all the catagories which that job_id is matched against, which id using:

$query = "SELECT * FROM tags t
JOIN tag_relational r   
ON t.tag_id=r.tag_id
WHERE r.job_id = $job_id";

$result=mysql_query($query) or die(mysql_error());
$cats=mysql_fetch_assoc($result);

In my code i'm using this to echo out the matched catagories:

<p>Job Catagories | <?php while ($cats=mysql_fetch_assoc($result)) { echo $cats['tag_name'];}?></p>

I have two problems:

The above echo seems to be ignoring the first tag name all together, so if a job is tagged with six catagories, it only echos five. If a job is only tagged with one, I get nothing. The query works in the SQL shell, so I'm assuming the problem lie in the PHP.
When I do get multiple echos, I want to be able to seperate them with a comma or a |, but I'm unfamiliar with using group_concat in queries, so I could also use a little help there.

Mark Biesheuvel · Accepted Answer · 2011-08-01 20:34:21Z

3

Because mysql_fetch_assoc is already called once before the while loop, the first row is always discarded.

This should solve your problems:

$query = "SELECT * FROM tags t
JOIN tag_relational r   
ON t.tag_id=r.tag_id
WHERE r.job_id = $job_id";

$result=mysql_query($query) or die(mysql_error());

<p>Job Catagories | 
<?php 
$first = true;
while ($cats=mysql_fetch_assoc($result)) { 
    if($first){
        $first = false;
    } else{
        echo ", ";
    }
    echo $cats['tag_name'];
}
?>
</p>

edited Aug 1, 2011 at 20:34

answered Aug 1, 2011 at 20:26

Mark Biesheuvel

6547 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Dan Over a year ago

Man i love SO. Thanks, that has done the trick with problem 1. I'd like to leave the question open to see if anyone can help with problem 2 as well, but thats spot on, thank you.

Mark Biesheuvel Over a year ago

I think my edit, should solve that problem now too. Accidentally posted it before I was done.

Dan Over a year ago

I seem to be getting an error for first = else (unexpected =)?

Mark Biesheuvel Over a year ago

Yeah, some typos. My bad. Forgot the $-sign a couple of times. And I forgot to delete the 'dot' after I switched from a concatenation to two echo's. I keep writing buggy code tonight...

Dan Over a year ago

No problem, i just picked up on those myself - serves me right for copy and pasting! Thanks M_rk, youre a star.

AMIC MING · Accepted Answer · 2012-12-28 19:46:04Z

0

use SEPARATOR in group_concat. see example below

mysql> SELECT student_name,  
        ->     GROUP_CONCAT(DISTINCT test_score
        ->               ORDER BY test_score DESC SEPARATOR ' ')
        ->     FROM student
        ->     GROUP BY student_name;

edited Dec 28, 2012 at 19:46

AMIC MING

6,3646 gold badges50 silver badges63 bronze badges

answered Dec 28, 2012 at 19:27

Fatmata

1

Collectives™ on Stack Overflow

Problem with mysql_fetch_assoc

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related