Help with multiple queries in one file with multiple tables

[phpnewbie1979]

Hello everyone

 

I need some help pulling data from two different tables. My problem is that the file I'm using to pulled the data is already running a query that selects data based on a link from a previous file. I need to be able to keep the code pulling data from the other file plus run another query that will select data from another table. I hope that makes since. The file currently pulls data from a table that holds neighborhood information. I need it to also pull data from another table that holds school information. Both tables have a common field but I just can't figure out how to pull the code for the school information. Any help would be greatly appreciated.

 

My code is:

<?php 
require_once "connect_to_mysql.php";
$query = "SELECT * FROM table WHERE id=".$_GET['id'];
$result=mysql_query($query);
while($row = mysql_fetch_array($result)){
$b1 = $row["fieldzero"];
$b2 = $row["fieldone"];
$b3 = $row["fieldtwo"];
$b5 = $row["fieldthree"];
$b6 = $row["fieldfour"];
$b7 = $row["fieldfive"];
$b8 = $row["fieldsix"];
}

?>
<div id="hood_container">

<div class="hood_table">

<div class="hood_theader"><h2><?php echo $b1; ?></h2></div>
<div><?php echo $b2; ?></div>


</div>

<div class="hood_table"><?php echo $b3; ?></div>

<div class="hood_table">

<div><?php echo $b5; ?></div>
<div><?php echo $b6; ?></div>
<div><?php echo $b7; ?></div>
</div>

<div>

<div><?php echo $b8; ?></div>
</div>

[trq]

You need to look into sql joins.

 

ps; You should also look into naming your database fields and variables something with meaning.

[phpnewbie1979]

Thanks for the reply. My fields are not really the names above. I just changed it beacuse I was placing it on the internet. I know it can't be access but I'm just a litle paranoid.

Also, I know the basic concept of using sql joins but I can't figure out how to write the select statement without interfering with my current select statement.

 

My current select statement is:

 $query = "SELECT * FROM table WHERE id=".$_GET['id'];

As you can see with the WHERE clause this statement is pulling data from the database based on a link from a previous file . I can't figure

out how to incorporate the join statement to also pull data from my second table.

[Kiwii]

you can just do another query?

 

require_once "connect_to_mysql.php";
$query = "SELECT * FROM table WHERE id=".$_GET['id'];
$result=mysql_query($query);

$query2 = "SELECT * FROM table2";
$result2=mysql_query($query2);

while($row = mysql_fetch_array($result)){
$b1 = $row["fieldzero"];
$b2 = $row["fieldone"];
$b3 = $row["fieldtwo"];
$b5 = $row["fieldthree"];
$b6 = $row["fieldfour"];
$b7 = $row["fieldfive"];
$b8 = $row["fieldsix"];
}

while($row2 = mysql_fetch_array($result2)){
$b1 = $row2["fieldzero"];
$b2 = $row2["fieldone"];
$b3 = $row2["fieldtwo"];
$b5 = $row2["fieldthree"];
$b6 = $row2["fieldfour"];
$b7 = $row2["fieldfive"];
$b8 = $row2["fieldsix"];
}

 

 

not totally sure it will work but can't see why not?

[sasa]

"SELECT table1.* , table2.* FROM table1

LEFT JOIN table2 ON some_field.table1=field.table2

WHERE table1.id=".$_GET['id']

[phpnewbie1979]

Thanks for everyone's help with this. I tried to use sasa's join command and I got the following error messge: Parse error: syntax error, unexpected T_STRING in /hermes/bosweb/web125/b1554/ipw.username/public_html/filename.php on line 5.

 

I made sure that the table names and fields were correct. I also made sure that the two tables share a common field_name. Help please.

[sasa]

it's php error

you miss some ; in the end of line

[afam4eva]

it's php error

you miss some ; in the end of line

 

yeah, that's right.

[phpnewbie1979]

Thanks everyone,

 

I figured it out. I left out the " at the beginning of the select command. However, i'm getting another error now. I use "or die" command to find out what the problem is and it states: Error: Unknown column 'school_district.hood' in 'on clause'

school_district is the common field between the two tables and I made sure that both fields' name were exactly the same and that both fields contained content. From what I researched it is a bug in php but I don't know how to fix it.

[sasa]

ups i make mistake

syntaks is table.field

so change 'school_district.hood' to 'hood.school_district'

[phpnewbie1979]

It's working perfectly. Thank you so much