Problem with SQL Queries

fRAiLtY- · June 15, 2011

Hi there,

I use mysql queries all the time, however I've been communicating with a MSSQL server and retrieving details. It all connects and works fine until I try and initiate a second query in the page, here's the code for an example.

<?php

// CHECK IF USER LOGGED IN
session_start();
if ($_SESSION['loggedIn'] != "true") {
 header("Location: /reps/");
}

// GRAB DATABASE DETAILS
require('../assets/mssql_connect.php');
require('../assets/connect.php');

// SET GLOBAL REP VARIABLES
$rep = $_SESSION['name'];
$admin = $_SESSION['id'];
$customer = $_GET['id'];
$code = $_POST['Code'];

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<link rel="stylesheet" type="text/css" href="../assets/css/dashstyles.css" />
<script language="javascript" src="/reps/assets/js/formvalidation.js"></script>
<title>Reps Dashboard - <?php echo $rep; ?></title>
</head>

<body>

<?php 

// GET HEADER INCLUDE
require('../assets/header.php');


// BUILD QUERY USING ID TO GET FIELDS
$edit = mssql_query("SELECT * FROM Customers WHERE ID='$customer'")
or die(mssql_get_last_message());

  while ($row = mssql_fetch_array($edit)) {
	$code = $row['Code'];
	$name = $row['Name'];
	$address = $row['Address'] . "<br />" . $row['Town'] . "<br/>" . $row['County'] . "<br />" . $row['Postcode'];
	$phone = $row['Tel'];
	$fax = $row['Fax'];
    }

mssql_free_result($edit);

?>

<div id="main">
<p class="welcome">Customer Detail: <?php echo $name; ?></p><p class="backlink"><img src="../assets/images/icons/back_icon.png" alt="Back" /> <a class="backlink" href="/reps/dashboard/">Back to dashboard</a></p>
	<table class="data">
		<tr>
			<td style="width:40%">
				<table class="data" style="width:100%">
					<tr class="theader">
						<td>Customer Details</td>
					</tr>
					<tr>
						<td><p><b>Customer Code: </b><?php echo $code; ?></p>
							<p><b>Address:</b><br /><?php echo $name . "<br />" . $address; ?></p>
							<p><b>Tel: </b><?php echo $phone . "<br /><b>Fax: </b>" . $fax; ?></p>
						</td>
					</tr>
				</table>
			</td>
			<td style="width:60%">
				<table class="data" style="width:100%">
					<tr  class="theader">
						<td>Customer Orders History</td>
					</tr>
					<tr>
						<td>
<?php

// BUILD NEW QUERY FOR ORDERS
$orders_query = "SELECT TOP 10 * FROM MainJobDetails WHERE InvoiceCustomerCode='$code'";

$orders=mssql_query($orders_query); var_dump($orders); die("dying");

// CHECK FOR 0 RECORDS
if(mssql_num_rows($orders) == 0) {
echo "No previous orders found.";
} else {

//OUTPUT PREVIOUS ORDERS
echo "Last 10 Orders<br />";
echo "<table class='orders'>";
echo "<tr><td>Date</td><td>Job Number</td><td>Description</td><td>Status</td><td>Value</td></tr>";

// ALTERNATE ROW COLOURS
$c = 0;

  while ($row = mssql_fetch_array($orders)) {
      
    $class = $c++ % 2 == 1 ? "odd" : "even"; 
echo "<tr class='$class'><td>"; 
echo $row['CreateDateTime'];
echo "</td><td>"; 
echo $row['JobNo'];
echo "</td><td>";
echo $row['JobDesc'];
echo "</td><td>"; 
echo $row['Description'];
echo "</td><td>";
echo $row['PriceEst'];
echo "</td></tr>";
  }  

echo "</table>";

}

?>
						</td>
					</tr>
				</table>
			</td>
		</tr>
		<tr>
			<td style="width:40%">
				<table class="data" style="width:100%">
					<tr class="theader">
						<td>Comments Log</td>
					</tr>
					<tr>
						<td>
<?php

// SWAP DATABASE!!
mssql_select_db("bjb_reps");

