Reusable SELECT from any table and fields

[aldo151]

 

Hi there, wanted to see how I would have the columns I specified in 

$fields = array('id','type');

 

then be generated dynamically in the while loop and any other field I state in

$fields = array('id','type');

then its added to the while loop in the below
 

include('db.php');

class SelectData{


public function wpquery_select($conn,$sql,$fields){

$results = $conn->query($sql);

if($results->num_rows > 0){

	while($row = $results->fetch_assoc()){
		
		// How would you process the array $fields which holds all the rows specified and dynamically create echo $row['id'], $row['id'] and so on?
		
	}
	
  }

$conn->close();

 }

	
}



$select = new SelectData();

$sql = "SELECT * FROM orders";


$fields = array('id','type');


$select->wpquery_select($conn,$sql,$fields);

Thank you

[requinix]

If you only want the id and type fields then why don't you simply

SELECT id, type FROM orders

 

[aldo151]

Thank you but I don't think I'm being clear. Yes I of course I know that, but I would like to specify the colums in my array .... 

$fields = array('id','type');

then pass it via my function wpquery_select  which I am doing and have it dynamically return the columns from the database in the while loop.

I don't want to have it static in the while look like... $row['id].$row[type] and so on. Basically would like to have it, where if I add more column names in the $fields = array('id','type'); its automatically taken care of within the fuction.

Edited March 3, 2021 by aldo151

[Phi11W]

2 hours ago, aldo151 said:

Basically would like to have it, where if I add more column names in the $fields = array('id','type'); its automatically taken care of within the function.

Personally, I prefer to have my SQL clean and self-contained but then I don't have to work with WordPress.  YMMV.  

Here's one way:  

public function wpquery_select($conn,$sql,$fields){
   $sql = replace($sql,'*',implode(',',$fields);    <-- Assumes your query has "select * ..." 
   $results = $conn->query($sql);
   . . . 

Regards,
   Phill  W.  

[requinix]

2 hours ago, aldo151 said:

Thank you but I don't think I'm being clear. Yes I of course I know that, but I would like to specify the colums in my array .... 

You've come up with a solution, but the only problem I'm hearing is "I'd like to get an array with the id and type columns from my query".
If you issue the proper query, which you should be doing anyway because retrieving every column from the table when you only want a couple is wasteful of both time and resources, then you'll get an array with those columns because they're the only ones in there to begin with.

[aldo151]

Awesome..thanks Phill, by looking at it it makes sense  ... I will give this a try. 

[aldo151]

Actually no, this is just adding fields to my query...I already have $sql = "SELECT * FROM orders"; outside of my function if you take a look at the entire code. What I am trying to do is add additional columns to $fields = array('id','type'); .... for example: $fields = array('id','type','etc'); and then have it pass through my function wpquery_select , which its already doing, and then have it add $rows['?'] to display or however I specifiy in my loop, without having me to manually add the columns to echo in the while loop.

while($row = $results->fetch_assoc()){

echo $row['id'] . "then" . $row['type']; // <--- I don't want to manually specifiy here the column to display...would like it to be added automatically if I have added columns to my $fields = array('id','type','etc');

}

[requinix]

So, like, this is going into some sort of table, and you want to be able to add columns to list in that table without having to change a whole lot of code?

Like I said, fix the query to return only the columns you care about. Want to add a new column? Add it to the query. Really easy.
Then when drawing the table or whatever, you can use a simple foreach($row as $column => $value) and output it as you want because $row will only have the columns you care about.

[NotionCommotion]

You mean this?

$fields = array('id','type');
$fields[] = 'etc';	//$fields now is array('id','type', 'etc')

 

[Phi11W]

18 hours ago, aldo151 said:

while($row = $results->fetch_assoc()){

echo $row['id'] . "then" . $row['type']; // <--- I don't want to manually specifiy here the column to display...would like it to be added automatically if I have added columns to my $fields = array('id','type','etc');

}

You have an array containing the field names that were passed into the function. 
That array is used to build the SQL statement so those columns will be returned in each row.  

Now, for each row in the returned data, you need to loop through your fields array and pull out each value from the row, by field name, something like this: 

while( $row = $results->fetch_assoc() ){
   $dlm = ''; 
   foreach( $fields as $field ){
      echo $dlm . $row[ $field ];
      $dlm = "\t"; 
   }
}

 

Regards, 
   Phill  W.

 

 

[aldo151]

Yes, this did it for me Phil...thanks. I think I might be just a bad communicator.