jQuery calls a PHP file to get data from mysql database?

Question

Ok so I have found a number of tutorials online and have followed each of them step by step. My problem is that I know javascript/jQuery much better than I know PHP and I cannot figure out how to even debug what is going wrong in that section. Basically I have a bunch of buttons and a from and when a button is pressed it determines what the default values are in the form.

jQuery Side

$(document).ready(function(){
// CSPC and ADDUPDATE TOGGLE ARE DEFINED GLOBALLY
    $('ul#parts').on('click', 'button', function(){
        ADDUPDATETOGGLE = "ADD";
        CSPC = $(this).attr("data-cspc");
        var   form = $('div.sidebar form'),
              sr = 0;
        form.find("#cspc").val(CSPC);
        $.ajax({
            type: "GET",
            url: "getRate.php",
            data: "pid=A5843",
            dataType: "json",
            success: function(data){
                sr = data;
            }
        });
        form.find("#strokeRate").val(sr);
        showForm();
    });
});

PHP side

<?php

$host = "localhost";
$user = "username";
$pass = "password";
$databaseName = "movedb";
$tableName = "part parameters";

$con = mysql_connect($host, $user, $pass);
$dbs = mysql_select_db($databaseName, $con);
//get the parameter from URL
$pid=$_GET["pid"];
if (empty($pid)){
    echo "1"; //default rate
}
else{
    $db=mysql_pconnect("localhost");//connect to local database
    mysql_select_db("movedb", $db);//select the database you want to use
    if (!$db){
        echo ("error connecting to database");              
    }
    else{
        //connection successful
        $sql = "SELECT 'Processing Rate (ppm)' FROM 'part parameters' WHERE 'Part Number' LIKE '" . $pid . "'";//sql string command
          $result=mysql_query($sql);//execute SQL string command
          //result contains rows
          $rows = mysql_fetch_row($result)
          echo json_encode($rows["Processing Rate (ppm)"]); 
    }

}

?>

Any ideas why sr is not getting set?

Am I way off base?

I will also shamelessly note that I do not know what $user and $pass should be set to. I cannot find that explained anywhere

Thanks in advance!

EDIT: I followed most of the directions below and now when I run

http://localhost/getRate.php?pid=A5843

it says "No database selected." Also, I dont have access to our original MS Access file now (one of my team members has it) but once I get it I will make all the headers into one word headers. This is our first job with web programming/database management so we are constantly learning.

MySQL is really running? Did you check for request errors on browser console? — Wesley Schleumer de Góes
– Wesley Schleumer de Góes, Commented Apr 18, 2013 at 16:07
For one thing, $rows = mysql_fetch_row($result) is missing a semi-colon which would result in a syntax error and render this page useless — Hanky Panky
– Hanky Panky, Commented Apr 18, 2013 at 16:08
$user and $password must be your credntials on the database server. They are most probably incorrect, nothing will ever work until you fix this. — RandomSeed
– RandomSeed, Commented Apr 18, 2013 at 16:08
For one, why re you connecting to 2 databases but the first one is unused? Secondly I think the spaces (or parens) in your column name are causing the issue. What does show up in the response? — Jason Kaczmarsky
– Jason Kaczmarsky, Commented Apr 18, 2013 at 16:08

Joe · Accepted Answer · 2013-04-18 16:24:58Z

$user and $pass should be set to your MySql User's username and password.

I'd use something like this:

JS

success: function(data){
             if(data.status === 1){
                 sr = data.rows;
             }else{
                 // db query failed, use data.message to get error message
             }
        }

PHP:

<?php

    $host = "localhost";
    $user = "username";
    $pass = "password";
    $databaseName = "movedb";
    $tableName = "part parameters";

    $con = mysql_pconnect($host, $user, $pass);
    $dbs = mysql_select_db($databaseName, $con);
    //get the parameter from URL
    $pid = $_GET["pid"];
    if(empty($pid)){
        echo json_encode(array('status' => 0, 'message' => 'PID invalid.'));
    } else{
        if (!$dbs){
            echo json_encode(array('status' => 0, 'message' => 'Couldn\'t connect to the db'));       
        }
        else{
            //connection successful
            $sql = "SELECT `Processing Rate (ppm)` FROM `part parameters` WHERE `Part Number` LIKE `" . mysqli_real_escape_string($pid) . "`"; //sql string command
            $result = mysql_query($sql) or die(mysql_error());//execute SQL string command
            if(mysql_num_rows($result) > 0){
                $rows = mysql_fetch_row($result);
                echo json_encode(array('status' => 1, 'rows' => $rows["Processing Rate (ppm)"]);
            }else{
                echo json_encode(array('status' => 0, 'message' => 'Couldn\'t find processing rate for the give PID.'));   
            }
        }

    }

?>

As another user said, you should try renaming your database fields without spaces so part parameters => part_parameters, Part Number => part_number.

If you're still having trouble then (as long as it's not a production server) put this at the top of your php file:

