query doesn't work

turpentyne · March 6, 2012

Another issue with trying to work on somebody else's script (and on no sleep!) I've got this mess of a page I'm trying to manipulate to add a feature to.

When somebody goes to this page, it pulls all records. At the top, I added a way to refine the results by dates. That query works fine, I tested it in mysql. To avoid fighting with the original code, I created an if statement to create $query_selectAll. But when I put it in the page, it would just refresh with the same data. I echoed the query to make sure it is correct. Then I realized that there was another query, $query_selectAllItems, further down the page that was generating the results, and none of what I was doing made a difference. But I'm not sure why it's ignoring my query or how to work around this. I tried deleting the query $query_selectAllItems, thinking it would take mine, because it ends with the same: $result_all

$result_all = mysql_query($query_selectall);

or

$result_all = mysql_query($query_selectAllItems);

<?php
require_once('calendar/classes/tc_calendar.php');
include("include/auth.php"); 
include("include/conn.php"); 
@$a_row = mysql_fetch_array($result);
// I added this if statement in to get my POST results, without having to make changes to the GET results he made.	
if (isset($_POST['view'])){

$view=$_POST['view'];
} else {
$view=$_GET['view'];
}


if ($view=="All") {
$query_selectall = 'select * from tbl_registration';
echo $query_selectall;
}
elseif ($view=="dated") {
$mydate = isset($_REQUEST["date5"]) ? $_REQUEST["date5"] : "";
$end_date = isset($_REQUEST["date6"]) ? $_REQUEST["date6"] : "";
$query_selectall = "SELECT * FROM tbl_registration WHERE reg_timestamped BETWEEN STR_TO_DATE('$mydate', '%Y-%m-%d')  AND STR_TO_DATE('$end_date' , '%Y-%m-%d')";
echo $query_selectall;
}

else {
$query_selectall = "select * from tbl_registration where category='$view'";

echo $query_selectall;
}

$result_all = mysql_query($query_selectall);
@$numRows_all = mysql_num_rows($result_all);

?>
<title>WYF Admin</title>

<html>
<head>
<script language="javascript" src="calendar/calendar.js"></script>
<link href="include/styles.css" rel="stylesheet" type="text/css">

<SCRIPT language="JavaScript">
<!--
function confirm_delete(idval)
{
var verify= confirm("Do you really want to delete this Registration?");
if (verify== true)
{
window.location="delete_registrations.php?action=delete&did=" + idval;
}
}
//-->
</SCRIPT> 
<script type="text/javascript" src="js/jquery-latest.js"></script> 
<script type="text/javascript" src="js/jquery.tablesorter.js"></script> 
	<script type="text/javascript">
	$(function() {
		$("table").tablesorter({debug: true});
	});
	</script>
</head>
<body leftmargin="0" rightmargin="0" bottommargin="0" topmargin="0" cellspacing="0" cellpadding="0" bgcolor="ffffff">
<table width="100%"><tr><td align="left"><img src="images/logo.jpg"></td></tr>
<tr><td><hr></td></tr>
</table>

<table><tr><td valign="top">

<table cellspacing="1" cellpadding="0" bgcolor="#cccccc" border="0" width="300">
<tr><td>
<table cellspacing="0" cellpadding="0" width="300" height="400" bgcolor="#ffffff" border="0">
<tr><td align="left" valign="top">

<table width="250"><tr><td valign="top">
<b>Welcome  </b><br><br>
<?php
//$query_selectAllItems = "SELECT *, DATE_FORMAT(workshop_date, '%e-%m-%Y') as 'my_date' FROM tbl_workshops"; 
$query_selectAllItems = "SELECT * FROM tbl_registration"; 
$result_all = mysql_query($query_selectAllItems);
$numRows_all = mysql_num_rows($result_all);
?>

<? include "include/nav.php"; ?>

</td></tr></table></center>
</td></tr>


<tr><td></td></tr></table></tr></td></table>
</td><td valign="top">
<div style="float:left;padding-right:40px;"><a href="getcsv.php">export to csv</a></div><div>
<div style="float:right;margin-right:100px;"><form name="form1" method="post" action="viewregistrations2.php"><div style="float:left;overflow:visible;padding-right:10px;">View between</div>
<div style="float:left;overflow:visible;padding-right:10px;">
        <?php
  $myCalendar = new tc_calendar("date5", true, false);
  $myCalendar->setIcon("calendar/images/iconCalendar.gif");
  $myCalendar->setDate(date('d'), date('m'), date('Y'));
  $myCalendar->setPath("calendar/");
  $myCalendar->setYearInterval(2000, 2015);
  $myCalendar->dateAllow('2008-05-13', '2015-03-01');
  $myCalendar->setDateFormat('j F Y');
  $myCalendar->setAlignment('left', 'bottom');
  $myCalendar->setSpecificDate(array("2011-04-01", "2011-04-04", "2011-12-25"), 0, 'year');
  $myCalendar->setSpecificDate(array("2011-04-10", "2011-04-14"), 0, 'month');
  $myCalendar->setSpecificDate(array("2011-06-01"), 0, '');
  $myCalendar->writeScript();
  ?>
       </div>
