Query multiple tables with duplicate data

So the prices are different in the two tables?

So the prices are different in the two tables?

Yes, product names will be very similar , prices different.

have multiple vendors , so I want the ability to perform a seach against the tables and have the Products shows via union all with the prices in different colums

Products | Price tbl1 | Price tbl2| price tble3 |

Trying to wrap my head around it still.

similar and the same are two different things. If you know for sure they will be the same then you can link the two tables together.

Give this a try. It will, however assume that the same product does in fact lie within both tables. If the product isn't in both tables, then I don't think it will return that item at all. If that's the case, then you'll need some sort of outer join.

$query = "SELECT table1.product,table1.price,table2.price FROM table1, table2 WHERE table1.product = table2.product";

$result = mysql_db_query($database,$query);
while ($row = mysql_fetch_array($result))
{
     echo $row[0]." - $".$row[1].", $".$row[2]."<br>";
}

Try this for a solution.
save the following as a php page or html doesn't matter.
search.php or search.html

<form name='search' action='search1.php' method='post'>
<CENTER>
<TABLE>
<TR>
    <TD>Search for Product:</TD>
    <TD><input type="text" name="product" size="25"></TD>
</TR>
</TABLE>
<CENTER>
<input type='submit'  name="submit"  value="Search For Product"'>
</form>

Save the following as search1.php

<?
// your connection to the server
require("includes/dbconnect.php");
?>
<?
//post sent from search form.
if(isset($_POST['submit'])) 
{
$product = addslashes(@$_POST['product']);
}
// Begin your table outside of the array 
echo "<table width='100%' border='0' cellpadding='4' cellspacing='0'> 
    <tr> 
        <td width='10%' bgcolor='#BBDDFF'><b>ID #</b></td> 
        <td width='20%' bgcolor='#BBDDFF'><b>Product</b></td>
        <td width='30%' bgcolor='#BBDDFF'><b>Tbl #1 Price</b></td>
    <td width='20%' bgcolor='#BBDDFF'><b>Tbl #2 Price</b></td> 
    <td width='20%' bgcolor='#BBDDFF'><b>Private</b></td>
    </tr>"; 
// Define your colors for the alternating rows 

$color1 = "#ffffff"; 
$color2 = "#ccffff";  
$row_count = 0; 
// Perform an statndard SQL query: 
$page = $_GET['page'];
$limit = 150;
$query_count = "SELECT * FROM n inner join p on n.product = p.product WHERE n.product LIKE '%$product%' order by n.product asc";
$result_count = mysql_query($query_count);
$totalrows = mysql_num_rows($result_count);
if(empty($page)){
$page = 1;
}
$limitvalue = $page * $limit - ($limit);

$sql_events = mysql_query("SELECT * FROM n inner join p on n.product = p.product WHERE n.product LIKE '%$product%' order by n.product asc LIMIT $limitvalue, $limit") or die (mysql_error());
// We are going to use the "$row" method for this query. This is just my preference. 
        
while ($row = mysql_fetch_array($sql_events)) { 
$Id = stripslashes($row[0]);
$product = stripslashes($row[1]);
$price = stripslashes($row[2]);
$price1 = stripslashes($row[3]);
$private = stripslashes($row[4]);

    /* Now we do this small line which is basically going to tell 
    PHP to alternate the colors between the two colors we defined above. */ 

    $row_color = ($row_count % 2) ? $color1 : $color2; 
                
    // Echo your table row and table data that you want to be looped over and over here. 

    echo "<tr> 
    <td bgcolor='$row_color'>$Id</td>  
    <td bgcolor='$row_color'>$product</td>
    <td bgcolor='$row_color'>$$price</td>
    <td bgcolor='$row_color'>$$price1</td>
     <td bgcolor='$row_color'>$private</td>
    </tr>"; 
    // Add 1 to the row count 
    $row_count++; 
} 
// Close out your table. 
echo "</TABLE>";
echo "<center><hr color=#c0c0c0>";
if($page != 1)
        {
            $pageprev = $page-1;
            echo("<a href=\"$PHP_SELF?page=$pageprev\">PREV</a>&nbsp;&nbsp;&nbsp;");
        }
        else
            {
                echo("PREV&nbsp;&nbsp;&nbsp;&nbsp;");
            }

    $numofpages = $totalrows / $limit;

    for($i = 1; $i <= $numofpages; $i++)
    {
        if($i == $page)
            {
            echo($i."&nbsp;");
            }
        else
            {
            echo("<a href=\"$PHP_SELF?page=$i\">$i</a>&nbsp;");
            }
    }
    if(($totalrows % $limit) != 0)
    {
    if($i == $page)
        {
        echo($i."&nbsp;");
        }
    else
        {
        echo("<a href=\"$PHP_SELF?page=$i\">$i</a>&nbsp;");
        }
    }

    if(($totalrows - ($limit * $page)) >=0)
    {
    $pagenext = $page+1;

    echo("&nbsp;&nbsp;&nbsp;<a href=\"$PHP_SELF?page=$pagenext\">NEXT</a>");
    }
    else
    {
    echo("&nbsp;&nbsp;&nbsp;NEXT&nbsp;");
    }
echo "</center>";
?>

Hope this helps.
Arty

Thanks Arty56 ,
When I execute the code I get this return in the browser

PREV "); } else { echo("PREV "); } $numofpages = $totalrows / $limit; for($i = 1; $i <= $numofpages; $i++) { if($i == $page) { echo($i." "); } else { echo("$i "); } } if(($totalrows - ($limit * $page)) >=0) { $pagenext = $page+1; echo(" <A href="http://127.0.0.1/%22$PHP_SELF?page=$pagenext\"">NEXT"); } else { echo(" NEXT "); } echo "
"; ?>

I've double checked everything...any ideas

Thanks Arty56 ,
When I execute the code I get this return in the browser

PREV "); } else { echo("PREV "); } $numofpages = $totalrows / $limit; for($i = 1; $i <= $numofpages; $i++) { if($i == $page) { echo($i." "); } else { echo("$i "); } } if(($totalrows - ($limit * $page)) >=0) { $pagenext = $page+1; echo(" <A href="http://127.0.0.1/%22$PHP_SELF?page=$pagenext\"">NEXT"); } else { echo(" NEXT "); } echo "
"; ?>

I've double checked everything...any ideas

I have checked the script and the only change I made was to remove line 4 ?> and line 5 <?php

I also see you have added a http://127.0.0.1/%22 infront of $PHP_SELF.
testing on your own computer should not require an IP address.
and diffinately not required on your hosting server.
$PHP_SELF is telling php to use the same php page, so that maybe throwing an error.
I use $PHP_SELF instead of typing in search1.php.
As far as why the php code is showing check to see if you have removed or added an extra ?> in the code just before that error.
or you have a <?php missing.
Because it is reading the code as text and not php.
If you use the code as given without adding the IP address it will work.
I use it on all my sites.

Example 1:
The example shows that the second segment of code is missing the opening php tag. Php should throw up an error instead of echoing the second segment of code.

<?php
echo " some php code";
?>

echo "some more php code";
?>

Example 2:
The example shows that the second segment of code is missing the opening and closing php tag. So it will be parsed as text and not php
<?php
echo " some php code";
?>
echo "some more php code";

--Arty