Nested looping through MSSQL results

omredux · December 10, 2007

I'm working on several scripts that form a small, custom build appointment scheduling application. I'm having some trouble with code I'm using to check whether appointments for individual times during a given day exist in the database. So far the function:

creates an HTML table
use the php date function to output a string with the time from 9:30 -- 4:30 into the string $time1, and outputs them into the first table row

I want the function to also:

get row['time'] from a MSSQL database (this row records the appointment time)
compare each row returned with the values in $time1 and see if they match
if they match, returns the text "Appointment closed". If they don't, returns a selectable radio button.

Here's the code I've written so far. I'm very new to PHP, so I'm sure I'm overlooking something:

//Set database access variables

$host =***********
$user = *********
$pass =*******
$db =  ******

//Open connection

$connection = mssql_connect($host, $user, $pass) or die("Unable to connect");

//Select database

mssql_select_db($db) or die("Unable to select database");

// create query

    //$query = "SELECT computer, time, netid FROM bloomberg WHERE(date=N'$date') AND (computer=N'$comp')";

$query = "SELECT time FROM bloomberg WHERE(date = N'11-30-2007')";

// execute query
    $result = mssql_query($query) or die ("Error in query");

//while ($row = mssql_fetch_array($result)) {
	//echo $row['time'];
	//}	
//for ($i = 0; $i < mssql_num_rows($result); $i++){
//$row= mssql_fetch_row($result);
//echo $time[3];
//}

//for ($i = 0; $i < mssql_num_rows($result); $i++){



//Create table from with each hour of the day
// $start is initial value

	$start = strtotime('8:30am'); 
	echo "<table border = '1' align = 'center'>";
	echo "<tr>";
	echo "<th>time</th>";
	echo "<th>select</th>";
	echo "</tr>";	




for($i = 0; $i < 17; $i++){

	//$tod increment each hour by 1
	$tod = $start + ($i * 60 * 30);	
	$display = date('h:i', $tod); 

	//check if $tableflag set
	echo "<tr>";
	echo "<td>$display</td>";
	echo "<td>$tableflag</td>";
		if($tableflag == 1){
			echo "<td>1</td>";
			}
		else {
			echo "<td>0</td>";
			}
	//set the tableflag to 1 for each value in $display that == a value in $time
	for ($j = 0; $j < mssql_num_rows($result); $j++){
		$row = mssql_fetch_row($result);
		$time = $row[0];
		if($time == $display){
			$tableflag = 1;
			}
	}
	echo "</tr>";
}
echo "</table>";


//close connection

mssql_close($connection);

Thanks for the help in advance,

David

lachild · December 10, 2007

Hmm.. I think the thing that would help you most is a general set of database calls, that will turn the results into something a bit more usefull and will keep your code from getting all jumbled up. Here's a copy of the database functions that I use in all my applications. Note: You could do a class like most of the others I know but I like the simplicity of functions for my db calls.

db_func.php

<?php
function db_update($query)
{
global $db_config;

$username = $db_config['user_name'];
$password = $db_config['pass_word'];
$db_host = $db_config['host'];
$database = $db_config['data_base'];

$connect = mysql_connect($db_host,$username,$password);
if (!$connect)
	return false;

$select = @mysql_select_db($database);
if (!$select)
	return false;

mysql_query($query) or die(mysql_error()." ".$query);

$insertedID = mysql_insert_id();

mysql_close($connect);

return $insertedID;
}

function db_query($query)
{
global $db_config;

$username = $db_config['user_name'];
$password = $db_config['pass_word'];
$db_host = $db_config['host'];
$database = $db_config['data_base'];

$connect = mysql_connect($db_host,$username,$password);
if (!connect)
	return false;

$select = @mysql_select_db($database);
if (!$select)
	return false;

$result = mysql_query($query) or die(mysql_error());

//Put Get Data into usable format
$myI = 0;
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
	{
	foreach ($row as $key => $value)
		{
		$return[$myI][$key] = $value;
		}
	$myI++;
	}	
mysql_close($connect);

if (empty($result))
	return false;
else
	return $return;

}

