0

I am trying to use a single select to get the first and last name for a group of users.
Here are the two approaches (Test 1 & Test 2) I tried to get the data.
Both approach gave the same error.

Error: Unknown column 'norma' in 'where clause'

What is wrong with my approch(s)?

<?
//open connection
$mysqli = db_connect();
db_select($mysqli, $db_id);

//initiate array
$per_list = array();
$per = array();

//assign values of user name to array
$per_list[] = "norma";
$per_list[] = "alex";
$per_list[] = "sean";



/**Test 1**/       
 $query = "SELECT userid, first_name, last_name
 	   FROM users
  	   WHERE username IN (".implode(',',$per_list).")";
  $result = mysqli_query ($mysqli, $query) or die("Error: ".mysqli_error($mysqli));
 while($row = mysqli_fetch_array($result))
   {
   	 $per[] = $row;
   }  
   

/**Test 2**/
$per_list = implode(", ", $per_list);   

$query = "SELECT userid, first_name, last_name
 	   FROM users
  	   WHERE username IN ($per_list)";
 $result = mysqli_query ($mysqli, $query) or die("Error: ".mysqli_error($mysqli));
 while($row = mysqli_fetch_array($result))
   {
   	 $per[] = $row;
   }
   
?>

Edited by assgar: n/a

2
Contributors
2
Replies
4
Views
6 Years
Discussion Span
Last Post by assgar
0
implode("','",$per_list)

I'm not good sure ...sorry if its stupid but give it a try :P

0

I am trying to use a single select to get the first and last name for a group of users.
Here are the two approaches (Test 1 & Test 2) I tried to get the data.
Both approach gave the same error.

Error: Unknown column 'norma' in 'where clause'

What is wrong with my approch(s)?

<?
//open connection
$mysqli = db_connect();
db_select($mysqli, $db_id);

//initiate array
$per_list = array();
$per = array();

//assign values of user name to array
$per_list[] = "norma";
$per_list[] = "alex";
$per_list[] = "sean";



/**Test 1**/       
 $query = "SELECT userid, first_name, last_name
 	   FROM users
  	   WHERE username IN (".implode(',',$per_list).")";
  $result = mysqli_query ($mysqli, $query) or die("Error: ".mysqli_error($mysqli));
 while($row = mysqli_fetch_array($result))
   {
   	 $per[] = $row;
   }  
   

/**Test 2**/
$per_list = implode(", ", $per_list);   

$query = "SELECT userid, first_name, last_name
 	   FROM users
  	   WHERE username IN ($per_list)";
 $result = mysqli_query ($mysqli, $query) or die("Error: ".mysqli_error($mysqli));
 while($row = mysqli_fetch_array($result))
   {
   	 $per[] = $row;
   }
   
?>

Thanks for the response. Your suggeston show another great way to accomplish the task.
I was able to correct my error. Below are three approaches that works

//Test 1 works

$query = "SELECT userid, first_name, last_name
 	   FROM users
  	   WHERE username IN ('".implode("','",$per_list)."')
       AND org_code = '$org_code'
       AND deleted = 'N'";
  $result = mysqli_query ($mysqli, $query) or die("Error: ".mysqli_error($mysqli));
 while($row = mysqli_fetch_array($result))
   {
   	 $per[] = $row;
   }

//Test 2 works

$per_list = implode("', '", $per_list);   

$query = "SELECT userid, first_name, last_name
 	   FROM users
  	   WHERE username IN ('$per_list')
       AND org_code = '$org_code'
       AND deleted = 'N'  ";
 $result = mysqli_query ($mysqli, $query) or die("Error: ".mysqli_error($mysqli));
 while($row = mysqli_fetch_array($result))
   {
   	 $per[] = $row;
   }

//test 3 works

$query = "SELECT userid, first_name, last_name 
          FROM users
          WHERE username IN ('".join("','", array_values($per_list))."')
          AND deleted = 'N'";
 $result = mysqli_query ($mysqli, $query) or die("Error: ".mysqli_error($mysqli));
 while($row = mysqli_fetch_array($result))
   {
   	 $per[] = $row;
   }
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.