Page 1 of 3

Selecting mutiple records problem.....

Posted: Wed Jun 27, 2007 6:52 am
by ghadacr
Ok i get a result from a database, and beside each record there is a checkbox as shown below:

Code: Select all

[<?PHP
extract($_GET);
$daterep = str_replace("/","-",$datefrom);
$datereps = str_replace("/","-",$dateto);


$newdate = date ("d M Y", strtotime ($daterep)); 
$newdates = date ("d M Y", strtotime ($datereps)); 

setcookie("from", $newdate, time(  )+600);
setcookie("to", $newdates, time(  )+600);
setcookie("hotel", $subcat, time(  )+600);

?>
<?PHP include 'opendb.php'; ?>

<?PHP include 'header.php'; ?>

<?php 


if(isset($_GET))
{

extract($_GET);


$optionsheld = $_GET['optionsheld'];
$confirmedroom = $_GET['confirmedroom'];
$cat = $_GET['cat'];
$Season = $_GET['Season'];


$query = mssql_init ("sp_SearchRooms"); 

mssql_bind($query, "@DateTo", $newdate, SQLVARCHAR);

mssql_bind($query, "@Season", $Season, SQLVARCHAR);

mssql_bind($query, "@resortID", $cat, SQLINT2);

mssql_bind($query, "@DateFrom", $newdates, SQLVARCHAR);

mssql_bind($query, "@HotelID", $subcat, SQLVARCHAR);

mssql_bind($query, "@SearchConfirmed", $groupname, SQLVARCHAR);

mssql_bind($query, "@SearchOptions", $optionsheld, SQLVARCHAR);
if (($result = mssql_execute($query)) === false) 
{ 
    die('Could not execute the query ' . $sql ); 
} 
//$held = "1";

	
//$que = mssql_init ("sp_SearchByName"); 

//mssql_bind($query, "@ClientName ", $optionsname, SQLVARCHAR);

//mssql_bind($query, "@UserName", $username, SQLVARCHAR);

//mssql_bind($query, "@SearchOptionsHeldByMe", $held, SQLINT2);

//if (($res = mssql_execute($que)) === false) 
//{ 
//    die('Could not execute the query ' . $sql ); 
//} 
	


?> 
<?PHP

$count=mssql_num_rows($result);	
//$count=mssql_num_rows($res);

 if ($count == 0)
  {
echo "<p>Sorry, your search returned no results</p><br><input type='button' value='Retry' onClick='history.go(-1)'>";

} else {

?>

<form action="roomhold.php" method="get"> 
  <table width="108%" border="0">
    <tr> 
     <td width="19%"></td>
	  <td width="8%"><strong>Room Type:</strong></td>
      <td width="8%"><strong>Available From:</strong></td>
      <td width="8%"><strong>Available To:</strong></td>
	  <td width="9%"><strong>Requested from</strong></td>
	  <td width="9%"><strong>Requested To:</strong></td>
      <td width="5%"><strong>Notes</strong></td>
      <td width="44%">&nbsp;</td>
    </tr>
    <?php 

	
while ($row = mssql_fetch_array($result))
		
{ 
    echo '<tr>';
	echo '<td><a href="roomdetails.php?HotelRoomID=' . $row['HotelRoomID'] . '">More information on hotel room</a></td>'; 
    echo '<td>' . $row['RoomType' ] . '</td>'; 
	echo '<td>' . $row['AvailableFrom' ] . '</td>';
	echo '<td>' . $row['AvailableTo' ] . '</td>';
	echo '<td><input type="hidden" name="datefrom" value="' . $datefrom . '" />' . $datefrom .'</td>';
	echo '<td><input type="hidden" name="dateto" value="' . $dateto . '" />' . $dateto .'</td>';
	echo '<td>' . $row['Notes' ] . '</td>';
    echo '<td><input type="checkbox" name="HotelRoomID[]" value="' . $row['HotelRoomID'] . '" />Select  ' . $row['RoomType'] .' to update</td>'; 
    echo '</tr>'; 

}





?>
    <tr> 
      <td colspan="6"><input type="Submit" value="Hold Room"> <INPUT name="button" type="button" onClick="location.href='http://pb-sql/admin.php'" value="Cancel"> 
      </td>
    </tr>
  </table> 
</form> 

<?PHP include 'footer.php'; ?>
<?PHP }} 
?>
What the problem is when i select more than one record or even just one record i get the following error from the handler script:
message: Syntax error converting the varchar value '=37' to a column of data type int. (severity 16) in C: line 26
Here is the code for the handler script

Code: Select all

<?PHP include 'header.php'; ?>
<?PHP include 'opendb.php'; ?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>

<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="css/calender.css" rel="stylesheet" type="text/css">
</head>

<body>
<?PHP 
//$HotelRoomID = $_GET['HotelRoomID'];
$datefrom = $_GET['datefrom'];
$dateto = $_GET['dateto'];

if(isset($_GET['HotelRoomID']))
{
foreach($_GET['HotelRoomID'] as $k=>$k1)
{


$sql="SELECT * FROM HotelRooms WHERE HotelRoomID='=$k1'"; 
$result=mssql_query($sql);

$ques="SELECT dbo.Hotels.HotelName FROM Hotels INNER JOIN HotelRooms ON dbo.Hotels.HotelID = dbo.HotelRooms.HotelID WHERE HotelRoomID='=$k1'";
$query="SELECT dbo.RoomAssociations.RoomAssociation FROM RoomAssociations INNER JOIN HotelRooms ON dbo.RoomAssociations.RoomAssociationID = dbo.HotelRooms.RoomAssociationID WHERE HotelRoomID='=$k1'"; 

$res=mssql_query($ques);
$reso=mssql_query($query);

$count=mssql_num_rows($result);


?>


<form method=get name=f1 action='confirmmholdroom.php'>
     <?php
$i=0;
while ($i < $count) {

$rows=mssql_fetch_array($result);
$row=mssql_fetch_array($res);		
$rowe=mssql_fetch_array($reso);
	

?> 
  <table width="85%" border="0">
    <tr> 
      <td width="23%"><strong>Requested from:</strong></td>
      <td width="16%"><strong>Requested to:</strong></td>
      <td width="16%"><strong>Hotel</strong></td>
      <td width="14%"><strong>Room:</strong></td>
      <td width="31%"><strong>Notes</strong></td>
    </tr>
    <tr> 
      <td><?php echo $datefrom; ?> <input type="hidden" name="$datefrom" value="<?PHP echo $datefrom; ?>"> 
        <input type="hidden" name="HotelRoomID" value="<?PHP echo $HotelRoomID; ?>"></td>
      <td><?php echo $dateto; ?> <input type="hidden" name="$dateto" value="<?PHP echo $dateto; ?>"></td>
      <td><?php echo $row['HotelName']; ?> <input type="hidden" name="HotelName" value="<?PHP echo $rows['HotelName']; ?>"></td>
      <td><?php echo $rows['RoomType']; ?> <input type="hidden" name="RoomType" value="<?PHP echo $rows['RoomType']; ?>"></td>
      <td><?php echo $rows['Notes']; ?> <input type="hidden" name="Notes" value="<?PHP echo $rows['Notes']; ?>"></td>
    </tr>
    <tr> 
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr> 
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr> 
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr> 
      <td>Sales consultant</td>
      <td><input readonly type="text" name="UserName" value="<?php $nw = new COM("WScript.Network"); echo $nw->username;  ?>"></td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr> 
      <td>Surname</td>
      <td><input type="text" name="surname"></td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp; </td>
    </tr>
    <tr> 
      <td>Forename</td>
      <td><input type="text" name="forename"></td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr> 
      <td>Option held:</td>
      <td><input readonly type="text" name="DateHeld" value="<?PHP echo date("d/m/Y"); ?>"></td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr> 
      <td><input type="submit" name="Submit" value="Hold room">
        <input name="button" type="button" onClick="location.href='http://pb-sql/wintersearch.php'" value="Cancel"></td>
      <td>&nbsp; </td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr> 
      <td>&nbsp; </td>
      <td>&nbsp;</td>
      <td>&nbsp; </td>
      <td>&nbsp;</td>
      <td>&nbsp; </td>
    </tr>
  </table>
</form>

<?PHP 
 ++$i;

} }} ?>         <?php 
mssql_close();
?>
</body>
</html>

<P>
<?PHP include 'footer.php'; ?>

Posted: Wed Jun 27, 2007 6:58 am
by feyd
Look closely at the queries you are creating.

Posted: Wed Jun 27, 2007 7:34 am
by ghadacr
Nothing wrong with them

Posted: Wed Jun 27, 2007 7:35 am
by feyd
Yes, there is.

Posted: Wed Jun 27, 2007 7:40 am
by Gente

Code: Select all

$sql="SELECT * FROM HotelRooms WHERE HotelRoomID='=$k1'";
Do you really have ids in '=Num' format??? :wink:

Posted: Wed Jun 27, 2007 7:40 am
by ghadacr
Please can you direct me towards the problem......

Posted: Wed Jun 27, 2007 7:52 am
by ghadacr
Gente wrote:

Code: Select all

$sql="SELECT * FROM HotelRooms WHERE HotelRoomID='=$k1'";
Do you really have ids in '=Num' format??? :wink:
Yes

Posted: Wed Jun 27, 2007 7:54 am
by feyd
Considering the error you are getting, no you don't. Your database is expecting a number, you're supplying a string.

Posted: Wed Jun 27, 2007 8:00 am
by volka
Take a break, go for a walk and then in an hour or so take a look at the query again.
The error stares at you, you just have to stare back ;)

