Coding help

chuck33

Hi all,

I need help again.

I have 2 tables, table a with 3 fields, table b with 2 fields.

Here is the code:

<html>
<head>
<title>MySQL Example</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<body text="#000000">
<p>
<?php
$db = mysql_pconnect("localhost", "username", "password");
if (!$db)
{

echo "Unable to make a connection to the database";
exit;
}

mysql_select_db("database");

// Define a SQL Select Query

$query = "SELECT table_a.name, table_a.lyrics, table_a.al_id, table_b.name, table_b.id
FROM table_a, table_b
WHERE table_a.al_id = table_b.id
AND LENGTH(table_a.lyrics) > 0
ORDER by table_b.name";

$result = mysql_query($query);
$num_results = mysql_num_rows($result);

echo "<p>Number of Lyrics found: ".$num_results."</p>";

// Display the results of the query in a table

print "<center><table width=\"600\" border=\"0\" cellpadding=\"5\" cellspacing=\"0\">\n";

print "<tr><td><b>Movie</b></td><td><b>Song</b></td><td><b>Lyrics</b></td></tr>";

while($row = mysql_fetch_array($result))
{

echo "<tr><td>";

echo ($row["table_a.name"]);

echo "</td><td>";

echo ($row["table_b.name"]);

echo "</td><td>";

echo "<a href=\"".$row ['table_a.lyrics']."\">Link</a>";
echo "</td>";
echo "</tr>";
echo "</table></center>";
}

</body>
</html>

The code should give 3 columns back with movie|song|lyrics

This is what I get:

<p>Number of Lyrics found: 578</p>
<center><table width="600" border="0" cellpadding="5" cellspacing="0">
<tr><td><b>Movie</b></td><td><b>Song</b></td><td><b>Lyrics</b></td></tr>
<tr><td></td><td></td><td><a href="">Link</a></td></tr>
</table></center>

the line with the link just loops 578 times.

I put the sql code into phpmyadmin and it returned the correct data.

why am i not getting any data here!!!!!!!?

Please help me if u can.

thanks
chuck

rohithmr

I think its cuz u when u display ur results u use $row['table_a.name'] instead of $row['name']. etc. When you have two fields with the same name, jus use the SQL 'AS' cluase to rename one of them. Read the $sql of t his code to se what i mean.

I bet you have your error_reporting to not show Notices.

try:

<html> 
<head> 
<title>MySQL Example</title> 
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"> 
</head> 
<body text="#000000"> 
<p> 
<?php 
$db = mysql_pconnect("localhost", "username", "password"); 
if (!$db) 
{ 
echo "Unable to make a connection to the database"; 
exit; 
} 

mysql_select_db("database"); 

// Define a SQL Select Query 

$query = "SELECT table_a.name as aname, table_a.lyrics, table_a.al_id, table_b.name as bname, table_b.id 
FROM table_a, table_b 
WHERE table_a.al_id = table_b.id 
AND LENGTH(table_a.lyrics) > 0 
ORDER by table_b.name"; 

$result = mysql_query($query); 
$num_results = mysql_num_rows($result); 

echo "<p>Number of Lyrics found: ".$num_results."</p>"; 

// Display the results of the query in a table 

print "<center><table width=\"600\" border=\"0\" cellpadding=\"5\" cellspacing=\"0\">\n"; 
print "<tr><td><b>Movie</b></td><td><b>Song</b></td><td><b>Lyrics</b></td></tr>"; 

while( ($row = mysql_fetch_array($result)) !== false ) 
{ 
echo "<tr><td>"; 
echo ($row["aname"]); 
echo "</td><td>"; 
echo ($row["bname"]); 
echo "</td><td>"; 
echo "<a href=\"".$row ['lyrics']."\">Link</a>"; 
echo "</td>"; 
echo "</tr>"; 
echo "</table></center>"; 
} 

?> 

</body> 
</html>

chuck33

thanks man!!!!!!!!!!!

That did the trick.

cheers
chuck