After reload, query forgotten desperate

Supergirl · August 27, 2006

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.

[code]
<?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');

?>
[/code]

[code]
<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> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr valign="top">
<td width="7%"><div align="right"></div></td>
<td width="12%"> </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> </td>
<td> </td>
<td> </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> </td>
<td> </td>
<td> </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> </td>
<td> </td>
<td> </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> </td>
<td> </td>
<td> </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> </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> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
</table>
</fieldset>
</form>

[/code]

jvalarta · August 27, 2006

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.

Supergirl · August 27, 2006

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!

jvalarta · August 27, 2006

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

[code]
<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>
[/code]

Supergirl · August 27, 2006

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

[code]

<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>

[/code]

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

jvalarta · August 27, 2006

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.

Supergirl · August 27, 2006

[quote author=jvalarta link=topic=105831.msg422863#msg422863 date=1156699856]
You have to have an if/else statement so that it chooses one of the options as the SELECTED choice. [/quote]

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]";[b]if(isset($POST['row2["asset_type"]']))
{print "selected = \"selected\"";}echo ">$row2[asset_type]</option>";[/b]

[quote author=jvalarta link=topic=105831.msg422863#msg422863 date=1156699856]
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.[/quote]

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!

sasa · August 27, 2006

try[code]
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>";
[/code]

Supergirl · August 27, 2006

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!

sasa · August 27, 2006

OK i find the error
try[code]
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>";[/code]

Supergirl · August 27, 2006

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

Thanks a million!

Supergirl · August 28, 2006

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!

[code]
<?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');

?>
[/code]

the form

[code]
<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> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr valign="top">
<td width="7%"><div align="right"></div></td>
<td width="12%"> </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> </td>
<td> </td>
<td> </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> </td>
<td> </td>
<td> </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> </td>
<td> </td>
<td> </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> </td>
<td> </td>
<td> </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> </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> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
</table>
</fieldset>
</form>

[/code]

[attachment deleted by admin]

Sign In

After reload, query forgotten *desperate*

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information

After reload, query forgotten desperate