Posted: Wed Jun 27, 2007 8:14 am
by CoderGoblin
volka wrote:Take a break, go for a walk and then in an hour or so take a look at the query again.
The error stares at you, you just have to stare back ;)
Lost track of the amount of times doing that has saved myself a headache in the long run.

Posted: Thu Jun 28, 2007 3:05 am
by ghadacr
volka wrote:Take a break, go for a walk and then in an hour or so take a look at the query again.
The error stares at you, you just have to stare back ;)
Done better than that, took the rest of the day off..... Ready to look at the problem... report back if i have any issues....

Posted: Thu Jun 28, 2007 3:23 am
by ghadacr
Ok i got that problem sorted, know i'm getting this error:
Warning: Invalid argument supplied for foreach() in C:\ line 21


Notice: Undefined variable: count in C:\41
here is the code in its current format

Code: Select all

<?PHP include 'header.php'; ?>
<?PHP include 'opendb.php'; ?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>

<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="css/calender.css" rel="stylesheet" type="text/css">
</head>

<body>
<?PHP 
//$HotelRoomID = $_GET['HotelRoomID'];
$datefrom = $_GET['datefrom'];
$dateto = $_GET['dateto'];

if(isset($_GET['HotelRoomID']))
{
foreach($_GET['HotelRoomID'] as $k=>$k1)
{


$sql="SELECT * FROM HotelRooms WHERE HotelRoomID=$k1"; 
$ques="SELECT dbo.Hotels.HotelName FROM Hotels INNER JOIN HotelRooms ON dbo.Hotels.HotelID = dbo.HotelRooms.HotelID WHERE HotelRoomID=$k1";
$query="SELECT dbo.RoomAssociations.RoomAssociation FROM RoomAssociations INNER JOIN HotelRooms ON dbo.RoomAssociations.RoomAssociationID = dbo.HotelRooms.RoomAssociationID WHERE HotelRoomID=$k1"; 
$result=mssql_query($sql);
$res=mssql_query($ques);
$reso=mssql_query($query);

$count=mssql_num_rows($result);

}}
?>


