Displaying Dynamic Results PHP mySQL

Question

I have a problem with the sql picking up the column name as the Value instead of the name itself.

So for example the result returned shows

SELECT ll_project.project_id, ll_project.size, ll_lessons.lesson_title FROM ll_project INNER JOIN ll_lessons ON ll_project.project_id = ll_lessons.project_id WHERE ll_project.project_id = BSKYB5555
Unknown column 'BSKYB5555' in 'where clause'

From the following Code

$pid = $_POST['project_id'] ;
$psize = $_POST['projectSize'] ;
$pdepts = $_POST['depts'] ;
$lstage = $_POST['stage'] ;
$ltype = $_POST['type'] ;
$impacted = $_POST['impacted'] ;
//Your columns in the DB 
$columns = array('project_id'=>'ll_project.project_id','projectSize'=>'ll_project.size','depts'=>'ll_project.deptartment','stage'=>'ll_lessons.stage','type'=>'ll_lessons.type','impacted'=>'impacted'); 

$sqlString = null;
echo "Total Number Of Captured Post Variables is:";
echo count($_POST);
echo '<br />';

$number = 0;
$queryStr = ""; 
$preStr = array(); 
foreach ($_POST as $key => $val ) {

if (!empty($_POST[$key])){
       if(!is_array($_POST[$key]))
           $currentStr = $columns[$key]." = ".$val; 
       else
       $currentStr = $columns[$key]." IN (".implode(',',$_POST[$key]).")"; 
       $preStr[] = $currentStr; 
   }
 }
$queryStr = "SELECT ll_project.project_id, ll_project.size, ll_lessons.lesson_title FROM ll_project INNER JOIN ll_lessons ON ll_project.project_id = ll_lessons.project_id  WHERE ".implode(' AND ',$preStr);

echo $queryStr; 
echo '<br />';
if($number ==1) {
}else{
}

$result = mysql_query($queryStr) or die(mysql_error());
 while($row = mysql_fetch_assoc($result)) {
 echo ' <tr>
<td>'.$row['project_name'].' </td>
<td>'.$row['project_id']. ''; 
 }

What am I doing wrong and why is this picking up the value as a column name?

Anigel · Accepted Answer · 2013-05-15 08:49:35Z

4

Add quotes around your query value

SELECT ll_project.project_id, ll_project.size, ll_lessons.lesson_title FROM ll_project INNER JOIN ll_lessons ON ll_project.project_id = ll_lessons.project_id WHERE ll_project.project_id = "BSKYB5555"

As there is no quoting, it does not treat it as a string

EDIT

Unfortunately the code and logic is a little hard to follow as there is no commenting

You can try replacing

$currentStr = $columns[$key]." = ".$val;

with

$currentStr = $columns[$key]." = '".mysql_real_escape_string($val)."'";

This should solve your issue and remove the sql injection vulnerability that you currently have by using user input directly in a query.

edited May 15, 2013 at 8:49

answered May 15, 2013 at 8:15

Anigel

3,4351 gold badge19 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Tazzy Over a year ago

Can you show me an example? Not sure where the quotes should go? AHh ok i seee ill try

Tazzy Over a year ago

The SELECT is Generated from the Dynamic Query which is from $columns = array('project_id'=>'ll_project.project_id', etc.... Is it double quotes i need around the 'project_id'?

Tazzy Over a year ago

I am not writing to query initially, It is being grabbed from the Dynamic Query coming from the $columns = array keys and values. I have no way of putting Double Quotes around the "Number" as it is shown when echod from the $queryStr;

Anigel Over a year ago

Is $val where the BSKYB5555 is coming from?

Tazzy Over a year ago

BSKYB5555 Is coming from $pid = $_POST['project_id'] ; And $columns = array('project_id'=>'ll_project.project_id' Im new to this whole thing so thanks for helping out

|

Michal Hatak · Accepted Answer · 2013-05-15 08:16:41Z

0

if you use string in query, you have to enclapse it

SELECT ll_project.project_id, ll_project.size, ll_lessons.lesson_title FROM ll_project INNER JOIN ll_lessons ON ll_project.project_id = ll_lessons.project_id WHERE ll_project.project_id = 'BSKYB5555'

answered May 15, 2013 at 8:16

Michal Hatak

7871 gold badge9 silver badges21 bronze badges

Collectives™ on Stack Overflow

Displaying Dynamic Results PHP mySQL

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related