[SOLVED] Selecting mulitple fields

Grant Holmes · February 2, 2008

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 · February 2, 2008

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

pocobueno1388 · February 2, 2008

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 · February 4, 2008

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 · February 4, 2008

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 · February 4, 2008

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 · February 4, 2008

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 · February 4, 2008

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

Grant Holmes · February 4, 2008

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 · February 4, 2008

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 · February 4, 2008

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 · February 4, 2008

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 · February 4, 2008

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

Grant Holmes · February 4, 2008

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 · February 5, 2008

EDIT: hold on...

pocobueno1388 · February 5, 2008

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 · February 5, 2008

This version returns a blank page.

(Thanks for your continued efforts!!

pocobueno1388 · February 5, 2008

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 · February 5, 2008

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

pocobueno1388 · February 5, 2008

No problem Glad I could help.

Sign In

[SOLVED] Selecting mulitple fields

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Archived

Important Information