Help retrieving data from database

[cs1h]

Hi,

 

I have been trying with no success to gather information from 3 databases and display the information in one table. So far it is getting information from two tables but not the third, from what I can work out from playing with it is that the search works, it just isn't getting the search criteria from the results from the second database.

 

The script is,

 

<?php 

mysql_connect("localhost","sgd","xxx"); 

mysql_select_db("xxx") 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","xxx","xxx"); 

mysql_select_db("xxx") 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","xxx","xxx"); 

mysql_select_db("xxx") 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 2</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=\"/avatar/{$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++;
}
?>

 

Can anyone see why this may be?

 

All help will be much appriciated,

Colin

[Northern Flame]

before re-connecting and re-selecting databases,

use the mysql_close() function.

[cs1h]

Hi there,

 

I tried that but I kept getting error messages, I presume that I did it wrong. Could you show me how to do it, I'm not very good with php.

 

thanks for the help so far,

 

Colin

[Northern Flame]

try this (and if it doesnt work show me your error messages):

 

<?php 

mysql_connect("localhost","sgd","xxx"); 

mysql_select_db("xxx") 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_close();

mysql_connect("localhost","xxx","xxx"); 

mysql_select_db("xxx") 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_close();

mysql_connect("localhost","xxx","xxx"); 

mysql_select_db("xxx") 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 2</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=\"/avatar/{$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++;
}
?>

[cs1h]

Hi,

 

I made those changes and I get no errors, but its still not giving me the result that its ment to.

Any other ideas to why this may be?

 

Thanks,

Colin

[Northern Flame]

change these lines:

 

$result = mysql_query($sql);

$result2 = mysql_query($sql2);

 

 

to

 

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

$result2 = mysql_query($sql2)or die(mysql_error());

[cs1h]

Hi,

 

I made those changes and they don't make any difference to the displayed result nor do they show any errors.

 

Thanks,

Colin