<form method=get name=f1 action='confirmmholdroom.php'>
     <?php
$i=0;
while ($i < $count) {

$rows=mssql_fetch_array($result);
$row=mssql_fetch_array($res);		
$rowe=mssql_fetch_array($reso);
	

?> 
  <table width="85%" border="0">
    <tr> 
      <td width="23%"><strong>Requested from:</strong></td>
      <td width="16%"><strong>Requested to:</strong></td>
      <td width="16%"><strong>Hotel</strong></td>
      <td width="14%"><strong>Room:</strong></td>
      <td width="31%"><strong>Notes</strong></td>
    </tr>
    <tr> 
      <td><?php echo $datefrom; ?> <input type="hidden" name="$datefrom" value="<?PHP echo $datefrom; ?>"> 
        <input type="hidden" name="HotelRoomID" value="<?PHP echo $HotelRoomID; ?>"></td>
      <td><?php echo $dateto; ?> <input type="hidden" name="$dateto" value="<?PHP echo $dateto; ?>"></td>
      <td><?php echo $row['HotelName']; ?> <input type="hidden" name="HotelName" value="<?PHP echo $rows['HotelName']; ?>"></td>
      <td><?php echo $rows['RoomType']; ?> <input type="hidden" name="RoomType" value="<?PHP echo $rows['RoomType']; ?>"></td>
      <td><?php echo $rows['Notes']; ?> <input type="hidden" name="Notes" value="<?PHP echo $rows['Notes']; ?>"></td>
    </tr>
    <tr> 
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr> 
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr> 
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr> 
      <td>Sales consultant</td>
      <td><input readonly type="text" name="UserName" value="<?php $nw = new COM("WScript.Network"); echo $nw->username;  ?>"></td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr> 
      <td>Surname</td>
      <td><input type="text" name="surname"></td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp; </td>
    </tr>
    <tr> 
      <td>Forename</td>
      <td><input type="text" name="forename"></td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr> 
      <td>Option held:</td>
      <td><input readonly type="text" name="DateHeld" value="<?PHP echo date("d/m/Y"); ?>"></td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr> 
      <td><input type="submit" name="Submit" value="Hold room">
        <input name="button" type="button" onClick="location.href='http://pb-sql/wintersearch.php'" value="Cancel"></td>
      <td>&nbsp; </td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr> 
      <td>&nbsp; </td>
      <td>&nbsp;</td>
      <td>&nbsp; </td>
      <td>&nbsp;</td>
      <td>&nbsp; </td>
    </tr>
  </table>
</form>

<?PHP 
 ++$i;

}  ?>         <?php 
mssql_close();
?>
</body>
</html>

