Unable to get join query to work

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
tbbd
Forum Newbie
Posts: 20
Joined: Sat Apr 22, 2006 2:54 pm

Unable to get join query to work

Post by tbbd »

I decided to try the join thing instead of doing 2 seperate queries, but am having trouble getting it to work
I read some posts on here and also the mysql docs and tried to copy pretty much exactly that way, but it doesn't work:
here is what I have:

Code: Select all

$ret = mysql_query( "select * from books JOIN user_book ON books.id=user_book.holding where user_name='{$session["name"]}'" );

$info = mysql_fetch_array($ret);
echo "<br>name is: ".$info["name"];
The name value is blank, nothing gets echoed for the name
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post by GM »

Using "SELECT * FROM ... " when you've joined tables is not a good idea.

Also in your query you have "WHERE USER_NAME=..." but you haven't specified the table it comes from

Are you returning any rows from the database with this query?

Are you getting any SQL errors when you run the query?
tbbd
Forum Newbie
Posts: 20
Joined: Sat Apr 22, 2006 2:54 pm

Post by tbbd »

well, I didn't know using * wasn't good, I'll just specify each field I need
I redid a few parts so all I need is the name from the items table where that id matches what is in the "holding" field fo the user_book table

I wasn't get any errors before, but I changed it to reflect what you suggested I trey and now I get an error:

Code: Select all

$ret = mysql_query( "select name from items JOIN user_book ON items.id=userbook.holding where userbook.user_name='{$session["name"]}'" );

$name = mysql_result($ret);
echo "<br>name is: ".$name;

and I now get this error:

Code: Select all

Warning: mysql_result(): Unable to jump to row 0 on MySQL result index 53
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post by GM »

Hi,

Now you've got user_book and userbook in the query - they need to be the same.

Also, your variable $name is a result set object. You were nearer with your first code, in that you need to extract the variables from the result set before you can use them, using mysql_fetch_array.
Post Reply