我们可以将列动态地传递到mysql select语句中

问题描述：

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.

我需要编写select语句，因为我们可以使用PHP语言动态查询列。 p> \ n

我有这样的查询。 p>

  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 
  code>  pre> 
 
 从界面中，用户可以选择要选择的主题。大约有130列。 因此，我想动态地处理它。 有没有办法做到这一点？ p> 
 
 如果我们不能用mysql做这个，并建议使用php语言也非常有帮助。 p> 
  div>

答

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

答

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";

我们可以将列动态地传递到mysql select语句中

相关推荐