<P>
<?PHP include 'footer.php'; ?>

Posted: Thu Jun 28, 2007 3:29 am
by volka
ghadacr wrote:foreach($_GET['HotelRoomID'] as $k=>$k1)
There is no array $_GET['HotelRoomID']. According to
ghadacr wrote:<input type="hidden" name="HotelRoomID" value="<?PHP echo $HotelRoomID; ?>"></td>
$_GET['HotelRoomID'] will be a simple string.

Posted: Thu Jun 28, 2007 3:38 am
by ghadacr
volka wrote:
ghadacr wrote:foreach($_GET['HotelRoomID'] as $k=>$k1)
There is no array $_GET['HotelRoomID']. According to
ghadacr wrote:<input type="hidden" name="HotelRoomID" value="<?PHP echo $HotelRoomID; ?>"></td>
$_GET['HotelRoomID'] will be a simple string.
No this is the array that is coming in:

Code: Select all

<input type="checkbox" name="HotelRoomID[]" value="' . $row['HotelRoomID'] . '" />Select  ' . $row['RoomType'] .' to update</td>';
The above code is what is being feed in...to this script..

Not
<input type="hidden" name="HotelRoomID" value="<?PHP echo $HotelRoomID; ?>>

Posted: Thu Jun 28, 2007 3:46 am
by volka
Ah, so now we're looking at roomhold.php, not confirmmholdroom.php
It's no easy to keep track since you remove the script name in the error/warning message.

please try

Code: Select all

if(isset($_GET['HotelRoomID']))
{
	echo '<pre>HotelRoomID: '; var_dump($_GET['HotelRoomID']); echo "</pre>\n";
	foreach($_GET['HotelRoomID'] as $k=>$k1)