php mysql where clause not reading variable

Question

I am starting to get furious here

mysql_query don't recognize my variable $d1 even I tried to rename it

here is the code..

html:

<form action ="manageVessel.php" method ="POST">
  <select onchange ="this.form.submit();" class ="form-control" name ="ViewPositionCertificates">
      <option>Choose a Position </option>                                                       
      <?php
      $ViewPCertificates = mysql_query("SELECT * FROM table_cmsjob") or die("error" . mysql_error());
      while ($rwViewPCertificates = mysql_fetch_array($ViewPCertificates)) {
          ?>
          <option value =" <?php echo $rwViewPCertificates['jobName']; ?> "> <?php echo $rwViewPCertificates['jobName']; ?></option>  
      <?php } ?>

       </select>   
</form>

php:

   <?php if (isset($_POST['ViewPositionCertificates'])) { ?>
      <table class = "table table-bordered">
          <tr class ="bg-primary">
              <td> List of Certificates </td>
          </tr>
          <?php
          $d1 = $_POST['ViewPositionCertificates'];
          echo $_POST['ViewPositionCertificates'];
          $ViewCertificatesFP = mysql_query("SELECT * FROM table_cmsjobassigning WHERE jobName =  '$d1' ") or die("error" . mysql_error());

          while ($rwViewCertificatesFP = mysql_fetch_array($ViewCertificatesFP)) {

              echo "<tr>";
              echo "<td>" . $rwViewCertificatesFP['Certificate'] . "</td>";
              echo "</tr>";
          }
          ?>

      </table>




  <?php } ?>

MYSQL WHERE clause is working fine when I used a string for example

    mysql_query("SELECT * FROM table_cmsjobassigning WHERE jobName =  'MASTER' ") or die("error" . mysql_error());

but when I used a variable to assign $_POST['ViewPositionCertificates'] to a variable MYSQL WHERE clause doesn't read it any help?

`$ViewCertificatesFP = mysql_query("SELECT * FROM table_cmsjobassigning WHERE jobName = '{$d1}' ") or die("error" . mysql_error());` use this query and a pair of avice, please prefer mysqli over mysql — Amit Sarwara, Dec 15 '15 at 07:04
As nobody said it I'll say it. Please **don't** use `mysql_*` functions as they are deprecated and in the newly released PHP 7.0 deleted use [mysqli](http://php.net/manual/en/book.mysqli.php) or [PDO](http://php.net/manual/en/book.pdo.php) instead. Also when handling user input use prepared statements otherwise your query is open for [SQL injections](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — BRoebie, Dec 15 '15 at 09:29

score 3 · Accepted Answer · answered Dec 15 '15 at 07:12

3

<option value ="<?php echo $rwViewPCertificates['jobName']; ?>"> <?php echo $rwViewPCertificates['jobName']; ?></option>   // remove xtra spaces from here......

Remove spaces from value attribute

answered Dec 15 '15 at 07:12

Shailesh Katarmal

2,757
1
12
15

thanks did not notice whitespace from value attribute – Salvador Mark Anjelo Dec 15 '15 at 07:20

score 1 · Answer 2 · answered Dec 15 '15 at 07:10

1

Try like this..

$ViewCertificatesFP = mysql_query("SELECT * FROM table_cmsjobassigning WHERE jobName =  '".mysql_real_escape_string(trim($d1))."' ") or die("error" . mysql_error());

or

$ViewCertificatesFP = mysql_query("SELECT * FROM table_cmsjobassigning WHERE jobName =  '".addslashes(trim($d1))."' ") or die("error" . mysql_error());

answered Dec 15 '15 at 07:10

Amit Rajput

2,061
1
9
27

I think there is some special character in value of "$d1" variable. Try using with mysql_real_escape_string() or addslashes() – Amit Rajput Dec 15 '15 at 07:12

score 0 · Answer 3 · edited Dec 15 '15 at 07:06

0

<?php if (isset($_POST['ViewPositionCertificates'])) { ?>
    <table class = "table table-bordered">
        <tr class ="bg-primary">
            <td> List of Certificates </td>
        </tr>
        <?php
        $d1 = $_POST['ViewPositionCertificates'];
        echo $_POST['ViewPositionCertificates'];
        $ViewCertificatesFP = mysql_query("SELECT * FROM table_cmsjobassigning WHERE jobName =  '".$d1."' ") or die("error" . mysql_error());

        while ($rwViewCertificatesFP = mysql_fetch_array($ViewCertificatesFP)) {

            echo "<tr>";
            echo "<td>" . $rwViewCertificatesFP['Certificate'] . "</td>";
            echo "</tr>";
        }
        ?>
    </table>
<?php } ?>

Change in select query syntax use single quote.

edited Dec 15 '15 at 07:06

Amit Rajput

2,061
1
9
27

answered Dec 15 '15 at 07:01

Vishal Kamal

1,104
2
10
35

thanks for the response :) but I already tried that so far wont work? I also tried to restart my apache and mysql but it still don't work – Salvador Mark Anjelo Dec 15 '15 at 07:04
Don't you know how to vote? Or you just need an answer. – Felix Kamote Dec 23 '15 at 01:44

score 0 · Answer 4 · answered Dec 15 '15 at 07:03

0

Try this one:

mysql_query("SELECT * FROM table_cmsjobassigning WHERE jobName =  ".$_POST['ViewPositionCertificates'])

answered Dec 15 '15 at 07:03

Felix Kamote

342
2
10

score 0 · Answer 5 · answered Dec 15 '15 at 07:08

0

You can not write variables inside single quotes, if you write it then PHP will consider it as string. so your query will be
$ViewCertificatesFP = mysql_query("SELECT * FROM table_cmsjobassigning WHERE jobName = $d1 ") or die("error" . mysql_error());

for more help , please read variable interpolation in PHP

answered Dec 15 '15 at 07:08

Sanjay

1,958
2
17
25

Thanks for the response I already tried that but I keep getting an error Unknown column 'MASTER' in 'where clause' master is one of the rows in my database for column jobName – Salvador Mark Anjelo Dec 15 '15 at 07:09
ok then please confirm 'MASTER' is column present in your MySQL table – Sanjay Dec 15 '15 at 07:12
please replace $d1 to ' " .$d1. " ' , I think it will solve your problem – Sanjay Dec 15 '15 at 07:18

score -1 · Answer 6 · answered Dec 15 '15 at 07:02

-1

Try to change your query method it may help...

$ViewCertificatesFP = mysql_query("SELECT * FROM table_cmsjobassigning WHERE jobName =  '".$d1."' ") or die("error" . mysql_error());

answered Dec 15 '15 at 07:02

rahul

776
5
30

php mysql where clause not reading variable

6 Answers6