function db_query_first($query)
{
global $db_config;

$username = $db_config['user_name'];
$password = $db_config['pass_word'];
$db_host = $db_config['host'];
$database = $db_config['data_base'];

$connect = mysql_connect($db_host,$username,$password);
if (!connect)
	return false;

$select = @mysql_select_db($database);
if (!$select)
	return false;

$patt = array('/LIMIT [.*]/', '/Limit [.*]/', '/limit [.*]/', '/lIMIT [.*]/', '/;/');
$query = preg_replace($patt, '', $query);
$result = mysql_query($query." LIMIT 1;") or die(mysql_error());

//Put Get Data into usable format
$myI = 0;
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
	{
	foreach ($row as $key => $value)
		{
		$return[$key] = $value;
		}
	}	
mysql_close($connect);

if (empty($result))
	return false;
else
	return $return;

}

function db_query_first_cell($query)
{
global $db_config;

$username = $db_config['user_name'];
$password = $db_config['pass_word'];
$db_host = $db_config['host'];
$database = $db_config['data_base'];

$connect = mysql_connect($db_host,$username,$password);
if (!connect)
	return false;

$select = @mysql_select_db($database);
if (!$select)
	return false;

$result = mysql_query($query) or die(mysql_error());

//Put Get Data into usable format
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
	{
	foreach ($row as $key => $value)
		{
		$return = $value;
		break 2;
		}
	}	
mysql_close($connect);

if (empty($result))
	return false;
else
	return $return;

}

To use you need to specify the following at the beginning of your applications

<?php
require_once('path_to_db_func.php');
$db_config['user_name'] = 'My DB User Name';
$db_config['pass_word'] = 'My DB Password';
$db_config['host'] = 'My DB Host Name';
$db_config['data_base'] = 'My database';

from here there are a couple of functions at your disposal.

db_update($query) = Will update the database with the sql you send it and return the last id that was entered. Great for quick inserts or updates and will generate the ID for use with following records.

db_query($query) = Will preform the query you ask and return the each row in it's own array

db_query_first($query) = Exactly the same as db_query but will return only the first row.

db_query_first_cell($query) = returns the first cell of the query.

Using your program as an example, the statement:

SELECT computer, time, netid FROM bloomberg WHERE(date=N'$date') AND (computer=N'$comp')

would return the following results:

db_query

$result = db_query("SELECT `computer`, `time`, `netid` FROM bloomberg WHERE `date`=N'$date' AND `computer`=N'$comp');");

print_r($result);

would output

array(
     [0] => array(
          [computer] => result one computer value
          [time] => result one time value
          [netid] => result one netid value
          )
     [1] => array(
          [computer] => result two computer value
          [time] => result two ne time value
          [netid] => result two netid value
          )
     [2] => etc...
     )

db_query_first

$result = db_query_first_row("SELECT `computer`, `time`, `netid` FROM bloomberg WHERE `date`=N'$date' AND `computer`=N'$comp');");

print_r($result);

would output

array(
     [computer] => result one computer value
     [time] => result one time value
     [netid] => result one netid value
     )

db_query_first_cell

$result = db_query_first_cell("SELECT `computer`, `time`, `netid` FROM bloomberg WHERE `date`=N'$date' AND `computer`=N'$comp');");

print_r($result);

would output

result one computer value

Ok now that that's out of the way let's get to the main question in your post...

To get the time from the rows the best way is to do something like this...

