Jump to content

Reusable SELECT from any table and fields


Go to solution Solved by Phi11W,

Recommended Posts

 

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

Link to post
Share on other sites
Posted (edited)

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 by aldo151
Link to post
Share on other sites
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.  

Link to post
Share on other sites
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.

Link to post
Share on other sites

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');

}

Link to post
Share on other sites

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.

Link to post
Share on other sites
  • Solution
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.

 

 

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.