error_reporting(E_ALL);
ini_set('display_errors', '1');

This will output any errors and should help you work out what's going wrong.

Marc B · Accepted Answer · 2013-04-18 16:16:59Z

4

Your DB query code is incorrect:

    $sql = "SELECT 'Processing Rate (ppm)' FROM 'part parameters' WHERE 'Part Number' LIKE '" . $pid . "'";//sql string command

using ' to quote things in the query turns them into STRINGS, not field/table names. So your query is syntactically and logically wrong. Your code is simply assuming success, and never catches the errors that mysql will be spitting out.

The query should be:

SELECT `Processing Rate (ppm)`
FROM `part parameters`
WHERE `Part Number` = '$pid'

Note the use of backticks (`) on the field/table names, and the use of single quotes (') on the $pid value.

Then you execute the query with:

$result = mysql_query($sql) or die(mysql_error());

If this fails, you will get the error message that mysql returns.

And in the grand scheme of things, your code is vulnerable to SQL injection attacks. Better read up and learn how to prevent that before you go any farther with this code.

answered Apr 18, 2013 at 16:16

Marc B

362k44 gold badges433 silver badges508 bronze badges

1 Comment

Mike Brant Over a year ago

It should also be noted that it is VERY UNUSUAL to use spaces and parenthesis in your field and table names. You should strongly consider changing these to eliminate spaces and parenthesis.

OctoD · Accepted Answer · 2013-04-18 16:13:36Z

2

sr it's outside the success callback function. Start putting it into the success function and see what happens

$.ajax({
        type: "GET",
        url: "getRate.php",
        data: "pid=A5843",
        dataType: "json",
        success: function(data){
            sr = data;
            form.find("#strokeRate").val(sr);
        }
    });

remember that, if data is expected to be a json, it will become a js object, so you will not be able to use data directly

answered Apr 18, 2013 at 16:13

OctoD

3615 silver badges14 bronze badges

Comments

Haris · Accepted Answer · 2022-07-27 10:52:28Z

Here is a compilation of the above with up-to-date code.

jQuery

$(document).ready(function(){
...
    $.ajax({
            type: "GET",
            url: "getRate.php",  //see note "url"
            data: {
                    pid : "A5843" 
                 //, ... : "..." 
                  }, 
            dataType: "json",
            success: function(data){
                sr = data;
            }
        });
...
    });

url > test your request-URL including any parameters (eg. http://localhost/XYZ/getRate.php?pid=251) and then enter it here, after removing anything after the '?' symbol (including '?')
data > https://api.jquery.com/Jquery.ajax/

PHP

<?php

    $host = "localhost";
    $user = "username";
    $pass = "password";
    $databaseName = "movedb";
    
    $pid=$_GET['pid']; //get the parameter from URL

    $con = mysqli_connect($host, $user, $pass, $databaseName);
    //$dbs = mysqli_select_db($con, $databaseName);


    if (empty($pid)){
        echo json_encode(array('status' => 0, 'message' => 'pid invalid.'));
    }
    else{
        if (mysqli_connect_errno()) {
          echo "Failed to connect to MySQL: " . mysqli_connect_error();
          exit;
        }
        //if (!$dbs)
            //echo json_encode(array('status' => 0, 'message' => 'Couldn\'t connect to the db'));          
        
        else{//connection successful
            $sql = SELECT `Processing Rate (ppm)` FROM `part parameters` WHERE `Part Number` LIKE `" . mysqli_real_escape_string($pid) . "`"; //https://www.geeksforgeeks.org/how-to-prevent-sql-injection-in-php/      
            $result = mysqli_query($con, $sql) or die(mysql_error());//execute query
            if($result){
                $rows = mysqli_fetch_row($result);
                echo json_encode(array('status' => 1, 'rows' => $rows["Processing Rate (ppm)"]);
            }
            else
                echo json_encode(array('status' => 0, 'message' => 'Couldn\'t find results'));   
            }
            mysqli_free_result($result);
            mysqli_close($con);
        }


?>

Please note that I am no PHP-expert. Any comments welcome.

Collectives™ on Stack Overflow

jQuery calls a PHP file to get data from mysql database?

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related