// GET CURRENT COMMENTS
$comments = mssql_query("SELECT * FROM comments WHERE customer_id='$customer' ORDER BY id DESC LIMIT 0,5")
or die(mssql_error());

// CHECK FOR 0 COMMENTS
if(mssql_num_rows($comments) == 0) {
echo "<p>No comments about this customer.</p>";
}

// LOOP THROUGH PREVIOUS COMMENTS
while ($row = mssql_fetch_array($comments)) {
echo "<p style='line-height:20px;'><b>" . $row['rep'] . "</b> <i>(" . $row['date'] . " at " . $row['time'] . ")</i><br />";
echo $row['comment'] . "</p>";
}

?>
						<form method="POST" name="comments" action="../assets/comment_post.php" onsubmit="return validateCForm();">
							<textarea rows="10" cols="45" name="comment_box"></textarea><br />
							<input type="hidden" name="comment_date" value="<?php echo date('d.m.Y'); ?>">
							<input type="hidden" name="comment_time" value="<?php echo date('H:i:s'); ?>">
							<input type="hidden" name="id" value="<?php echo $customer; ?>">
							<input type="hidden" name="rep" value="<?php echo $rep; ?>"><br />
							<input type="submit" class="search" name="submit" value="Post Comment">
						</form>
						</td>
					</tr>
				</table>
			</td>
			<td style="width:60%">
				<table class="data" style="width:100%">
					<tr  class="theader">
						<td>Customer Estimates History</td>
					</tr>
					<tr>
						<td>
<?php

// SWAP DATABASE!!
mssql_select_db("bjb_tharstern");

// BUILD NEW QUERY FOR QUOTES
$quotes = mssql_query("SELECT * FROM MainEstimateDetails WHERE CustomerRef = '$code' ORDER by EstimateDate DESC LIMIT 0,10")
or die (mssql_error());

// CHECK FOR 0 QUOTES
if(mssql_num_rows($quotes) == 0) {
echo "No previous quotes found.";
} else {

// OUTPUT PREVIOUS ORDERS
echo "<table class='orders'>";
echo "<tr><td>Date</td><td>Job Number</td><td>Description</td><td>Status</td><td>Value</td></tr>";

// ALTERNATE ROW COLOURS
$c = 0;

while ($row = mssql_fetch_array($quotes)) {
    
    $class = $c++ % 2 == 1 ? "odd" : "even"; 
echo "<tr class='$class'><td>"; 
echo $row['CreateDateTime'];
echo "</td><td>"; 
echo $row['JobNo'];
echo "</td><td>";
echo $row['JobDesc'];
echo "</td><td>"; 
echo $row['description'];
echo "</td><td>";
echo $row['PriceEst'];
echo "</td></tr>";
  }  

echo "</table>";


}

?>
						</td>
					</tr>
				</table>
			</td>
		</tr>
	</table>
</div>
</body>
</html>

The first query is spot on, works great. The second query does nothing and halts the script. If I view source of the generated page the HTML output stops at the <td> just before the $orders query. ANYTHING at all, whether it's simple echo's or prints DO NOT display after the second mssql_query. Neither does var_dump. I get no errors messages anywhere be it in Apache logs or error_logs.

:wtf: :wtf:

If I echo the query string I get what I expect, $code is correct as is the rest, just as soon as it hits the secondary mssql_query the script dies, very quickly, no timeouts, within a second.

Help!

fugix · June 15, 2011

have you tried troubleshooting the query

$orders=mssql_query($orders_query) or die(mssql_get_last_message());

fRAiLtY- · June 15, 2011

Hi,

Thanks for the reply. Yes I've tried this and nothing displays. It's very strange.

Literally as soon as that 2nd mssql_query is typed it stops the script... as such any subsequent strings/variables/functions even HTML do not output. The script just stops dead without error.

Cheers.

cssfreakie · June 15, 2011

it could very well be that you require a new connection handle, since your using another database.

http://www.php.net/manual/en/function.mysql-select-db.php#52584

fRAiLtY- · June 15, 2011

Is this something to do with the 4th argument on mssql_connect($host,$username,$password,$new_link); ?

I've tried setting this to "TRUE" and "1" as the manual says it's a boolean phrase but no change.

Is that the right way to go about it or not?

Cheers.

