Jump to content


Photo

After reload, query forgotten *desperate*


  • Please log in to reply
11 replies to this topic

#1 Supergirl

Supergirl
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 27 August 2006 - 04:46 PM

Hello there,

I've been trying to fix the problem for days, so that I don't even know what I tried and what not.
I am using apache and mysql. My OS is Win XP on this machine.

I am trying to do the following:

Show the form to allow user to make a simple query and find the assets in the database, filtering by location, s/n or similar.
Then the results are displayed in a table form.
The headings should be hyperlinked to sort the columns.
If the results exceed more than 25 (for testing only 2) records, I should see hyperlinks to other pages.
If possible, append the form at the bottom, for a new query.

I said I tried a lot, so the version that I am appending already shows a select *, instead of only the form.
I believe that either, if I made the dropdown sticky, my query would be remembered, or it is a logic problem,
where I have to make some if statements, so that the query is not forgotten, but since I tried passing the queries,
in the hyperlinks and $_GET them, and making hidden fields query in the form, I don't know anymore what I tried and what not.

Problem: I get the data displayed, (the echos of queries are displayed for testing), but when I click on the links
at the bottom, or the sorting links, I either get the empty form (earlier versions), or I get the standard query (select * ...).

Please, please help me, I am going insane.  :o

Thanks a bunch!

S.

Here is the .php, after that I will attach the form.

<?php
/**
 * viewassets4.php
 *
 * @version $Id$
 * @copyright 2006
 */

 session_start();
 $page_title = "View Current Assets";
 include('./includes/header.inc.php');
 require_once('../mysqlconnect.inc.php');

 if(!isset($_SESSION['user_id']))
 {
	// start defining URL
	$url = 'http://'.$_SERVER['HTTP_HOST'].dirname($_SERVER['PHP_SELF']);

	// check for a trailing slash
	if((substr($url, -1) == '/')
	OR (substr($url, -1) == '\\'))
	{
		$url = substr($url, 0, -1);
	}
	$url .= '/index.php';
	header("Location: $url");
	exit();
 }

 // this script will allow the results to be sorted in different ways
 // and the results will be divided over more than one page if the
 // query returns more than 25 records

 // number of records to show per page
 $display = 2;


 // determine where in the database to start returning results
 if(isset($_GET['s']))
 {
 	$start = $_GET['s'];
 }
 else
 {
 	$start = 0;
 }

 // default column links
 $link1 = "{$_SERVER['PHP_SELF']}?sort=ata";
 $link2 = "{$_SERVER['PHP_SELF']}?sort=mfa";
 $link3 = "{$_SERVER['PHP_SELF']}?sort=mda";
 $link4 = "{$_SERVER['PHP_SELF']}?sort=loa";
 $link5 = "{$_SERVER['PHP_SELF']}?sort=sta";
 $link6 = "{$_SERVER['PHP_SELF']}?sort=aia";

 // determine the sorting order
 if(isset($_GET['sort']))
 {
	 switch($_GET['sort'])
	 {
	 	case 'ata':
	 		$order_by = 'asset_type ASC';
	 		$link1 = "{$_SERVER['PHP_SELF']}?sort=atd";
			break;
		case 'atd':
			$order_by = 'asset_type DESC';
	 		$link1 = "{$_SERVER['PHP_SELF']}?sort=ata";
			break;
		case 'mfa':
	 		$order_by = 'mfg ASC';
	 		$link2 = "{$_SERVER['PHP_SELF']}?sort=mfd";
			break;
		case 'mfd':
			$order_by = 'mfg DESC';
	 		$link2 = "{$_SERVER['PHP_SELF']}?sort=mfa";
			break;
		case 'mda':
	 		$order_by = 'mfg_date ASC';
	 		$link3 = "{$_SERVER['PHP_SELF']}?sort=mdd";
			break;
		case 'mdd':
			$order_by = 'mfg_date DESC';
	 		$link3 = "{$_SERVER['PHP_SELF']}?sort=mda";
			break;
		case 'loa':
	 		$order_by = 'location ASC';
	 		$link4 = "{$_SERVER['PHP_SELF']}?sort=lod";
			break;
		case 'lod':
			$order_by = 'location DESC';
	 		$link4 = "{$_SERVER['PHP_SELF']}?sort=loa";
			break;
		case 'sta':
	 		$order_by = 'status ASC';
	 		$link5 = "{$_SERVER['PHP_SELF']}?sort=std";
			break;
		case 'std':
			$order_by = 'status DESC';
	 		$link5 = "{$_SERVER['PHP_SELF']}?sort=sta";
			break;
		case 'aia':
	 		$order_by = 'asset_id ASC';
	 		$link6 = "{$_SERVER['PHP_SELF']}?sort=aid";
			break;
		case 'aid':
			$order_by = 'asset_id DESC';
	 		$link6 = "{$_SERVER['PHP_SELF']}?sort=aia";
			break;
		default:
			$order_by = 'asset_id ASC';
			break;
	 }

         // $sort will be appended to the pagination links
         $sort = $_GET['sort'];

 }
 else
 {
 	// use the default sorting order
 	$order_by = 'asset_id ASC';
 	$sort = 'aia';
 }


  // this is the problem  !isset($_POST['query1']) ||
