Jump to content


Photo

sql statement to select from multiple tables


  • Please log in to reply
3 replies to this topic

#1 debcous

debcous
  • Members
  • PipPip
  • Member
  • 11 posts

Posted 24 April 2006 - 02:03 PM

<?php
include "db.php";

$submit=$HTTP_POST_VARS["submit"];
$reset=$HTTP_POST_VARS["reset"];

$source =$HTTP_POST_VARS["source"];
$destination=$HTTP_POST_VARS["destination"];
$travelday=$HTTP_POST_VARS["travelday"];

$query = "SELECT * FROM bus AND train where bus.source='$source' and bus.destination='$destination' and bus.travelday='$travelday' and train.source='$source' and train.destination='$destination' and train.travelday='$travelday'";


$result = @mysql_query($query, $conn);
if($result){
echo'
<table align="center" cellspacing="0" cellpadding="5" bgcolor="white" border=0 bordercolor="#0099CC" font="white">

<td align="left" bgcolor="#0099CC"><center><font color="white"><b>Source</b></td>

<td align="left" bgcolor="#0099CC"><center><font color="white"><b>Destination</b></td>

<td align="left" bgcolor="#0099CC"><center><font color="white"><b>Departure Time</b></td>

<td align="left" bgcolor="#0099CC"><center><font color="white"><b>Arrival Time</b></td>

<td align="left" bgcolor="#0099CC"><center><font color="white"><b>Travel Day</b></td>

<td align="left" bgcolor="#0099CC"><center><font color="white"><b>Operator</b></td></tr>';

while($row = mysql_fetch_array($result, MYSQL_ASSOC)){
echo'<tr>
<td align="center">'.$row['source'].'</td>
<td align="center">'.$row['destination'].'</td>
<td align="center">'.$row['dept_time'].'</td>
<td align="center">'.$row['arr_time'].'</td>
<td align="center">'.$row['travelday'].'</td>
<td align="center">'.$row['operator_name'].'</td></tr>';

echo'</table>';}

}

else{
echo'<h1> System Error </h1> table ';
exit();
}
mysql_close();

?>


Hi...I'm trying to display details from 2 seperate tabels in my database onto the screen in the form of a table. Can someone please tell me if the code i am using is correct for this function. At the moment it is displaying 'system error'. I would really appreciate your help.

Debbie


#2 shocker-z

shocker-z
  • Members
  • PipPipPip
  • Advanced Member
  • 864 posts
  • LocationNottingham

Posted 24 April 2006 - 02:45 PM

change

$query = "SELECT * FROM bus AND train where bus.source='$source' and bus.destination='$destination' and bus.travelday='$travelday' and train.source='$source' and train.destination='$destination' and train.travelday='$travelday'";

to

$query = "SELECT * FROM bus,train where bus.source='$source' and bus.destination='$destination' and bus.travelday='$travelday' and train.source='$source' and train.destination='$destination' and train.travelday='$travelday'";


just needed , in place of AND and that should now work for you :)


Regards
Liam
www: www.ukchat.ws | irc: irc.ukchat.ws chan: #blufudge

#3 debcous

debcous
  • Members
  • PipPip
  • Member
  • 11 posts

Posted 24 April 2006 - 03:11 PM

[!--quoteo(post=367997:date=Apr 24 2006, 09:45 AM:name=shocker-z)--][div class=\'quotetop\']QUOTE(shocker-z @ Apr 24 2006, 09:45 AM) View Post[/div][div class=\'quotemain\'][!--quotec--]
change

$query = "SELECT * FROM bus AND train where bus.source='$source' and bus.destination='$destination' and bus.travelday='$travelday' and train.source='$source' and train.destination='$destination' and train.travelday='$travelday'";

to

$query = "SELECT * FROM bus,train where bus.source='$source' and bus.destination='$destination' and bus.travelday='$travelday' and train.source='$source' and train.destination='$destination' and train.travelday='$travelday'";
just needed , in place of AND and that should now work for you :)
Regards
Liam
[/quote]


i had that already but gave me the same error. does the rest of the statement look ok to you?


#4 shocker-z

shocker-z
  • Members
  • PipPipPip
  • Advanced Member
  • 864 posts
  • LocationNottingham

Posted 24 April 2006 - 04:12 PM

had a quick look and looks fine, try adding or die(mysql_error());


$result = @mysql_query($query, $conn) or die(mysql_error());


and that should say where abouts the error is.. then paste back in here and we can have a look :)
www: www.ukchat.ws | irc: irc.ukchat.ws chan: #blufudge




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users