[SOLVED] Searching dynamic driven list

[karenn1]

Hey everyone!

 

I have a members database will all their personal details in. I have a page running a SQL query to display a complete list of all these people. This is what my query looks like:

 

$sql_prop = "SELECT * FROM $db.members WHERE area = '".$_REQUEST['area']. "' ORDER BY name ASC;
$result_prop = mysql_query($sql_prop) or die(error_report("Mysql Error", mysql_error()."\n\n$sql_prop", $_SERVER['REMOTE_ADDR']));

 

"Error Report" is just one of my functions. I have two forms fields on this page, "Name" and "Address". I want to be able to search the listing that this SQL query has returned for those fields, ie. the user enters "John" in the form field and it returns all entries within the query matching "John". The reason I'm using this:

 

area = '".$_REQUEST['area']. "'

 

in the WHERE clause, is just to ensure that the person viewing this list can only see members that are in his area. On the previous page there is a piece of code that gets the session "area" and brings it across to this page. I'm keen on keeping that code in. It works for me at the moment.

 

Any ideas? How can I search this already filtered list?

 

Thanks,

Karen

[Yesideez]

$sql_prop="SELECT * FROM $db.members WHERE `area`='".$_REQUEST['area']."' AND `name` LIKE '%".$_REQUEST['name']."%' OR `name` LIKE '%".$_REQUEST['name']."%' ORDER BY `name` ASC";

 

Something like that. The % symbol is a bit like a pattern match:

 

%string = everything with 'string' at the end

%string% = everything containing 'string'

string% = everything starting with 'string'

[karenn1]

Hey Yesideez,

 

Thank you so much! It works great. Just one question, it works fine for my name form field but if I want to add a dropdown to search on along with "name", how would I change the coding?

 

 

Karen

[Yesideez]

Depends on the data you're using and how you set up the select options. What sort of data are you thinking of using with what field in the database?

 

btw, I just noticed I forgot to add "address" into that query and am using two "name"s instead!

[karenn1]

Basically, each member has a status - "Active" or "Inactive". When you select either option on the dropdown, it should search the list for the "active" field on each member. It can either be "True" or "False". Does that make sense?

[karenn1]

Also, I need an option to display all members as well. If I select "Active" on the dropdown, I need to search the members on that active list. Is that too much to ask? 

[Yesideez]

I think so.

 

Status <select name="status"><option value="active">Active</option><option value="inactive">Inactive</option></select>

That would be your HTML to make the drop-down selection box.

 

Use this to read the contents of that box:

$status=$_POST['status'];

 

Then modify your query to include this:

WHERE `status`='".$status."'

[Yesideez]

$sql_prop="SELECT * FROM $db.members WHERE `area`='".$_REQUEST['area']."' AND `name` LIKE '%".$_REQUEST['name']."%' OR `address` LIKE '%".$_REQUEST['address']."%' AND `status`="'.$status.'" ORDER BY `name` ASC";

That might work.

[karenn1]

I've placed this bit "$status=$_POST['status'];" above the SQL query. Is that right? I'm getting the following error:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'status = '' ORDER BY name ASC' at line 1

SELECT COUNT(*) FROM neigha_db1.members WHERE area = 'welgemoed' AND name LIKE '%%' status = '' ORDER BY name ASC

 

This is what my SQL now looks like, I'm not going to use address anymore:

 

$sql_prop = "SELECT * FROM $db.members WHERE area = '".$_REQUEST['area']. "' AND name LIKE '%".$_REQUEST['name']."%' AND status = '".$status."' ORDER BY name ASC";

 

Help!!

[Yesideez]

Add this line after the bottom line you specified:

echo 'SQL='.$sql_prop;

 

You'll still get the error but this time you'll be shown what your actualy query contains. Please post what is shown.

[karenn1]

This is displayed at the top of the page:

 

ASCSQL=SELECT * FROM neigha_db1.members WHERE area = 'welgemoed' AND name LIKE '%%' AND status='' ORDER BY name ASC 

[karenn1]

I've found the problem. The name of the field in the database is actually "active" and not "status". Now it works fine BUT when you first come to the page, it displays no rows. It only shows the info once you select an option from the dropdown and then click on search. Is there any way to have it display all the rows once you get to the page?

[Yesideez]

Two problems:

1. Your "name" field isn't being populated

2. The status isn't being populated.

 

Third, can't figure out where ASC before SQL is coming from. Can you post more of your script?

[karenn1]

I think we have crossing lines, check my post above. I'll post it again:

 

I've found the problem. The name of the field in the database is actually "active" and not "status". Now it works fine BUT when you first come to the page, it displays no rows. It only shows the info once you select an option from the dropdown and then click on search. Is there any way to have it display all the rows once you get to the page?

 

 

What do you think?

 

[Yesideez]

