PDA

View Full Version : Create a php filter for dynamic table


smitho
02-04-2008, 04:53 AM
Using davidj's cars example I've created a dynamic table to the car_model table. What I'd like to be able to do is create a filter that when you type in 147 only that result appears. I can get it to either work with just the text field input or showing the full list.

This example shows the list and will filter if you type some text in the text field and hit submit. The problem is getting the list back. If I clear the text field and hit submit the list will not appear. Is there a way to reset the list?


<?php
//connect to the database
$db_connection = mysql_pconnect ('localhost','user','password');
$db_status = mysql_select_db ('dwc', $db_connection);
if (!$db_status){ echo "Data_base Connection Failed";
exit;}

if(!isset($_POST['filter'])){
$query = sprintf("SELECT * FROM car_model");
}else{
$filter=$_POST['filter'];
$query = sprintf("SELECT * FROM car_model WHERE car_model='$filter'");
}

//query the database to display the table.

$result = mysql_query($query);
$rowmodel = mysql_fetch_array($result);
$totalrowsmodel = mysql_num_rows($result);
?>


<!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>Untitled Document</title>
</head>

<body>
<form id="form1" name="form1" method="post" action="<?php $_SERVER['PHP_SELF'];?>">
<table id="table2" width="0" border="0">
<tr>
<td><strong><input type="text" name="filter" id="filter" /></strong></td>
</tr>
<tr>
<td><div align="center">CAR MODEL</div></td>
</tr>
<?php do { ?>
<tr>
<td><div align="center"><?php echo $rowmodel['car_model']; ?></div></td>
</tr>
<?php }while ($rowmodel = mysql_fetch_array($result)); ?>
</table>
<input type="submit" name="submit" id="submit" value="Submit" />
</form>
</body>
</html>

smitho
02-04-2008, 05:56 AM
I found that if I called the page again the list appeared again. So using a reset button shown below.

<input name="Button" type="button" value="Reset" onclick="parent.location='FilterTest.php'" />I can enter text like X5 and submit and the list will only show the X5 entry. I did find that it will not do partial works like typing in sport to find A3 Sportback I have to type in "A3 Sportback" then the list is filtered. Somehow I don't think I going the right way about this.

davidj
02-04-2008, 06:45 AM
in your SQL string use a LIKE instead of an = operator

then in your form field you can then use * wild cards

smitho
02-04-2008, 11:32 AM
Hi davidj,

tried
$query = sprintf("SELECT * FROM car_model WHERE car_model LIKE '$filter'");
but still not getting sport to filter the list.

davidj
02-04-2008, 11:34 AM
you need to use the wildcards like this

%sport%

davidj
02-04-2008, 11:38 AM
you can include the cards outside of the field by concat them inside the SQL or PHP then it becomes an invisible process

smitho
02-04-2008, 09:47 PM
After you mentioned LIKE I did a quick search on the web and found some examples where you use %. Now if I change the line to

$query = sprintf("SELECT * FROM car_model WHERE car_model LIKE '%$filter%'");
I get the following errors:


Warning: sprintf() [function.sprintf]: Too few arguments in ..../FilterTest.php on line 12

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in ..../FilterTest.php on line 19

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in ..../FilterTest.php on line 20

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in ..../FilterTest.php on line 47



<?php
//line 12
$query = sprintf("SELECT * FROM car_model WHERE car_model LIKE '%$filter%'");

//line 19
$rowmodel = mysql_fetch_array($result);

//line 20
$totalrowsmodel = mysql_num_rows($result);

//line 47 ?>
<?php }while ($rowmodel = mysql_fetch_array($result)); ?>

smitho
02-04-2008, 10:10 PM
Just did a quick check on terminal on the mac and using

SELECT * FROM car_model WHERE car_model LIKE '%sport%';

I got:

+--------------+--------------+
| car_model_id | car_model |
+--------------+--------------+
| 2 | A3 Sportback |
+--------------+--------------+
1 row in set (0.00 sec)


Must be something with my php.

smitho
02-04-2008, 11:15 PM
The answer seemed to be remove sprintf():

<?php
$query="SELECT * FROM car_model WHERE car_model LIKE '%$filter%'";
?>

Now that this is working would I be able to create filter for more columns?

For example lets say we had car_model, car_colour, car_extras etc.

smitho
02-05-2008, 01:05 AM
I've added a column to the car_model table called car_colour.