if(($_POST['submitted']) && ($query1 == ""))
 {

 	// initialize error array
 	$errors = array();
 	$query1 = "SELECT * FROM asset ";

 	// check the fields
 	/* This was a bit tougher. I had to determine if the WHERE clause
 	   was used at all before using AND clause. I used a $flag boolean
 	   variable to set it to true if WHERE was used.
 	   I am first checking how many categories were selected,
 	   or if they were all selected. Then, I am checking the checkboxes
 	   with their correspondenting textboxes for the search criteria
 	   and concatenate it to the query. */

 	if(isset($_POST['asset_type']))
	{
		$asset_type = ($_POST['asset_type']);
		if($asset_type[0] != "All" )
		{
			// gotta fix this too, the first element is
			// repeated.
			$query .= "WHERE asset_type = '$asset_type[0]'";
			$flag = TRUE;
			foreach($asset_type as $v)
			{
			    $query .= " OR asset_type = '$v'";
			}
		}
		else
		{
			$flag = FALSE;
		}
	}

	if(isset($_POST['sn']))
	{
		if(!empty($_POST['serial_num']))
		{
			$serial_num = escape_data($_POST['serial_num']);
			if($flag == TRUE)
			{
				$query .= " AND serial_num = '$serial_num'";
			}
			else
			{
				$query .= " WHERE serial_num = '$serial_num'";
				$flag = TRUE;
			}
		}
		else
		{
			$errors[] = "Add serial number";
		}
	}

	if(isset($_POST['ma']))
	{
		if(!empty($_POST['mac_address']))
		{
			$mac_address = escape_data($_POST['mac_address']);
			if($flag == TRUE)
			{
				$query .= " AND mac_address = '$mac_address'";

			}
			else
			{
				$query .= " WHERE mac_address = '$mac_address'";
				$flag = TRUE;
			}
		}
		else
		{
			$errors[] = "Add mac address";
		}
	}

	if(isset($_POST['cp']))
	{
		if(!empty($_POST['cpu']))
		{
			$cpu = escape_data($_POST['cpu']);
			if($flag == TRUE)
			{
				$query .= " AND cpu = '$cpu'";

			}
			else
			{
				$query .= " WHERE cpu = '$cpu'";
				$flag = TRUE;
			}
		}
		else
		{
			$errors[] = "Add cpu criteria";
		}
	}

	if(isset($_POST['ra']))
	{
		if(!empty($_POST['ram']))
		{
			$ram = escape_data($_POST['ram']);
			if($flag == TRUE)
			{
				$query .= " AND ram = '$ram'";

			}
			else
			{
				$query .= " WHERE ram = '$ram'";
				$flag = TRUE;
			}
		}
		else
		{
			$errors[] = "Add ram criteria";
		}
	}

	if(isset($_POST['hd']))
	{
		if(!empty($_POST['hard_drive']))
		{
			$hard_drive = escape_data($_POST['hard_drive']);
			if($flag == TRUE)
			{
				$query .= " AND hard_drive = '$hard_drive'";

			}
			else
			{
				$query .= " WHERE hard_drive = '$hard_drive'";
				$flag = TRUE;
			}
		}
		else
		{
			$errors[] = "Add hard drive criteria";
		}
	}

	if(isset($_POST['mf']))
	{
		if(!empty($_POST['mfg']))
		{
			$mfg = escape_data($_POST['mfg']);
			if($flag == TRUE)
			{
				$query .= " AND mfg = '$mfg'";

			}
			else
			{
				$query .= " WHERE mfg = '$mfg'";
				$flag = TRUE;
			}
		}
		else
		{
			$errors[] = "Add manufacturer criteria";
		}
	}

	if(isset($_POST['md']))
	{
		if(!empty($_POST['mfg_date']))
		{
			$mfg_date = escape_data($_POST['mfg_date']);
			if($flag == TRUE)
			{
				$query .= " AND mfg_date = '$mfg_date'";

			}
			else
			{
				$query .= " WHERE mfg_date = '$mfg_date'";
				$flag = TRUE;
			}
		}
		else
		{
			$errors[] = "Add the date criteria";
		}
	}

	if(isset($_POST['wd']))
	{
		if(!empty($_POST['warranty_duration']))
		{
			$warranty_duration = escape_data($_POST['warranty_duration']);
			if($flag == TRUE)
			{
				$query .= " AND warranty_duration = '$warranty_duration'";

			}
			else
			{
				$query .= " WHERE warranty_duration = '$warranty_duration'";
				$flag = TRUE;
			}
		}
		else
		{
			$errors[] = "Add the warranty duration criteria";
		}
	}

	if(isset($_POST['lo']))
	{
		if(!empty($_POST['location']))
		{
			$location = escape_data($_POST['location']);
			if($flag == TRUE)
			{
				$query .= " AND location = '$location'";

			}
			else
			{
				$query .= " WHERE location = '$location'";
				$flag = TRUE;
			}
		}
		else
		{
			$errors[] = "Add the location criteria";
		}
	}

	if(isset($_POST['st']))
	{
		if(!empty($_POST['status']))
		{
			$status = escape_data($_POST['status']);
			if($flag == TRUE)
			{
				$query .= " AND status = '$status'";

			}
			else
			{
				$query .= " WHERE status = '$status'";
				$flag = TRUE;
			}
		}
		else
		{
			$errors[] = "Add the status criteria";
		}
	}

  if(empty($errors))
  {
  
      echo "alles klar<br>";

     
      
      // if here everything is fine
      // add the sorting order to the query
      $query .= " ORDER BY $order_by LIMIT $start, $display";
      $query1 .= $query;
      $query2 = "SELECT COUNT(*) FROM asset " . $query;
      echo "$query1";
      echo "<br>";
      echo $query2;
  
      // make the query
      $result = @mysql_query($query1);
      echo $result;

        if($result)
      {
          // Create a table.
          echo '<br><table border=0 align=center cellspacing=0 cellpadding=4>
            	  <th><a href = "' . $link6 . '">ID</a></th>
                <th><a href = "' . $link1 . '">Asset Type</a></th>
                <th>S/N</th>
                <th>Mac Address</th>
                <th>Cpu</th>
                <th>Ram</th></font>
                <th>Hard Drive</th>
                <th><a href = "' . $link2 . '">Mfg</a></th>
                <th><a href = "' . $link3 . '">Mfg. Date</a></th>
                <th>Warranty</th>
                <th><a href = "' . $link4 . '">Location</a></th>
                <th><a href = "' . $link5 . '">Status</a></th>
                <th>Notes</th>
                <tr></tr>';
  
  
            // Fetch the results from the database.
            $bg = '#BDD7F0'; // set the bg color
  
            while ($row = mysql_fetch_array($result))
            {
                // switch the bg color
                $bg = ($bg == '#BDD7F0' ? '#ffffff' : '#BDD7F0');
                $id = $row['asset_id'];
                echo '<tr bgcolor = "' . $bg .'">';
                echo "<td><a href='edit.php?stage=edit&id=$id'>$id</a></td></td>
                <td>$row[asset_type]</td>
                <td>$row[serial_num]</td>
                <td>$row[mac_address]</td>
                <td>$row[cpu]</td>
                <td>$row[ram]</td>
                <td>$row[hard_drive]</td>
                <td>$row[mfg]</td>
                <td>$row[mfg_date]</td>
                <td>$row[warranty_duration]</td>
                <td>$row[location]</td>
                <td>$row[status]</td>
                <td>$row[notes]</td>
                </tr>";
            }
            
            // determine how many pages are there
            if(isset($_GET['np']))
            {
                $num_pages = $_GET['np'];
  	          echo "<br>$num_pages";
            }
            else
            {
                $result2 = @mysql_query($query2);
                $row2 = mysql_fetch_array($result2, MYSQL_NUM);
                $num_records = $row2[0];
                echo "<br><br>";
                echo $num_records;
  
                // calculate the number of pages
                if($num_records > $display)
                {
                    $num_pages = ceil($num_records/$display);
                }
                else
                {
                    $num_pages = 1;
                }
            }// end of number pages IF
  
  		  
            echo '</TABLE><br><br>';
                    if($num_pages > 1)
                    {
                        echo '<br /><p>';
                        // determine what page the script is on
                        $current_page = ($start/$display) + 1;
                        // if not the first, make a previous link
                        if($current_page != 1)
                        {
                            echo '<a href = "viewassets4.php?s=' . ($start - $display) .
                            '&np=' . $num_pages . '&sort=' . $sort . '">Previous</a>';
                        }
          
                        // make all the numbered pages
                        for($i = 1; $i <= $num_pages; $i++)
                        {
                            if($i != $current_page)
                            {
                                echo '<a href = "viewassets4.php?s=' .
                                (($display * ($i - 1))) . '&np=' . $num_pages .
                                '&sort=' . $sort . '">' . $i . '</a> ';
                            }
                            else
                            {
                                echo $i . ' ';
                            }
                        }
          
                        // if it is not the last page, make a next link
                        if($current_page != $num_pages)
                        {
                            echo '<a href = "viewassets4.php?s=' . ($start + $display) .
                            '&np=' . $num_pages . '&sort=' . $sort . '">Next</a>';
                        }
                        echo '</p>';
                        echo $query;
          
  		  }// end of links section
  }  //end of if result

}
else if($errors != "")
{
    echo "not klar";
    $query1 = "";
 	// report the errors
	echo "<h1>Error!</h1><br />
	The following error(s) occured:<br />";
	foreach($errors as $msg)
	{
        echo " - $msg<br />\n";
	}
	echo "Please try again.<p></p>";
} // end of if empty errors
}


