Wrox Programmer Forums
|
Beginning PHP Beginning-level PHP discussions. More advanced coders should post to the Pro PHP forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Beginning PHP section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 20th, 2005, 11:06 AM
Authorized User
 
Join Date: Jan 2005
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default Search function

I want to search and retrieve a particular record using any one of a number of fields. The problem I'm having is that if I use the following:

Code:
$sql  = "SELECT * FROM items";
$sql .= "WHERE item1 LIKE ('%" . $_POST['item1'] . "%') OR item2 LIKE ('%" . $_POST['item2'] . "%') OR item3 LIKE ('%" . $_POST['item3'] . "%')
$sql .= "ORDER BY item1";
It just gives me everything in the table because of the '%' method of matching.

What I want to do is be able to match on any or all of the criteria in the form.

I hope this makes sense!
 
Old January 20th, 2005, 11:19 AM
Authorized User
 
Join Date: Jan 2005
Posts: 82
Thanks: 0
Thanked 0 Times in 0 Posts
Default

first you need to make sure you have space between items and WHERE
...second, even though I am not an expert in this, I would choose a rather different method:


$sql = "Select * from items WHERE*;

if (isset($_Post['item1'])){

     $sql .= " item1 = '$item1' "

}

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

      $sql .= " AND item 2 = ....




something of that sort...It just makes it easier for mysql to choose items that are entered and not worry about blanks and null fields...


 
Old January 20th, 2005, 11:39 AM
Authorized User
 
Join Date: Jan 2005
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

That looks like a much better idea!

The only problem is now get a error msg:

Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in etc

refering to the line:

Code:
if (isset($_Post['item1']))
Any ideas?
 
Old January 20th, 2005, 12:01 PM
Authorized User
 
Join Date: Jan 2005
Posts: 82
Thanks: 0
Thanked 0 Times in 0 Posts
Default

did you forget the semi colon

 
Old January 20th, 2005, 12:32 PM
Authorized User
 
Join Date: Jan 2005
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I did but I put it in and it still does the same. I'm very new to this so I'm sorry if I'm not giving enough info.
 
Old January 20th, 2005, 01:15 PM
Authorized User
 
Join Date: Jan 2005
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Make sure that it is

$_POST['item1']

and not

$_Post['item1']

One is a superglobal and one is probably not yet defined as an array.



 
Old January 20th, 2005, 01:29 PM
Authorized User
 
Join Date: Jan 2005
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Okay thanks for that Basil. I changed it and unfortunatly it STIll doesn't work. :(

Code:
$sql = "Select * from item WHERE*;

if (isset($_POST['item1']));
    {     
    $sql .= " item1 = '$item1' "
    } 

etc
 
Old January 20th, 2005, 02:12 PM
Authorized User
 
Join Date: Jan 2005
Posts: 82
Thanks: 0
Thanked 0 Times in 0 Posts
Default

hmm
well when I wrote that, I didn't actually do a clean code...understanding that you would change it to your own needs.

it should really be like this...to make it easy:

$item1 = $_POST['item1'];
$item2 = $_POST['item2'];
..
..
..

then


//the id field is there, just in case the user doesn't enter any text in any of the fields: could be any field in your table:
$sql = "SELECT * FROM items WHERE id = id"

if (isset($item1)){
$sql .= ", item1 = '%$item1%'";
}
if (isset($item2)){ //edited typo

$sql .= ", item2 = '%$item2%'";

}

..
.

.

.

and then,

$sql .= " ORDER BY item1";

$sql2 = mysql_query($sql) or die (mysql_error());

and then some more code....to see if it works...

while ($row = mysql_fetch_array($sql2)){

echo $row['item1'];

}
 
Old January 21st, 2005, 05:29 AM
Authorized User
 
Join Date: Jan 2005
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by Gotaka4
 hmm
well when I wrote that, I didn't actually do a clean code...understanding that you would change it to your own needs.


Thanks Gotaka4. Sorry I really am a noobie at this so I kinda didn't see where I was supposed to go with it after you helped.

Sorry to be a pain in the ass. I'm trying... very trying I've been told!
 
Old January 21st, 2005, 08:31 AM
Authorized User
 
Join Date: Jan 2005
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Right, having tried some stuff and learned a bit I've now applied it to a helpdesk system I want to write. Nothing to flash but functional all the same. The problem I'm having now is that while the following code works, when it comes to searching a form with more than a couple of fields in it the number of if statements is going to be huge and very complicated. Can anybody suggest a better way of doing this or some way of contracting this a little?

Code:
$ticket_id = $_POST['ticket_id'];
$ticket_sdesc = $_POST['sdesc'];
$open_event = $_POST['open_event'];

$sql = "SELECT * FROM ticket";


if (!empty($ticket_id))
{
$sql .= " WHERE ticket_id = '$ticket_id'";
}

if (!empty($ticket_id))
{
    if (!empty($ticket_sdesc))
    {
    $sql .= " AND ";
    }
}

if (!empty($ticket_sdesc))
{
    if (empty($ticket_id))
    {
    $sql .= " WHERE ";
    }
$sql .= " ticket_sdesc LIKE '%" . $ticket_sdesc ."%'";
}

if (!empty($ticket_id))
{
    if (!empty($ticket_sdesc))
    {
    $sql .= " ORDER BY ticket_id";
    }
}





Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding a search function Brendan Bartley ASP.NET 1.0 and 1.1 Basics 1 September 30th, 2008 03:40 PM
Search function talktome Excel VBA 1 September 6th, 2005 12:47 AM
search function elania MySQL 1 February 6th, 2005 07:00 PM
Search Function rylemer Pro VB 6 1 March 24th, 2004 11:37 PM
Search Function damnnono_86 Access 30 October 14th, 2003 09:02 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.