/* This will format a mktime() into the format listed in your code, However to make life easier I'd suggest making your date table store a unix timestamp instead as it's easier to work with in php */
$time1 = 'N'.(date("m-d-Y", $search_timestamp); 
$result = db_query("SELECT * FROM bloomberg WHERE `time` LIKE '".$time1."';");

print "The following appointments are closed";
foreach ($result as $value){
    $displaytime = strtotime($value['time']);
    echo date("m-d-Y", $displaytime).'<br />';
   }

Hope this helps and gets you moving in the right direction. Let me know if you still need help.

omredux · December 11, 2007

Thanks for the help! The next time I'm going to work on the code is Thursday, and I'll let you know how things go.

omredux · December 14, 2007

Thanks again for the help! I'm not going to implement the revised database calls right away, but I'm going to test them out and put them in my code over the next week or two (I have about 5 different function that access the database, so it could take a little while). I've noticed that the functions are written with MYSQL in mind. I'm using MS SQL Sever. Can I still use a constant similar to MYSQL_ASSOC?

Sorry it took me so long to respond, but I only do this programming job part time, and I don't have work during the middle of the week. I've used your code to modify the function, and it makes things much easier!

lachild · December 14, 2007

Sorry I thought the "MS" was "MY" when I read your post.

Anyway yes, MS SQL and MySql have almost the same commands with the exception of mysql_insert_id. MS SQL does not have this ability but I did find a site that gave a work around. http://forums.whirlpool.net.au/forum-replies-archive.cfm/375667.html

With that said here is the code again for use with ms sql.

function db_update($query)
{
global $db_config;

$username = $db_config['user_name'];
$password = $db_config['pass_word'];
$db_host = $db_config['host'];
$database = $db_config['data_base'];

$connect = mssql_connect($db_host,$username,$password);
if (!$connect)
	return false;

$select = @mssql_select_db($database);
if (!$select)
	return false;

mssql_query($query) or die(mssql_get_last_message()." ".$query);

mssql_close($connect);

return true;
}

function db_query($query)
{
global $db_config;

$username = $db_config['user_name'];
$password = $db_config['pass_word'];
$db_host = $db_config['host'];
$database = $db_config['data_base'];

$connect = mssql_connect($db_host,$username,$password);
if (!connect)
	return false;

$select = @mssql_select_db($database);
if (!$select)
	return false;

$result = mssql_query($query) or die(mssql_get_last_message());

//Put Get Data into usable format
$myI = 0;
while($row = mssql_fetch_array($result, MSSQL_ASSOC))
	{
	foreach ($row as $key => $value)
		{
		$return[$myI][$key] = $value;
		}
	$myI++;
	}	
mssql_close($connect);

if (empty($result))
	return false;
else
	return $return;

}

function db_query_first($query)
{
global $db_config;

$username = $db_config['user_name'];
$password = $db_config['pass_word'];
$db_host = $db_config['host'];
$database = $db_config['data_base'];

$connect = mssql_connect($db_host,$username,$password);
if (!connect)
	return false;

$select = @mssql_select_db($database);
if (!$select)
	return false;

$patt = array('/LIMIT [.*]/', '/Limit [.*]/', '/limit [.*]/', '/lIMIT [.*]/', '/;/');
$query = preg_replace($patt, '', $query);
$result = mssql_query($query." LIMIT 1;") or die(mssql_get_last_message());

//Put Get Data into usable format
$myI = 0;
while($row = mssql_fetch_array($result, MSSQL_ASSOC))
	{
	foreach ($row as $key => $value)
		{
		$return[$key] = $value;
		}
	}	
mssql_close($connect);

if (empty($result))
	return false;
else
	return $return;

}

function db_query_first_cell($query)
{
global $db_config;

$username = $db_config['user_name'];
$password = $db_config['pass_word'];
$db_host = $db_config['host'];
$database = $db_config['data_base'];

$connect = mssql_connect($db_host,$username,$password);
if (!connect)
	return false;

$select = @mssql_select_db($database);
if (!$select)
	return false;

$result = mssql_query($query) or die(mssql_get_last_message());

//Put Get Data into usable format
while($row = mssql_fetch_array($result, MSSQL_ASSOC))
	{
	foreach ($row as $key => $value)
		{
		$return = $value;
		break 2;
		}
	}	
mssql_close($connect);

if (empty($result))
	return false;
else
	return $return;

}

Edit: Found some mysql statements still floating around. Fixed.

Sign In

Nested looping through MSSQL results

Recommended Posts

omredux

Link to comment

Share on other sites

lachild

Link to comment

Share on other sites

omredux

Link to comment

Share on other sites

omredux

Link to comment

Share on other sites

lachild

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information