MySQL Conditional Query

[jackakos]

I am trying to query a table where at least three conditions have to be met and then the outcome stored in an array and printed into a table.

I tried with the code, but I am having problems setting the conditional query and would be happy with an assistance

$search1 = mysql_connect('','', '') or die('Could not connect: ' . mysql_error());
echo 'Connected successfully';
mysql_select_db("hello",$search1) or die ('Could not connect ' .mysql_error());
$my_checked = $_SESSION['check_array'];

FOREACH ($_SESSION['check_array'] as $check_array)
{	// conditional query of database
	$get_event = "select * from event where event_time like $_SESSION['event_time'] and emp_no like $_SESSION['check_array']";
		
/**
* put the results into an array and then print a table like this
* the status should be checked
* 	if my_status =='ongoing' status should be busy
*  if my_status =='cancelled' status should be free
*  if my_status =='' status should be free
* 
* fname     lname      event_date     event_time      status
* 
* john	 andrews     20-11-2008     1120	          free
* 
* sly	         samuels    20-11-2008   1120		  busy
* 
* 
*/
	
}

?>

[queenc]

dont use session variable in the condition.

[Shanti C]

hello jackakos,
in this line

$get_event = "select * from event where event_time like $_SESSION['event_time'] and emp_no like $_SESSION['check_array']";

it is better to use = while getting data eith emp_no.
and you can use $check_array in your query like:

$get_event = "select * from event where event_time like $_SESSION['event_time'] and emp_no like $check_array";

because we are using foreach..
if my post is wrong...please ignore..

[jackakos]

Shanti,

I think you are things out clearly, except it has date which is critical - because, I should be able to query the said date, time and ensure the emp_no also matches.

$get_event = "select * from event where event_date like $_SESSION['event_date'] event_time like $_SESSION['event_time'] and emp_no like $check_array";

Is the above possible?

[Shanti C]

not the matter , the above query is possible or not...if our session time and date matches the database fields , it will give according result...
But we want the desired output from that query...thats why post what you want,table structure and any other variables...then i will look into that...

[jackakos]

The Event table has the following attributes
(`event_id`, `start_time`, `event_date`, `description`, `location`, `duration`, `emp_no`)

What is expected of the query is to search the event table based on the date scheduled for a new event by checking if the person is already booked for that time.

I have session variable for the above attributes:

$_SESSION
$_SESSION
$_SESSION
$_SESSION
$_SESSION
$_SESSION

Below is the code

$search1 = mysql_connect('','', '') or die('Could not connect: ' . mysql_error());
echo 'Connected successfully';
mysql_select_db("hello",$search1) or die ('Could not connect ' .mysql_error());
$my_checked = $_SESSION['check_array'];

FOREACH ($_SESSION['check_array'] as $check_array)
{	// conditional query of database
	$get_event = "select * from event where emp_no like $check_array and event_date like $_SESSION['event_date'] and event_time like $_SESSION['event_time']";
		
/**
* put the results into an array and then print a table like this
* the status should be checked
* 	if my_status =='ongoing' status should be busy
*  if my_status =='cancelled' status should be free
*  if my_status =='' status should be free
* 
* fname     lname      event_date     event_time      status
* 
* john	 andrews     20-11-2008     1120	          free
* 
* sly	         samuels    20-11-2008   1120		  busy
* 
* 
*/
	
}

?>

[jackakos]

I have sorted the MySQL syntax, however, I need help.

I can now read from only one table and this is the results.

I have separated the queries because I was not getting any record.

line 28, 30 and 32 if used does not give any record, if that happens to be used then the others need to be commented out.

I was expecting to have the date, time and agenda to fill the table but it didn't

Connected successfully
Display Name Date Time Agenda Status
Ian Johnson
Jane White

<?php
session_start(); 
 
$search1 = mysql_connect('','', '') or die('Could not connect: ' . mysql_error());
echo 'Connected successfully';
mysql_select_db("",$search1) or die ('Could not connect ' .mysql_error());
$my_checked = $_SESSION['check_array'];

$e_date = $_SESSION['event_date'];
$s_time = $_SESSION['start_time'];
$halo_size = sizeof($_SESSION['check_array']);
// echo $halo_size;

echo "<br />";
echo "<table border='1'>
<tr>
<th>Display Name</th>
<th>Date        </th>
<th>Time        </th>
<th>Agenda      </th>
<th>Status      </th>";

FOREACH ($_SESSION['check_array'] as $check_array)
{	// conditional query of database

$get_event = "select * from event where event_date = $e_date own_no = $check_array";

//$get_mill = "SELECT own_no,  event_date, start_time, fname, lname FROM event, employee WHERE start_time= $s_time own_no=emp_no";

//$get_mill_res = mysql_query($get_mill) or die(mysql_error());

//$my_mill = mysql_fetch_array($get_mill_res);


$get_event_res = mysql_query($get_event) or die(mysql_error());
$row = mysql_fetch_array($get_event_res);

$get_fname = $my_mill['fname'];
$get_lname = $my_mill['lname'];
$get_date = $my_mill['event_date'];
$get_time = $my_mill['start_time'];
$get_des = $my_mill['description'];

$get_name = "select fname, lname from employee where emp_no = $check_array";

$get_name_res = mysql_query($get_name) or die(mysql_error());

$name = mysql_fetch_array($get_name_res);



echo "<tr>";
echo "<td>" . $name['fname']. " " .$name['lname'] ."</td>";
echo "<td>" . $get_date . "</td>";
echo "<td>" . $get_time . "</td>";
echo "<td>" . $get_des . "</td>";
echo "</tr>";
 
 
		
	/** stored procedures and PHP
	 * put the results into an array and then print a table like this
	 * the status should be checked
	 * 	if my_status =='ongoing' status should be busy
	 *  if my_status =='cancelled' status should be free
	 *  if my_status =='' status should be free
	 * 
	 * fname     lname      event_date     event_time      status
	 * 
	 * john		 andrews     20-11-2008     1120		    free
	 * 
	 * 	sly		 samuels     20-11-2008		1120			busy
	 * 
	 * 
	 */
	
}

?>

[jackakos]

Folks, I have solved the problem with the follwoing script just in case any one may be interested.

FOREACH statement worked for me alright by using it on the array to search the database

<?php
session_start(); 
?>

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title> </title>
</head>
 
<body>
<form name="check" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="POST"> 

<?php
echo "<table border='1'>
<tr>
<th>Name</th>
<th>Date  </th>
<th>Time  </th>
<th>Agenda </th>
<th>Status </th>";

$poll = mysql_connect('','', '') or die('Could not connect: ' . mysql_error());
echo 'Connected successfully';
mysql_select_db("",$poll) or die ('Could not connect ' .mysql_error());
 

FOREACH ($_SESSION['check_array'] as $check_array)
{
   
$get_event = "select * from event where event_date = $e_date own_no = $check_array";

$result = mysql_query($get_event) or die(mysql_error());
 
while ($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['fname']. " " .$row['lname'] ."</td>";
echo "<td>" . $row['event_date'] . "</td>";
echo "<td>" . $row['start_time'] . "</td>";
echo "<td>" . $row['description'] . "</td>";
echo "</tr>";
 
}
}
echo "</table>";
echo "<br />";
echo "<br />";
?>
 
<table>
  <tr>
    <td><input type="submit" value="Stop"></td>
	<td><input type="submit" name="link" value="Next" /> </td>
  </tr>
</table>
</form>
</body>
</html>