Is data being displayed when the user submits data via a form? If yes, please can you post the line that makes the submit button please?

[karenn1]

I realise now that the "Active" dropdown works but when I search on name at the same time, it doesn't pick anything up.

 

This is the submit procedure on the search form on the same page:

 

<form action="list2.php?area=<?= $result2['area']; ?>" method="post" name="form" id="form">

 

The $result2 variable is there to pick up the session "area" of the logged in person like I explained in my first post on this thread. The coding you gave me for the SQL clause only worked once I put that variable there.

[Yesideez]

Please can you post the HTML that makes the SUBMIT button - I need to know what you called it!

[karenn1]

Is this what you are looking for:

 

<input type="submit" name="submit" value="Search">

[Yesideez]

Edit this:

if ($_POST['submit']) {
  $sql_prop = "SELECT * FROM $db.members WHERE area = '".$_REQUEST['area']. "' AND name LIKE '%".$_REQUEST['name']."%' AND status = '".$status."' ORDER BY name ASC";
} else {
  $sql_prop="SELECT * FROM $db.members WHERE area = '".$_REQUEST['area']. "' ORDER BY name ASC";
}

 

That will make it so if the submit button is pressed the contents of the form are used. If the page is loaded for the first time the submit button hasn't been pressed so it defaults to showing all in the current area.

 

This is a crude way of doing it as it can be done a better way but it involves adding a lot more code.

[Yesideez]

EDIT: Fixed it!

[karenn1]

That works great! Thanks. But here's the new problem. The name search doesn't work anymore. It returns no results.

[Yesideez]

OK can we post the entire script as it is? So much has been modified that its now difficult to think how the script works.

[karenn1]

The SQL_ROWS bit is another feature I have in there, it just counts all the rows and displays it no the page. The code below:

 

<?php
$validate = new validate("error");


$i = 0;
$status = $_POST['status'];

//$sql_rows = "SELECT COUNT(*) FROM $db.members WHERE area = '".$_REQUEST['area']. "' AND name LIKE '%".$_REQUEST['name']."%' AND active='".$status."' ORDER BY name ASC";
//$result_rows = mysql_query($sql_rows) or die(error_report("Mysql Error", mysql_error()."\n\n$sql_rows", $_SERVER['REMOTE_ADDR']));
//$rs_rows = mysql_fetch_array($result_rows);
//$numrows = $rs_rows[0];


//$sql_prop = "SELECT * FROM $db.members WHERE area = '".$_REQUEST['area']. "' AND name LIKE '%".$_REQUEST['name']."%' AND active='".$status."' ORDER BY name ASC";
//$result_prop = mysql_query($sql_prop) or die(error_report("Mysql Error", mysql_error()."\n\n$sql_rows", $_SERVER['REMOTE_ADDR']));


if ($_POST['submit']) {
  		$sql_prop = "SELECT * FROM $db.members WHERE area = '".$_REQUEST['area']. "' AND name LIKE '%".$_REQUEST['name']."%' AND active = '".$status."' ORDER BY name ASC";
	$result_prop = mysql_query($sql_prop) or die(error_report("Mysql Error", mysql_error()."\n\n$sql_rows", $_SERVER['REMOTE_ADDR']));	

	$sql_rows = "SELECT COUNT(*) FROM $db.members WHERE area = '".$_REQUEST['area']. "' AND name LIKE '%".$_REQUEST['name']."%' AND active='".$status."' ORDER BY name ASC";
	$result_rows = mysql_query($sql_rows) or die(error_report("Mysql Error", mysql_error()."\n\n$sql_rows", $_SERVER['REMOTE_ADDR']));
	$rs_rows = mysql_fetch_array($result_rows);
	$numrows = $rs_rows[0];	

} else {
  		$sql_prop="SELECT * FROM $db.members WHERE area = '".$_REQUEST['area']. "' ORDER BY name ASC";
	$result_prop = mysql_query($sql_prop) or die(error_report("Mysql Error", mysql_error()."\n\n$sql_rows", $_SERVER['REMOTE_ADDR']));

	$sql_rows = "SELECT COUNT(*) FROM $db.members WHERE area = '".$_REQUEST['area']. "' ORDER BY name ASC";
	$result_rows = mysql_query($sql_rows) or die(error_report("Mysql Error", mysql_error()."\n\n$sql_rows", $_SERVER['REMOTE_ADDR']));
	$rs_rows = mysql_fetch_array($result_rows);
	$numrows = $rs_rows[0];	

	}


?>

[Yesideez]

heh I can't do anythign with that.

 

You'll need to find how the name variable is being pulled from the form and make sure the query is being populated with the data. Echo the query to the browser as well as the name variable to see exactly what is being used.

[karenn1]

Forgive me for being really stupid, I'm not quite sure how to approach that. Where do I put the echo in?