** Just seen your edit. I have adjusted code accordingly. I'm only using 1 MSSQL database and one MySQL database in this script, my queries were labelled wrong but result still stands. Script is "terminated" at the $orders query with nothing output after...

<?php

// CHECK IF USER LOGGED IN
session_start();
if ($_SESSION['loggedIn'] != "true") {
 header("Location: /reps/");
}

// GRAB DATABASE DETAILS
require('../assets/mssql_connect.php');
require('../assets/connect.php');

// SET GLOBAL REP VARIABLES
$rep = $_SESSION['name'];
$admin = $_SESSION['id'];
$customer = $_GET['id'];
$code = $_POST['Code'];

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<link rel="stylesheet" type="text/css" href="../assets/css/dashstyles.css" />
<script language="javascript" src="/reps/assets/js/formvalidation.js"></script>
<title>Reps Dashboard - <?php echo $rep; ?></title>
</head>

<body>

<?php 

// GET HEADER INCLUDE
require('../assets/header.php');


// BUILD QUERY USING ID TO GET FIELDS
$edit = mssql_query("SELECT * FROM Customers WHERE ID='$customer'")
or die(mssql_get_last_message());

  while ($row = mssql_fetch_array($edit)) {
	$code = $row['Code'];
	$name = $row['Name'];
	$address = $row['Address'] . "<br />" . $row['Town'] . "<br/>" . $row['County'] . "<br />" . $row['Postcode'];
	$phone = $row['Tel'];
	$fax = $row['Fax'];
    }

mssql_free_result($edit);

?>

<div id="main">
<p class="welcome">Customer Detail: <?php echo $name; ?></p><p class="backlink"><img src="../assets/images/icons/back_icon.png" alt="Back" /> <a class="backlink" href="/reps/dashboard/">Back to dashboard</a></p>
	<table class="data">
		<tr>
			<td style="width:40%">
				<table class="data" style="width:100%">
					<tr class="theader">
						<td>Customer Details</td>
					</tr>
					<tr>
						<td><p><b>Customer Code: </b><?php echo $code; ?></p>
							<p><b>Address:</b><br /><?php echo $name . "<br />" . $address; ?></p>
							<p><b>Tel: </b><?php echo $phone . "<br /><b>Fax: </b>" . $fax; ?></p>
						</td>
					</tr>
				</table>
			</td>
			<td style="width:60%">
				<table class="data" style="width:100%">
					<tr  class="theader">
						<td>Customer Orders History</td>
					</tr>
					<tr>
						<td>
<?php

// BUILD NEW QUERY FOR ORDERS
$orders = mssql_query("SELECT TOP 10 * FROM MainJobDetails WHERE InvoiceCustomerCode='$code'")
or die(mssql_get_last_message($orders));

// CHECK FOR 0 RECORDS
if(mssql_num_rows($orders) == 0) {
echo "No previous orders found.";
} else {

//OUTPUT PREVIOUS ORDERS
echo "Last 10 Orders<br />";
echo "<table class='orders'>";
echo "<tr><td>Date</td><td>Job Number</td><td>Description</td><td>Status</td><td>Value</td></tr>";

// ALTERNATE ROW COLOURS
$c = 0;

  while ($row = mssql_fetch_array($orders)) {
      
    $class = $c++ % 2 == 1 ? "odd" : "even"; 
echo "<tr class='$class'><td>"; 
echo $row['CreateDateTime'];
echo "</td><td>"; 
echo $row['JobNo'];
echo "</td><td>";
echo $row['JobDesc'];
echo "</td><td>"; 
echo $row['Description'];
echo "</td><td>";
echo $row['PriceEst'];
echo "</td></tr>";
  }  

echo "</table>";

}

mssql_free_result($orders);

?>
						</td>
					</tr>
				</table>
			</td>
		</tr>
		<tr>
			<td style="width:40%">
				<table class="data" style="width:100%">
					<tr class="theader">
						<td>Comments Log</td>
					</tr>
					<tr>
						<td>
<?php

// GET CURRENT COMMENTS
$comments = mysql_query("SELECT * FROM comments WHERE customer_id='$customer' ORDER BY id DESC LIMIT 0,5")
or die(mysql_error());