Using
<?php
if(!isset($_POST['filter'])||($_POST['filter2'])){
$query= sprintf("SELECT * FROM car_model");
}else{
/*$filter="GT";
$filter2="red";*/
$filter=$_POST['filter'];
$filter2=$_POST['filter2'];
$query="SELECT * FROM car_model WHERE car_model LIKE '%$filter%' OR car_colour LIKE '%$filter2%'";
?>I get nothing, yet if I change



$filter="GT";
$filter2="red";
/*$filter=$_POST['filter'];
$filter2=$_POST['filter2'];*/
Then hit the submit button the list is filtered and I get:

CAR MODEL CAR COLOUR

159 red

GT red

I can not see why the $filter=$_POST['filter']; is not working.

smitho
02-05-2008, 05:00 AM
Just had to set the second text field to !isset. But if I don't type some text in the both the search will not work. Would I be able to make it so that even if you do not type in one of the field you'll get a result on the one you did?


if(!isset($_POST['filter'])||!isset($_POST['filter2'])){
$query= sprintf("SELECT * FROM car_model");
}else{
//$filter="GT";
//$filter2="red";
$filter=$_POST['filter'];
$filter2=$_POST['filter2'];
$query="SELECT * FROM car_model WHERE car_model LIKE '%$filter%' OR car_colour LIKE '%$filter2%'";
}
Also once you type in something and hit submit the text is gone. If I try something like:


<input type="text" name="filter" id="filter" value="<?php echo $_POST['filter'];?>" />
I get <br/> displayed in the text field on the page should that be there? Can I get the text filed to display the last input until you hit reset?

davidj
02-05-2008, 06:55 AM
Would I be able to make it so that even if you do not type in one of the field you'll get a result on the one you did?

use a condition to test the $_POST var when you submit the query. IF the field is empty pass a wild card into the value within your PHP.

example...

$field1 = $_POST['field1';]

IF(! $field1){

$field1 = "%";

}

Can I get the text filed to display the last input until you hit reset?

you are submitting the form therefore setting vars. Echo the var in the value attribute of the field and then every time the form is submitted the value will be retained

smitho
02-05-2008, 11:37 PM
Thanks davidj,

This seems to work for some reason the wild card didn't stick. As soon as I changed "%" to " " the filter works. I did notice that if I only used one space " " then things like A4 Cabriolet would be filtered.

Should a wild card work? It would be better than having 4 or 5 spaces as the value.


if(!isset($_POST['filter'])||!isset($_POST['filter2'])){
$query= sprintf("SELECT * FROM car_model");
}else{
$filter = $_POST['filter'];
if(!$filter){
$filter = " ";
}
$filter2 = $_POST['filter2'];
if(!$filter2){
$filter2 = " ";
}
$query="SELECT * FROM car_model WHERE car_model LIKE '%$filter%' OR car_colour LIKE '%$filter2%'";
}
Could you make the filter dynamic? So as you type a letter the list would start to filter?

smitho
02-06-2008, 04:57 AM
After some more testing it seems the wildcards in this way


$filter = $_POST['filter'];
if(!$filter){
$filter = "%";
}
$filter2 = $_POST['filter2'];
if(!$filter2){
$filter2 = "%";
}
Will work with AND


$query="SELECT * FROM car_model WHERE car_model LIKE '%$filter%' AND car_colour LIKE '%$filter2%' AND message LIKE '%$filter3%'";
For some reason it does not like OR.

davidj
02-06-2008, 06:56 AM
Could you make the filter dynamic? So as you type a letter the list would start to filter?

you are looking into the realms of AJAX

check out my tut on AJAX dynamic dropdowns

smitho
02-13-2008, 04:22 AM
I've been working on some of the tutorials and thought that I'd submit the filter I've applied to the Beginners Update tut.

Where this comes in handy is if your list becomes very long or you want to find a specific entry you can type all or part of the name. Then update or delete that entry.

Note name the file dwc2.php.


<?php
require_once("Connections/dwc.php"); // database connection
///////////////////////////////////////////
@$data2 = $_POST['data2'];
@$data3 = $_POST['data3'];
@$data4 = $_POST['data4'];
@$submit = $_POST['submit'];
@$update_id = $_POST['update_id'];
@$del = $_GET['del'];
@$upd = $_GET['upd'];
///////////////////////////////////////////

///////////////////////////////////////////
$query = sprintf("SELECT * FROM table1 where field1_id='$upd'");
$result = @mysql_query($query);
$rowUpdate = @mysql_fetch_array($result);
///////////////////////////////////////////
if ($submit && $data2 && $data3 && $data4 && ! $update_id){
///////////////////////////////////////////
$query = sprintf("INSERT into table1 (field2, field3, field4) values ('$data2', '$data3', '$data4')");
mysql_query($query)or die(mysql_error());
///////////////////////////////////////////
}elseif($submit && $update_id){
///////////////////////////////////////////
$query = sprintf("UPDATE table1 set field2='$data2', field3='$data3', field4='$data4' where field1_id = '$update_id'");
$result = @mysql_query($query);
///////////////////////////////////////////
}
///////////////////////////////////////////
$query = sprintf("SELECT * FROM table1 where field1_id='$upd'");
$result = @mysql_query($query);
$rowUpdate = @mysql_fetch_array($result);
///////////////////////////////////////////
//delete record
if ($del){
///////////////////////////////////////////
$query = sprintf("DELETE FROM table1 where field1_id='$del'");
mysql_query($query)or die(mysql_error());
///////////////////////////////////////////
}
///////////////////////////////////////////
if(!isset($_POST['data2filter'])||!isset($_POST['data3filter'])){
$query = sprintf("SELECT * FROM table1");
}else{
$data2filter=$_POST['data2filter'];
if(!$data2filter){
$data2filter = "%";
}
$data3filter=$_POST['data3filter'];
if(!$data3filter){
$data3filter = "%";
}
$query = "SELECT * FROM table1 WHERE field2 LIKE '%$data2filter%' AND field3 LIKE '%$data3filter%'";
}
$result = @mysql_query($query);
$row = mysql_fetch_array($result);
///////////////////////////////////////////

//do {
//echo $row['field3']."<br>";
//}while ($row = mysql_fetch_array($result))
@$filterdata2=$_POST['data2filter'];
@$filterdata3=$_POST['data3filter'];
?>


<!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>Untitled Document</title>
</head>

<body>
<form id="form1" name="form1" method="post" action="<?php $_SERVER['PHP_SELF'];?>">
<table width="1" border="1">
<tr>
<td>data2</td>
<td>data3</td>
<td>data4</td>
</tr>
<tr>
<td><input type="text" name="data2" id="data2" value="<?php echo $rowUpdate['field2'];?>"/></td>
<td><input type="text" name="data3" id="data3" value="<?php echo $rowUpdate['field3'];?>"/></td>
<td><input type="text" name="data4" id="data4" value="<?php echo $rowUpdate['field4'];?>"/></td>
</tr>
<tr>
<td colspan="3"><div align="center">
<input type="button" name="submit3" value="Clear" onclick="document.location.href='dwc2.php'"/>
<input type="reset" name="submit2" value="Reset" />
<input type="submit" name="Submit" value="Submit" />
<input name="submit" type="hidden" id="submit" value="1" />
<input name="update_id" type="hidden" id="update_id" value="<?php echo $rowUpdate['field1_id'];?>" />
</div></td>
</tr>
</table>
<p>&nbsp;</p>
</form>
<p>&nbsp;</p>
<form id="filter" name="filter" method="post" action="<?php $_SERVER['PHP_SELF'];?>">
<table width="50%" border="1" cellpadding="2" cellspacing="2">
<tr>
<td><input type="submit" name="button2" id="button2" value="Filter" /></td>
<td>&nbsp;</td>
<td><input type="text" name="data2filter" id="data2filter" value="<?php echo $filterdata2;?>"/></td>
<td><input type="text" name="data3filter" id="data3filter" value="<?php echo $filterdata3;?>"/></td>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
<tr>
<td>&nbsp;</td>
<td>ID1</td>
<td>data2</td>
<td>data3</td>
<td>data4</td>
<td>&nbsp;</td>
</tr>
<?php do { ?>
<tr>
<td><input type="button" name="button" id="button" value="Update" onclick="document.location.href='dwc2.php?upd=<?php echo $row['field1_id'];?>'" /></td>
<td><?php echo $row['field1_id'];?></td>
<td><?php echo $row['field2'];?></td>
<td><?php echo $row['field3'];?></td>
<td><?php echo $row['field4'];?></td>
<td><input type="button" name="button" id="button" value="Delete" onclick="document.location.href='dwc2.php?del=<?php echo $row['field1_id'];?>'" /></td>
</tr>
<?php }while ($row = mysql_fetch_array($result)); ?>
</table>
</form>
<p>&nbsp;</p>
</body>
</html>

davidj
02-13-2008, 06:44 AM
nice one

why have you added a warning suppressor @ to your variables

smitho
02-13-2008, 09:34 AM
When I first setup my php and apache server I followed a tut that suggested enableing error messges for dev purposes. I think that's why I get errors and why I suppress them. Even your dropdown menu tut was giving me errors which I think was cause by the feature being turned on.

davidj
02-13-2008, 11:06 AM
in your PHP.ini file (accessed from the WAMP icon in the sys tray) look for the following block...

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
; Error handling and logging ;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

you can comment and uncomment the level of error reporting you want from here by removing or adding a semicolon at the beginning of the line

smitho
02-14-2008, 04:36 AM
Thanks davidj,

I might leave it on at the moment. If I turn it off will it still give me errors if I type in things wrong?

Also I've decided to try and limit the table and apply some navigation at the bottom. I know dreamweaver does this but there is so such code that I don't think you need.

To start the process I'm setting the number of rows to display at 5.

Using these 2 variables above the query.


$maxRows = 5;
$pageNum = 0;
Then in the query I've added:


$query = sprintf("SELECT * FROM table1 LIMIT $pageNum, $maxRows");
.....................

$query = "SELECT * FROM table1 WHERE field2 LIKE '%$data2filter%' AND field3 LIKE '%$data3filter%'LIMIT $pageNum, $maxRows";
Now I've discovered that if I change $pageNum = 0; to $pageNum = 5; the table displays the next 5 rows. So to make some navigation I would just need to have next +5 and previous -5 first would be set to 0 and last to the total number of rows. Correct?

smitho
02-14-2008, 09:45 AM
I've just noticed that you can not type 0 in the data2 field. If you wanted to say I recieved 0 apples on 2006-11-11 you can not seem to do so. I gather this is a mysql issue? I believe data2 was set up as int(10). Would it be possible to enter a value of 0?

davidj
02-14-2008, 09:54 AM
what happens if you use a 0

do you get an error

smitho
02-14-2008, 10:24 AM
Nothing happens the screen refreshes but no entry is made. If I type in a space for NA in the field the entry is made a NA or the space is saved as 0.

I changed


$data2=$_POST['data2'];
to


if(isset($data2)){
$data2=$_POST['data2'];
}else{
$data2=' ';
}
This seems to work if you leave the field blake, 0 is entered into the database.

After more testing like typing a number into field2 example 23423 I still get 0 so this doesn't work. Try again.

davidj
02-14-2008, 01:49 PM
you have to understand the difference between between null and zero

by adding a space in the field is not the same as adding zero. 0 should be allowed to be entered as its an integer. Have you checked your db and also have you echo'd your SQL to the page to see if its being passed correctly?


// 5000 posts and nothing! Was expecting at least one party popper and a balloon

// stats for 5000 posts.

// If i posted 1 post every minute it would take 83 hours to equal (or 3.5 days!)

smitho
02-14-2008, 10:01 PM
Thanks davidj,

5000 plus post when do you get a gold watch?

I had a look at the mysql table:


--
-- Table structure for table `table1`
--

CREATE TABLE IF NOT EXISTS `table1` (
`field1_id` int(11) NOT NULL auto_increment,
`field2` int(10) default NULL,
`field3` varchar(20) default NULL,
`field4` date default NULL,
PRIMARY KEY (`field1_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=31 ;
What I thought about this morning after a good night sleep what that in the tut you have "if ($submit && $data2 && $data3 && $data4 && ! $update_id){" Would it be better to try || rather than && ?

OK tried this and it seems to work:

Even if you leave the field blank 0 will be entered.


if($submit || $data2 || $data3 || $data4 && ! $update_id){


Is there anything wrong with using || instead of && ?

smitho
02-15-2008, 02:51 AM
Here's my contribution,

The table now has some navigation at the bottom to take the user through the pages. I've put some comments in the code so I think it will make sense. It's straight forward I think and if some could check it as well that would be great. At the moment the number of rows display is coded into the page but if you wanted to it would be easy enough to use a field that the user could key in a number and display the number set by the user.

Any suggestion on improving the page navigation are welcome.


<?php
require_once("Connections/dwc.php"); // database connection
///////////////////////////////////////////
@$data2 = $_POST['data2'];
@$data3 = $_POST['data3'];
@$data4 = $_POST['data4'];
@$submit = $_POST['submit'];
@$update_id = $_POST['update_id'];
@$del = $_GET['del'];
@$upd = $_GET['upd'];
///////////////////////////////////////////

$currentPage=$_SERVER['PHP_SELF']; //set the current Page this way so that you can use this with any page

$maxRows = 5; //to change the number of rows displayed set 5 to any number you wish
$noRows = 0; //this is our starting row number
if (isset($_GET['noRows'])) { //if if a page link is hit get increase the $noRow by either + or - $maxRows
$noRows = $_GET['noRows'];
}

$firstPage = 0; //first page will always be 0
$nextPage = $noRows + $maxRows; //next page will be + $maxRows
$prevPage = $noRows - $maxRows; //previous page will always be - $maxRows



///////////////////////////////////////////
$query = sprintf("SELECT * FROM table1 where field1_id='$upd'");
$result = @mysql_query($query);
$rowUpdate = @mysql_fetch_array($result);
///////////////////////////////////////////
if ($submit || $data2 || $data3 || $data4 && ! $update_id){
///////////////////////////////////////////
$query = sprintf("INSERT into table1 (field2, field3, field4) values ('$data2', '$data3', '$data4')");
mysql_query($query)or die(mysql_error());
///////////////////////////////////////////
}elseif($submit && $update_id){
///////////////////////////////////////////
$query = sprintf("UPDATE table1 set field2='$data2', field3='$data3', field4='$data4' where field1_id = '$update_id'");
$result = @mysql_query($query);
///////////////////////////////////////////
}
///////////////////////////////////////////
$query = sprintf("SELECT * FROM table1 where field1_id='$upd'");
$result = @mysql_query($query);
$rowUpdate = @mysql_fetch_array($result);
///////////////////////////////////////////
//delete record
if ($del){
///////////////////////////////////////////
$query = sprintf("DELETE FROM table1 where field1_id='$del'");
mysql_query($query)or die(mysql_error());
///////////////////////////////////////////
}
///////////////////////////////////////////
if(!isset($_POST['data2filter'])||!isset($_POST['data3filter'])){
$query = sprintf("SELECT * FROM table1 LIMIT $noRows, $maxRows"); //set the query to display the LIMIT we set.
}else{
$data2filter=$_POST['data2filter'];
if(!$data2filter){
$data2filter = "%";
}
$data3filter=$_POST['data3filter'];
if(!$data3filter){
$data3filter = "%";
}
$query = "SELECT * FROM table1 WHERE field2 LIKE '%$data2filter%' AND field3 LIKE '%$data3filter%'"; //we can also limit the filter list by the LIMIT we set.
}
$allrows = sprintf("SELECT * FROM table1");
$result = @mysql_query($query);
$row = mysql_fetch_array($result);
///////////////////////////////////////////

$all = mysql_query($allrows); //because we have a limit set the query we need to set another query to determine the number of rows in our database.
$totalRows = mysql_num_rows($all);
$totalPages = ceil($totalRows/$maxRows)-1; //now that me now the number we can determine the number of pages -1 to get the last page.
$lastPage = $totalPages * $maxRows; //to get the last page as a row figure we multiple totalpages by the maxrows.


@$filterdata2=$_POST['data2filter'];
@$filterdata3=$_POST['data3filter'];
?>


<!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>Untitled Document</title>
</head>

<body>
<form id="form1" name="form1" method="post" action="<?php $_SERVER['PHP_SELF'];?>">
<table width="1" border="1">
<tr>
<td>data2</td>
<td>data3</td>
<td>data4</td>
</tr>
<tr>
<td><input type="text" name="data2" id="data2" value="<?php echo $rowUpdate['field2'];?>"/></td>
<td><input type="text" name="data3" id="data3" value="<?php echo $rowUpdate['field3'];?>"/></td>
<td><input type="text" name="data4" id="data4" value="<?php echo $rowUpdate['field4'];?>"/></td>
</tr>
<tr>
<td colspan="3"><div align="center">
<input type="button" name="submit3" value="Clear" onclick="document.location.href='dwc2.php'"/>
<input type="reset" name="submit2" value="Reset" />
<input type="submit" name="Submit" value="Submit" />
<input name="submit" type="hidden" id="submit" value="1" />
<input name="update_id" type="hidden" id="update_id" value="<?php echo $rowUpdate['field1_id'];?>" />
</div></td>
</tr>
</table>
<p>&nbsp;</p>
</form>
<p>&nbsp;</p>
<form id="filter" name="filter" method="post" action="<?php $_SERVER['PHP_SELF'];?>">
<table width="50%" border="1" cellpadding="2" cellspacing="2">
<tr>
<td><input type="submit" name="button2" id="button2" value="Filter" /></td>
<td>&nbsp;</td>
<td><input type="text" name="data2filter" id="data2filter" value="<?php echo $filterdata2;?>"/></td>
<td><input type="text" name="data3filter" id="data3filter" value="<?php echo $filterdata3;?>"/></td>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
<tr>
<td>&nbsp;</td>
<td>ID1</td>
<td>data2</td>
<td>data3</td>
<td>data4</td>
<td>&nbsp;</td>
</tr>
<?php do { ?>
<tr>
<td><input type="button" name="button" id="button" value="Update" onclick="document.location.href='dwc2.php?upd=<?php echo $row['field1_id'];?>'" /></td>
<td><?php echo $row['field1_id'];?></td>
<td><?php echo $row['field2'];?></td>
<td><?php echo $row['field3'];?></td>
<td><?php echo $row['field4'];?></td>
<td><input type="button" name="button" id="button" value="Delete" onclick="document.location.href='dwc2.php?del=<?php echo $row['field1_id'];?>'" /></td>
</tr>
<?php }while ($row = mysql_fetch_array($result)); ?>
</table>
</form>
<p>
<?php
//here we create the links to the pages and use the row counts to display the pages.
if ($noRows > 0) { // Show if not first page ?>
<a href="<?php echo sprintf("%s?noRows=%s", $currentPage, $firstPage);?>">First</a>
<?php } // Show if not first page ?>

<?php if ($noRows > 0) { // Show if not first page ?>
<a href="<?php echo sprintf("%s?noRows=%s", $currentPage, $prevPage);?>">Previous</a>
<?php } // Show if not first page ?>

<?php if ($nextPage < $totalRows) { // Show if not last page ?>
<a href="<?php echo sprintf("%s?noRows=%s", $currentPage, $nextPage);?>">Next</a>
<?php } // Show if not last page ?>

<?php if ($nextPage < $totalRows) { // Show if not last page ?>
<a href="<?php echo sprintf("%s?noRows=%s", $currentPage, $lastPage);?>">Last</a>
<?php } // Show if not last page ?>
</p>
</body>
</html>

davidj
02-15-2008, 08:23 AM
without testing it it looks ok

remember the difference between && and ||

&& = AND
||= OR

so what you have here...

($submit || $data2 || $data3 || $data4 && ! $update_id){

// do_stuff()

}

pseudo code...

run do_stuff() if $submit is set OR $data2 is set OR $data3 is set OR $data4 is set AND $update_id is empty.

so do_stuff() will run if $submit is set or .....

is this correct

smitho
02-15-2008, 10:07 AM
Thanks davidj,

you are correct and the OR works well becuase in the instance the user does not need to enter any data the INSERT will work because it ignores the field and enters the default (NULL) and 0 is inserts.

So in the instance where the user just wants to enter, 0 apples where recieved on 2006-11-11 becuase the statement is data2 OR data3 OR data4 it will work.

On the other hand if I did not want the user to enter blank fields && would be a better option. For example a log in screen should have username && password this would make sure the user enters data in both fields.

That's how I understand it anyway.

davidj
02-15-2008, 10:14 AM
great mate

have you looked at OOP

i think your advanced enough to benefit from it

smitho
02-21-2008, 03:07 AM
Found a problem in the || setup.

Having

if ($submit || $data2 || $data3 || $data4 && ! $update_id){
///////////////////////////////////////////
$query = sprintf("INSERT into table1 (field2, field3, field4) values ('$data2', '$data3', '$data4')");
mysql_query($query)or die(mysql_error());
///////////////////////////////////////////
}elseif($submit && $update_id){
///////////////////////////////////////////
$query = sprintf("UPDATE table1 set field2='$data2', field3='$data3', field4='$data4' where field1_id = '$update_id'");
$result = @mysql_query($query);
///////////////////////////////////////////
}Will allow the user to insert data leaving fields blank where they may want to enter a 0 value or information.

The problem I found was that I could not update data that had been put in. It will just insert a new record with the changes made and keep the other data from the other fields.


by changing


///////////////////////////////////////////
}elseif($submit && $update_id){
///////////////////////////////////////////

//change to:

///////////////////////////////////////////
}
if($submit && $update_id){
///////////////////////////////////////////



Will allow the update to occur but you also create a new entry.

I'll try and work this out but if anyone has any ideas or suggestions they would be appreciated. Need a way to input data to some or all field and than also update them when required.

Cheers.

smitho
02-21-2008, 05:40 AM
The only way I could find to get around my problem was to do this:

if ($submit && ! $update_id){
///////////////////////////////////////////
$query = sprintf("INSERT into table1 (field2, field3, field4) values ('$data2', '$data3', '$data4')");
mysql_query($query)or die(mysql_error());
///////////////////////////////////////////
}elseif($submit && $update_id){
///////////////////////////////////////////
$query = sprintf("UPDATE table1 set field2='$data2', field3='$data3', field4='$data4' where field1_id = '$update_id'");
$result = @mysql_query($query);
///////////////////////////////////////////
}


Thi way if you want to leave a field blank you can and you can still update as well.

davidj
02-21-2008, 06:57 AM
yes mate

its probably the way i would have done it

you don't need to assign the variable ...

$result = @mysql_query($query);

in your update statement

just call the mysql_query()


mysql_query($query);

smitho
02-28-2008, 11:59 PM
Having a slight problem trying to make a checkbox filter.

The code below displays the car make table with some additions. I've linked this onto the login tutorial so to make it work login and change the welcome.php page from the tutorial to the code below. Using name dj password dreamweaver as shown in the tutorial you'll be taken to the welcome page. The make table will display with a number of check boxes one for service one for testdrive and one for cleaned. By clicking on the the name of the car the second table will populate with only that row of information. Now as dj you can tick any of the checkboxes and it will also capture that dj ticked the box by displaying the user id in the column next to the check box. I've wanted to all the filter process as I had done in the previous example but this time not just on text but if a checkbox is ticked or not. The problem I have is that I can not filter the check boxes that are not ticked by selecting No from the dropmenu.

No if I change the database to check boxes being set to N by default (N == not checked) then when user dj ticks on of the check boxes all checkboxes have his details applied when it should only be the ones ticked by dj. I'm thinking I need to change the if ($rowmake2['service']!=$_POST['servicech']){ portion of the code.

Here is the code for the welcome.php page.


<?php
require_once("Connections/dwc.php");//database connection
session_start();
include("includes/security.php");



$id = $_SESSION['id'];
@$submit=$_POST['submit'];
@$recordid=$_GET['recordid'];

if(isset($_POST['servicech'])){
$service=$_POST['servicech'];
}else{
$service='N';
}
if(isset($_POST['testdrivech'])){
$testdrive=$_POST['testdrivech'];
}else{
$testdrive='N';
}
if(isset($_POST['cleanedch'])){
$cleaned=$_POST['cleanedch'];
}else{
$cleaned='N';
}


//////////////////////////////////////////////////////
$query = sprintf("SELECT * FROM users WHERE user_id='$id'");
$result = @mysql_query($query);
$rowAccount = @mysql_fetch_array($result);
//////////////////////////////////////////////////////

if(!isset($_POST['modelfilter'])){
$query2 = sprintf("SELECT * FROM car_make");
}else{
$filtermodel=$_POST['modelfilter'];
if(!$filtermodel){
$filtermodel = "%";
}
$filterservice=$_POST['servicefilter'];
if(!$filterservice){
$filterservice = "%";
}
$filtertest=$_POST['testfilter'];
if(!$filtertest){
$filtertest = "%";
}
$filterclean=$_POST['cleanfilter'];
if(!$filterclean){
$filterclean = "%";
}
$query2 ="SELECT * FROM car_make WHERE make LIKE '%$filtermodel%' AND service LIKE '%$filterservice%' AND testdrive LIKE '%$filtertest%' AND cleaned LIKE '%$filterclean%'";
}
$result2 = mysql_query($query2);
$rowmake = mysql_fetch_array($result2);


$query3 = sprintf("SELECT * FROM car_make WHERE make_id=$recordid");
$result3 = mysql_query($query3);
$rowmake2 = @mysql_fetch_array($result3);

if (isset($_POST['Submit'])) {

if ($rowmake2['service']!=$_POST['servicech']){
$query = sprintf("UPDATE car_make SET service='$service', serviceby='$id' WHERE make_id='$recordid'");
mysql_query($query)or die(mysql_error);
header("Location: welcome.php?recordid=$recordid");
}
if ($rowmake2['testdrive']!=$_POST['testdrivech']){
$query = sprintf("UPDATE car_make SET testdrive='$testdrive', testdriveby='$id' WHERE make_id='$recordid'");
mysql_query($query)or die(mysql_error);
header("Location: welcome.php?recordid=$recordid");
}
if ($rowmake2['cleaned']!=$_POST['cleanedch']){
$query = sprintf("UPDATE car_make SET cleaned='$cleaned', cleanedby='$id' WHERE make_id='$recordid'");
mysql_query($query)or die(mysql_error);
header("Location: welcome.php?recordid=$recordid");
}
}

@$filtermodel=$_POST['modelfilter'];
@$filterservice=$_POST['servicefilter'];
@$filtertest=$_POST['testfilter'];
@$filterclean=$_POST['cleanfilter'];


?>
<!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>Untitled Document</title>
</head>

<body>
<p>Welcome to the application <?php echo $rowAccount['user_name'];?></p>
<form id="filter" name="filter" method="post" action="<?php $_SERVER['PHP_SELF'];?>">
<table id="table2" border="0">
<tr>
<td><div align="center">
<input type="text" name="modelfilter" id="modelfilter" />
</div></td>
<td><div align="center">
<select name="servicefilter" id="servicefilter">
<option> </option>
<option value="Y">YES</option>
<option value="N">NO</option>
</select>
</div></td>
<td><div align="center">
<select name="testfilter" id="testfilter">
<option> </option>
<option value="Y">YES</option>
<option value="N">NO</option>
</select>
</div></td>
<td><div align="center">
<select name="cleanfilter" id="cleanfilter">
<option> </option>
<option value="Y">YES</option>
<option value="N">NO</option>
</select>
</div></td>
<td><input type="reset" name="Reset" id="Reset" value="Reset" onclick="document.location.href='welcome.php'"/>
<input type="submit" name="Reset2" id="Reset2" value="Submit" onclick="document.location.href='welcome.php'"/></td>
</tr>
<tr>
<td><div align="center">CAR MODEL</div></td>
<td><div align="center">SERVICE</div></td>
<td><div align="center">TESTDRIVE</div></td>
<td><div align="center">CLEANED</div></td>
<td>&nbsp;</td>
</tr>
<?php do { ?>
<tr>
<td><div align="center"><a href="welcome.php?recordid=<?php echo $rowmake['make_id']; ?>"><?php echo $rowmake['make']; ?></a></div></td>
<td><div align="center">
<input type="checkbox" name="service" id="service" value=""<?php if (!(strcmp($rowmake['service'],'Y'))) {echo "checked";} ?> />
</div></td>
<td><div align="center">
<input type="checkbox" name="testdrive" id="testdrive" value=""<?php if (!(strcmp($rowmake['testdrive'],'Y'))) {echo "checked";} ?>/>
</div></td>
<td><div align="center">
<input type="checkbox" name="cleaned" id="cleaned" value=""<?php if (!(strcmp($rowmake['cleaned'],'Y'))) {echo "checked";} ?>/>
</div></td>
<td>&nbsp;</td>
</tr>
<?php }while ($rowmake = mysql_fetch_array($result2)); ?>
</table>
</form>
<p>&nbsp;</p>
<form id="form1" name="form1" method="post" action="<?php $_SERVER['PHP_SELF'];?>">
<table width="0" border="1">
<tr>
<td>id</td>
<td><?php echo $rowmake2['make_id']; ?></td>
<td>User Name</td>
</tr>
<tr>
<td>Make</td>
<td><?php echo $rowmake2['make']; ?></td>
<td>&nbsp;</td>
</tr>
<tr>
<td>SERVICE</td>
<td><input type="checkbox" name="servicech" id="servicech" value="Y" <?php if (!(strcmp($rowmake2['service'],'Y'))) {echo "checked";} ?>/></td>
<td><?php echo $rowmake2['serviceby']; ?></td>
</tr>
<tr>
<td>TESTDRIVE</td>
<td><input type="checkbox" name="testdrivech" id="testdrivech" value="Y" <?php if (!(strcmp($rowmake2['testdrive'],'Y'))) {echo "checked";}?> /></td>
<td><?php echo $rowmake2['testdriveby']; ?></td>
</tr>
<tr>
<td>CLEANED</td>
<td><input type="checkbox" name="cleanedch" id="cleanedch" value="Y" <?php if (!(strcmp($rowmake2['cleaned'],'Y'))) {echo "checked";}?> /></td>
<td><?php echo $rowmake2['cleanedby']; ?></td>
</tr>
<tr>
<td><input type="submit" name="Submit" value="Submit" />
<input name="submit" type="hidden" id="submit" value="1" /></td>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
</table>
</form>
<p>&nbsp;</p>
<p>
<input type="button" name="button" id="LogOff" value="LogOff" onclick="document.location.href='logoff.php'" />
</p>
</body>
</html>

here the table changes to the make table:


-- phpMyAdmin SQL Dump
-- version 2.11.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Feb 29, 2008 at 10:54 AM
-- Server version: 5.0.45
-- PHP Version: 5.2.4

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `dwc`
--

-- --------------------------------------------------------

--
-- Table structure for table `car_make`
--

CREATE TABLE IF NOT EXISTS `car_make` (
`make_id` int(11) NOT NULL auto_increment,
`make` varchar(20) default NULL,
`service` varchar(1) NOT NULL,
`serviceby` varchar(20) default NULL,
`testdrive` varchar(1) NOT NULL,
`testdriveby` varchar(20) default NULL,
`cleaned` varchar(1) NOT NULL,
`cleanedby` varchar(20) default NULL,
PRIMARY KEY (`make_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `car_make`
--

INSERT INTO `car_make` (`make_id`, `make`, `service`, `serviceby`, `testdrive`, `testdriveby`, `cleaned`, `cleanedby`) VALUES
(1, 'Alfa', 'Y', '1', 'Y', '2', '', ''),
(2, 'Audi', '', '', 'Y', '2', 'Y', '1'),
(3, 'BMW', '', '', '', '', 'Y', '2'),
(4, 'Citroen', '', '', '', '', '', '');

So is there a way to filter check boxes that have not been checked?

smitho
02-29-2008, 01:33 AM
OK worked it out.

I'm sure I tried this but this is working now.

The key change was in the database I set the default for the checkboxes as N. Then where I had if ($rowmake2['service']!=$_POST['servicech']){ I changed it to if ($rowmake2['service']!=$service){ etc.

I've also set to insert the user name rather than the id.

To insert name:

$name=$rowAccount['user_name'];

$query = sprintf("UPDATE car_make SET service='$service', serviceby='$name' WHERE make_id='$recordid'");

smitho
02-29-2008, 02:42 AM
Would it be possible to change the color of a row depending on the check box status?

If no check boxes are ticked the row color is red.
if one check box is ticked the row color is white.
if two are checked the row goes yellow.
If all are checked the row goes green.

Another option would the color them on the option. So if we know the last stage it the car is cleaned all rows that have the cleaned check box ticked would go green.

Any suggestions.

smitho
02-29-2008, 04:49 AM
Not sure if this is correct but it seems to work.



<tr <?php if ($rowmake['service']=='Y') {echo "class=\"red\"";} if ($rowmake['test']=='Y') {echo "class=\"green\"";} ?>>

The only this I noticed was that if service and tested are both checked then the color will be red. If I can the order then it will be green. Have I got this correct?

davidj
02-29-2008, 07:13 AM
so does it work now?

smitho
02-29-2008, 09:51 AM
Yes davidj thanks,

I just could not get my head around how to apply the Y/N filter and still keep the function of inserting the name of the user that ticked the checkbox. Once I made the changes mentioned about the dropmenu filter worked and the check boxes work as well. I went back to the alt row color example you gave me and used that to workout how to apply the set row colors to a row with a set value. All has worked out so far I'm posting the example so others may use them or even suggest better ways of coding. Thanks for all your help and I hope others find these examples useful.

Cheers.

davidj
02-29-2008, 11:18 AM
great mate