0

Hi , I've been working on comparison query with PHP and MySql 5.0 yet not getting anywhere with it.

I'm trying to perform a search ( query )

I have several tables within the same database that contain same fields yet hold different data

eg
Table 1
id |Product | Price | Private

Table 2
id| Product | Price | Private

etc...

What I'm trying to do is have the ability to search the tables and display it as follows

Products (tables 1 & 2 ) | Price( Table 1 ) | Price (Table 2 )

I've been playing with UNION

$query = "Select `product`, `private` as p, 'table1' as n from `table1` where `name` REGEXP '".$trimmed."' UNION ALL Select `product`, `private` as p, 'table2' as n from `table2` where `name` REGEXP '".$trimmed."' ORDER by `product`";

Output in php as follows

while($row = mysql_fetch_array($result))
 
 
 
                                                 {
                                                 echo "<tr>";
 
                                                 //echo "<td>" . $row['Product'] . "</td>";
                                                                // echo "<td>" . $row['Product'] . "</td>";
                                                                // echo "<td>" . $row['private'] . "</td>";
                                                                // echo "<td>" . $row['id'] . "</td>";
                                                                // echo "<td>" . $row['table1.price'] . "</td>";
                                                                // echo "<td>" . $row['table2.price'] . "</td>";
                                                                // echo "</tr>";
 
                                                 echo "<td>" . $row['name'] . "</td>";
                                                 echo "<td>" . $row['n'] . "</td>"; 
                                                 echo "<td>" . $row['p'] . "</td>"; 
                                                 echo "</tr>";
                                                 }

So far I cannot seem to get the products nor pricing in line , any ideas ?

4
Contributors
6
Replies
7
Views
10 Years
Discussion Span
Last Post by arty56
0

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.

0

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>";
}
0

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

0

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

0

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

This topic has been dead for over six months. 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.