include('./includes/formselect.inc');
          

?>

<form method="post" action="<?php $_SERVER['PHP_SELF'] ?>">
<fieldset><legend>Select View</legend>
  <table width="90%"  border="0" align="center">
    <tr valign="top">
      <td><div align="right">Select:</div></td>
      <td><?php 
	  		$query2 = "SELECT * FROM asset_type";
			$result2 = @mysql_query($query2);
			//$row = mysql_fetch_array($result);
		
        	echo "<select name=asset_type[] size = 5 MULTIPLE>";
			echo "<option value = \"\">Choose one or more</option>";
			echo "<option value = \"All\">All</option>";
			while($row2 = mysql_fetch_array($result2))
			{		
			//doesn't work yet  
				echo "<option value=$row2[asset_type]";if(isset($POST['row2["asset_type"]']))
{print "selected = \"selected\"";}echo ">$row2[asset_type]</option>";
			}	
			echo "</select>";	
			 ?></td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr valign="top">
      <td width="7%"><div align="right"></div></td>
      <td width="12%">&nbsp;</td>
      <td width="7%"><div align="right">Where:</div></td>
      <td width="16%"><input name="sn" type="checkbox" id="sn" <?php if (isset($_POST['sn'])) {print ' checked="checked"';} ?>>
        S/N</td>
      <td width="19%"><input name="serial_num" type="text" value = "<?php if(!empty($_POST['serial_num'])) echo $_POST['serial_num']; ?>"></td>
      <td width="19%"><input name="mf" type="checkbox" id="mf" <?php if (isset($_POST['mf'])) {print ' checked="checked"';} ?>> 
        Manufacturer </td>
      <td width="20%"><input name="mfg" type="text" id="mfg" value = "<?php if(!empty($_POST['mfg'])) echo $_POST['mfg']; ?>"></td>
    </tr>
    <tr valign="top">
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td><input name="ma" type="checkbox" id="ma" value="mac_addres<?php if (isset($_POST['ma'])) {print ' checked="checked"';} ?>s">
        Mac Address </td>
      <td><input name="mac_address" type="text" id="mac_address" value = "<?php if(!empty($_POST['mac_address'])) echo $_POST['mac_address']; ?>"></td>
      <td><input name="md" type="checkbox" id="md" <?php if (isset($_POST['md'])) {print ' checked="checked"';} ?>> 
        Manufacturing Date </td>
      <td><input name="mfg_date" type="text" value = "<?php if(!empty($_POST['mfg_date'])) echo $_POST['mfg_date']; ?>"></td>
    </tr>
    <tr valign="top">
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td> <input name="cp" type="checkbox" id="cp" <?php if (isset($_POST['cp'])) {print ' checked="checked"';} ?>>
      Cpu </td>
      <td><input name="cpu" type="text" value = "<?php if(!empty($_POST['cpu'])) echo $_POST['cpu']; ?>"></td>
      <td><input name="wd" type="checkbox" id="wd" <?php if (isset($_POST['wd'])) {print ' checked="checked"';} ?>> 
        Warranty Duration </td>
      <td><input name="warranty_duration" type="text" value = "<?php if(!empty($_POST['warranty_duration'])) echo $_POST['warranty_duration']; ?>"></td>
    </tr>
    <tr valign="top">
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td><input name="ra" type="checkbox" id="ra" <?php if (isset($_POST['ra'])) {print ' checked="checked"';} ?>> 
        Ram </td>
      <td><input name="ram" type="text" value = "<?php if(!empty($_POST['ram'])) echo $_POST['ram']; ?>"></td>
      <td><input name="lo" type="checkbox" id="lo" <?php if (isset($_POST['lo'])) {print ' checked="checked"';} ?>> 
        Location </td>
      <td><input name="location" type="text" value = "<?php if(!empty($_POST['location'])) echo $_POST['location']; ?>"></td>
    </tr>
    <tr valign="top">
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td><input name="hd" type="checkbox" id="hd" <?php if (isset($_POST['hd'])) {print ' checked="checked"';} ?>> 
      Hard Drive </td>
      <td><input name="hard_drive" type="text" value = "<?php if(!empty($_POST['hard_drive'])) echo $_POST['hard_drive']; ?>"></td>
      <td> <input name="st" type="checkbox" id="st" <?php if (isset($_POST['st'])) {print ' checked="checked"';} ?>> 
        Status</td>
      <td><input name="status" type="text" value = "<?php if(!empty($_POST['status'])) echo $_POST['status']; ?>"></td>
    </tr>
    <tr valign="top">
      <td>&nbsp;</td>
      <td><input type="submit" name="Submit" value="Submit">
      <input type="reset" name="reset" value="Reset">
	  <input type="hidden" name="submitted" value="TRUE">
	  <input type="hidden" name="id" value="' . $id . '">
	  </td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
  </table>
  </fieldset>
