[SOLVED] Selecting mulitple fields

[Grant Holmes]

I had a "PHP Guy" write some code for me that selects a type of event from my table. It works great. I can reproduce the page and change the expected field value in the line shown below and that page will then pull the records corresponding to that event.

 

Line 30 is:

$default_event='test';

So this would return all the records with "test" in my table.

 

I would like to alter this line to select multiple events. I've tried just adding a comma and the next event like:

$default_event='test,request';

but I get NO records when I do that.

 

This is all the code that selects the records. Later we display them. I just need help on changing this to be able to select multiples at once.

 

<?php
include("dbinfo.inc.php");
mysql_connect(mysql,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$default_event='test';   /////THIS IS THE LINE FROM THE QUESTION*****************//
if(isset($_REQUEST['changed']) && count($_REQUEST['changed']) > 0 && !empty($_REQUEST['command'])) {
  $sql = "";
  $insql = "IN('" . join("','",array_keys($_REQUEST['changed'])) . "')";
  switch($_REQUEST['command']) {
  case "Deactivate":
    $sql = "UPDATE birthdays 
            SET ACTIVE='0' 
            WHERE id " . $insql;
    break;
  case "Activate":
    $sql = "UPDATE birthdays 
            SET ACTIVE='1' 
            WHERE id " . $insql;
    break;
  case "Delete":
    $sql = "DELETE FROM birthdays 
            WHERE id " . $insql;
    break;
  }
  if(!empty($sql)) {
    mysql_query($sql);
  }
}

$where = $url = array();
if(!empty($_REQUEST['event'])) {
  $where[] = "Event='" . addslashes($_REQUEST['event']) . "'";
  $url[] = "event=" . $_REQUEST['event'];
} else {
  $where[] = "Event='$default_event'"; 
  $_REQUEST['event'] = $default_event; 
}
if(strlen($active) > 0) {
  $where[] = "Active='" . addslashes($_REQUEST['active']) . "'";
  $url[] = "active=" . $_REQUEST['active'];
  switch($active) {
  case 1:
    $page_type = "ACTIVE";
    break;
  case 0:
    $page_type = "INACTIVE";
    break;
  }
} else {
    $page_type = "ALL";
}

$query="SELECT * FROM birthdays";

if(count($where) > 0) {
  $query .= " WHERE " . join(" AND ",$where);
}

$result=mysql_query($query);

$num=mysql_numrows($result); 

mysql_close();

echo "<A name=\"top\"></A><b><center><H1>Display Records \"<I>$page_type $default_event records</I>\":</H1>";
?>

[Grant Holmes]

Seeing no responses/help... can someone tell me if it's even possible?? Or if not, please tell me.

[pocobueno1388]

Try this

 

<?php
include("dbinfo.inc.php");
mysql_connect(mysql,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$default_event= array('event1', 'event2');

if(isset($_REQUEST['changed']) && count($_REQUEST['changed']) > 0 && !empty($_REQUEST['command'])) {
  $sql = "";
  $insql = "IN('" . join("','",array_keys($_REQUEST['changed'])) . "')";
  switch($_REQUEST['command']) {
  case "Deactivate":
    $sql = "UPDATE birthdays 
            SET ACTIVE='0' 
            WHERE id " . $insql;
    break;
  case "Activate":
    $sql = "UPDATE birthdays 
            SET ACTIVE='1' 
            WHERE id " . $insql;
    break;
  case "Delete":
    $sql = "DELETE FROM birthdays 
            WHERE id " . $insql;
    break;
  }
  if(!empty($sql)) {
    mysql_query($sql);
  }
}

$where = $url = array();
if(!empty($_REQUEST['event'])) {
  $where[] = "Event='" . addslashes($_REQUEST['event']) . "'";
  $url[] = "event=" . $_REQUEST['event'];
} else {

  $num_of_events = count($default_event);
  
  for($i=0; $i<$num_of_events;$i++){
  
     if ($i == 0)
        $where[] = "Event='$default_event[$i]'"; 
     else
        $where[] = " OR Event='$default_event[$i]'";
  }
  
  $_REQUEST['event'] = $default_event; 
}
if(strlen($active) > 0) {
  $where[] = "Active='" . addslashes($_REQUEST['active']) . "'";
  $url[] = "active=" . $_REQUEST['active'];
  switch($active) {
  case 1:
    $page_type = "ACTIVE";
    break;
  case 0:
    $page_type = "INACTIVE";
    break;
  }
} else {
    $page_type = "ALL";
}

$query="SELECT * FROM birthdays";

if(count($where) > 0) {
  $query .= " WHERE " . join(" AND ",$where);
}

$result=mysql_query($query);

$num=mysql_numrows($result); 

mysql_close();

echo "<A name=\"top\"></A><b><center><H1>Display Records \"<I>$page_type $default_event records</I>\":</H1>";
?>

 

On this line

$default_event= array('event1', 'event2');

 

You can add as many events as you would like to the array. The code is untested, so hopefully it will work.

[Grant Holmes]

pocobueno,

 

Thanks for your post. I really appreciate you working on this!

 

We've made progress. The page no longer fails, but displays no results. I changed your array line to:

$default_event= array('feedback', 'test');

As I know that should return five records (2 for test, 3 for feedback).

 

FWIW, at the bottom of the page, after parsing the results in a "while ($row = mysql_fetch_assoc($result))..." loop, I ECHO:

echo "<P>Total Records: $i</P>";

and it returns "2", and if I add a third event, it displays "3", but still no records display.

[pocobueno1388]

Okay, lets try to echo the query out to make sure it looks okay.

 

<?php
include("dbinfo.inc.php");
mysql_connect(mysql,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$default_event= array('event1', 'event2');

if(isset($_REQUEST['changed']) && count($_REQUEST['changed']) > 0 && !empty($_REQUEST['command'])) {
  $sql = "";
  $insql = "IN('" . join("','",array_keys($_REQUEST['changed'])) . "')";
  switch($_REQUEST['command']) {
  case "Deactivate":
    $sql = "UPDATE birthdays 
            SET ACTIVE='0' 
            WHERE id " . $insql;
    break;
  case "Activate":
    $sql = "UPDATE birthdays 
            SET ACTIVE='1' 
            WHERE id " . $insql;
    break;
  case "Delete":
    $sql = "DELETE FROM birthdays 
            WHERE id " . $insql;
    break;
  }
  if(!empty($sql)) {
    mysql_query($sql);
  }
}

$where = $url = array();
if(!empty($_REQUEST['event'])) {
  $where[] = "Event='" . addslashes($_REQUEST['event']) . "'";
  $url[] = "event=" . $_REQUEST['event'];
} else {

  $num_of_events = count($default_event);
  
  for($i=0; $i<$num_of_events;$i++){
  
     if ($i == 0)
        $where[] = "Event='$default_event[$i]'"; 
     else
        $where[] = " OR Event='$default_event[$i]'";
  }
  
  $_REQUEST['event'] = $default_event; 
}
if(strlen($active) > 0) {
  $where[] = "Active='" . addslashes($_REQUEST['active']) . "'";
  $url[] = "active=" . $_REQUEST['active'];
  switch($active) {
  case 1:
    $page_type = "ACTIVE";
    break;
  case 0:
    $page_type = "INACTIVE";
    break;
  }
} else {
    $page_type = "ALL";
}

$query="SELECT * FROM birthdays";

if(count($where) > 0) {
  $query .= " WHERE " . join(" AND ",$where);
}

$result=mysql_query($query)or die(mysql_error() . "<p>With Query:<br>$query");
$num=mysql_num_rows($result); 

mysql_close();

echo "<p><b>$query</b><p>";

echo "<A name=\"top\"></A><b><center><H1>Display Records \"<I>$page_type $default_event records</I>\":</H1>";
?>

 

The query should come out bold on the page, so copy and paste whatever you see.

[Grant Holmes]

Result:

 

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 'OR Event='test'' at line 1

 

With Query:

SELECT * FROM birthdays WHERE Event='feedback' AND OR Event='test'

 

 

[pocobueno1388]

Okay, I see a problem with that query.

 

Try this

 

<?php
include("dbinfo.inc.php");
mysql_connect(mysql,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$default_event= array('event1', 'event2');

if(isset($_REQUEST['changed']) && count($_REQUEST['changed']) > 0 && !empty($_REQUEST['command'])) {
  $sql = "";
  $insql = "IN('" . join("','",array_keys($_REQUEST['changed'])) . "')";
  switch($_REQUEST['command']) {
  case "Deactivate":
    $sql = "UPDATE birthdays 
            SET ACTIVE='0' 
            WHERE id " . $insql;
    break;
  case "Activate":
    $sql = "UPDATE birthdays 
            SET ACTIVE='1' 
            WHERE id " . $insql;
    break;
  case "Delete":
    $sql = "DELETE FROM birthdays 
            WHERE id " . $insql;
    break;
  }
  if(!empty($sql)) {
    mysql_query($sql);
  }
}

$where = $url = array();
if(!empty($_REQUEST['event'])) {
  $where[] = "Event='" . addslashes($_REQUEST['event']) . "'";
  $url[] = "event=" . $_REQUEST['event'];
} else {

  $num_of_events = count($default_event);
  
  for($i=0; $i<$num_of_events;$i++){
  
     if ($i == 0)
        $where[] = "Event='$default_event[$i]'"; 
     else
        $where[] = " OR Event='$default_event[$i]'";
  }
  
  $_REQUEST['event'] = $default_event; 
}
if(strlen($active) > 0) {
  $where[] = "Active='" . addslashes($_REQUEST['active']) . "'";
  $url[] = "active=" . $_REQUEST['active'];
  switch($active) {
  case 1:
    $page_type = "ACTIVE";
    break;
  case 0:
    $page_type = "INACTIVE";
    break;
  }
} else {
    $page_type = "ALL";
}

$query="SELECT * FROM birthdays";

if(count($where) > 0) {
  $query .= " WHERE " . join(" ",$where);
}

$result=mysql_query($query)or die(mysql_error() . "<p>With Query:<br>$query");
$num=mysql_num_rows($result); 

mysql_close();

echo "<p><b>$query</b><p>";

echo "<A name=\"top\"></A><b><center><H1>Display Records \"<I>$page_type $default_event records</I>\":</H1>";
?>

 

Hopefully this gets it working. If not, post the new query it outputs.

[craygo]

Wouldn't it be easier to just add the requested event to the array??

 

 

[Grant Holmes]

ECHO at top said:

SELECT * FROM birthdays WHERE Event='feedback' OR Event='test'

 

AND it returned the 5 results we were looking for!!  And when I added the third choice, that also appeared to work.

Thanks!!!!!

 

I call this solved!!

[Grant Holmes]

I may have been premature. The query works for the initial display, but when you choose the "view inactive" or "view acive" links, I get the error again

==

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 'Active='0'' at line 1

 

With Query:

SELECT * FROM birthdays WHERE Event='feedback' OR Event='test' Active='0'

 

(Or Active=1 when Active was the choice)

===

 

AND...in order to show my TOTAL noobness, I cannot figure out where you are ECHOing the select statement. I don't want to remove it, just comment it out.

[pocobueno1388]

Okay, I think this will solve the problem. I commented where I outputted the query, I would keep it there until this is solved though.

 

<?php
include("dbinfo.inc.php");
mysql_connect(mysql,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$default_event= array('event1', 'event2');

if(isset($_REQUEST['changed']) && count($_REQUEST['changed']) > 0 && !empty($_REQUEST['command'])) {
  $sql = "";
  $insql = "IN('" . join("','",array_keys($_REQUEST['changed'])) . "')";
  switch($_REQUEST['command']) {
  case "Deactivate":
    $sql = "UPDATE birthdays 
            SET ACTIVE='0' 
            WHERE id " . $insql;
    break;
  case "Activate":
    $sql = "UPDATE birthdays 
            SET ACTIVE='1' 
            WHERE id " . $insql;
    break;
  case "Delete":
    $sql = "DELETE FROM birthdays 
            WHERE id " . $insql;
    break;
  }
  if(!empty($sql)) {
    mysql_query($sql);
  }
}

$where = $url = array();
if(!empty($_REQUEST['event'])) {
  $where[] = "Event='" . addslashes($_REQUEST['event']) . "'";
  $url[] = "event=" . $_REQUEST['event'];
} else {

  $num_of_events = count($default_event);
  
  for($i=0; $i<$num_of_events;$i++){
  
     if ($i == 0)
        $where[] = "Event='$default_event[$i]'"; 
     else
        $where[] = " OR Event='$default_event[$i]'";
  }
  
  $_REQUEST['event'] = $default_event; 
}
if(strlen($active) > 0) {
  $where[] = " AND Active='" . addslashes($_REQUEST['active']) . "'";
  $url[] = "active=" . $_REQUEST['active'];
  switch($active) {
  case 1:
    $page_type = "ACTIVE";
    break;
  case 0:
    $page_type = "INACTIVE";
    break;
  }
} else {
    $page_type = "ALL";
}

$query="SELECT * FROM birthdays";

if(count($where) > 0) {
  $query .= " WHERE " . join(" ",$where);
}

$result=mysql_query($query)or die(mysql_error() . "<p>With Query:<br>$query");
$num=mysql_num_rows($result); 

mysql_close();

echo "<p><b>$query</b><p>"; //################## Query outputted on this line

echo "<A name=\"top\"></A><b><center><H1>Display Records \"<I>$page_type $default_event records</I>\":</H1>";
?>

[Grant Holmes]

slowly, but surely we sneak up on it.

 

For the fields, I'm using...$default_event= array('feedback', 'test');

 

I get the five results I expect when the page is run.

 

When I click, "view active only", I get the three feedback records (that are still inactive)

The top ECHO is: SELECT * FROM birthdays WHERE Event='feedback' OR Event='test' AND Active='1'

 

And when I click, "view inactive only", I get all five records (that are still inactive)

The top ECHO is: SELECT * FROM birthdays WHERE Event='feedback' OR Event='test' AND Active='0'

[pocobueno1388]

So whats the problem? Is it when you click active it is showing inactive records?

[Grant Holmes]

Sort of. When the page loads normally, the results are correct.

 

(There are 3 feedback records and 2 test records- they are all INactive)

 

When I click, "view active only", I get the 3 feedback records

There should be none

 

And when I click, "view inactive only", I get all 5records

==

Just to test, I made one FEEDBACK record Active.

"view inactive only", I still get all 5 records

 

"view active only" returns the same 3 feedback records (Even though only 1 is active.

[pocobueno1388]

EDIT: hold on...

[pocobueno1388]

Okay, give this a shot. It's hard to tell if it's going to work when I can't test it on your server...but I think it will.

 

<?php
include("dbinfo.inc.php");
mysql_connect(mysql,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$default_event= array('event1', 'event2');

if(isset($_REQUEST['changed']) && count($_REQUEST['changed']) > 0 && !empty($_REQUEST['command'])) {
  $sql = "";
  $insql = "IN('" . join("','",array_keys($_REQUEST['changed'])) . "')";
  switch($_REQUEST['command']) {
  case "Deactivate":
    $sql = "UPDATE birthdays 
            SET ACTIVE='0' 
            WHERE id " . $insql;
    break;
  case "Activate":
    $sql = "UPDATE birthdays 
            SET ACTIVE='1' 
            WHERE id " . $insql;
    break;
  case "Delete":
    $sql = "DELETE FROM birthdays 
            WHERE id " . $insql;
    break;
  }
  if(!empty($sql)) {
    mysql_query($sql);
  }
}

$where = $url = array();
if(!empty($_REQUEST['event'])) {
  $where[] = "Event='" . addslashes($_REQUEST['event']) . "'";
  $url[] = "event=" . $_REQUEST['event'];
} else {

  $num_of_events = count($default_event);
  
  for($i=0; $i<$num_of_events;$i++){
  
     if ($i == 0) {
      
        $where_s = "(Event='$default_event[$i]'"; 
        if ($num_of_events <= 1) $where_s .= ')';
        
        $where[] = $where_s;
      
     } else {
        
        $stop = $i+1;
        $where[] 
        $where_s = " OR Event='$default_event[$i]' ";
        if ($num_of_events == $stop) $where_s .= ')';
        
        $where[] = $where_s;
        
     }
     
  }
  
  $_REQUEST['event'] = $default_event; 
}
if(strlen($active) > 0) {
  $where[] = " AND Active='" . addslashes($_REQUEST['active']) . "'";
  $url[] = "active=" . $_REQUEST['active'];
  switch($active) {
  case 1:
    $page_type = "ACTIVE";
    break;
  case 0:
    $page_type = "INACTIVE";
    break;
  }
} else {
    $page_type = "ALL";
}

$query="SELECT * FROM birthdays";

if(count($where) > 0) {
  $query .= " WHERE " . join(" ",$where);
}

$result=mysql_query($query)or die(mysql_error() . "<p>With Query:<br>$query");
$num=mysql_num_rows($result); 

mysql_close();

echo "<p><b>$query</b><p>"; //################## Query outputted on this line

echo "<A name=\"top\"></A><b><center><H1>Display Records \"<I>$page_type $default_event records</I>\":</H1>";
?>

[Grant Holmes]

This version returns a blank page.

 

(Thanks for your continued efforts!!

[pocobueno1388]

Oops, I accidentally left a line in there.

 

<?php
include("dbinfo.inc.php");
mysql_connect(mysql,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$default_event= array('event1', 'event2');

if(isset($_REQUEST['changed']) && count($_REQUEST['changed']) > 0 && !empty($_REQUEST['command'])) {
  $sql = "";
  $insql = "IN('" . join("','",array_keys($_REQUEST['changed'])) . "')";
  switch($_REQUEST['command']) {
  case "Deactivate":
    $sql = "UPDATE birthdays 
            SET ACTIVE='0' 
            WHERE id " . $insql;
    break;
  case "Activate":
    $sql = "UPDATE birthdays 
            SET ACTIVE='1' 
            WHERE id " . $insql;
    break;
  case "Delete":
    $sql = "DELETE FROM birthdays 
            WHERE id " . $insql;
    break;
  }
  if(!empty($sql)) {
    mysql_query($sql);
  }
}

$where = $url = array();
if(!empty($_REQUEST['event'])) {
  $where[] = "Event='" . addslashes($_REQUEST['event']) . "'";
  $url[] = "event=" . $_REQUEST['event'];
} else {

  $num_of_events = count($default_event);
  
  for($i=0; $i<$num_of_events;$i++){
  
     if ($i == 0) {
      
        $where_s = "(Event='$default_event[$i]'"; 
        if ($num_of_events <= 1) $where_s .= ')';
        
        $where[] = $where_s;
      
     } else {
        
        $stop = $i+1;
        $where_s = " OR Event='$default_event[$i]' ";
        if ($num_of_events == $stop) $where_s .= ')';
        
        $where[] = $where_s;
        
     }
     
  }
  
  $_REQUEST['event'] = $default_event; 
}
if(strlen($active) > 0) {
  $where[] = " AND Active='" . addslashes($_REQUEST['active']) . "'";
  $url[] = "active=" . $_REQUEST['active'];
  switch($active) {
  case 1:
    $page_type = "ACTIVE";
    break;
  case 0:
    $page_type = "INACTIVE";
    break;
  }
} else {
    $page_type = "ALL";
}

$query="SELECT * FROM birthdays";

if(count($where) > 0) {
  $query .= " WHERE " . join(" ",$where);
}

$result=mysql_query($query)or die(mysql_error() . "<p>With Query:<br>$query");
$num=mysql_num_rows($result); 

mysql_close();

echo "<p><b>$query</b><p>"; //################## Query outputted on this line

echo "<A name=\"top\"></A><b><center><H1>Display Records \"<I>$page_type $default_event records</I>\":</H1>";
?>

[Grant Holmes]

You nailed it dude. THAT fixed it all. HUGE thanks to you for your continued efforts to solve this!!

[pocobueno1388]

No problem Glad I could help.