alpha1 Posted January 11, 2012 Share Posted January 11, 2012 i have added a ajax chained dropdown to my site, however i realised i have to leave the connection open so it can connect to the database to alter the second and third drop down. so i created a new sql to keep it more secure which would only hold this information <?php // Script Error Reporting error_reporting(E_ALL); ini_set('display_errors', '1'); ?> <?php // Run a select query to get my letest 6 items // Connect to the MySQL database include "storescripts/connect_to_mysql.php"; $dynamicCat = ""; $sql = mysql_query("SELECT * FROM categories ORDER BY category_name DESC LIMIT 6"); $categoriesCount = mysql_num_rows($sql); // count the output amount if ($categoriesCount > 0) { while($row = mysql_fetch_array($sql)){ $id = $row["id"]; $category_name = $row["category_name"]; $dynamicCat .= '<table> <a href="category.php?id=' . $id . '">' . $category_name . '</a></td> </tr> </table>'; } } else { $dynamicCat = "We have no products listed in our store yet"; } mysql_close(); ?> <?php // Run a select query to get my letest 6 items // Connect to the MySQL database include "storescripts/connect_to_mysql.php"; $dynamicList = ""; $sql = mysql_query("SELECT * FROM products ORDER BY date_added DESC LIMIT 6"); $productCount = mysql_num_rows($sql); // count the output amount if ($productCount > 0) { while($row = mysql_fetch_array($sql)){ $id = $row["id"]; $product_name = $row["product_name"]; $price = $row["price"]; $date_added = strftime("%b %d, %Y", strtotime($row["date_added"])); $dynamicList .= '<table width="100%" border="0" cellspacing="0" cellpadding="6"> <tr> <td width="17%" valign="top"><a href="product.php?id=' . $id . '"><img style="border:#666 1px solid;" src="inventory_images/' . $id . '.jpg" alt="' . $product_name . '" width="102" height="102" border="1" /></a></td> <td width="83%" valign="top">' . $product_name . '<br /> £' . $price . '<br /> <a href="product.php?id=' . $id . '">View Product Details</a></td> </tr> </table>'; } } else { $dynamicList = "We have no products listed in our store yet"; } mysql_close(); ?> <?php include("storescripts/connect_to_mysql2.php"); function createoptions($table , $id , $field) { $sql = "select * from $table ORDER BY $field"; $res = mysql_query($sql) or die(mysql_error()); while ($a = mysql_fetch_assoc($res)) echo "<option value=\"{$a[$id]}\">$a[$field]</option>"; } ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <link rel="stylesheet" href="style/style.css" type="text/css" media="screen" /> <script type="text/javascript" src="storescripts/jquery.js"></script> <script type="text/javascript" charset="utf-8"> $(function(){ $("select#Make").change(function(){ $.getJSON("select.php",{Make: $(this).val(), ajax: 'true'}, function(j){ var options = ''; for (var i = 0; i < j.length; i++) { options += '<option value="' + j[i].optionValue + '">' + j[i].optionDisplay + '</option>'; } $("select#Model").html(options); }) }) // Year elemeinek feltöltése $("select#Model").change(function(){ $.getJSON("select.php",{Model: $(this).val(), ajax: 'true'}, function(j){ var options = ''; for (var i = 0; i < j.length; i++) { options += '<option value="' + j[i].optionValue + '">' + j[i].optionDisplay + '</option>'; } $("select#Year").html(options); }) }) }) </script> <title>home</title> </head> <body> <div align="center" id="mainWrapper"> <?php include_once("template_header.php");?> <div id="pageContent"><table width="100%" border="1"> <tr> <td width="24%" valign="top"><p>some crap</p> <p><?php echo $dynamicCat; ?></p> <p> </p> <p> </p> <p> </p></td> <td width="39%" valign="top"><p>newiest crap added to store</p> <p><?php echo $dynamicList; ?><br /> <br /> </p> <!--<table width="100%" border="1" cellpadding="2"> <tr> <td width="38%" valign="top"><a href="product.php?"><img style= "border:#000 1px solid;" src="inventory_images/HID_Kit-EPE_Package_small__39228_zoom.jpg" alt="$dynamicTitle" width="149" height="110" border="1" /></a></td> <td width="62%" valign="top"><p>Product Title</p> <p>Product Price</p> <p>View Product</p></td> </tr> </table> --> <p> </p></td> <td width="37%" valign="top"><p>more crap</p> <p> <select id="Make"> <option value="-1">--Select--</option> <?php createoptions("Make", "Make_id", "Make"); ?> </select> <select id="Model"> </select> <select id="Year"> </select></p></td> </tr> </table> </div> <?php include_once("template_footer.php");?> </div> </body> </html> however now the dropdowns are not working, i have changed the connection details in the select file also to the newconnection file any advice??? Quote Link to comment https://forums.phpfreaks.com/topic/254825-isit-possible-to-have-two-different-databses-connect-to-one-page/ Share on other sites More sharing options...
WTFranklin Posted January 11, 2012 Share Posted January 11, 2012 Hello, I found a link that might help you http://stackoverflow.com/questions/274892/how-do-you-connect-to-multiple-mysql-databases-on-a-single-webpage This is an assumption from me (which I know isn't good ) but it looks like you didn't store the connections into multiple variables, which you'll see more about in this page. Hope that helps! -Frank Quote Link to comment https://forums.phpfreaks.com/topic/254825-isit-possible-to-have-two-different-databses-connect-to-one-page/#findComment-1306648 Share on other sites More sharing options...
wolfcry Posted January 11, 2012 Share Posted January 11, 2012 i have added a ajax chained dropdown to my site, however i realised i have to leave the connection open so it can connect to the database to alter the second and third drop down. The connection will automatically close once the page is done, so that shouldn't be the problem. As far as I'm aware, you must manually close the first connection if you wish to open a new connection like you have, but I'm thinking it might be interfering somehow? I might be wrong there though. Try removing the first mysql_close and see if that helps. It could be what WTFranklin posted as well though too lol. However, can you post your connection details for both connections? It might be how your connecting and trying to query the data also. Quote Link to comment https://forums.phpfreaks.com/topic/254825-isit-possible-to-have-two-different-databses-connect-to-one-page/#findComment-1306650 Share on other sites More sharing options...
jwalpole Posted January 11, 2012 Share Posted January 11, 2012 You can also use one of these pear database abstraction classes. http://pear.php.net/package/MDB2 or find one that suites your tastes/needs here: http://pear.php.net/packages.php?catpid=7&catname=Database Then you can create you connections: <?php //first connection $dbh = DB::connect('mysql://user:pswd@localhost/database') or die("Can't Connect"); $result_set = $dbh->query("SELECT * FROM tbl_tablename"); $result_row = $result_set->fetchRow(); // to get single row OR while($result_row = $result_set->fetchRow()){ echo $result_row->column_name; } //second connection $dbi = DB::connect('mysql://user:pswd@localhost/database2') or die("Can't Connect"); $result_se2t = $dbi->query("SELECT * FROM tbl_tablename"); $result_row2 = $result_set2->fetchRow(); // to get single row OR while($result_row2 = $result_set2->fetchRow()){ echo $result_row2->column_name2; } ?> Hope that helps Quote Link to comment https://forums.phpfreaks.com/topic/254825-isit-possible-to-have-two-different-databses-connect-to-one-page/#findComment-1306672 Share on other sites More sharing options...
PFMaBiSmAd Posted January 11, 2012 Share Posted January 11, 2012 So, is that code you posted, for your select.php page, that your ajax http requests go to? If so, there's no code there to distinguish if the request is from the ajax to return select menu data or if it is the request for the main page. Every http request to your page is completely separate. When you request the main page, that is one http request. By the time you see the page being rendered in your browser the php code on the server that is outputting that page has finished and any database connection it made has been automatically closed. When the jquery on that page makes the http requests to get the chained-select data, those are completely separate http requests and any connection you make in the php code that is servicing those requests is also automatically closed by the time the select menus have been built and rendered in the browser. Quote Link to comment https://forums.phpfreaks.com/topic/254825-isit-possible-to-have-two-different-databses-connect-to-one-page/#findComment-1306678 Share on other sites More sharing options...
jwalpole Posted January 11, 2012 Share Posted January 11, 2012 @ PFMaBiSmAd wouldn't he be able to put the results in an array? <?php while($row = $res->fetchRow()){ $results_array[] = array('col1'=>$row->col1, 'col2'=>$row->col2); } then use a for loop to show results in the table? Quote Link to comment https://forums.phpfreaks.com/topic/254825-isit-possible-to-have-two-different-databses-connect-to-one-page/#findComment-1306685 Share on other sites More sharing options...
PFMaBiSmAd Posted January 11, 2012 Share Posted January 11, 2012 Doesn't matter where he puts the data while the php code is running, there are separate http requests being made to one (or more) .php page(s) that do and output something different for each separate http request. Quote Link to comment https://forums.phpfreaks.com/topic/254825-isit-possible-to-have-two-different-databses-connect-to-one-page/#findComment-1306693 Share on other sites More sharing options...
PFMaBiSmAd Posted January 12, 2012 Share Posted January 12, 2012 @alpha1, I have looked more at your code (the posted code is only for your main page) and I'm not even sure why you think using a second database connection is needed. All your queries are (or should be) SELECT queries on different tables in the same database. The only thing you could accomplish by having database connections with different privileges would be to restrict the type of queries that can run. You do need to make your existing code more efficient. Opening and closing any database connection multiple times in the code on one page takes a lot of time (for the opening part.) For the code you posted, you should make the database connection once, before the first query statement, and then either let php close that connection automatically when the script on that page ends or you can close it yourself after you have made the last database call. Read the next paragraph about where exactly your last database call is occurring at (it's not anywhere near the createoptions() function definition.) Concerning your createoptions() function definition and the database connection that you are making right before that function definition. That's not doing what you think. The function definition is just the definition. You can put it anywhere in the code on that page. What matters is where you call the function. It's where you put the createoptions("Make", "Make_id", "Make"); statement that you would need the database connection to be present. But as already stated, you should only be opening one single database connection in the entire code you posted. Edit: Here's a hint based on where you are calling the createoptions() funciton at on your page and php errors on your page. Because you calling the createoptions() function inside of a HTML <select> .... </select> tag but outside of any <option></option> tag, any php errors that are being output will likely only appear in the 'view source' of the page in your browser. Quote Link to comment https://forums.phpfreaks.com/topic/254825-isit-possible-to-have-two-different-databses-connect-to-one-page/#findComment-1306709 Share on other sites More sharing options...
alpha1 Posted January 12, 2012 Author Share Posted January 12, 2012 thanks for all your help guys, this is my select code <?php error_reporting(E_ALL); ini_set("display_errors", 0); include "storescripts/connect_to_mysql2.php"; function createoptions($table , $id , $field , $condition_field , $value) { $sql = sprintf("select * from $table WHERE $condition_field=%d ORDER BY $field" , $value); $res = mysql_query($sql) or die(mysql_error()); if (mysql_num_rows($res) > 0) { while ($a = mysql_fetch_assoc($res)) $out[] = "{optionValue: {$a[$id]}, optionDisplay: '$a[$field]'}"; return "[" . implode("," , $out) . "]"; } else return "[{optionValue: -1 , optionDisplay: 'No result'}]"; } if (isset($_GET['Make'])) { echo createoptions("Model" , "Model_id" , "Model" , "Make_id" , $_GET['Make']); } if (isset($_GET['Model'])) { echo createoptions("Year" , "Year_id" , "Year" , "Model_id" , $_GET['Model']); } die(); ?> But as you explained earlier that a connection is automatically closed at the end, what i might do to make it easier is to place everything in the 1st db again and remove the second connection if i don't need it. I’m going to go through my code and look over all of my connections to make the code more efficient also. Quote Link to comment https://forums.phpfreaks.com/topic/254825-isit-possible-to-have-two-different-databses-connect-to-one-page/#findComment-1306733 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.