[SOLVED] Why can't I get my SQL query to populate my table?

matt.sisto · May 2, 2009

Hi all, I am having problems getting my check availability form to work. I have echoed out the SQL which is giving the correct result, but the table appears empty.

<?php
  session_start();
  
if (!isset($_SESSION['username']))
    {
header("Location: login.php");
exit();
    }

  require "dbconn2.php";
  
  $month = $_POST["month"];
  $day = $_POST["day"];
  $year= $_POST["year"];
  $con_id1= $_POST["con_id1"];

  $event_start = $year."-".$month."-".$day." ".$_POST["event_start"];
  
  $sql = "SELECT * FROM calendar_events WHERE event_start='".$event_start."' AND (con_1='".$con_id1."' OR con_2='".$con_id1."' OR con_3='".$con_id1."')";
  $result = mysql_query ($sql, $connection)
    or die ("Couldn't perform query $sql <br />".mysql_error());


  $sql="SELECT con_id, first_name, last_name FROM consultant";
  $result=mysql_query($sql);
  $rconsultant_option="";

while ($row=mysql_fetch_array($result)) {

    $con_id=$row["con_id"];
    $first_name=$row["first_name"];
$last_name=$row["last_name"];
    $consultant_option1.="<OPTION VALUE=\"$con_id\">".$first_name." ".$last_name;
}

  $yeartoday = date(Y);
  $yearplus1 = date(Y)+1;
  
  for($y=$yeartoday;$y<=$yearplus1;$y++){  
       $selected = "";
       $longDate = date("Y", mktime(0,0,0,1,1,$y));
       if ($Year==$y){ 
       $selected = "selected \n";
        }
        $year_option.="<option value=\"$y\"$selected>$longDate</option>\n";
        }

?>

<!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=UTF-8" />
<title>List All</title>
<script language="JavaScript">
function win2() {
    window.open("addeventform.php","Window2","menubar=no,width=460,height=360,toolbar=no");
}

</script>
</head>

<body>


<legend>Check Availability
<div id="mid"> 
  <table border="1" cellpadding="3">
    <tr bgcolor="#CCCCCC">
      <th align="left">ID </th>
      <th align="left">Start Date </th>
      <th align="left">End Date </th>
      <th align="left">Start Time</th>
      <th align="left">Consultant 1</th>         
      <th align="left">Consultant 2</th>
      <th align="left">Consultant 3</th>
      <th align="left">Country</th>
    </tr>
    
    <?php
    while ($row = mysql_fetch_array($result))
{?>
    <tr>
      <td><?=$row['event_id']?></td>
      <td><?=$row['event_start']?></td>
      <td><?=$row['event_end']?></td>
      <td><?=$row['start_time']?></td>
      <td><?=$row['con_1']?></td>
      <td><?=$row['con_2']?></td>
      <td><?=$row['con_3']?></td>
      <td><?=$row['country']?></td>
    </tr>

    <?php } ?>
  </table>
</div>

<div id="left">
<p>
<input type="button" onClick="javascript:self.close()" value="close">
<input type="submit" onClick="javascript:win2()" value="Add an Event"></p>
</div>
</legend> 
<form name="bookingForm"  action="check.php" method="post">
<label id="eventStart">Event Date:</label><br />
<select id="year3" name="year"><option value="<?=$row['year']?>">Year<?=$year_option?></option></select>
<?php
$arrMonth = array('01','02','03','04','05','06','07','08','09','10','11','12');
?>
<select name="month" value="<?=$row['month']?>"/>
<?php
$months = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec");
for ($x=1; $x <= count($months); $x++) {
   echo"<option value=\"$x\"";
   if ($x == $month) {
      echo " selected";
   }
   echo ">".$months[$x-1]."</option>";
}
?>
</select> 
<select name="day" value="<?=$row['day']?>">
<?php
for ($x= 1; $x<=31; $x++) {
   echo "<option";
   if ($x == $day) {
      echo " selected";
   }
   echo ">$x</option>";
}
?>
</select>

<label id="con4">Consultant [1]:</label><br />
<select id="conSelect4" name="con_id1"><option value="<?=$row['con_id1']?>">Select<?=$consultant_option1?></select>
<input id= "check1" type ="submit" value="Check"></p>
</form>
</div>
</body>
</html>

Any help appreciated.

gffg4574fghsDSGDGKJYM · May 2, 2009

You want to populate a table...and you don't have any INSERT in your code ?

matt.sisto · May 2, 2009

Yes my HTML table from the SELECT SQL query.

Mikedean · May 2, 2009

You'll have to reset the pointer in your results after your first while loop.

mysql_data_seek( $result );

Mchl · May 2, 2009

You're storing results of two queries into one variable (virtually overwriting results of the first one, with results from the second one).

matt.sisto · May 2, 2009

