[RESOLVED] SQL JOINS :( help ?

[TheStalker]

ok this is the first time ive ever tried to use SQL joins within PHP.

i have two tables "userdetails" and "plan"

i need just title, firstname and surname from the "userdetails" table and all* from the "plan" table.

This was the original SQL statment i came up with to get the right data from the "plan" table

$data = mysql_query("SELECT * FROM plan WHERE ref = '".$ref."' AND link ='Ref1' ");

This works fine to get the data from the "plan" table but i need the title, firstname and lastname from the "userdetails"

This is the join that ive come up with. It does NOT work as i would like it to.

$data = mysql_query("SELECT* FROM  details, plan WHERE userdetails.oref = '".$ref."'AND plan.link ='Ref1' ");

When i echo the the data into a table in my while loop it echos the right title, firstname and surname but also echos all the other records from the "plan" table.

Can anyone help ??? im sure im prob going the wrong way about this so any advice would be great 🙂

[laserlight]

How are the tables related?

[TheStalker]

they both have ref in the table. I have it stored within the page in $ref

[laserlight]

In that case I would expect something like this:

$sql = sprintf("SELECT userdetails.title, userdetails.firstname, userdetails.surname, plan.*
    FROM userdetails, plan
    WHERE userdetails.oref = plan.ref AND plan.ref = '%s' AND plan.link = 'Ref1'",
    mysql_real_escape_string($ref));
$result = mysql_query($sql);

[djjjozsi]

here you can read about this issue:

http://www.w3schools.com/Sql/sql_join_inner.asp

Hello

jjozsi

[laserlight]

Actually, you are already using an inner join with your first example, except with a different syntax. In any case, I asked how the tables are related because with the join columns known, the join can be constructed correctly (hence the "userdetails.oref = plan.ref" clause in my example).

[TheStalker]

Cool thanks ill give it ago and let you know how i get on with it 🙂

[TheStalker]

Ok ive tried the code you gave me and am now getting the following error

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\Program Files\xxxxxxx\xxxxxxxxx on line 41

my while loop is below:

while($row = mysql_fetch_array($result)) 
	{
	echo "<table class='' border='1' width ='500'>";
		echo "<tr>";
			echo "<td>Name: </td>";
			echo "<td> " .$row['title']." ".$row['firstname']." ".$row['surname']."</td>";
		echo "</tr>";

etc
etc

[laserlight]

To diagnose the problem change this:

$result = mysql_query($sql);

to:

$result = mysql_query($sql) or die('Could not execute SQL statement: ' . mysql_error());

[TheStalker]

my fault , yours did work thanks for the help 🙂 i had somthing in the wrong place.

[TheStalker]

Thanks very much i think i understand the syntax of joins a little now lol🙂

[djjjozsi]

write after the query the mysql_error() to know what is the problem with your query.

or test in phpmyadmin,

and post that query to us with the mysql error message too..

[TheStalker]

ive sorted it now i had one of the tables named wrong in mine, thanks for the tip tho 🙂

[laserlight]

TheStalker wrote:

Thanks very much i think i understand the syntax of joins a little now lol

You're welcome, and if you made the change I suggested, remember that it is for debugging purposes only. In production you should handle such errors more gracefully.

Oh, and remember to mark this thread as resolved using the thread tools.