Having Trouble with a Table join

reub77

I'm not getting any results when I join a table with another one. When I just call one of them the results appear just fine. I've done this before and never had a problem. Isn't that the way it always is? Here's my database call:

  $conn = db_connect();
  $query = "SELECT * FROM movies, users WHERE id.movies='".$id."' AND owner.movies = name.users AND  owner.movies='".$OWN."'";

//execute query
$result = mysql_query($query);
$x = 0;
while ($x < mysql_num_rows($result)) :
   $ID = mysql_result($result, $x, 'id.movies');
   $mName = mysql_result($result, $x, 'movieName.movies');
   $own = mysql_result($result, $x, 'owner.movies');
   $Email = mysql_result($result, $x, 'email.users');
   $x++;
endwhile;
mysql_close();

Here's my error

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/shabangw/public_html/dvdDepot/formmail.php on line 17

I don't get this error if I don't join the tables. I've obviously screwed up somewhere in my syntax.

TheIceman5

can someone tell me why people use this first example over the second example like i use.

while ($x < mysql_num_rows($result)) : 
   $ID = mysql_result($result, $x, 'id.movies'); 
   $mName = mysql_result($result, $x, 'movieName.movies'); 
   $own = mysql_result($result, $x, 'owner.movies'); 
   $Email = mysql_result($result, $x, 'email.users'); 
   $x++; 
endwhile;

this is how i normally do it, why dont others?? is my way fuked or dont people know how to do it the easy and seemingly more efficient way??

while ($x=mysql_fetch_array($result)){ 
   $ID = $x["id"]; 
   $mName = $x["movieName] 
   $own = $x["owner"]; 
   $Email = $x["email"]; 
}

Anyway, whats the point of this loop any how? it will only save one record at a time, the last record, the others will be deleted.

reub77

I only need one record. You didn't really solve my problem. I want to know why my table join isn't working. I didn't ask what is a more efficient way to do the code I'm doing? If you can, answer the question.

thx

ahundiak

What does mysql_error() indicate?
My guess is that either $id or $own is not set.

Also, this:
while ($x < mysql_num_rows($result)) :
Is not a valid while loop.

reub77

What does mysql_error() indicate?
My guess is that either $id or $own is not set.

$id and $OWN are set. I thought that too but I made them print out on the page that I was using them on and they did show up. As well they were sent through the header and I can see them.

Also, this:
while ($x < mysql_num_rows($result)) :
Is not a valid while loop.

I've been using this while loop successfully for quite some time now. It does the trick when I am only using one table. The moment I start joining tables is where the values turn into empty values. My guess is that maybe my syntax is off somehow or maybe you're somewhat right in that I can't use

while ($x < mysql_num_rows($result)) :

when I'm using more than one table. All I'm hoping for is someone to say why I can't do it and to provide an alternate solution that will work for this instance. And then I'd like to know why the solution would work better.

To tell you the honest truth, my code is code that I've scavenged from this site as well as others. If there are better ways to do things I'm all ears. I'd just like to know why or I'm reluctant to change it.

TheIceman5

i gave you better code but u didnt seem to want it.
Only other thing i can suggest is get microsoft access out and do up the query in that and copy and paste it into ya script and repplace the variables and it should work, that what i do with queries that dont wanna work and it works 99% of the time.

ahundiak

You are right about the while statement. I haven't see the : syntax used before but it's valid.

But you should run add:
if (mysql_errno()) die(mysql_error());
after mysql_query() and look at the results.

The error message you posted indicated an invalid query. mysql_error will provide some more information.

tsinka

Hi,

there are some errors in the query ... you use field.table in your where clause, you must use table.field, e.g.

$query = "SELECT * FROM movies, users WHERE movies.id='".$id."' AND movies.owner = users.name AND  movies.owner='".$OWN."'";

If you do not need all fields of movies and users I'd suggest to specifiy which fields you want to select instead of using *.

reub77

I used to have the specific fields I needed typed in, but for testing I put in the *. The error message helped me to ascertain that I had just put the field.table thing around the wrong way. It's supposed to be table.field. I guess when I start doing more joins in my database SQL coding I'll not make this same mistake again. The error checking line you gave me is brilliant. Saved me so much time, thanks.