isit possible to have two different databses connect to one page

alpha1 · 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???

WTFranklin · 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

wolfcry · 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.

jwalpole · 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

PFMaBiSmAd · 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.

jwalpole · 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?

PFMaBiSmAd · 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.

PFMaBiSmAd · 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.

alpha1 · 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.

Sign In

isit possible to have two different databses connect to one page

Recommended Posts

alpha1

Link to comment

Share on other sites

WTFranklin

Link to comment

Share on other sites

wolfcry

Link to comment

Share on other sites

jwalpole

Link to comment

Share on other sites

PFMaBiSmAd

Link to comment

Share on other sites

jwalpole

Link to comment

Share on other sites

PFMaBiSmAd

Link to comment

Share on other sites

PFMaBiSmAd

Link to comment

Share on other sites

alpha1

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information