Help displaying results involving multiple databases

[cs1h]

Hi,

 

I have a script that I hoped would display the first 3 results it finds but it only displays one. To do this though it has to access 3 databases, the first one to get the code for the correct table in the second database, the second one to get the main information and from that the third one to get the link to the persons avatar.

 

 

The code is:

<?php 

mysql_connect("localhost","fds","dsf"); 

mysql_select_db("redddddl") or die("Unable to select database");

$art = $_GET['id'];
$sql = "SELECT * FROM items WHERE id=$art";

$result = mysql_query($sql);
$num_rows = mysql_num_rows($result);
if($num_rows == 0) {
echo "No results please try a different <a href=asearch.html>search</a>.";
} else {
while($row = mysql_fetch_array($result)) {
$code1 = $row['code1']; 
$code2 = $row['code2'];
}
}

mysql_connect("localhost","fds","dddd"); 

mysql_select_db("cddd") or die("Unable to select database"); 

$sql = "SELECT * FROM `$code1 by $code2` ORDER BY id DESC LIMIT 3";

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

$num_rows = mysql_num_rows($result);
if($num_rows == 0) {
echo "";
} else {
while($row = mysql_fetch_array($result)) {
$CommAuth = $row['name']; 
$Comment = $row['message'];
$horde = $row['horde'];
$title = $row['title'];
}
}

mysql_connect("localhost","fffssh","fdgdf"); 

mysql_select_db("sdfgfdgfdy") or die("Unable to select database");

$sql2 = "SELECT * FROM ac_users WHERE username='$title' LIMIT 3";

$result2 = mysql_query($sql2);
$num_rows2 = mysql_num_rows($result2);
if($num_rows2 == 0) {
echo "No results please try a different <a href=reports.php>search</a>.";
} else {
while($row2 = mysql_fetch_array($result2)) {
$CommAvatar = $row2['avatar']; 
}
}

echo "<div id=\"comment\">
<img src=\"$CommAvatar\" width=\"56\" height=\"56\" class=\"comment_photo\" />
<div id=\"comment_header\"><span class=\"comment_author\"> $CommAuth </span><span class=\"comment_wrote\">wrote...</span><span class=\"comment_stars\"><img src=\"stars_5.png\"/></span></div>
<div class=\"comment_text\">$Comment</div>
<div id=\"comment_footer\"><span class=\"comment_delete\">Report | Comment on $CommAuth Profile | Message $CommAuth</span></div>
</div>"

?>

 

Does any one know if this will work or how to make it work?

 

All help is very much appreciated.

 

Thanks,

Colin

[jonsjava]

<?php 

mysql_connect("localhost","fds","dsf"); 

mysql_select_db("redddddl") or die("Unable to select database");

$art = $_GET['id'];
$sql = "SELECT * FROM items WHERE id=$art";

$result = mysql_query($sql);
$num_rows = mysql_num_rows($result);
if($num_rows == 0) {
echo "No results please try a different <a href=asearch.html>search</a>.";
} else {
while($row = mysql_fetch_array($result)) {
$code1 = $row['code1']; 
$code2 = $row['code2'];
}
}

mysql_connect("localhost","fds","dddd"); 

mysql_select_db("cddd") or die("Unable to select database"); 

$sql = "SELECT * FROM `$code1 by $code2` ORDER BY id DESC LIMIT 3";

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

$num_rows = mysql_num_rows($result);
if($num_rows == 0) {
echo "";
} else {
$CommAuth = array();
$Comment = array();
$horde = array();
$title = array();
while($row = mysql_fetch_assoc($result)) {
$CommAuth[] .= $row['name']; 
$Comment[] .= $row['message'];
$horde[] .= $row['horde'];
$title[] .= $row['title'];
}
}

mysql_connect("localhost","fffssh","fdgdf"); 

mysql_select_db("sdfgfdgfdy") or die("Unable to select database");

$sql2 = "SELECT * FROM ac_users WHERE username='$title' LIMIT 3";

$result2 = mysql_query($sql2);
$num_rows2 = mysql_num_rows($result2);
if($num_rows2 == 0) {
echo "No results please try a different <a href=reports.php>search</a>.";
} else {
$CommAvatar = array();
while($row2 = mysql_fetch_array($result2)) {
$CommAvatar[] .= $row2['avatar']; 
}
}
$count = 0;
foreach ($CommAuth as $value){

print "<div id=\"comment\">
<img src=\"{$CommAvatar[$count]}\" width=\"56\" height=\"56\" class=\"comment_photo\" />
<div id=\"comment_header\"><span class=\"comment_author\"> {$value} </span><span class=\"comment_wrote\">wrote...</span><span class=\"comment_stars\"><img src=\"stars_5.png\"/></span></div>
<div class=\"comment_text\">{$Comment[$count]}</div>
<div id=\"comment_footer\"><span class=\"comment_delete\">Report | Comment on {$value} Profile | Message {$value}</span></div>
</div>";
$count++
}
?>

added it all into separate arrays, and then requested the array info

[cs1h]

Hi, thanks for the help its a great help, but its not getting any results for the avatar any idea why this may be?

 

Apart from that it seems to work.

 

Thanks,

Colin

[jonsjava]

no idea. could it be an SQL issue? I don't know your tables, and I'm about to go to bed...soooooo..................tired............zzzzzzzzzzzzzzzzzzzz

[AndyB]

Just curious, but why do you have three databases instead of one database with multiple tables?