</form>



#2 jvalarta

jvalarta
  • Members
  • PipPipPip
  • Advanced Member
  • 42 posts

Posted 27 August 2006 - 04:53 PM

You have to make the page "sticky", meaning, the page has to know what the last query was...by way of remembering the variables. You could do this by simply appending the variables in a GET string to each link (how I often do it) or use hidden HTML fields within a form to store the query variables (then you need a big of javascript for onclicks to execute the form action).

Bottom line, you have to rebuild the query in in it's entirety each time the user clicks.

#3 Supergirl

Supergirl
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 27 August 2006 - 05:02 PM

Thanks for your answer!

As you can see, all the fields except the drop down are sticky.
I tried to make the dropdown sticky, but I can't figure it out.

The dropdown has to be dynamic, because it is possible to add asset_types to the asset_type table,
so it grabs all the types in there.

Making it sticky was my first idea, cause I thought the same, it will build the query every time, but if you or someone can help
me make the dropdown sticky, that would be great!

Thanks a lot!

#4 jvalarta

jvalarta
  • Members
  • PipPipPip
  • Advanced Member
  • 42 posts

Posted 27 August 2006 - 05:05 PM

Use an Array to build your HTML SELECT menu...something like this should do it:

<SELECT NAME="PullDownID">
<OPTION VALUE="">Choose...

<?

$PullDownArray = array (
"1" => "One",
"2" => "Two",
"3" => "Three")

while (list($key, $var) = each($PullDownArray)) {
	if ($PullDownID == $key) {
		echo "<OPTION VALUE=\"$key\" SELECTED>$var\n";
	}
	else {
		echo "<OPTION VALUE=\"$key\">$var\n";
	}
}
?>
</SELECT>


#5 Supergirl

Supergirl
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 27 August 2006 - 05:10 PM

I can't hard code it, it has to stay dynamic and read possible values from another table.


<td><div align="right">Select:</div></td>
      <td><?php 
	  		$query2 = "SELECT * FROM asset_type";
			$result2 = @mysql_query($query2);
		
        	echo "<select name=asset_type[] size = 5 MULTIPLE>";
			echo "<option value = \"\">Choose one or more</option>";
			echo "<option value = \"All\">All</option>";
			while($row2 = mysql_fetch_array($result2))
			{		
			//doesn't work yet  
				echo "<option value=$row2[asset_type]";if(isset($POST['row2["asset_type"]']))
{print "selected = \"selected\"";}echo ">$row2[asset_type]</option>";
			}	
			echo "</select>";	
			 ?></td>


the way I have it now, doesn't work ;(

