0

I have a dropdown list and 2 queries. Whenever I select an option in the dropdown list, I want the rows that correlate to the selection in the database to be displayed in a table. If I echo out the query that should get and display the values, and paste it in my SQLPro Studio, it works! However, it doesn't work when running the script! Why is this problem happening?

Query that populates dropdown:

$sql = "
SELECT DISTINCT
       CONCAT(CAST(t1.MR_ID AS INT),' - ', COALESCE(t2.MR_Name, '')) AS MR_ID,
       t1.MR_ID AS sort_column 
FROM Stage_Rebate_Index t1
      LEFT JOIN Stage_Rebate_Master t2
         ON t2.MR_ID = t1.MR_ID
ORDER BY sort_column";

Query that should bring in table values that correlate to the dropdown selection:

$sql_one = "
SELECT 
       CONCAT(CAST(t1.MR_ID AS INT),' - ', COALESCE(t2.MR_Name, '')) AS MR_ID,
       t1.MR_ID AS sort_column, 
       CAST(Supp_ID as INT) AS Supp_ID
FROM Stage_Rebate_Index t1
      LEFT JOIN Stage_Rebate_Master t2
         ON t2.MR_ID = t1.MR_ID
WHERE  
  CONCAT(CAST(t1.MR_ID AS INT),' - ', t2.MR_Name) = LTRIM(RTRIM('$mr_id'))
ORDER BY sort_column";

Ajax that picks up what was selected:

function updatetable(myForm) {

    function show() { document.getElementById('index-table').style.display = 'block'; }


    var selIndex = myForm.selectedIndex;
    console.log();
    var selName = $( "#mr_id option:selected" ).text();

// Ajax sends POST method to Stage_Rebate_Index table and pulls information based on drop down selection
$.ajax ({
    url: "test-table.php",
    method: "POST", //can be post or get, up to you
    data: {
        mr_id : selName
    },
    beforeSend: function () {
        //Might want to delete table and put a loading screen, otherwise ignore this
    },
    success: function(data){
        $("#table_div").html(data); // table_div is the div you're going to put the table into, and 'data' is the table itself.
        console.log(data);
        console.log(selName)
    }
 });

}

Php script that is called from Ajax function:

<?php
$host="xxxxxxxx"; 
$dbName="xxxx"; 
$dbUser="xxxxxxxxxx"; 
$dbPass="xxxxxxxx";

$mr_id = $_POST['mr_id'];

$dbh = new PDO( "sqlsrv:server=".$host."; Database=".$dbName, $dbUser, $dbPass);
$dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
//$sql = "SELECT DISTINCT CAST(MR_ID AS INT) AS MR_ID FROM Stage_Rebate_Index WHERE MR_ID = '$mr_id'";
//$sql_one = "SELECT CAST(Supp_ID AS INT) AS Supp_ID, CAST(MR_ID AS INT) AS MR_ID FROM Stage_Rebate_Index WHERE MR_ID = '$mr_id'";

$sql_one = "
SELECT 
       CONCAT(CAST(t1.MR_ID AS INT),' - ', COALESCE(t2.MR_Name, '')) AS MR_ID,
       t1.MR_ID AS sort_column, 
       CAST(Supp_ID as INT) AS Supp_ID
FROM Stage_Rebate_Index t1
      LEFT JOIN Stage_Rebate_Master t2
         ON t2.MR_ID = t1.MR_ID
WHERE  
  CONCAT(CAST(t1.MR_ID AS INT),' - ', t2.MR_Name) = LTRIM(RTRIM('$mr_id'))
ORDER BY sort_column";

//$users = $dbh->query($sql);
$users_one = $dbh->query($sql_one);
?>

<html>
    <body>

        <!-- Table -->
<p> 
    <div id="table_div">
        <table border="1" id="index_table" class="ui-widget ui-widget-content">
            <thead>
                <tr class="ui-widget-header">
                <td>MR ID</td>
                <td>Supplier ID</td>
                </tr>
            </thead>
            <?php foreach($users_one->fetchAll() as $supp) { ?>
            <tr>
                <td class="mr_id"><?php echo $supp['MR_ID'];?></td>
                <td class="supp_id"><?php echo $supp['Supp_ID'];?></td>
            </tr>
            <?php } ?>
        </table>
    </div>

    </body>
    </html>
Rataiczak24
  • 1,032
  • 18
  • 53
  • your table "#table_div" is in the ajax called, test-table.php script. put it in the source php script (that calls the ajax). Your function may produce the results, but it has nowhere to go. "alert" the data, you get from the success function – JustBaron Dec 19 '16 at 15:27
  • So what would it look like then if my #table_div was moved? – Rataiczak24 Dec 19 '16 at 15:34
  • You need to use parameterized queries instead of building up a string and executing it. Bobby tables likes to visit code like this. http://bobby-tables.com/ – Sean Lange Dec 19 '16 at 15:49
  • Could you post an example? I had 2 original queries that work just fine...but then I had to concatenate 2 values together and it hasnt worked since – Rataiczak24 Dec 19 '16 at 15:53
  • Have a look at this: http://stackoverflow.com/a/14217926/715105 – JustBaron Dec 20 '16 at 08:12

0 Answers0