PHP Inner Join Question 2

[sd0t1]

Hello everyone, I am new to JOINS in my programming. I continue to get a blank screen with the code below.
The SQL select statement works by itself, but when I try to output it in a WHILE loop I get a blank screen.

Any help is appreciated, Thanks.


$query = "SELECT DISTINCT program.program_name FROM program INNER JOIN schedule ON program.id = schedule.pid ";
$result = mysql_query($query, $user_login) or die(mysql_error());


while ($row = mysql_fetch_array($result));
{
echo $row['program.program_name'];
}

 

[Vragabond]

I'm not sure but I believe PHP does not include table name in the columns it retrieves from the query, so I would try:

while ($row = mysql_fetch_array($result));
    {
        echo $row['program_name'];
    }

___________________________________________________________
[small]Do something about world cancer today: Comprehensive cancer control information at PACT[/small]

 

[sd0t1]

Thanks for the response. But I did try that and still... nothing.

 

[IPGuru]

1 try mysql_fetch_assoc intdead of mysql_fech_array

if that fails try Var_dump($row) to see what has actualy been retrived.

 

[jpadie]

is that a valid use of inner join? i think i'd use a subselect (assuming you want the program names contained in all rows of the programs table to the extent that there is a matching entry in the schedule table.

anyway - i agree that for debugging the best thing is to dump raw data to the screen. if you are getting nil results (which i suspect is the case) then i'd look at your query and check out with the chaps in the mysql forum.

 

[sd0t1]

Hello guys, sorry I wasn't able to respond earlier I've been occupied with starting a business.

IPGuru, I tried you suggestion and still nothing. When I Var_dump it came back 'NULL' just as JPadie suspected.

JPadie I did post the problem in the mysql forum. I found a similiar post, but they told the poster that they weren't familiar with PHP. Then he found the answer on his own but didn't post what the answer was.
Hopefully someone will be able to help me anyway.

My query however works fine in my SQL editor, it's when I put it in the php While loop is when it becomes 'NULL'.

anybody have any other suggestions?

 

[jpadie]

try this query and code and see what is returned. it's assumed you make the connection and select the database above.

$query = "SELECT program_name FROM program where program.id in (select pid from schedule) ";
$result = mysql_query($query) or die(mysql_error());
$nR = mysql_num_rows($result);
echo 'returned ' .$nR.' rows from the query <br/>";
while ($row = mysql_fetch_assoc($result)){
 echo "<pre>".print_r($row, true) . "</pre>";
}

 

[sd0t1]

This is the wierdest thing. I'm not sure why, but this is what worked. (I haven't tried your example yet JPadie, but I will and let you know)

It seems when I broke the SELECT statement up into sections and concatenated them it worked. I saw a tutorial on

http://www.tizag.com

do it that way so I tried it. it's the same as before just seperated into concatenated sections.

$query = "SELECT program.program_name, program.id ".
"FROM program, schedule ".
"WHERE program.id = schedule.pid";

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

$list = "<select name='program_list' id='program_list' onchange='ajaxFunction($row[id]);'>";
while($row = mysql_fetch_array($result))
{
$list .= "<option value='$row[id]'>$row[program_name]</option>";

}
$list .= "</select>";

 

[jpadie]

it's not the same. you have program_id in the query as well and the query is 'well-formed' - i.e. the syntax is correct and the use of the where clause to create a late join is ok. concatenation will make no difference.