So how do I remedy that? is it better to divide the scripts into 2 seperate pages?

Mchl · May 2, 2009

Just use two different variables

$result1 and $result2 for example.

matt.sisto · May 2, 2009

That is sorted now, thank you. Here is the working script

<?php
  session_start();
  
if (!isset($_SESSION['username']))
    {
   header("Location: login.php");
   exit();
    }
   
  require "dbconn2.php";
  
  $month = $_POST["month"];
  $day = $_POST["day"];
  $year= $_POST["year"];
  $con_id1= $_POST["con_id1"];

  $event_start = $year."-".$month."-".$day." ".$_POST["event_start"];
  
  $sql = "SELECT * FROM calendar_events WHERE event_start='".$event_start."' AND (con_1='".$con_id1."' OR con_2='".$con_id1."' OR con_3='".$con_id1."')";
  $result = mysql_query ($sql, $connection)
    or die ("Couldn't perform query $sql <br />".mysql_error());


  $sql="SELECT con_id, first_name, last_name FROM consultant";
  $result2=mysql_query($sql);
  $rconsultant_option="";

while ($row=mysql_fetch_array($result2)) {

    $con_id=$row["con_id"];
    $first_name=$row["first_name"];
   $last_name=$row["last_name"];
    $consultant_option1.="<OPTION VALUE=\"$con_id\">".$first_name." ".$last_name;
   }

  $yeartoday = date(Y);
  $yearplus1 = date(Y)+1;
  
  for($y=$yeartoday;$y<=$yearplus1;$y++){  
       $selected = "";
       $longDate = date("Y", mktime(0,0,0,1,1,$y));
       if ($Year==$y){ 
       $selected = "selected \n";
        }
        $year_option.="<option value=\"$y\"$selected>$longDate</option>\n";
        }

?>

<!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=UTF-8" />
<title>List All</title>
<script language="JavaScript">
function win2() {
    window.open("addeventform.php","Window2","menubar=no,width=460,height=360,toolbar=no");
}

</script>
</head>

<body>


<legend>Check Availability
<div id="mid">
  <table border="1" cellpadding="3">
    <tr bgcolor="#CCCCCC">
      <th align="left">ID </th>
      <th align="left">Start Date </th>
      <th align="left">End Date </th>
      <th align="left">Start Time</th>
      <th align="left">Consultant 1</th>         
      <th align="left">Consultant 2</th>
      <th align="left">Consultant 3</th>
      <th align="left">Country</th>
    </tr>
   
    <?php
    while ($row = mysql_fetch_array($result))
   {?>
    <tr>
      <td><?=$row['event_id']?></td>
      <td><?=$row['event_start']?></td>
      <td><?=$row['event_end']?></td>
      <td><?=$row['start_time']?></td>
      <td><?=$row['con_1']?></td>
      <td><?=$row['con_2']?></td>
      <td><?=$row['con_3']?></td>
      <td><?=$row['country']?></td>
    </tr>

    <?php } ?>
  </table>
</div>

<div id="left">
<p>
<input type="button" onClick="javascript:self.close()" value="close">
<input type="submit" onClick="javascript:win2()" value="Add an Event"></p>
</div>
</legend>
<form name="bookingForm"  action="check.php" method="post">
<label id="eventStart">Event Date:</label><br />
<select id="year3" name="year"><option value="<?=$row['year']?>">Year<?=$year_option?></option></select>
<?php
$arrMonth = array('01','02','03','04','05','06','07','08','09','10','11','12');
?>
<select name="month" value="<?=$row['month']?>"/>
<?php
$months = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec");
for ($x=1; $x <= count($months); $x++) {
   echo"<option value=\"$x\"";
   if ($x == $month) {
      echo " selected";
   }
   echo ">".$months[$x-1]."</option>";
}
?>
</select>
<select name="day" value="<?=$row['day']?>">
<?php
for ($x= 1; $x<=31; $x++) {
   echo "<option";
   if ($x == $day) {
      echo " selected";
   }
   echo ">$x</option>";
}
?>
</select>

<label id="con4">Consultant [1]:</label><br />
<select id="conSelect4" name="con_id1"><option value="<?=$row['con_id1']?>">Select<?=$consultant_option1?></select>
<input id= "check1" type ="submit" value="Check"></p>
</form>
</div>
</body>
</html>

Thanks again.

Sign In

[SOLVED] Why can't I get my SQL query to populate my table?

Recommended Posts

matt.sisto

Link to comment

Share on other sites

gffg4574fghsDSGDGKJYM

Link to comment

Share on other sites

matt.sisto

Link to comment

Share on other sites

Mikedean

Link to comment

Share on other sites

Mchl

Link to comment

Share on other sites

matt.sisto

Link to comment

Share on other sites

Mchl

Link to comment

Share on other sites

matt.sisto

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information