// CHECK FOR 0 COMMENTS
if(mysql_num_rows($comments) == 0) {
echo "<p>No comments about this customer.</p>";
}

// LOOP THROUGH PREVIOUS COMMENTS
while ($row = mysql_fetch_array($comments)) {
echo "<p style='line-height:20px;'><b>" . $row['rep'] . "</b> <i>(" . $row['date'] . " at " . $row['time'] . ")</i><br />";
echo $row['comment'] . "</p>";
}

?>
						<form method="POST" name="comments" action="../assets/comment_post.php" onsubmit="return validateCForm();">
							<textarea rows="10" cols="45" name="comment_box"></textarea><br />
							<input type="hidden" name="comment_date" value="<?php echo date('d.m.Y'); ?>">
							<input type="hidden" name="comment_time" value="<?php echo date('H:i:s'); ?>">
							<input type="hidden" name="id" value="<?php echo $customer; ?>">
							<input type="hidden" name="rep" value="<?php echo $rep; ?>"><br />
							<input type="submit" class="search" name="submit" value="Post Comment">
						</form>
						</td>
					</tr>
				</table>
			</td>
			<td style="width:60%">
				<table class="data" style="width:100%">
					<tr  class="theader">
						<td>Customer Estimates History</td>
					</tr>
					<tr>
						<td>
<?php

// BUILD NEW QUERY FOR QUOTES
$quotes = mssql_query("SELECT * FROM MainEstimateDetails WHERE CustomerRef = '$code' ORDER by EstimateDate DESC LIMIT 0,10")
or die (mssql_error());

// CHECK FOR 0 QUOTES
if(mssql_num_rows($quotes) == 0) {
echo "No previous quotes found.";
} else {

// OUTPUT PREVIOUS ORDERS
echo "<table class='orders'>";
echo "<tr><td>Date</td><td>Job Number</td><td>Description</td><td>Status</td><td>Value</td></tr>";

// ALTERNATE ROW COLOURS
$c = 0;

while ($row = mssql_fetch_array($quotes)) {
    
    $class = $c++ % 2 == 1 ? "odd" : "even"; 
echo "<tr class='$class'><td>"; 
echo $row['CreateDateTime'];
echo "</td><td>"; 
echo $row['JobNo'];
echo "</td><td>";
echo $row['JobDesc'];
echo "</td><td>"; 
echo $row['description'];
echo "</td><td>";
echo $row['PriceEst'];
echo "</td></tr>";
  }  

echo "</table>";


}

?>
						</td>
					</tr>
				</table>
			</td>
		</tr>
	</table>
</div>
</body>
</html>

fugix · June 15, 2011

you will most certainly need a new handle for the mysql connection, as far as mssql_connect() is concerned, you should only be passing 3 parameters in the function, the default 4th is false, which is what you want here

fRAiLtY- · June 15, 2011

Hi,

In the requires I have a connect.php (MySQL) and a mssql_connect.php (MSSQL), is this not suitable?

It's the second MSSQL query that fails/stops the script.

Here's my mssql_connect.php script:

// CONNECT TO THARSTERN DATABASE
$conn = mssql_connect('REMOTEIP', 'USER', 'PASS');
if (!$conn) {
    die(mssql_get_last_message());
}

mssql_select_db('DATABASE');

Also I commented out the entire MySQL query and connection so it was essentially MSSQL only and same result.

fugix · June 15, 2011

have you tried echoing the number of rows of your query

fRAiLtY- · June 15, 2011

Yes,

As I said, ANYTHING after the phrase mssql_query is truncated/not displayed or whatever you want to call it. It's as soon as I type that secondary mssql_query it stops. ANYTHING after isn't displayed no matter it's simplicity.

Here's a view source of the output, as you can see it literally halts everything at that <td> which is the start <td> for the $orders query.

<html> 
<head> 
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"> 
<link rel="stylesheet" type="text/css" href="../assets/css/dashstyles.css" /> 
<script language="javascript" src="/reps/assets/js/formvalidation.js"></script> 
<title>Reps Dashboard - Tom Davison</title> 
</head> 

<body> 

