[SOLVED] handling multiple sql statements

bruckerrlb · August 5, 2007

Hello All,

I am trying to do multiple queries from two databases on two different servers.I try to always use comments, what i'm doing here is having a select option, the select option has a value based on a session, basically session_id = userid, select userid in products,, print products, that.

From there, store the product id in a variable, and call the product id, as well as the form to get me info from the other database, and then display the info. Sounds easy when I write it like this, but when I write it in code, it doesn't seem to work, it pulls me info, from the product id, but it's not taking any data in the form. I know I should not have a form submitted this way, it's bad for security reasons, but honestly I have no idea how put it in another form, and then call the info back to put it in the specific spot of the table. Can anyone tell me why it's not taking my form data, and also how I can make a query that goes something like this

$query1 = "SELECT * FROM ei10800010001200708 WHERE NumeroExterno = '$productid'"; and (part I don't know how to search for) in between fetchadesde and fetchahasta where fetchadesde = $startdate and fetchahasta = $enddate and in between horadesde and horahasta where horadessde = $starthour and horahasta = $endhour

That is a type of query i'm trying to get for my results, and I apoligize for the Spanglish, but I work for a spanish company and don't speak good spanish, which frankly just makes life hard. Does anyone have any recommendations? My code is below Thanks!

<?php
   require_once('auth.php');
?>
<!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=ISO-8859-1" />
<title>Seguimiento de llamadas</title>
<style type="text/css">
<!--

-->
</style>
<link href="style.css" rel="stylesheet" type="text/css" />

</head>

<body>
<div id="Layer2"></div>
<div class="main">

<div class="header">

<h1><center>Cabecera</center></h1></div>

<div class="toolbar">

<span class="style2">Está Usten En: <a href="principle_menu_user.php" class="style2">Menú Principle[/url]
-</span> <span class="style3">Seguimiento de llamadas</span></div>

<div class="userchooser">
<center>
<table><form action="<?php echo $_SERVER['PHP_SELF']?>" method="post">
<tr><td width="340" height="37">Seleccione el producto:</td>
<td width="123">

<select>
<?php
   //Connect to mysql server
   $link=mysql_connect("localhost","root");
   if(!$link) {
      die('Failed to connect to server: ' . mysql_error());
   }
   //Select database
   $db = mysql_select_db("users");
   if(!$db) {
      die("Unable to select database");
   }

//$query = 'SELECT * FROM login';

//if this causes errors, move the variable getting the session id somewhere else

$memberid = $_SESSION['SESS_MEMBER_ID'];

$query = "SELECT * FROM products WHERE clientid = '$memberid'";

      if ($r = mysql_query ($query)) {
      
      while ($row = mysql_fetch_array ($r)) {
      
      print "
            <option id=\"productname\">{$row['productname']}</option>";
      
      
      
         }
      
      }
      
      else {
      die ('could not do it because' .mysql_error(). "Damn");
      }
     
      mysql_close();
?>
</select>
</td>
</tr>
<tr>

<td height="45">Llamadas realizadas los días:</td>
<td><input type="text" id="startdate" name="startdate" /></td>
<td width="108"><input type="text" id="enddate" name="enddate" /></td>
</tr>
<tr>
<td height="41">Durante las horas:</td>
<td><input type="text" id="starthour" name="starthour" /></td>

<td><input type="text" id="endhour" name="endhour" /></td>
<td><input type="submit" value="buscar" /></td></tr>

</form></table></center>
</div>

<div class="datasectionuser">

<table width="769" height="24">
<tr bgcolor="#bbe0e3">
<td width="141">Nº teléfono cliente</td>
<td width="185">Nº teléfono en centralita</td>
<td width="184">Producto asociado</td>
<td width="34">fecha</td>
<td width="80">hora</td>
<td width="117">minutos</td>
</tr>
<tr>
<td>
<?php

ini_set ('display_errors', 1);
error_reporting (E_ALL & ~E_NOTICE);
// Connects to your Database

//declare my variables from the post
$startdate = $_POST['startdate'];

$enddate = $_POST['enddate'];
$starthour = $_POST['starthour'];
$endhour = $_POST['endhour'];

//this is from the select option where the user can select his/her option
$product = $_POST['productname'];

//get the sql connection for the database on my server

$connect2 = mysql_connect("localhost","root") or die('Failed to connect to thelocalserverdatabase: ' . mysql_error());
   
   //select a certain database from my server
$db2 = mysql_select_db("users") or die('nope, you didnt get the second database scratch head and burn another');

//query a specific table in my database on my server, basically I need to translate product name to product id
$query2 = "SELECT * FROM products WHERE '$productname' = productname";

//bring back the query into a variable
$r = mysql_query ($query2);

$row = mysql_fetch_array ($r);

//I need this product id, to query the other database, on the other server
$productid = $row['productid'];


//connects to the other server
$connect1 =  mysql_connect("192.168.1.2", "fakeusernamehere", "fakepasswordhere") or  die('could not select the database1 because :'. mysql_error() . ' ');
//select the specific database
$db1 = mysql_select_db("eisw") or die('could not select the database1 because :'. mysql_error() . ' ');

//query the remote database for the specific user
$query1 = "SELECT * FROM ei10800010001200708 WHERE NumeroExterno = '$productid'";

if ($r1 = mysql_query ($query1)) {
      
      while ($row1 = mysql_fetch_array ($r1)) {
      //print everything
      print "
            <tr><td width=\"141\"><center>{$row1['NumeroExterno']}</center></td>
            <td><center>{$row1['NumeroExtension']}</center></td>
            <td><center>{$row['productname']}</center></td>
            <td><center>{$row1['FechaDesde']}</center></td>
            <td><center>{$row1['HoraDesde']}</center></td>
            <td><center>{$row1['Duracion']}</center></td>
            </tr>";      
         }
      
      }

else {
      die ('problem with gigantic wierd query' .mysql_error(). "Damn");
      }

/*
mysql_disconnect($connect1);
mysql_disconnect($connect2);

*/

?>
</table>
<!--I will Figure this export thing out if this query works-->
<a href="excel.php" class="createnew">Exportar a Excel[/url]

</div>

</div>

</body>
</html>

There it is, the whole shabang.

EDITED: Please use the coded tags (

) when posting code within posts.

wildteen88 · August 5, 2007

When you connect to multiple databases you must pass the link identifier (this is returned from mysql_connect) as the second parameter for the mysql_query function when you want to run a query for one of the databases.

Try the following:

<?php
// Always add these lines at the top of script
ini_set ('display_errors', 1);
error_reporting (E_ALL & ~E_NOTICE);

require_once 'auth.php';

// Local Database connection
$local_db = mysql_connect('localhost', 'root') or die('localhost connecion failed:<br />' . mysql_error($local_db));
mysql_select_db('users', $local_db) or die('localhost database selection failed:<br />' . mysql_error($local_db));

// Remote Database connection
$remote_db = mysql_connect('192.168.1.2', 'fakeusernamehere', 'fakepasswordhere') or die('remote connecion failed:<br />' . mysql_error($remote_db));
mysql_select_db('eisw', $remote_db) or die('remote database selection failed:<br />' . mysql_error($remote_db));

?>
<!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=ISO-8859-1" />
<title>Seguimiento de llamadas</title>
<link href="style.css" rel="stylesheet" type="text/css" />
</head>

<body>

<div id="Layer2"></div>

<div class="main">

  <div class="header" style="text-align: center">
    <h1>Cabecera</h1>
  </div>

  <div class="toolbar">
    <span class="style2">Está Usten En: <a href="principle_menu_user.php" class="style2">Menú Principle</a></span> -
    <span class="style3">Seguimiento de llamadas</span>
  </div>

  <div class="userchooser" style="text-align: center">

    <form action="<?php echo $_SERVER['PHP_SELF']?>" method="post">
      <table>
        <tr>
          <td width="340" height="37">Seleccione el producto:</td>
          <td width="123">
            <select>

<?php
//if this causes errors, move the variable getting the session id somewhere else
$memberid = $_SESSION['SESS_MEMBER_ID'];

$query = "SELECT * FROM products WHERE clientid = '$memberid'";

$r = mysql_query($query, $local_db) or die ("Could not perform query:<br />\n$query<br />\n" . mysql_error($local_db));

while ($row = mysql_fetch_array($r))
{
    print '              <option id="productname">' . $row['productname'] . "</option>\n";
}

?>
            </select>
          </td>
        </tr>
        <tr>
          <td height="45">Llamadas realizadas los días:</td>
          <td><input type="text" id="startdate" name="startdate" /></td>
          <td width="108"><input type="text" id="enddate" name="enddate" /></td>
        </tr>
        <tr>
          <td height="41">Durante las horas:</td>
          <td><input type="text" id="starthour" name="starthour" /></td>
          <td><input type="text" id="endhour" name="endhour" /></td>
          <td><input type="submit" value="buscar" name="submit" /></td>
        </tr>
      </table>
    </form>

  </div>

<?php
// check that form has been submitted
// When the form is submitted the followng will be run.
if(isset($_POST['submit']))
{
?>
  <div class="datasectionuser">

    <table width="769" height="24">
      <tr bgcolor="#bbe0e3">
        <td width="141">Nº teléfono cliente</td>
        <td width="185">Nº teléfono en centralita</td>
        <td width="184">Producto asociado</td>
        <td width="34">fecha</td>
        <td width="80">hora</td>
        <td width="117">minutos</td>
      </tr>
<?php
    // declare my variables from the post
    $startdate   = $_POST['startdate'];
    $enddate     = $_POST['enddate'];
    $starthour   = $_POST['starthour'];
    $endhour     = $_POST['endhour'];
    $productname = $_POST['productname']; //this is from the select option where the user can select his/her option

    // query a specific table in my database on my server, basically I need to translate product name to product id
    $query = "SELECT * FROM products WHERE productname = '$productname'";

    // bring back the query into a variable
    $r = mysql_query($query, $local_db);

    // Get the product id
    list($productid) = mysql_fetch_array($r);

    //query the remote database for the specific user
    $query = "SELECT * FROM ei10800010001200708 WHERE NumeroExterno = '$productid' AND " .
             "FechaDesde > '$startdate' AND FechaHasta < '$enddate' AND HoraDesde > '$starthour' AND HoraFecha < '$endhour'";

    mysql_query($query, $remote_db) or die ("Could not perform query:<br />\n$query<br />\n" . mysql_error($remote_db));

    while ($row = mysql_fetch_array($r))
    {
?>
      <tr>
        <td width="141" align="center"><?php echo $row['NumeroExterno']; ?></td>
        <td width="141" align="center"><?php echo $row['NumeroExtension']; ?></td>
        <td align="center"><?php echo $row['productname']; ?></td>
        <td align="center"><?php echo $row1['FechaDesde']; ?></td>
        <td align="center"><?php echo $row1['HoraDesde']; ?></td>
        <td align="center"><?php echo $row1['Duracion']; ?></td>
      </tr>
<?php
    }
?>
    </table>

    <!--I will Figure this export thing out if this query works-->
    <a href="excel.php" class="createnew">Exportar a Excel</a>

  </div>
<?php
} // close if
?>
</div>

</body>
</html>
<?php

// close connections to database
mysql_close($connect1);
mysql_close($connect2);


?>

I have tidied up your code a bit so its more easer to read.

Sign In

[SOLVED] handling multiple sql statements

Recommended Posts

bruckerrlb

Link to comment

Share on other sites

wildteen88

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information