?
Solved

How do you do a PHP if statement in a Mysql WHERE statement

Posted on 2011-05-09
8
Medium Priority
?
368 Views
Last Modified: 2012-06-22
Hi Experts,
I am using dreramweaver and would like to know how to drop a php if statement into a mysql where clause.

What I want to do is first check if there is a form post. If no form post then run the default where statement else run the where statement for the form post to apply additional filters.

The default where clause is

WHERE theme_keywords.SiloFlag = 'Research' AND theme_keywords.profileid = %s AND projectprofile.ProjectClusterid = %s AND projectprofile.CompanyID = %s ORDER BY RPM DESC"

The filter where clause is

WHERE theme_keywords.SiloFlag = 'Research' AND theme_keywords.profileid = %s AND projectprofile.ProjectClusterid = %s AND projectprofile.CompanyID = %s AND theme_keywords.theme_keywords LIKE %s AND theme_keywords.CompetingPages < %s ORDER BY RPM DESC"

The form POST id is
name="KW_filter" id="KW_filter"

Query attached

$colname_RS_grid = "-1";
if (isset($_GET['profileid'])) {
  $colname_RS_grid = $_GET['profileid'];
}
$colname2_RS_grid = "-1";
if (isset($_GET['clid'])) {
  $colname2_RS_grid = $_GET['clid'];
}
$colname3_RS_grid = "-1";
if (isset($_SESSION['kt_CompanyID'])) {
  $colname3_RS_grid = $_SESSION['kt_CompanyID'];
}
$colname4_RS_grid = "-1";
if (isset($_POST['kw'])) {
  $colname4_RS_grid = $_POST['kw'];
}
$colname5_RS_grid = "-1";
if (isset($_POST['cmp'])) {
  $colname5_RS_grid = $_POST['cmp'];
}
mysql_select_db($database_dws, $dws);
$query_RS_grid = sprintf("SELECT theme_keywords.theme_keywordsid, theme_keywords.profileid, projectprofile.ProjectClusterid, theme_keywords.theme_keywords,
FROM theme_keywords LEFT JOIN projectprofile ON projectprofile.profileid = theme_keywords.profileid WHERE theme_keywords.SiloFlag = 'Research' AND theme_keywords.profileid = %s AND projectprofile.ProjectClusterid = %s AND projectprofile.CompanyID = %s AND theme_keywords.theme_keywords LIKE %s AND theme_keywords.CompetingPages < %s ORDER BY RPM DESC", GetSQLValueString($colname_RS_grid, "int"),GetSQLValueString($colname2_RS_grid, "int"),GetSQLValueString($colname3_RS_grid, "int"),GetSQLValueString("%" . $colname4_RS_grid . "%", "text"),GetSQLValueString($colname5_RS_grid, "int"));
$RS_grid = mysql_query($query_RS_grid, $dws) or die(mysql_error());
$row_RS_grid = mysql_fetch_assoc($RS_grid);
$totalRows_RS_grid = mysql_num_rows($RS_grid);

Open in new window

0
Comment
Question by:matthewdacruz
  • 3
  • 3
  • 2
8 Comments
 
LVL 11

Expert Comment

by:brutaldev
ID: 35723214
You don't need to use sprintf, you can simply provide the variables directly in the string you are using as your query and they will get expanded EG:
 
$query_RS_grid = "SELECT theme_keywords.theme_keywordsid, theme_keywords.profileid, projectprofile.ProjectClusterid, theme_keywords.theme_keywords

FROM theme_keywords LEFT JOIN projectprofile ON projectprofile.profileid = theme_keywords.profileid

WHERE theme_keywords.SiloFlag = 'Research'
AND theme_keywords.profileid = $colname_RS_grid
AND projectprofile.ProjectClusterid = $colname2_RS_grid
AND projectprofile.CompanyID = $colname3_RS_grid
AND theme_keywords.theme_keywords LIKE '%$colname4_RS_grid%'
AND theme_keywords.CompetingPages < $colname5_RS_grid
ORDER BY RPM DESC";

Open in new window

0
 
LVL 11

Expert Comment

by:brutaldev
ID: 35723299
Hmmm, reading the question again and I may have misunderstood. If you want to use a specific WHERE clause based on whether the form has been posted or not you can use this:
 
...
$query_RS_grid = "SELECT theme_keywords.theme_keywordsid, theme_keywords.profileid, projectprofile.ProjectClusterid, theme_keywords.theme_keywords

FROM theme_keywords LEFT JOIN projectprofile ON projectprofile.profileid = theme_keywords.profileid";

if($_SERVER['REQUEST_METHOD'] == "POST") {
  $query_RS_grid=$query_RS_grid . "WHERE theme_keywords.SiloFlag = 'Research'
    AND theme_keywords.profileid = $colname_RS_grid
    AND projectprofile.ProjectClusterid = $colname2_RS_grid
    AND projectprofile.CompanyID = $colname3_RS_grid
    AND theme_keywords.theme_keywords LIKE '%$colname4_RS_grid%'
    AND theme_keywords.CompetingPages < $colname5_RS_grid
    ORDER BY RPM DESC";
}
else {
  $query_RS_grid=$query_RS_grid . "WHERE theme_keywords.SiloFlag = 'Research'
    AND theme_keywords.profileid = $colname_RS_grid
    AND projectprofile.ProjectClusterid = $colname2_RS_grid
    AND projectprofile.CompanyID = $colname3_RS_grid
    ORDER BY RPM DESC";
}
...

Open in new window

0
 

Author Comment

by:matthewdacruz
ID: 35724666
HI Brutaldev
Thanks for the help.

I keep getting an error on line 40

Parse error: syntax error, unexpected ',' in ....php on line 40

I have tried remove the the comma but cant seem to get the query to run


$colname_RS_grid = "-1";
if (isset($_GET['profileid'])) {
  $colname_RS_grid = $_GET['profileid'];
}
$colname2_RS_grid = "-1";
if (isset($_GET['clid'])) {
  $colname2_RS_grid = $_GET['clid'];
}
$colname3_RS_grid = "-1";
if (isset($_SESSION['kt_CompanyID'])) {
  $colname3_RS_grid = $_SESSION['kt_CompanyID'];
}
$colname4_RS_grid = "-1";
if (isset($_POST['kw'])) {
  $colname4_RS_grid = $_POST['kw'];
}
$colname5_RS_grid = "-1";
if (isset($_POST['cmp'])) {
  $colname5_RS_grid = $_POST['cmp'];
}
mysql_select_db($database_dws, $dws);
$query_RS_grid = "SELECT theme_keywords.theme_keywordsid, theme_keywords.profileid, projectprofile.ProjectClusterid, theme_keywords.theme_keywords,theme_keywords.CompetingPages

FROM theme_keywords LEFT JOIN projectprofile ON projectprofile.profileid = theme_keywords.profileid";
if($_SERVER['REQUEST_METHOD'] == "POST") {
  $query_RS_grid=$query_RS_grid . "WHERE theme_keywords.SiloFlag = 'Research'
    AND theme_keywords.profileid = $colname_RS_grid
    AND projectprofile.ProjectClusterid = $colname2_RS_grid
    AND projectprofile.CompanyID = $colname3_RS_grid
    AND theme_keywords.theme_keywords LIKE '%$colname4_RS_grid%'
    AND theme_keywords.CompetingPages < $colname5_RS_grid
    ORDER BY RPM DESC";
}
else {
  $query_RS_grid=$query_RS_grid . "WHERE theme_keywords.SiloFlag = 'Research'
    AND theme_keywords.profileid = $colname_RS_grid
    AND projectprofile.ProjectClusterid = $colname2_RS_grid
    AND projectprofile.CompanyID = $colname3_RS_grid
    ORDER BY RPM DESC";
},GetSQLValueString($colname_RS_grid, "int"),GetSQLValueString($colname2_RS_grid, "int"),GetSQLValueString($colname3_RS_grid, "int"),GetSQLValueString("%" . $colname4_RS_grid . "%", "text"),GetSQLValueString($colname5_RS_grid, "int");
$RS_grid = mysql_query($query_RS_grid, $dws) or die(mysql_error());
$row_RS_grid = mysql_fetch_assoc($RS_grid);
$totalRows_RS_grid = mysql_num_rows($RS_grid);

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 82

Expert Comment

by:hielo
ID: 35724757
try:
$colname_RS_grid = "-1";
if (isset($_GET['profileid'])) {
  $colname_RS_grid = $_GET['profileid'];
}
$colname2_RS_grid = "-1";
if (isset($_GET['clid'])) {
  $colname2_RS_grid = $_GET['clid'];
}
$colname3_RS_grid = "-1";
if (isset($_SESSION['kt_CompanyID'])) {
  $colname3_RS_grid = $_SESSION['kt_CompanyID'];
}
$colname4_RS_grid = "-1";
if (isset($_POST['kw'])) {
  $colname4_RS_grid = $_POST['kw'];
}
$colname5_RS_grid = "-1";
if (isset($_POST['cmp'])) {
  $colname5_RS_grid = $_POST['cmp'];
}
mysql_select_db($database_dws, $dws);
$query_RS_grid = "SELECT theme_keywords.theme_keywordsid, theme_keywords.profileid, projectprofile.ProjectClusterid, theme_keywords.theme_keywords,theme_keywords.CompetingPages
FROM theme_keywords LEFT JOIN projectprofile ON projectprofile.profileid = theme_keywords.profileid ";

if($_SERVER['REQUEST_METHOD'] == "POST") {
  $query_RS_grid=$query_RS_grid . " WHERE theme_keywords.SiloFlag = 'Research'
    AND theme_keywords.profileid = $colname_RS_grid
    AND projectprofile.ProjectClusterid = $colname2_RS_grid
    AND projectprofile.CompanyID = $colname3_RS_grid
    AND theme_keywords.theme_keywords LIKE '%$colname4_RS_grid%'
    AND theme_keywords.CompetingPages < $colname5_RS_grid
    ORDER BY RPM DESC";
}
else {
  $query_RS_grid=$query_RS_grid . " WHERE theme_keywords.SiloFlag = 'Research'
    AND theme_keywords.profileid = $colname_RS_grid
    AND projectprofile.ProjectClusterid = $colname2_RS_grid
    AND projectprofile.CompanyID = $colname3_RS_grid
    ORDER BY RPM DESC";
}
/* ,GetSQLValueString($colname_RS_grid, "int"),GetSQLValueString($colname2_RS_grid, "int"),GetSQLValueString($colname3_RS_grid, "int"),GetSQLValueString("%" . $colname4_RS_grid . "%", "text"),GetSQLValueString($colname5_RS_grid, "int"); */
$RS_grid = mysql_query($query_RS_grid, $dws) or die(mysql_error());
$row_RS_grid = mysql_fetch_assoc($RS_grid);
$totalRows_RS_grid = mysql_num_rows($RS_grid);

Open in new window

0
 

Author Comment

by:matthewdacruz
ID: 35724779
Hi Hielo,

by commenting out the line it gives the following sql error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'theme_keywords.SiloFlag = 'Research' AND theme_keywords.profileid = 92 ' at line 1
0
 
LVL 82

Accepted Solution

by:
hielo earned 1000 total points
ID: 35725260
Did you copy and paste? Notice that I added a space to the LEFT of the WHERE keyword!
0
 

Author Comment

by:matthewdacruz
ID: 35771268
Yes I did
0
 
LVL 11

Assisted Solution

by:brutaldev
brutaldev earned 1000 total points
ID: 35772038
Firstly, thanks hielo for correcting my typo's, it was late :/

I've looked at the code and it looks correct, you could echo the value of $query_RS_grid so you can see what the resulting SQL actually looks like. You can also take the echo'd SQL and try run the query yourself in your database query tool of choice to try and determine where the fault is.
 
...
echo $query_RS_grid;
$RS_grid = mysql_query($query_RS_grid, $dws) or die(mysql_error());
...

Open in new window

Based on the error message it's also possible that the profileid column is not a number data type, in which case you should wrap the values in quotes like a string so that the query ends up being  theme_keywords.profileid = '92'
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
The viewer will learn how to dynamically set the form action using jQuery.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month13 days, 9 hours left to enroll

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question