<div id="header-wrap"><form name="search" action="/reps/dashboard/search.php" method="get">Welcome Tom Davison! (<a class="header" href="../assets/logout.php">Logout</a>)
  <input type="text" size="30" name="q" class="search"/> 
  <select name="searchtype" class="search"> 
  	<option name="quotes" value="quotes">Quotes</option> 
  	<option name="reports" value="reports">Reports</option> 
  	<option name="reports" value="Customers">Customers</option> 
  </select> 
  <input class="search" type="submit" name="Submit" value="Search" /> 
</form> 
<div id="header-options"><a class="header" href="/reps/dashboard/customers.php">CRM</a> | <a class="header" href="/reps/dashboard/">Dashboard</a></div></div> 
<div id="main"> 
<p class="welcome">Customer Detail: Hardy Printing Service</p><p class="backlink"><img src="../assets/images/icons/back_icon.png" alt="Back" /> <a class="backlink" href="/reps/dashboard/">Back to dashboard</a></p> 
	<table class="data"> 
		<tr> 
			<td style="width:40%"> 
				<table class="data" style="width:100%"> 
					<tr class="theader"> 
						<td>Customer Details</td> 
					</tr> 
					<tr> 
						<td><p><b>Customer Code: </b>HP0001</p> 
							<p><b>Address:</b><br />Hardy Printing Service<br />23 Cotman Drive
Bradwell<br />Great Yarmouth<br/>Norfolk<br />NR31 9RE</p> 
							<p><b>Tel: </b>01493 661347<br /><b>Fax: </b></p> 
						</td> 
					</tr> 
				</table> 
			</td> 
			<td style="width:60%"> 
				<table class="data" style="width:100%"> 
					<tr  class="theader"> 
						<td>Customer Orders History</td> 
					</tr> 
					<tr> 
						<td>

fugix · June 15, 2011

comment retracted, i would try including your connection again just before calling your second query

fRAiLtY- · June 15, 2011

Hi,

I've left the mssql_free_result where it was and added another connect call, see below:

<?php

// CHECK IF USER LOGGED IN
session_start();
if ($_SESSION['loggedIn'] != "true") {
 header("Location: /reps/");
}

// GRAB DATABASE DETAILS
require('../assets/mssql_connect.php');
require('../assets/connect.php');

// SET GLOBAL REP VARIABLES
$rep = $_SESSION['name'];
$admin = $_SESSION['id'];
$customer = $_GET['id'];
$code = $_POST['Code'];

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<link rel="stylesheet" type="text/css" href="../assets/css/dashstyles.css" />
<script language="javascript" src="/reps/assets/js/formvalidation.js"></script>
<title>Reps Dashboard - <?php echo $rep; ?></title>
</head>

<body>

<?php 

// GET HEADER INCLUDE
require('../assets/header.php');


// BUILD QUERY USING ID TO GET FIELDS
$edit = mssql_query("SELECT * FROM Customers WHERE ID='$customer'")
or die(mssql_get_last_message());

  while ($row = mssql_fetch_array($edit)) {
	$code = $row['Code'];
	$name = $row['Name'];
	$address = $row['Address'] . "<br />" . $row['Town'] . "<br/>" . $row['County'] . "<br />" . $row['Postcode'];
	$phone = $row['Tel'];
	$fax = $row['Fax'];
    }

mssql_free_result($edit);

?>

<div id="main">
<p class="welcome">Customer Detail: <?php echo $name; ?></p><p class="backlink"><img src="../assets/images/icons/back_icon.png" alt="Back" /> <a class="backlink" href="/reps/dashboard/">Back to dashboard</a></p>
	<table class="data">
		<tr>
			<td style="width:40%">
				<table class="data" style="width:100%">
					<tr class="theader">
						<td>Customer Details</td>
					</tr>
					<tr>
						<td><p><b>Customer Code: </b><?php echo $code; ?></p>
							<p><b>Address:</b><br /><?php echo $name . "<br />" . $address; ?></p>
							<p><b>Tel: </b><?php echo $phone . "<br /><b>Fax: </b>" . $fax; ?></p>
						</td>
					</tr>
				</table>
			</td>
			<td style="width:60%">
				<table class="data" style="width:100%">
					<tr  class="theader">
						<td>Customer Orders History</td>
					</tr>
					<tr>
						<td>
