CODEWITHSUNDEEP
phpmysql
wordpressm
wordpressm

Reputation: 3267

Can we passe columns dynamically into mysql select statement

I need to write select statement as we can passe columns in to query dynamically using PHP language.

I have a query like this.

SELECT  `Pro_Csharp` +  `Pro_Java` +  `Pro_VBA` +  `Pro_Cplus` +  `Frameworks_Aspdotnet` +  `Fram_MVCdotnet` +  `Fram_WCF` +  `Fram_Hibernate` +  `Fram_Struts` +  `Fram_Spring` +  `Data_transformations_XML` +  `Data_transformations_XSL` AS total_skill
FROM wp_skilllist
ORDER BY total_skill DESC

From the interface users can select which subject they want to select.There are about 130 columns. Therefore, I want to handel it dynamically. Is there way to do this?

If we can't do this with mysql and suggestion to this with php language also very helpful.

Upvotes: 0

Views: 102

Answers (2)

Robert Seddon-Smith
Robert Seddon-Smith

Reputation: 1002

This can be done.

The best way to do this depends on whether you intend to use parameterised statements or not. As you are new, it would be best to keep it simple. The following pseudocode gives you an infinitely expandable query. If you just have a page of checkboxes with names equal to the names of your coloumns, this will work. Otherwise, you will need to do a bit of cleaning up of the array.

$keys=array_keys($_POST);
$sql="SELECT ";
foreach($keys AS $thecolumn)
    {
    $sql.="`$thecolumn`+ ";
    }
$sql=substr($sql,0,strlen($sql)-2); //trim off last +
$sql.=" AS total_skill FROM wp_skilllist ORDER BY total_skill DESC";

Upvotes: 1

RandomSeed
RandomSeed

Reputation: 29809

Build the query string dynamically while parsing the user input:

$sqlString = 'SELECT ';
if ($userInput['csharp']) $fields[] = 'Pro_Csharp';
...
$sqlString .= implode (' + ', $fields);
$sqlString .= ' AS total_skill FROM...'; // rest of your query
sendQueryWithDriverOfYourChoice($sqlQuery);

Upvotes: 3

Related Questions