String to Query for All Records in Table

Question

When I pull each leadstatus individually (?leadstatus=New, ?leadstatus=Hot, etc.) they work, but when trying to get All, I can't seem to get it to work. The default on the page is New leads as you can see.

`$query = "SELECT * FROM contacts WHERE contacttype IN ('New','Buyer','Seller','Buyer / Seller','Investor') AND leadstatus = 'New' ORDER BY date DESC";

    if(isset($_GET['leadstatus']) && in_array($_GET['leadstatus'], array('New', 'Hot', 'Warm', 'Cold', 'Rejected', 'Closed')))
    {      
    $status = $_GET['leadstatus'];   
    $query = "SELECT * FROM contacts WHERE leadstatus = '".$status."' ORDER BY contacts.date DESC";  
    }`

Here are some of the strings I've tried with no luck:

?leadstatus=New&leadstatus=Hot&leadstatus=Warm&leadstatus=Rejected&leadstatus=Cold - Only pulls last listed, which is Cold

?leadstatus[]=New&leadstatus=[]Hot&leadstatus[]=Warm&leadstatus[]=Rejected&leadstatus[]=Cold - Returns default, which is New

?leadstatus=New&Hot&Warm&Rejected&Cold

Returns default, which is New

Why not just make the where statement conditional? If status indicates that you want all records, remove the where clause. — Gordon Linoff, Jun 25 '12 at 18:03

score 1 · Accepted Answer · answered Jun 25 '12 at 18:05

if(isset($_GET['leadstatus']) && $_GET['leadstatus'] == "all") {
    $query = "SELECT * FROM contacts ORDER BY contacts.date DESC";  
} else if (in_array($_GET['leadstatus'], array('New', 'Hot', 'Warm', 'Cold', 'Rejected', 'Closed'))) {      
    $status = $_GET['leadstatus'];   
    $query = "SELECT * FROM contacts WHERE leadstatus = '".$status."' ORDER BY contacts.date DESC";  
}

Then, make leadstatus = all.

score 0 · Answer 2 · answered Jun 25 '12 at 18:11

0

Try this:

if(isset($_GET['leadstatus']) && in_array($_GET['leadstatus'], array('New', 'Hot', 'Warm', 'Cold', 'Rejected', 'Closed')))
{      
  $status = $_GET['leadstatus'];   
  if(!empty($status)) {
    $query = "SELECT * FROM contacts WHERE leadstatus = '".$status."' ORDER BY contacts.date DESC";  
  } else {
    $query = "SELECT * FROM contacts ORDER BY contacts.date DESC"; 
  }
}`

However, may I also suggest that you use a parameterized query? You are wide open to a SQL Injection attack here.

answered Jun 25 '12 at 18:11

ametren

2,186
15
19

it will be behind a login screen, so shouldn't matter, right? – Josh Jun 25 '12 at 18:18
It always matters! injection attacks give the attacker a LOT of power very easily. for example, what if I put in a request that said `?leadstatus='; DROP TABLE contacts; --` Relevant: http://xkcd.com/327/ – ametren Jun 25 '12 at 18:20

score 0 · Answer 3 · answered Jun 25 '12 at 18:17

Something like this should match multiple conditions, allowing you to mix-and match several at a time, rather than 1 or all.

$status = join(',',$_GET['leadstatus']); 
$query = "SELECT * FROM contacts WHERE leadstatus IN($status) ORDER BY contacts.date DESC";

String to Query for All Records in Table

3 Answers3