<?php

require('../assets/mssql_connect.php');

// BUILD NEW QUERY FOR ORDERS
$orders = mssql_query("SELECT TOP 10 * FROM MainJobDetails WHERE InvoiceCustomerCode='$code'")
or die(mssql_get_last_message($orders));

// CHECK FOR 0 RECORDS
if(mssql_num_rows($orders) == 0) {
echo "No previous orders found.";
} else {

//OUTPUT PREVIOUS ORDERS
echo "Last 10 Orders<br />";
echo "<table class='orders'>";
echo "<tr><td>Date</td><td>Job Number</td><td>Description</td><td>Status</td><td>Value</td></tr>";

// ALTERNATE ROW COLOURS
$c = 0;

  while ($row = mssql_fetch_array($orders)) {
      
    $class = $c++ % 2 == 1 ? "odd" : "even"; 
echo "<tr class='$class'><td>"; 
echo $row['CreateDateTime'];
echo "</td><td>"; 
echo $row['JobNo'];
echo "</td><td>";
echo $row['JobDesc'];
echo "</td><td>"; 
echo $row['Description'];
echo "</td><td>";
echo $row['PriceEst'];
echo "</td></tr>";
  }  

echo "</table>";

}

mssql_free_result($orders);

?>
						</td>
					</tr>
				</table>
			</td>
		</tr>
		<tr>
			<td style="width:40%">
				<table class="data" style="width:100%">
					<tr class="theader">
						<td>Comments Log</td>
					</tr>
					<tr>
						<td>
<?php

// GET CURRENT COMMENTS
$comments = mysql_query("SELECT * FROM comments WHERE customer_id='$customer' ORDER BY id DESC LIMIT 0,5")
or die(mysql_error());

// CHECK FOR 0 COMMENTS
if(mysql_num_rows($comments) == 0) {
echo "<p>No comments about this customer.</p>";
}

// LOOP THROUGH PREVIOUS COMMENTS
while ($row = mysql_fetch_array($comments)) {
echo "<p style='line-height:20px;'><b>" . $row['rep'] . "</b> <i>(" . $row['date'] . " at " . $row['time'] . ")</i><br />";
echo $row['comment'] . "</p>";
}

?>
						<form method="POST" name="comments" action="../assets/comment_post.php" onsubmit="return validateCForm();">
							<textarea rows="10" cols="45" name="comment_box"></textarea><br />
							<input type="hidden" name="comment_date" value="<?php echo date('d.m.Y'); ?>">
							<input type="hidden" name="comment_time" value="<?php echo date('H:i:s'); ?>">
							<input type="hidden" name="id" value="<?php echo $customer; ?>">
							<input type="hidden" name="rep" value="<?php echo $rep; ?>"><br />
							<input type="submit" class="search" name="submit" value="Post Comment">
						</form>
						</td>
					</tr>
				</table>
			</td>
			<td style="width:60%">
				<table class="data" style="width:100%">
					<tr  class="theader">
						<td>Customer Estimates History</td>
					</tr>
					<tr>
						<td>
<?php

// BUILD NEW QUERY FOR QUOTES
$quotes = mssql_query("SELECT * FROM MainEstimateDetails WHERE CustomerRef = '$code' ORDER by EstimateDate DESC LIMIT 0,10")
or die (mssql_error());

// CHECK FOR 0 QUOTES
if(mssql_num_rows($quotes) == 0) {
echo "No previous quotes found.";
} else {

// OUTPUT PREVIOUS ORDERS
echo "<table class='orders'>";
echo "<tr><td>Date</td><td>Job Number</td><td>Description</td><td>Status</td><td>Value</td></tr>";

// ALTERNATE ROW COLOURS
$c = 0;

while ($row = mssql_fetch_array($quotes)) {
    
    $class = $c++ % 2 == 1 ? "odd" : "even"; 
echo "<tr class='$class'><td>"; 
echo $row['CreateDateTime'];
echo "</td><td>"; 
echo $row['JobNo'];
echo "</td><td>";
echo $row['JobDesc'];
echo "</td><td>"; 
echo $row['description'];
echo "</td><td>";
echo $row['PriceEst'];
echo "</td></tr>";
  }  

echo "</table>";


}

