stoni23 Posted January 2, 2010 Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/186950-connecting-three-tables-from-the-database/ Share on other sites More sharing options...
stoni23 Posted January 2, 2010 Author Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/186950-connecting-three-tables-from-the-database/#findComment-987227 Share on other sites More sharing options...
wildteen88 Posted January 2, 2010 Share Posted 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!"; } Quote Link to comment https://forums.phpfreaks.com/topic/186950-connecting-three-tables-from-the-database/#findComment-987242 Share on other sites More sharing options...
stoni23 Posted January 2, 2010 Author Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/186950-connecting-three-tables-from-the-database/#findComment-987251 Share on other sites More sharing options...
wildteen88 Posted January 2, 2010 Share Posted January 2, 2010 Woops! This line while($row = mysql_num_rows($result)) should be while($row = mysql_fetch_assoc($result)) Quote Link to comment https://forums.phpfreaks.com/topic/186950-connecting-three-tables-from-the-database/#findComment-987254 Share on other sites More sharing options...
stoni23 Posted January 2, 2010 Author Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/186950-connecting-three-tables-from-the-database/#findComment-987259 Share on other sites More sharing options...
stoni23 Posted January 2, 2010 Author Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/186950-connecting-three-tables-from-the-database/#findComment-987323 Share on other sites More sharing options...
wildteen88 Posted January 3, 2010 Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/186950-connecting-three-tables-from-the-database/#findComment-987602 Share on other sites More sharing options...
sasa Posted January 3, 2010 Share Posted 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"; Quote Link to comment https://forums.phpfreaks.com/topic/186950-connecting-three-tables-from-the-database/#findComment-987615 Share on other sites More sharing options...
stoni23 Posted January 3, 2010 Author Share Posted January 3, 2010 the single quotes fixed it, thanks Quote Link to comment https://forums.phpfreaks.com/topic/186950-connecting-three-tables-from-the-database/#findComment-987719 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.