[SOLVED] Searching dynamic driven list

[Yesideez]

To display the contents of the query add the ECHO immediately after you populate the $sql_prop variable.

 

To display the contents of the $name variable you can echo this anywhere like this:

echo $_REQUEST['name'];

 

Just wondering, any specific reason why you're using $_REQUEST and not $_POST?

[karenn1]

I've placed in the echo and typed something in that form field. On submit, it echo's what I've typed in that box at the top of the page. How is this going to help me?

[Yesideez]

Are you showing the contents of the MySQL query?

 

This will help by showing you what data is being used for the name. If nothing is being shown where the name should be then you know that it isn't pulling the name from the form.

[karenn1]

That is the contents of the MySQL query. In the SQL query, when I take away the "AND active =....." bit and only leave the AND for name, then it works fine. As soon as I add that back in again, it falls flat.

[Yesideez]

Can you post the MySQL query as it gets displayed in your browser?

[karenn1]

It doesn't display the full query, only the variable.

[Yesideez]

After the line that makes $sql_prop contain the query you need to use:

echo $sql_prop;

[Yesideez]

Add it after the IF statement - I've added it for you:

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";
}
echo $sql_prop;

[karenn1]

That's fine. I added it myself. Here's what gets echoed:

 

SELECT * FROM neigha_db1.members WHERE area = 'welgemoed' AND name LIKE '%nortje%' OR active=''ORDER BY name ASC 

[Yesideez]

The active variable isn't being populated. If the field is either 'active' or 'inactive' then nothign will be shown because the active will never be true. Also need to add a space immediately before the word "ORDER"

[Yesideez]

Try this:

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

 

Basically we've added condition ? true : false. If $active contains 'active' then use it. If $active contains anything else then use 'inactive'

[karenn1]

The above code doesn't work all that well. I'm struggling to understand it. Remember, the values for  "active" must be "True" or "False", not "Active" and "Inactive" like you had it.

[Yesideez]

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

 

Sorry, I thought they contained either 'active' or 'inactive', not true or false.

 

See how that works.

[karenn1]

I've changed it to this:

 

active = '".($status ? 'true' : 'false')."'

 

and it seems to work but when I select "Inactive" (value = false) from the dropdown, it still returns true. SQL echo:

 

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

 

As soon as I get this code, and I then search on name, it reverts to false. Any ideas? Other than that, when active is true, then I can search for name as well. That's at least one good thing.

[Yesideez]

In the meantime I'll break it down so you can maybe understand what is happening.

 

All the line is doing is populating the variable $sql_prop with a MySQL query.

 

The extra bit I added was the condition ? true : false bit. This is exactly the same as this:

<?php
$sql_prop = "SELECT * FROM $db.members WHERE area = '".$_REQUEST['area']. "' AND name LIKE '%".$_REQUEST['name']."%' AND status = '";
if ($_REQUEST['status']==true) {
  $sql_prop .= 'active';
} else {
  $sql_prop .= 'inactive';
}
$sql_prop .= "' ORDER BY name ASC";
?>

 

Here's the amended line:

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

 

I suspect that the variable $status isn't being set to the contents of the drop-down box. Difficult to tell without seeing the entire script.

 

Can you show the HTML that makes the drop-down box? We need to make sure we're checking the right contents.

[karenn1]

Here's the code:

 

<select name="status">
                    <option value="True" selected>Active</option>
                    <option value="False">Inactive</option>
                  </select>

[Yesideez]

Ah!!!

 

I see you're using the WORDS "true" and "false" instead of numerical values!

 

$sql_prop = "SELECT * FROM $db.members WHERE area = '".$_REQUEST['area']. "' AND name LIKE '%".$_REQUEST['name']."%' AND status = '".($_REQUEST['status']=='True' ? 'active' : 'inactive')."' ORDER BY name ASC";

[Yesideez]

Is there any particular reason why you're using $_REQUEST and not $_POST?

[karenn1]

Hallelujah! It works!!!! Regarding your question - not really. Didn't think there was really a difference. One last question, is there any way that I can add an option for "All" to my dropdown then it will show all members in my database? Is that too much to aks?

[karenn1]

I have to head off now. Thank you SOOO much for all your help! You are a star. Can you perhaps just answer that last question about displaying all members from the dropdown box? I would appreciate just one last bit of help!

 

Thanks,

Karen

[Yesideez]

It can be added but you'd need to change your code to change the query accordingly.

 

At the moment you're checking for either active or inactive. If you want to check for both then simply remove that part of the query.

 

Try this:

<select name="status"><option="All">All</option><option value="True">Active</option><option value="False">Inactive</option></select>

 

That adds "All" into the drop-down box with the value "All".

 

Next we need to change the way the query is built:

if ($_POST['submit']) {
  switch ($_REQUEST['status']) {
    case "True":$sqlstatus="AND status = 'active' ";break;
    case "False":$sqlstatus="AND status = 'inactive' ";break;
    default:$sqlstatus="";
  }
  $sql_prop = "SELECT * FROM $db.members WHERE area = '".$_REQUEST['area']. "' AND name LIKE '%".$_REQUEST['name']."%' ".$sqlstatus."ORDER BY name ASC";
} else {
  $sql_prop="SELECT * FROM $db.members WHERE area = '".$_REQUEST['area']. "' ORDER BY name ASC";
}

[Yesideez]

$_GET reads values from the URL. When a form is set to method GET it adds the form data onto the URL.

 

$_POST reads data from a form when method is set to "POST"

 

$_REQUEST uses both the above methods and will also read from cookies.

[karenn1]

It all works 100% now. I even managed to add another dropdown to the SQL query code to search for language as well. Thanks again for all your help!!!!

 

 

Karen