?>
						</td>
					</tr>
				</table>
			</td>
		</tr>
	</table>
</div>
</body>
</html>

However, still same result. Script stops.

fugix · June 15, 2011

side note, you really only need to use mssql_free_result() at the end of your script, isnt needed after 1 query, locate it there

fRAiLtY- · June 15, 2011

Do I put any variables in it? as by that time I have 3 variables stored/queries.

.. Or just mssql_free_result(); at the bottom of the script?

mikosiko · June 15, 2011

had you try to debug your code with errors report/display enabled?

<?php
   // Define how to display/report errors
   ini_set("display_errors", "1");
   error_reporting(E_ALL);

require('../assets/mssql_connect.php');

// BUILD NEW QUERY FOR ORDERS
$orders = mssql_query("SELECT TOP 10 * FROM MainJobDetails WHERE InvoiceCustomerCode='$code'")

try that and see if you get some error

fugix · June 15, 2011

Do I put any variables in it? as by that time I have 3 variables stored/queries.

.. Or just mssql_free_result(); at the bottom of the script?

to my knowledge, not declaring an argument in the function will result in all memory to be freed, otherwise the memory the the result resource specified will be freed

fRAiLtY- · June 15, 2011

Hi,

I have tried the error reporting as suggested, nothing happens at all script still stops in same place.

Also I have moved mssql_free_result(); to the end of the script, no change.

Cheers.

fugix · June 15, 2011

have you tried declaring the link_identifier in your mssql_query(), in your include file where you connect to mssql, set it to a variable

$link = mssql_connect(info,info,info);

then in your queries declare it,

$query = mssql_query(info, $link)

mikosiko · June 15, 2011

Hi,

I have tried the error reporting as suggested, nothing happens at all script still stops in same place.

Also I have moved mssql_free_result(); to the end of the script, no change.

Cheers.

I'm not an expert in mssql but a quick search for mssql_get_last_message() shows me that the function doesn't need parameters.. also noticed that in other parts of your code you are using it without parameters... why are you using it differently here?... could be that the cause of your problem? ... why you don't try it without parameters ?

fRAiLtY- · June 15, 2011

Hi guys,

Ok, I've tried declaring the link from my mssql_connect which is $conn, I now have an error. The error is:

Warning: mssql_query(): supplied resource is not a valid MS SQL-Link resource in /home/bjb/public_html/reps/dashboard/customer.php on line 39

Changed database context to 'Thardata'.

Line 39 is: $edit = mssql_query("SELECT * FROM Customers WHERE ID='$customer'", $conn)

There's no line terminator as it has an or die(); on the line after.

So, progress, we have an error? This query is the first query, that before this modification worked fine.

Cheers.

fugix · June 15, 2011

this basically means that your $conn is not supplying a valid connection..

fRAiLtY- · June 15, 2011

How curious, why does it let me execute 1 query then?

What are the solutions, if any? If I run mssql_connect.php I get success messages?

Confused.

fugix · June 15, 2011

I find that interesting, so basically you have a connection from your included file otherwise your original query would not have worked. Do you set the same connection to a variable ($conn) and applied it as the second argument in your mssql_query() and received that error? Perhaps it's because your 4th parameter in you connection functions is not set to true. I'm running out of ideas for this one

fRAiLtY- · June 16, 2011

Hi, thanks for the suggestions.

I've tried setting the 4th variable to TRUE but no change. Error remains. If I remove the $conn from the mssql_query(); the script returns to a running state but halts at the same point.

:wtf: :wtf:

cssfreakie · June 16, 2011

it could very well be that you require a new connection handle, since your using another database.

http://www.php.net/manual/en/function.mysql-select-db.php#52584

Did you read the topic i linked to , and did you try it??

fRAiLtY- · June 16, 2011

Yes and no it didn't :-[

I sent an e-mail to Microsoft today and they said there is no stable connection between Linux and MSSQL and that FreeTDS is buggy. Also PDO isn't supported apparently so using this would cause problems too.

I've abandoned it for now. Thanks for help guys, apparently it's an incompatibility/bug with the "driver" I'm using to connect Linux to MSSQL

:shrug:

Sign In

Problem with SQL Queries

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information