Connecting three tables from the database

stoni23 · January 2, 2010

Before I start, I'll point out that I'm a newbie and trying to do most of the coding myself, using a previously working search function. Anyway, I'll explain the predicament as best as I can.

In a bit of pickle on this current task. My cart program has three different tables that it uses to complete an order. The tables are orders, cart, and cartoptions. My client wants to be able to search a phone number and a 6 digit pin and have it pull certain information. Only problem is that the 6 digit pin is on the cartoptions table and the phone number is on the orders table. The only reason I need the cart table is because he wants other information to display, and it's located on the cart table.

The only way these tables have similar values is as follows:

ordSessionID from the orders table matches cartSessionID from the cart table.

and then cartID from the cart table matches coCartID from the cartoptions table.

Hopefully I haven't lost everybody. Anyway, I've started with code that I used from a different search function that does work. And I'm able to search on the orders table and post the results perfectly. But, when it pulls information from the cartoptions and the cart tables it is displaying every single value from whatever field I attempt to display, when I'm only wanting to display the ones that match up with the correct variables. So I've managed to pull information from the appropriate tables, but I haven't figured out how to connect all three tables together appropriately... I've seen online there are ways to join two tables, but that doesn't help me because I'm using three tables. Anyway, here's the code:


<?php 
session_start(); 
include('vsadmin/db_conn_open.php');

// Search Form Processor
$ordPhone 	= mysql_real_escape_string($_POST['ordPhone']);

$sql = "";
$sql2 = "";
$sql3 = "";

// SQL SELECT 
if(isset($_POST['searchordPhone'])){
$sql = "SELECT * FROM orders WHERE ordPhone LIKE '{$ordPhone}%'";

}//end if

if(isset($_GET['query'])){
$query = $_GET['query'];
$sql = "SELECT ordPhone, ordSessionID FROM orders WHERE ordPhone = '{$query}'";
$sql2 = "SELECT cartSessionID, cartID FROM cart WHERE cartSessionID = '{$ordSessionID}'";
$sql3 = "SELECT coCartID FROM cartoptions WHERE coCartID = '{$cartID}'";
}//end if

?>
<body>
<form name="searchordPhone" method="post" action="<?php $PHP_SELF; ?>">
Enter Phone Number:<br />
<input name="ordPhone" type="text" id="ordPhone" size="20"><br />
<input type="submit" name="searchordPhone" id="search" value="Search">
</form><br />
<br /><br /><br />
<?php 
if($sql !== ""){$result = mysql_query($sql);
	if(mysql_num_rows($result) > 0){while($row = mysql_fetch_assoc($result)){
			echo $row['ordPhone']."<br>";
			echo $row['ordSessionID']."<br>";
	}//end wh	
	}else{
		print "<tr><td colspan=\"3\" class=\"description\">There are no results to 
display</td><td> </td></tr>";
	}//end if

}//end if
?>
<?php 
if($sql2 !== ""){$result = mysql_query($sql2);
	if(mysql_num_rows($result) > 0){while($row = mysql_fetch_assoc($result)){
			echo $row['cartSessionID']."<br>";

	}//end wh	
	}else{
		print "<tr><td colspan=\"3\" class=\"description\">There are no results to
display</td><td> </td></tr>";
	}//end if

}//end if
?><?php 
if($sql3 !== ""){$result = mysql_query($sql3);
	if(mysql_num_rows($result) > 0){while($row = mysql_fetch_assoc($result)){
			echo $row['coCartID']."<br>";
	}//end wh	
	}else{
		print "<tr><td colspan=\"3\" class=\"description\">There are no results to
display</td><td> </td></tr>";
	}//end if

}//end if
?>
</body>

I'm probably way off here by declaring extra sql variables. I just didn't know how else to take this, so if anybody has some pointers I would be in their debt.

stoni23 · January 2, 2010

I forgot to mention that I've left out some of the fields from the tables, because my first goal is simply to get this thing to function... and display the correct value from each of the three tables. I'll worry about the semantics like getting it to display the specific values later. I'm only concerned about making it work for now.

wildteen88 · January 2, 2010

You can query more than one table at a time within a single query using joins. Trying to stitch together results from separate queries is not recommended.

The following is your three queries combined into one

$ordPhone    = mysql_real_escape_string($_POST['ordPhone']);

$query = "SELECT O.ordPhone, O.ordSessionID, C.cartSessionID, C.cartID, Co.coCartID FROM orders O
LEFT JOIN cart C ON C.cartSessionID = O.ordSessionID
LEFT JOIN cartoptions Co ON Co.coCartID = C.cartID
WHERE O.ordPhone = $ordPhone
GROUP BY O.ordPhone";
$result = mysql_query($query);

if($result && mysql_num_rows($result) > 0)
{
    while($row = mysql_num_rows($result))
    {
        printf("<pre>%s</pre><hr />", print_r($row, true));
    }
}
else
{
    if(!$result)
        prinft("Error with query: <pre>%s</pre>Reason: <pre>%s</pre>", $query, mysql_error());
    else
        echo "No results!";
}

stoni23 · January 2, 2010

I tried that and it actually made an infinite loop that had the number 1 and then an <hr> all the way down the page.

wildteen88 · January 2, 2010

Woops!

This line

    while($row = mysql_num_rows($result))

should be

    while($row = mysql_fetch_assoc($result))

stoni23 · January 2, 2010

you have saved my life... it's working. It's displaying as an array, but that's fine I can easily have it display by row. Thanks a ton for the help.

stoni23 · January 2, 2010

actually... I'm running into a new problem. Before it would get any phone number to work... but now it doesn't recognize phone numbers that have non numerical symbols in them.

wildteen88 · January 3, 2010

What datatype is the ordPhone column set to in your database?

What do you mean by

but now it doesn't recognize phone numbers that have non numerical symbols in them.

Could you post some example data. Post any errors you are getting too.

sasa · January 3, 2010

add ' around $ordPhone in your query

$query = "SELECT O.ordPhone, O.ordSessionID, C.cartSessionID, C.cartID, Co.coCartID FROM orders O
LEFT JOIN cart C ON C.cartSessionID = O.ordSessionID
LEFT JOIN cartoptions Co ON Co.coCartID = C.cartID
WHERE O.ordPhone = '$ordPhone'
GROUP BY O.ordPhone";

stoni23 · January 3, 2010

the single quotes fixed it, thanks

Sign In

Connecting three tables from the database

Recommended Posts

stoni23

Link to comment

Share on other sites

stoni23

Link to comment

Share on other sites

wildteen88

Link to comment

Share on other sites

stoni23

Link to comment

Share on other sites

wildteen88

Link to comment

Share on other sites

stoni23

Link to comment

Share on other sites

stoni23

Link to comment

Share on other sites

wildteen88

Link to comment

Share on other sites

sasa

Link to comment

Share on other sites

stoni23

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information