<div style="float:left;overflow:visible;padding-right:10px;"> and </div>

<div style="float:left;overflow:visible;padding-right:10px;">
        <?php
  $myCalendar = new tc_calendar("date6", true, false);
  $myCalendar->setIcon("calendar/images/iconCalendar.gif");
  $myCalendar->setDate(date('d'), date('m'), date('Y'));
  $myCalendar->setPath("calendar/");
  $myCalendar->setYearInterval(2000, 2015);
  $myCalendar->dateAllow('2008-05-13', '2015-03-01');
  $myCalendar->setDateFormat('j F Y');
  $myCalendar->setAlignment('left', 'bottom');
  $myCalendar->setSpecificDate(array("2011-04-01", "2011-04-04", "2011-12-25"), 0, 'year');
  $myCalendar->setSpecificDate(array("2011-04-10", "2011-04-14"), 0, 'month');
  $myCalendar->setSpecificDate(array("2011-06-01"), 0, '');
  $myCalendar->writeScript();
  ?>
      </div>
<div style="float:left;overflow:visible;padding-right:10px;"><input type="hidden" name="view" id="view" value="dated"><input type="submit" value="go!">
</div></form>


</div><br><br><br><br>
<table width="95%" border="1" cellpadding="3" cellspacing="1" bordercolor="#999999" id="rowspan" class="tablesorter" name="table"><thead>
  <tr bgcolor="#ffffff"> 
    <th width="125">Name</th>
	<th width="125">Address</th>
        <th width="125">Phone</th>
	<th width="125">Email</th>
	<th width="125">Workshop</th>	
<td width="50"> </td>
    <td width="50"> </td><td> </td>
    </tr></thead>
    <tbody>
  <? //output each row 
  while ($c_row = mysql_fetch_array($result_all)){
    ?>
        <tr>
    <td><?= $c_row['reg_fname'] ?> <?= $c_row['reg_lname'] ?></td>
<td><?= $c_row['reg_address'] ?></td>
    <td><?= $c_row['reg_phone'] ?></td>
<td><?= $c_row['reg_email'] ?></td>
<td>
<?php
$catid = $c_row['reg_workshopid'];
$query="SELECT * FROM tbl_workshops where workshop_id = '$catid'";
//Run query;
$result=@mysql_query($query);

$row=mysql_fetch_array($result);
?>
<?php echo($row['workshop_title']); ?>

</td>
<td width="50" bgcolor="#DEE1EB"><div align="center"><a href="edit_registration.php?id=<? print $c_row['reg_id'] ?>">edit</a></div></td>
    <td width="50" bgcolor="#DEE1EB"><div align="center"><a href="javascript://" onClick="confirm_delete(<? print $c_row['reg_id'] ?>); return false">delete</a></div></td>
    
    <td width="50" bgcolor="#DEE1EB"><div align="center"><a href="printregistration.php?regid=<? print $c_row['reg_id'] ?>">view all / print</a></div></td>
  </tr>
  <?
  } //end while
  ?>
   </tbody>

</table><br><br>

</td></tr></table>

marcus · March 6, 2012

The second query on the page is overwriting your query. Just comment out this patch of code:

<?php
//$query_selectAllItems = "SELECT *, DATE_FORMAT(workshop_date, '%e-%m-%Y') as 'my_date' FROM tbl_workshops"; 
$query_selectAllItems = "SELECT * FROM tbl_registration"; 
$result_all = mysql_query($query_selectAllItems);
$numRows_all = mysql_num_rows($result_all);
?>

You would obviously be getting the same data because:

while ($c_row = mysql_fetch_array($result_all)){

is going to be corresponding to the latest data stores in the $result_all variable.

turpentyne · March 6, 2012

unfortunately that's the first thing I tried, and I got the error:

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /home/workshop/public_html/admin/viewregistrations2.php on line 145

which stumped me because it looks like its the same syntax being used isn't it?

marcus · March 6, 2012

That means there's an error in your query.

Change:

$result_all = mysql_query($query_selectall);

To:

$result_all = mysql_query($query_selectall) or die(mysql_error());

Sign In

query doesn't work

Recommended Posts

turpentyne

Link to comment

Share on other sites

marcus

Link to comment

Share on other sites

turpentyne

Link to comment

Share on other sites

marcus

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information