#6 jvalarta

jvalarta
  • Members
  • PipPipPip
  • Advanced Member
  • 42 posts

Posted 27 August 2006 - 05:30 PM

You have to have an if/else statement so that it chooses one of the options as the SELECTED choice. You have a multi-select form field...do you need the query to build based on more than one choice? If so, you're going to need more logic when you build the query string itself.

#7 Supergirl

Supergirl
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 27 August 2006 - 05:39 PM

You have to have an if/else statement so that it chooses one of the options as the SELECTED choice.


That is what I tried, and it does not work. I am asking if someone can take a look at it, because I am running out of ideas, and I came for help  ;)

echo "<option value=$row2[asset_type]";if(isset($POST['row2["asset_type"]']))
{print "selected = \"selected\"";}echo ">$row2[asset_type]</option>";


You have a multi-select form field...do you need the query to build based on more than one choice? If so, you're going to need more logic when you build the query string itself.


I certainly need multi-select dropdown. I wouldn't have made it multiple, if not ;)

The query itself workd, except that it repeats the first clause, which does not affect the result. This is cosmetics that I will fix, once everything else works. So, I don't see a need for even more query logic. If I am mistaken on this, please be specific and explain what you mean, because I don't understand it.

Thanks!





#8 sasa

sasa
  • Staff Alumni
  • Advanced Member
  • 2,804 posts
  • LocationHrvatska

Posted 27 August 2006 - 07:54 PM

try
echo "<option value=\"$row2[asset_type]";
if(isset($POST['asset_type']) AND in_array($POST['asset_type'], $row2[asset_type])) print "selected = \"selected\"";
echo "\">$row2[asset_type]</option>";


#9 Supergirl

Supergirl
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 27 August 2006 - 07:59 PM

