0

I need one result from 3 tables. The JOIN query fields of the first table repeat throughout the 3 tables. Is the JOIN query set out incorrectly or do I have to re-define the $row index in some way to avoid repetition of the fields.
I have been using 3 separate SELECT queries to obtain the results (without error) but wanted to try JOIN.
Is there any advantge in using JOIN anyway ?
Many thanks to anyone that can explain.
`

<?php //3 MySQL queries-allnames,launch,fate
error_reporting(E_ALL & ~E_NOTICE);              // set to ignore Notices 
if (isset($_POST['name'])) $name = ($_POST ['name']);
else $name = "no name entered";
if($name== "")
{
echo "<p><font color='red' size='6px'>You have not entered a name</font></p>";
exit;
}
else 
{
echo"<br />";
echo "<font color='#3F7cef'size='4'/>You searched for:</font>";
echo "<p>";
echo "<font color='#3F7cef'size='6'>";
require_once "fix_it.php";
echo fix_it($name);
echo"</font>";
}
require_once 'dbconnect.php';
  $name = strtoupper($name);
  $name = strip_tags($name);
  $name = trim ($name);
$query = "SELECT * FROM launch JOIN allnames ON allnames.id=launch.id JOIN fate ON fate.id=launch.id WHERE name1 LIKE '$name%' OR name2 LIKE '$name%' OR name3 LIKE '$name%' OR name4 LIKE '$name%' OR name5 LIKE '$name%' OR name6 LIKE '$name%' OR name7 LIKE '$name%' OR name8 LIKE '$name%' OR name9 LIKE '$name%' OR name10 LIKE '$name%' OR name11 LIKE '$name%' OR name12 LIKE '$name%'OR name13 LIKE '$name%' OR name14 LIKE '$name%' OR name15 LIKE '$name%'OR name16 LIKE '$name%' OR name17 LIKE '$name%'";
$result = mysql_query($query);
if (!$result) die ("Database access failed: " . mysql_error());
$rows=mysql_num_rows($result);
if(mysql_num_rows($result)==0)
{
echo "<p><font color='red' size='6px'>No data found for your request. Try a different query</font></p>";
exit;
}
while($row = mysql_fetch_array($result)){
    require_once "launch_tableID.php";                //prints launch table with ID number
    echo launch_table();
    echo "<tr><td>";
    echo $row[0];
    echo "</td><td>";
    echo $row[1];   
    echo "</td><td>";
    echo $row[2];   
    echo "</td><td>";
        echo $row[3];   
    echo "</td><td>";
    echo $row[4];   
    echo "</td><td>";
    echo $row[5];   
    echo "</td><td>";
if($row[6] > 0000-00-00)
{
$row[6] = date('d m Y',strtotime($row[6]));
}
else
{
$row[6] = 0;
}   
    echo $row[6];   
    echo "</td></tr>";
    require_once "names_table.php";                             // sets allnames table
    echo names_table();     
    echo "<tr><td>$row[2]</td><td>$row[3]</td><td>$row[4]</td><td>$row[5]</td><td>$row[6]</td><td>$row[7]</td><td>$row[8]</td><td>$row[9]</td><td>$row[10]</td><td>$row[11]</td><td>$row[12]</td><td>$row[13]</td><td>$row[14]</td><td>$row[15]</td><td>$row[16]</td><td>$row[17]</td></tr>";     
    require_once "fate_table.php";                // sets fate table
    echo fate_table();
    echo "<tr><td>";
    echo $row['1']; 
    echo "</td><td>";
    echo $row['2']; 
    echo "</td></tr>";    
    echo" </table>";
}   
mysql_close($db_server);
function get_post($var)
{
    return mysql_real_escape_string(htmlentities(trim(strip_tags($_POST[$var]))));
}
?>
2
Contributors
2
Replies
3
Views
4 Years
Discussion Span
Last Post by furlanut
0

first joins are fine then runing 3 times query

you must select col names youwant in select statment to avoid duplicat columsn

SELECT launh.name as lname, ......... FROM launch

then instead of col index , use column name

echo $row['lname'];
0

Thank you urtrivedi.
As the JOIN was correct,I did eventually work something out using at line 34:
$newArray=$row
print_r($newArray)
This showed me the col index which ran from [1] to [33] and I used these in the various tables as for instance

    echo "<tr><td>";
    echo $row[28];                         // sets fate.events field
    echo "</td><td>";
    echo $row[29];                         // sets fate.fate field
    echo "</td></tr>";    
    echo" </table>";
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.