Unfortunatelly it does not make it sticky.  :(

thanks a lot! I didn't know about the in_array, I can play around with it and keep trying.

Thanks again!

#10 sasa

sasa
  • Staff Alumni
  • Advanced Member
  • 2,804 posts
  • LocationHrvatska

Posted 27 August 2006 - 09:03 PM

OK i find the error
try
echo "<option value=\"$row2[asset_type]\"";
if(isset($_POST['asset_type']) AND in_array($row2[asset_type], $_POST['asset_type'])) print " selected = \"selected\"";
echo ">$row2[asset_type]</option>";


#11 Supergirl

Supergirl
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 27 August 2006 - 09:59 PM

OMG Thank you sooooo much!!! It works, I can't believe it!!!

Thanks a million!

#12 Supergirl

Supergirl
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 28 August 2006 - 12:19 AM

I still can't get the page to work. I thought that the sticky drop down would solve the problem, but my queries are lost when I klick on the link to the next page or to sort the column.

When the query seems to be lost after click to the next page, if I again click submit, I see the result.
I tried solving this with passing submitted=true in the url, but no help.

I have a feeling that it is something rather simple and I didn't think of it.
I will attach the latest version, if someone wants to look through it.
I will also attach the sql for the 2 tables, if you want to create them locally.

Thanks!

<?php
/**
 * viewassets4.php
 *
 * @version $Id$
 * @copyright 2006
 */

 session_start();
 $page_title = "View Current Assets";
 include('./includes/header.inc.php');
 require_once('../mysqlconnect.inc.php');

 if(!isset($_SESSION['user_id']))
 {
	// start defining URL
	$url = 'http://'.$_SERVER['HTTP_HOST'].dirname($_SERVER['PHP_SELF']);

	// check for a trailing slash
	if((substr($url, -1) == '/')
	|| (substr($url, -1) == '\\'))
	{
		$url = substr($url, 0, -1);
	}
	$url .= '/index.php';
	header("Location: $url");
	exit();
 }

 // this script will allow the results to be sorted in different ways
 // and the results will be divided over more than one page if the
 // query returns more than 25 records

 // number of records to show per page
 $display = 2;


 // determine where in the database to start returning results
 if(isset($_GET['s']))
 {
 	$start = $_GET['s'];
 }
 else
 {
 	$start = 0;
 }

 // default column links
 $link1 = "{$_SERVER['PHP_SELF']}?sort=ata";
 $link2 = "{$_SERVER['PHP_SELF']}?sort=mfa";
 $link3 = "{$_SERVER['PHP_SELF']}?sort=mda";
 $link4 = "{$_SERVER['PHP_SELF']}?sort=loa";
 $link5 = "{$_SERVER['PHP_SELF']}?sort=sta";
 $link6 = "{$_SERVER['PHP_SELF']}?sort=aia";

 // determine the sorting order
 if(isset($_GET['sort']))
 {
	 switch($_GET['sort'])
	 {
	 	case 'ata':
	 		$order_by = 'asset_type ASC';
	 		$link1 = "{$_SERVER['PHP_SELF']}?sort=atd";
			break;
		case 'atd':
			$order_by = 'asset_type DESC';
	 		$link1 = "{$_SERVER['PHP_SELF']}?sort=ata";
			break;
		case 'mfa':
	 		$order_by = 'mfg ASC';
	 		$link2 = "{$_SERVER['PHP_SELF']}?sort=mfd";
			break;
		case 'mfd':
			$order_by = 'mfg DESC';
	 		$link2 = "{$_SERVER['PHP_SELF']}?sort=mfa";
			break;
		case 'mda':
	 		$order_by = 'mfg_date ASC';
	 		$link3 = "{$_SERVER['PHP_SELF']}?sort=mdd";
			break;
		case 'mdd':
			$order_by = 'mfg_date DESC';
	 		$link3 = "{$_SERVER['PHP_SELF']}?sort=mda";
			break;
		case 'loa':
	 		$order_by = 'location ASC';
	 		$link4 = "{$_SERVER['PHP_SELF']}?sort=lod";
			break;
		case 'lod':
			$order_by = 'location DESC';
	 		$link4 = "{$_SERVER['PHP_SELF']}?sort=loa";
			break;
		case 'sta':
	 		$order_by = 'status ASC';
	 		$link5 = "{$_SERVER['PHP_SELF']}?sort=std";
			break;
		case 'std':
			$order_by = 'status DESC';
	 		$link5 = "{$_SERVER['PHP_SELF']}?sort=sta";
			break;
		case 'aia':
	 		$order_by = 'asset_id ASC';
	 		$link6 = "{$_SERVER['PHP_SELF']}?sort=aid";
			break;
		case 'aid':
			$order_by = 'asset_id DESC';
	 		$link6 = "{$_SERVER['PHP_SELF']}?sort=aia";
			break;
		default:
			$order_by = 'asset_id ASC';
			break;
	 }

         // $sort will be appended to the pagination links
         $sort = $_GET['sort'];

 }
 else
 {
 	// use the default sorting order
 	$order_by = 'asset_id ASC';
 	$sort = 'aia';
 }


  // this is the problem  !isset($_POST['query1']) ||
if(isset($_POST['submitted']) || $_GET['submitted'])
 {


 	// initialize error array
 	$errors = array();

          echo $query1;
          echo "<br>$q";
         	$query1 = "SELECT * FROM asset ";
        
         	// check the fields
         	/* This was a bit tougher. I had to determine if the WHERE clause
         	   was used at all before using AND clause. I used a $flag boolean
         	   variable to set it to true if WHERE was used.
         	   I am first checking how many categories were selected,
         	   or if they were all selected. Then, I am checking the checkboxes
         	   with their correspondenting textboxes for the search criteria
         	   and concatenate it to the query. */
        
         	if(isset($_POST['asset_type']))
        	{
        		$asset_type = ($_POST['asset_type']);
        		if($asset_type[0] != "All" )
        		{
        			// gotta fix this too, the first element is
        			// repeated.
        			$query .= "WHERE asset_type = '$asset_type[0]'";
        			$flag = TRUE;
        			foreach($asset_type as $v)
        			{
        			    $query .= " OR asset_type = '$v'";
        			}
        		}
        		else
        		{
        			$flag = FALSE;
        		}
        	}
        
        	if(isset($_POST['sn']))
        	{
        		if(!empty($_POST['serial_num']))
        		{
        			$serial_num = escape_data($_POST['serial_num']);
        			if($flag == TRUE)
        			{
        				$query .= " AND serial_num = '$serial_num'";
        			}
        			else
        			{
        				$query .= " WHERE serial_num = '$serial_num'";
        				$flag = TRUE;
        			}
        		}
        		else
        		{
        			$errors[] = "Add serial number";
        		}
        	}
        
        	if(isset($_POST['ma']))
        	{
        		if(!empty($_POST['mac_address']))
        		{
        			$mac_address = escape_data($_POST['mac_address']);
        			if($flag == TRUE)
        			{
        				$query .= " AND mac_address = '$mac_address'";
        
        			}
        			else
        			{
        				$query .= " WHERE mac_address = '$mac_address'";
        				$flag = TRUE;
        			}
        		}
        		else
        		{
        			$errors[] = "Add mac address";
        		}
        	}
        
        	if(isset($_POST['cp']))
        	{
        		if(!empty($_POST['cpu']))
        		{
        			$cpu = escape_data($_POST['cpu']);
        			if($flag == TRUE)
        			{
        				$query .= " AND cpu = '$cpu'";
        
        			}
        			else
        			{
        				$query .= " WHERE cpu = '$cpu'";
        				$flag = TRUE;
        			}
        		}
        		else
        		{
        			$errors[] = "Add cpu criteria";
        		}
        	}
        
        	if(isset($_POST['ra']))
        	{
        		if(!empty($_POST['ram']))
        		{
        			$ram = escape_data($_POST['ram']);
        			if($flag == TRUE)
        			{
        				$query .= " AND ram = '$ram'";
        
        			}
        			else
        			{
        				$query .= " WHERE ram = '$ram'";
        				$flag = TRUE;
        			}
        		}
        		else
        		{
        			$errors[] = "Add ram criteria";
        		}
        	}
        
        	if(isset($_POST['hd']))
        	{
        		if(!empty($_POST['hard_drive']))
        		{
        			$hard_drive = escape_data($_POST['hard_drive']);
        			if($flag == TRUE)
        			{
        				$query .= " AND hard_drive = '$hard_drive'";
        
        			}
        			else
        			{
        				$query .= " WHERE hard_drive = '$hard_drive'";
        				$flag = TRUE;
        			}
        		}
        		else
        		{
        			$errors[] = "Add hard drive criteria";
        		}
        	}
        
        	if(isset($_POST['mf']))
        	{
        		if(!empty($_POST['mfg']))
        		{
        			$mfg = escape_data($_POST['mfg']);
        			if($flag == TRUE)
        			{
        				$query .= " AND mfg = '$mfg'";
        
        			}
        			else
        			{
        				$query .= " WHERE mfg = '$mfg'";
        				$flag = TRUE;
        			}
        		}
        		else
        		{
        			$errors[] = "Add manufacturer criteria";
        		}
        	}
        
        	if(isset($_POST['md']))
        	{
        		if(!empty($_POST['mfg_date']))
        		{
        			$mfg_date = escape_data($_POST['mfg_date']);
        			if($flag == TRUE)
        			{
        				$query .= " AND mfg_date = '$mfg_date'";
        
        			}
        			else
        			{
        				$query .= " WHERE mfg_date = '$mfg_date'";
        				$flag = TRUE;
        			}
        		}
        		else
        		{
        			$errors[] = "Add the date criteria";
        		}
        	}
        
        	if(isset($_POST['wd']))
        	{
        		if(!empty($_POST['warranty_duration']))
        		{
        			$warranty_duration = escape_data($_POST['warranty_duration']);
        			if($flag == TRUE)
        			{
        				$query .= " AND warranty_duration = '$warranty_duration'";
        
        			}
        			else
        			{
        				$query .= " WHERE warranty_duration = '$warranty_duration'";
        				$flag = TRUE;
        			}
        		}
        		else
        		{
        			$errors[] = "Add the warranty duration criteria";
        		}
        	}
        
        	if(isset($_POST['lo']))
        	{
        		if(!empty($_POST['location']))
        		{
        			$location = escape_data($_POST['location']);
        			if($flag == TRUE)
        			{
        				$query .= " AND location = '$location'";
        
        			}
        			else
        			{
        				$query .= " WHERE location = '$location'";
        				$flag = TRUE;
        			}
        		}
        		else
        		{
        			$errors[] = "Add the location criteria";
        		}
        	}
        
        	if(isset($_POST['st']))
        	{
        		if(!empty($_POST['status']))
        		{
        			$status = escape_data($_POST['status']);
        			if($flag == TRUE)
        			{
        				$query .= " AND status = '$status'";
        
        			}
        			else
        			{
        				$query .= " WHERE status = '$status'";
        				$flag = TRUE;
        			}
        		}
        		else
        		{
        			$errors[] = "Add the status criteria";
        		}
                         
                            
  }
  if(empty($errors))
  {

      echo "alles klar<br>";
      // add the sorting order to the query
      $query .= " ORDER BY $order_by LIMIT $start, $display";
      $query1 .= $query;
      $query2 = "SELECT COUNT(*) FROM asset " . $query;

      
      
      // if here everything is fine
      
      echo "$query1";
      echo "<br>";
      echo $query2;

      // make the query
      $result = @mysql_query($query1);
      echo $result;

        if($result)
      {
          // Create a table.
          echo '<br><table border=0 align=center cellspacing=0 cellpadding=4>
            	  <th><a href = "' . $link6 . '">ID</a></th>
                <th><a href = "' . $link1 . '">Asset Type</a></th>
                <th>S/N</th>
                <th>Mac Address</th>
                <th>Cpu</th>
                <th>Ram</th></font>
                <th>Hard Drive</th>
                <th><a href = "' . $link2 . '">Mfg</a></th>
                <th><a href = "' . $link3 . '">Mfg. Date</a></th>
                <th>Warranty</th>
                <th><a href = "' . $link4 . '">Location</a></th>
                <th><a href = "' . $link5 . '">Status</a></th>
                <th>Notes</th>
                <tr></tr>';
  
  
            // Fetch the results from the database.
            $bg = '#BDD7F0'; // set the bg color
  
            while ($row = mysql_fetch_array($result))
            {
                // switch the bg color
                $bg = ($bg == '#BDD7F0' ? '#ffffff' : '#BDD7F0');
                $id = $row['asset_id'];
                echo '<tr bgcolor = "' . $bg .'">';
                echo "<td><a href='edit.php?stage=edit&id=$id'>$id</a></td></td>
                <td>$row[asset_type]</td>
                <td>$row[serial_num]</td>
                <td>$row[mac_address]</td>
                <td>$row[cpu]</td>
                <td>$row[ram]</td>
                <td>$row[hard_drive]</td>
                <td>$row[mfg]</td>
                <td>$row[mfg_date]</td>
                <td>$row[warranty_duration]</td>
                <td>$row[location]</td>
                <td>$row[status]</td>
                <td>$row[notes]</td>
                </tr>";
            }
            
            // determine how many pages are there
            if(isset($_GET['np']))
            {
                $num_pages = $_GET['np'];
  	          echo "<br>$num_pages";
            }
            else
            {
                $result2 = @mysql_query($query2);
                $row2 = mysql_fetch_array($result2, MYSQL_NUM);
                $num_records = $row2[0];
                echo "<br><br>";
                echo $num_records;
  
                // calculate the number of pages
                if($num_records > $display)
                {
                    $num_pages = ceil($num_records/$display);
                }
                else
                {
                    $num_pages = 1;
                }
            }// end of number pages IF
  
  		  
            echo '</TABLE><br><br>';
                    if($num_pages > 1)
                    {
                        echo '<br /><p>';
                        // determine what page the script is on
                        $current_page = ($start/$display) + 1;
                        // if not the first, make a previous link
                        if($current_page != 1)
                        {
                            echo '<a href = "'. $_SERVER['PHP_SELF']. '?submitted=true&s=' . ($start - $display) .
                            '&np=' . $num_pages . '&sort=' . $sort . '">Previous</a> ';
                        }
          
                        // make all the numbered pages
                        for($i = 1; $i <= $num_pages; $i++)
                        {
                            if($i != $current_page)
                            {
                                echo '<a href = "'. $_SERVER['PHP_SELF']. '?submitted=true&s=' .
                                (($display * ($i - 1))) . '&np=' . $num_pages .
                                '&sort=' . $sort . '">' . $i . '</a> ';
                            }
                            else
                            {
                                echo $i . ' ';
                            }
                        }
          
                        // if it is not the last page, make a next link
                        if($current_page != $num_pages)
                        {
                            echo '<a href = "'. $_SERVER['PHP_SELF']. '?submitted=true&s=' . ($start + $display) .
                            '&np=' . $num_pages . '&sort=' . $sort . '">Next</a>';
                        }
                        echo '</p>';

          
  		  }// end of links section
  }  //end of if result

}
else if($errors != "")
{
    echo "not klar";
    $query1 = "";
 	// report the errors
	echo "<h1>Error!</h1><br />
	The following error(s) occured:<br />";
	foreach($errors as $msg)
	{
        echo " - $msg<br />\n";
	}
	echo "Please try again.<p></p>";
} // end of if empty errors
}


include('./includes/formselect.inc');
          

?>

the form

<form method="post" action="<?php $_SERVER['PHP_SELF'] ?>">
<fieldset><legend>Select View</legend>
  <table width="90%"  border="0" align="center">
    <tr valign="top">
      <td><?php 
	  		$query2 = "SELECT * FROM asset_type";
			$result2 = @mysql_query($query2);
					
        	echo "<select name=asset_type[] size = 6 MULTIPLE>";
			echo "<option value = \"All\">All</option>";
			while($row2 = mysql_fetch_array($result2))
			{		
				//works now!
				echo "<option value=\"$row2[asset_type]\"";
				if(isset($_POST['asset_type']) AND in_array($row2[asset_type], $_POST['asset_type'])) print " selected = \"selected\"";
				echo ">$row2[asset_type]</option>";
			}
			echo "</select>";	
			 ?>
	  </td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr valign="top">
      <td width="7%"><div align="right"></div></td>
      <td width="12%">&nbsp;</td>
      <td width="7%"><div align="right">Where:</div></td>
      <td width="16%"><input name="sn" type="checkbox" id="sn" <?php if (isset($_POST['sn'])) {print ' checked="checked"';} ?>>
        S/N</td>
      <td width="19%"><input name="serial_num" type="text" value = "<?php if(!empty($_POST['serial_num'])) echo $_POST['serial_num']; ?>"></td>
      <td width="19%"><input name="mf" type="checkbox" id="mf" <?php if (isset($_POST['mf'])) {print ' checked="checked"';} ?>> 
        Manufacturer </td>
      <td width="20%"><input name="mfg" type="text" id="mfg" value = "<?php if(!empty($_POST['mfg'])) echo $_POST['mfg']; ?>"></td>
    </tr>
    <tr valign="top">
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td><input name="ma" type="checkbox" id="ma" value="mac_addres<?php if (isset($_POST['ma'])) {print ' checked="checked"';} ?>s">
        Mac Address </td>
      <td><input name="mac_address" type="text" id="mac_address" value = "<?php if(!empty($_POST['mac_address'])) echo $_POST['mac_address']; ?>"></td>
      <td><input name="md" type="checkbox" id="md" <?php if (isset($_POST['md'])) {print ' checked="checked"';} ?>> 
        Manufacturing Date </td>
      <td><input name="mfg_date" type="text" value = "<?php if(!empty($_POST['mfg_date'])) echo $_POST['mfg_date']; ?>"></td>
    </tr>
    <tr valign="top">
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td> <input name="cp" type="checkbox" id="cp" <?php if (isset($_POST['cp'])) {print ' checked="checked"';} ?>>
      Cpu </td>
      <td><input name="cpu" type="text" value = "<?php if(!empty($_POST['cpu'])) echo $_POST['cpu']; ?>"></td>
      <td><input name="wd" type="checkbox" id="wd" <?php if (isset($_POST['wd'])) {print ' checked="checked"';} ?>> 
        Warranty Duration </td>
      <td><input name="warranty_duration" type="text" value = "<?php if(!empty($_POST['warranty_duration'])) echo $_POST['warranty_duration']; ?>"></td>
    </tr>
    <tr valign="top">
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td><input name="ra" type="checkbox" id="ra" <?php if (isset($_POST['ra'])) {print ' checked="checked"';} ?>> 
        Ram </td>
      <td><input name="ram" type="text" value = "<?php if(!empty($_POST['ram'])) echo $_POST['ram']; ?>"></td>
      <td><input name="lo" type="checkbox" id="lo" <?php if (isset($_POST['lo'])) {print ' checked="checked"';} ?>> 
        Location </td>
      <td><input name="location" type="text" value = "<?php if(!empty($_POST['location'])) echo $_POST['location']; ?>"></td>
    </tr>
    <tr valign="top">
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td><input name="hd" type="checkbox" id="hd" <?php if (isset($_POST['hd'])) {print ' checked="checked"';} ?>> 
      Hard Drive </td>
      <td><input name="hard_drive" type="text" value = "<?php if(!empty($_POST['hard_drive'])) echo $_POST['hard_drive']; ?>"></td>
      <td> <input name="st" type="checkbox" id="st" <?php if (isset($_POST['st'])) {print ' checked="checked"';} ?>> 
        Status</td>
      <td><input name="status" type="text" value = "<?php if(!empty($_POST['status'])) echo $_POST['status']; ?>"></td>
    </tr>
    <tr valign="top">
      <td>&nbsp;</td>
      <td><input type="submit" name="Submit" value="Submit">
      <input type="reset" name="reset" value="Reset">
	  <input type="hidden" name="submitted" value="TRUE">
	  <input type="hidden" name="query1" value="">
	  <input type="hidden" name="id" value="' . $id . '">
	  </td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
  </table>
  </fieldset>
</form>


[attachment deleted by admin]




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users